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: 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: Code of Conduct plan
ame 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: Wanted: postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb
On 3 May 2018 at 16:46, Karsten Hilbert <karsten.hilb...@gmx.net> 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
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