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

directly to the other person. 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: 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: 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: Postgis - geography function

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

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

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

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

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

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: 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: 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: query causes connection termination

install debuginfo first, so that the backtrace makes sense. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Error installing PostgreSQL 8.3 (password short, not complex)

but I think that could be the issue. I would really appreciate your support with this. Maybe you can tell me how to avoid the require of a complex password during the installation of postgre. Best regards, Tomas Tomás Guerra | Loader Coordinator *:+507 000- | Ë: +507 66768250 | *: tom

Re: a question about oddities in the data directory

de FROM pg_class WHERE relname = 'earlyprinttuples'; regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: large numbers of inserts out of memory strategy

> migration utility to generate multiple files each with a limited > number of INSERTs to get around this issue but maybe there's > another/better way? > The question is what exactly runs out of memory, and how did you modify the configuration (particularly related to memory)

Re: large numbers of inserts out of memory strategy

On 11/28/2017 06:54 PM, Ted Toth wrote: > On Tue, Nov 28, 2017 at 11:22 AM, Tomas Vondra > wrote: >> Hi, >> >> On 11/28/2017 06:17 PM, Ted Toth wrote: >>> I'm writing a migration utility to move data from non-rdbms data >>> source to a p

Re: large numbers of inserts out of memory strategy

On 11/28/2017 07:26 PM, Ted Toth wrote: > On Tue, Nov 28, 2017 at 12:01 PM, Tomas Vondra > wrote: >> >> ... >> >> That is, most of the memory is allocated for SPI (2.4GB) and PL/pgSQL >> procedure (500MB). How do you do the load? What libraries/drivers? >

Re: Centos 6.9 and centos 7

e policy? That typically affects short CPU-bound queries. Other than that, I recommend performing basic system benchmarks (CPU, memory, ...) and only if those machines perform equally should you look for issues in PostgreSQL. Chances are the root cause is in hw or OS, in which case yo

Re: Centos 6.9 and centos 7

On 12/04/2017 04:57 PM, Nicola Contu wrote: > No I did not run a vacuum analyze. Do you want me to try with that first? > > @Tomas: > Talking abut power management, I changed the profile for tuned-adm > to latency-performance instead of balanced (that is the default) > &g

Re: Size of pg_multixact/members increases 11355

it's unclear why the directory got so large. You could try setting autovacuum_multixact_freeze_max_age=0 and vacuuming the database, but it's hard to say if that will help. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: postgresql 9.5 has ocuuered OOM

a single process, it might be a poor plan choice (e.g. hash aggregate may easily cause that). If all processes combined, then perhaps it's due to work_mem being too high. > what should I do to deal with this problem? > Hard to say, until you provide enough information. regards -- Tom

Re: Hardware advice

ither find out what your actual needs are (by looking at the current system and extrapolating it in some way) and sizing the hardware accordingly. Or you can squeeze as much money from the management as possible, and buying the shiniest stuff out possible. > Oh yeah, apparently we're ma

Re: Using AWS ephemeral SSD storage for production database workload?

naging such clusters, like for example [2]. Not only it's rather bad idea to implement this on your own (bugs, unnecessary effort) but the tools also show how to do stuff. [1] https://www.packtpub.com/big-data-and-business-intelligence/postgresql-replication-second-edition [2] https://repmgr

Re: EXPLAIN BUFFERS: dirtied

ading/updating/writing the page again)? > Checkpoint only deals with 8kB chunks of data. Hint bits are not set on a page, but on individual items (rows), so it's not up to the checkpoint process to tweak that - that's up to queries accessing the data. regards -- Tomas Vondra

Re: ERROR: invalid memory alloc request size 1073741824

Both of those threads are about a hashjoin issue, which should have been fixed in 9.5. Moreover, you haven't shared the query and it's query plan, so it's unclear if it's doing hash joins at all. That would be useful for investigating this issue, BTW. > > p

Re: ERROR: invalid memory alloc request size 1073741824

It's quite unlikely that would fail with exactly 1GB request size. It seems much more like a buffer that we keep to be power of 2. The question is which one. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Issue with WAL logs temporary not replaying

applying WAL segments? > There's only one log for PostgreSQL, so it would have to be in that file depending on the log level. > * What measures could be done to prevent this in future, or at least > make the situation easier to recover from (e.g. without data loss)? > The jury is still out on the root cause, so it's too soon to say. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: ERROR: invalid memory alloc request size 1073741824

dically without issue. > > >> I have my money on a corrupted TOAST entry. Is this happening on > >> trustworthy hardware or beige box with no ECC or RAID? > > It's good quality commercial hardware in our colo - no exactly sure > what. > > &

Re: Issue with WAL logs temporary not replaying

On 02/01/2018 08:17 AM, Eugene Pirogov wrote: > Tomas, > > Indeed, I haven't put enough detail in the original post. That is a fair > point, thank you. Let me follow up on each of your comments. > > unfortunately your message is missing some pretty important >

