Wanted: postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb
Hi all I'm trying to debug a PostgreSQL install with a very hard to reproduce bug. The user did not install debug info, and apt.postgresql.org has purged the packages. 2ndQuadrant doesn't yet have a mirror of all historical packages up and running (though we're working on it). So I need postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb . If anyone has it in a mirror repo, their /var/cache/apt/archives/, or even has it installed and can supply the files it contains, I'd be extremely grateful. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: Wanted: postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb
On 3 May 2018 at 16:46, Karsten Hilbert wrote: > On Thu, May 03, 2018 at 04:23:14PM +0800, Craig Ringer wrote: > >> I'm trying to debug a PostgreSQL install with a very hard to reproduce >> bug. The user did not install debug info, and apt.postgresql.org has >> purged the packages. 2ndQuadrant doesn't yet have a mirror of all >> historical packages up and running (though we're working on it). >> >> So I need postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb . >> >> If anyone has it in a mirror repo, their /var/cache/apt/archives/, or >> even has it installed and can supply the files it contains, I'd be >> extremely grateful. > > This is the closest I found with a quick searc > > > http://snapshot.debian.org/archive/debian/20170831T163230Z/pool/main/p/postgresql-9.6/postgresql-9.6-dbg_9.6.5-1_amd64.deb > > Not sure this is close enough though. Nope, but solved. http://atalia.postgresql.org/morgue/ is what I was looking for. It's linked to via this post https://www.postgresql.org/message-id/20160731194944.amiwidhsoqh4osac%40msg.df7cb.de which is linked from apt.postgresql.org, so I'm blind. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: Code of Conduct plan
se. Every player in that story was an idiot, and while none deserved the abuse and harrassment that came their way, it's a shame it wan't handled by a complaint to a conference CoC group instead. I'd like the CoC to emphasise that while we don't want to restrain people from "calling out" egregious behaviour, going via the CoC team is often more likely to lead to constructive communication and positive change. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: postgres_fdw insert extremely slow
On Thu, Nov 26, 2020 at 12:37 AM Mats Julian Olsen wrote: > > We have just set up postgres_fdw between two postgres databases, x and y, > with the plan to periodically insert data from x into y. > > We've successfully set up the connection with a few options: > `use_remote_estimate 'true'` and `fetch_size '5'` (the latter we've > played around with). We've run ANALYZE on the foreign server. > > SELECTs against the foreign table returns in milliseconds, however an > INSERT of 100 rows takes 10 seconds. we have roughly 200 000 rows for the > initial sync, which translates into ~6 hours. > > Is this the expected performance of postgre_fdw? Is there anything we've > overlooked when setting this up? Very curious to hear experiences from the > community when doing read/write and not just read from foreign sources. > Are your inserts run in individual transactions or grouped into one transaction? If the latter, commit time will be a factor. What's the round-trip time (ping time) to the foreign server? Since postgres_fdw runs each individual insert as a separate statement, you're going to face insert times of (n * RTT) for inserts. Assuming negligible time for insert execution on the foreign server, your runtime is 21600 seconds for 20 rows, i.e. 9.25 rows/second or 0.108 seconds/row. That would be consistent with a 90-100ms ping time to the foreign server. You'll be pleased to know that there is currently work ongoing in pgsql-hackers to add the capability to batch INSERTs in postgres_fdw to improve performance on higher latency links. See https://www.postgresql.org/message-id/flat/20200628151002.7x5laxwpgvkyiu3q%40development . That could well reduce your RTTs immensely. Try the patch out if you can and report back please. If you can get the client application to manage the foreign insert directly, then handle commit consistency using two-phase commit, you should be able to do the insert in half an hour or less instead (assuming ~10ms execution time per insert and 90ms RTT). If you use `COPY`, or if you can use JDBC to benefit from PgJDBC's support for the JDBC addBatch() and executeBatch() APIs, you should be able to get it down way lower than that. Assuming your RTT latency is 90ms and you spend 10ms executing each insert, your insert time might well go down to 0.010 * 20 + 90*2 = 2180 seconds or about 36 minutes. If you can insert a row in 3ms with COPY, 13 minutes. There's work ongoing on making libpq (which underlies postgres_fdw) capable of running multiple statements at the same time, i.e. "pipelining". That won't immediately benefit postgres_fdw because using it in postgres_fdw would require changes to the whole postgres executor as well. But if adopted, it'd allow postgres_fdw to achieve that sort of performance transparently.
Re: PGDLLIMPORT: patch or not to patch
On Wed, 30 Jun 2021 at 04:49, Tom Lane wrote:
> George Tarasov writes:
> > So, my questions are there any rules / descriptions / agreements inside
> > the PostgreSQL Project that define which global variables inside a core
> > code should by specified by a PGDLLIMPORT and which should not?? Or
> > there is freedom; you need this variable in the extension (under
> > Windows), make patch for it yourself! Or there is plan in the community
> > that all global non-static variables should be PGDLLIMPORT-ed by default
> > in the future?? What the right way to propose the PGDLLIMPORT patch to
> > the master and back-ported PostgreSQL code in order to avoid dup patches
> > in the extensions?
>
> Our policy so far has been to add PGDLLIMPORT to variables for which
> someone makes a case that an extension would have a reasonable use
> for it. The bar's not terribly high, but it does exist. The idea of
> just doing a blanket s/extern/extern PGDLLIMPORT/g has been discussed
> and rejected, because we don't want to commit to supporting absolutely
> every global variable as something that's okay for extensions to touch.
>
I agree that it doesn't make sense to mark all of them as a blanket rule.
I'd like to explicitly tag *non*-exported externs as
__attribute__(("hidden")) on GCC-alike ELF systems to ensure that extension
authors don't rely on them then later find they cannot be used on Windows.
Obviously wrapped in some PG_NO_EXPORT or PG_DLL_HIDDEN macro.
I'm updating a patch at the moment that makes all GUC storage and most
variables computed from GUCs during hook execution PGDLLIMPORT. It might
make sense to follow that up with a patch to make non-export vars hidden.
But I vaguely recall raising this before and some folks not being a fan of
the extra noise on each line?
Re: unsubscribe
On 21 November 2017 at 15:17, Rafal Pietrak wrote: > Hi all, > > Have anyone noticed, that last couple of days on the list, can become a > nice example of HOWTO turn the best list on the planet into a nightmare? > > Pls forgive rudeness, but IMHO, whoever made the "upgrade" should > manually take all the "unsubscribe" messages from archive and do that > "unsubscription" by hand instead of giving not always helpfull > guidance. The list used to have a filter that blocked messages with "unsubscribe" in the subject or forced them into moderation the moderation queue. I thought that'd be preserved with the PgLister migration. But really, a nightmare? Yeah, it's a pain, but I think that's laying it on a bit strong. Personally I appreciate the hard and usually thankless work the infrastructure and admin team do. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: On Judging the Value of Tests
On 22 November 2017 at 08:43, Jankirk.Vincent., Jamison < k.jami...@jp.fujitsu.com> wrote: > Dear PG Experts, > > > > I am entirely very new to PG development. > > Currently, I’m studying the test suites, and I feel the more experienced > PG devs here could provide some insights on testing because I could not > find concrete answers in the Postgres documentations. > > > > 1. How do you judge when a test suite is acceptable to be added to > Postgres OSS source code? (How do you judge the value of a test suite?) > Make your argument for it, and see if others agree. There's no formal process. Inputs into the decision making process include: * How much coverage of previously untested functionality it adds * How much code coverage it adds * How long the test takes to run, especially considering the slow buildfarm boxes and development turnaround time * Whether the test fits into one of the existing suites we run routinely, or requires separate steps * How much work will be required to maintain the test > 3. Is there a standard way of writing tests on the source code that > we should follow, like when should test be written in TAP/SQL/C formats and > how long should it be? > > ① I know that TAP test is for client program tests, SQL for > regression tests with sql queries, but there are instances also where tests > are also written in C like isolation tests, etc. How do we best judge which > language is preferred to use when writing tests for Postgres components? > How long should a test be when proposing them to hackers page? > In general, prefer pg_regress if you can use it. Isolation tests for concurrency issues. TAP tests if you can't write it with pg_regress or isolation tester. Test modules only if you really must. > > > 4. In the src/test/examples directory (which are all libpq tests), > why is the “examples” directory not included when building postgres? (Why > weren't these libpq tests added to src/interface/libpq/test or in > regression test suite instead?) In short, how to know where (in which > file/directory in source code) to put a test? > Dunno, sorry. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: migrations (was Re: To all who wish to unsubscribe)
On 22 November 2017 at 03:35, Tom Lane wrote: > Magnus Hagander writes: > > On Tue, Nov 21, 2017 at 8:14 PM, David G. Johnston < > > david.g.johns...@gmail.com> wrote: > >> What would help the majority of readers is if a rule could be added that > >> keys off of the word "unsubscribe" (and maybe some other key phrases) > and > >> dumps it into a non-public moderation queue where requests can be > handled > >> manually and prevented from reaching the list members. > > > This is already the case, but clearly this rule was a bit too restrictive > > to avoid too many false positives. > > It should also be pointed out that the old system did WAY too much of > that, often causing messages to get dumped into the moderation queue > because they happened to contain words that looked like majordomo > commands. I do not think that an aggressive filter for "unsubscribe" > is a good idea --- note, for example, that none of this thread would > be seeing the light of day in any timely fashion if the new code > did that. Not to mention that we have publish/subscribe in logical replication now. "How do I unsubscribe my table" -> /dev/null. Ooops. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: equalant of msdb in sql server
On 27 November 2017 at 04:59, Daniel Gustafsson wrote: > > On 26 Nov 2017, at 17:54, chandra sekhar > wrote: > > > > sql server has msdb database which stores the history of backups taken. > > is there any table in postgres which keep track of when the archive log > files are archived ? > No, there isn't. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: equalant of msdb in sql server
On 27 November 2017 at 12:10, chandra sekhar wrote: > What is the query to get list of the archive log files to capture start > date and time of each archive log > There isn't one. You may be looking for a tool like PgBarman or other archive and backup managers. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Should we document the cost of pg_database_size()? Alternatives?
B, an initial relpages sum based estimate of 308MB (unchanged from before temp table creation) and a relpages sum estimate after VACUUM of 319MB. The on-disk size of the temp table is 11MB. Meanwhile the actual size of base/16385 is 323MB according to `du` so ... good enough. But the overall size of the whole datadir is 1093MB at the moment due to WAL. Excluding WAL it's 356, mostly due to other DBs in the same instance's base/. == Any other alternatives? == I'm interested in any other options anyone might suggest on better ways to track DB size. Obviously there's going to be a trade-off between freshness and cost; pg_database_size() is expensive because it's immediate and current, whereas using relpages is inaccurate because it waits for vacuum. Any suggestions for a middle ground? If using relpages is considered good-enough, would anyone be interested if I was to cook up a patch for a more user-friendly interface like pg_database_size_cached() to present it to users? Whew. /novel. -- Craig Ringer EnterpriseDB
Re: Should we document the cost of pg_database_size()? Alternatives?
On Fri, 18 Jul 2025 at 12:54, Craig Ringer
wrote:
> [...]
> I recently ran into an issue where a monitoring component was calling
> pg_catalog.pg_database_size() at a ~15s interval on a large
> schema-sharded database. It took so long that the query was timing out
> before the next 15s scrape interval would start.
Fast-follow on this, because I thought to do a local experiment on performance.
I suspect that there's more at work with the reported issue than just
the cost of statting all the files, because even if I create a 100,000
table db with at least 2 indexes per table (and all the auxiliary
forks etc that will result in)
I created 100,000 tables to fake up a DB that looks like a
schema-sharded one, then tested timing on pg_database_size().
test=# select count(1) from pg_class;
count
---
40496
(1 row)
and now
test=# select pg_database_size(current_database());
pg_database_size
--
660013571
(1 row)
Time: 75.614 ms
... it still takes hardly any time at all.
If I drop my disk cache on my workstation:
echo 3 > /proc/sys/vm/drop_caches
then repeating pg_database_size() only takes 254 ms the first time,
then back to 74ms.
In other words, even if the DB has many millions of tables, there's no
way that pg_database_size() could reasonably take minutes to run and
contribute meaningfully to a timeout of a monitoring or scrape
process.
Not unless there's something (or several somethings) else badly wrong
on the DB instance - extreme inode cache thrashing, excessive block
device read-ahead, unreasonably high block device I/O latencies, etc.
I still think it's worth mentioning pg_database_size() needing to stat
every file in the docs, but it's clear there's more going on in the
particular case I'm seeing than that alone. I'll try to report back if
I learn anything interesting that explains the rest of the performance
issues.
FYI the crude method used to create the schema since I couldn't be
bothered scripting something sensible up was:
turn fsync off (never do this unless you're happy to completely
destroy all data in your postgres instance), set
max_locks_per_transaction=1000 and restart Pg
then
DO LANGUAGE plpgsql
$$
BEGIN FOR i IN 1..1 LOOP
EXECUTE format('CREATE TABLE %I(x serial primary key, y integer
unique)', 'padding_table_b_'||i);
EXECUTE format('INSERT INTO %I(y) VALUES (0)', 'padding_table_b_'||i);
IF i % 100 = 0 THEN RAISE NOTICE 'up to: %', i; END IF;
END LOOP;
END;
$$;
--
Craig Ringer
EnterpriseDB