Re: Critical errors during logical decoding

an you share an example of data causing this issue (or even better a script reproducing it)? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Critical errors during logical decoding

On 02/07/2018 10:22 PM, Colin Morelli wrote: > Hey Tomas, > > We're using v1.1.0 of pglogical. I can't honestly say what could > reproduce this issue as I'm not entirely sure and haven't tested it yet. > Well, consider updating - there's 1.1.2 in that

Re: New Copy Formats - avro/orc/parquet

would be written "... with (format 'orc')" and your best bet would be > to create an extension.  I don't think that having such code in core (or > contrib) is desirable. > I don't think make this extensible by an extension (i.e. the formats supported by COPY are

Re: New Copy Formats - avro/orc/parquet

> BINARY to AVRO for example. > > Here, I am talking about native format exports/imports for > performance considerations. > That is true, but the question is how significant the overhead is. If it's 50% then reducing it would make perfect sense. If it's 1% then no one if going to be bothered by it. Without these numbers it's hard to make any judgments. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: shared_buffers 8GB maximum

onal shared space provides any real benefit. > Gradual increases are a good approach in general. And yes, having buffers_checkpoint > buffers_clean > buffers_backend is a good idea too. Together with the cache hit ratio it's probably a more sensible metric than looking at usagecount directly. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: shared_buffers 8GB maximum

and for serving queries) > It depends on how large is the active part of the data set is. If it fits into 200GB but not to smaller shared buffers (say, 100GB), then using 200GB may be a win. If the active set is much larger than RAM, smaller shared_buffer values work better in my experience. r

Re: query performance

especially be good to know if you've > added an index on product (establishment_id), for example. > There's actually a wiki page with overview of what should be included in "slow query" questions: https://wiki.postgresql.org/wiki/Slow_Query_Questions regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

hrough the application, or generate synthetic data in some other way. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

will be referenced by WHERE conditions, and data ingestion has lower priority than queries). But that doesn't seem to be the case here - you know the WHERE conditions, and people are likely sending a lot of inserts (and expecting low latency responses). regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Backup

nd compress that way. Something like pg_dump -Fc | gzip -c > compressed.dump.gz However, be aware that pg_dump is more an export tool than a backup suitable for large databases / quick recovery. It won't allow doing PITR and similar stuff. regards -- Tomas Vondra

Re: Commit Latency

vided. I suggest you start by answering the questions you've been asked by me & Alvaro, explain why you concluded it's related to WAL creation, etc. regards -- Tomas Vondra

Re: Commit Latency

he fsync may be slow even when nothing else competes for I/O. regards Tomas On 2/5/25 11:18, Ramakrishna m wrote: > HI  Team, > I have a system handling *300 TPS*, with resource usage *below 10%*. > However, I’m noticing *commit latency of around 200ms* for *1% of > transactions*, occa

Re: Data Out of Sync with Physical Streaming Replication

nk it'd be interesting to compare the tuples returned on primary vs. standby, including system columns etc. Ideally using pageinspect. That might tell you what exactly is the difference, and perhaps also say which XID to look at. regards -- Tomas Vondra

Re: Meson and Numa: C header not found

Fedora 41 too, and I don't have this issue. If I do $ meson setup build I get libnuma : YES 2.0.19 and then ninja -C build completes just fine. What exactly are the commands you're executing? FWIW I guess -hackers would be a better place for this question. regards -- Tomas Vondra

Re: Meson and Numa: C header not found

gt; ninja > ninja install > Are you sure this is the sequence that fails? Because this has -Dlibnuma=disabled so that shouldn't build the NUMA stuff at all. regards -- Tomas Vondra

Re: Logical decoding

; is active for PID 2525720 No, not really. The whole point of this error is to prevent exactly this, pretty much - to only allow a single process doing logical decoding on a slot. What are you trying to solve / achieve? Why do you need (or think you need) logical decoding on an active slot? regards -- Tomas Vondra

Re: Extension disappearing act

; The only idea I can come up with is that pgcrypto was in one of those dropped schemas (but I know you're saying it was in public). Are you able to reproduce this? If yes, can you share a reproducer? regards -- Tomas Vondra

Re: Extension disappearing act

reproducers generally means simplifying the example as much as possible anyway. And I wouldn't be surprised if in the process of doing that you find the answer yourself. regards -- Tomas Vondra

Re: PostgreSQL 17.5 - could not map dynamic shared memory segment

vm.overcommit_memory And what's CommitLimit/Committed_AS in /proc/meminfo? IIRC the shmem is counted against the limit, and if the system does not have significant swap, it's not uncommon to hit that (esp. with overcommit_memory=2). regards -- Tomas Vondra

connection timeout with psycopg2

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

<    1   2