Optimze usage of immutable functions as relation

Hi hackers, There is a strange behavior of the query planner in some cases if stable/immutable was used a relation. In some cases, it affects costs of operations and leads to a bad plan of the execution. Oleg Bartunov noticed such behavior in queries with a to_tsvector as a relation: =# explain

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

> "Peter" == Peter Geoghegan writes: Peter> There are a couple of other odd things that look related, such Peter> as this extract from the triggers.out section of my Peter> regression.diffs: That one is pretty obvious (from RelationBuildTriggers): * Note: since we scan the triggers

Re: Proper way to reload config files in backend SIGHUP handler

On 05/03/2018 08:43 PM, Euler Taveira wrote: > 2018-05-03 19:25 GMT-03:00 Mike Palmiotto : >> I am writing a PostgreSQL extension and need to reload certain >> extension-managed files whenever the postmaster is reloaded/receives SIGHUP, >> but cannot find anything that looks like a standard way to

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

On Thu, May 3, 2018 at 7:26 PM, Alvaro Herrera wrote: > I bet this is related to how are these objects reached while walking the > dependency graph -- i.e. they are reached first as columns and reported > explicitly in the second case, but in the first case the tables are > reached first so the co

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

On Thu, May 3, 2018 at 7:31 PM, Tom Lane wrote: > Ah, I think it's just an order-of-visitation issue then. There are > dependencies at both the column and whole-table level, specifically > > schema collate_tests -> table collate_test4 > schema collate_tests -> domain testdomain_p -> column collat

Re: unused_oids script is broken with bsd sed

Peter Eisentraut writes: > On 5/3/18 15:37, Tom Lane wrote: >> I took a quick look into this. It's very easy to do so far as the Perl >> code is concerned: > I think in order to introduce warts like that, we have to have really > great savings. I haven't seen any actual analysis what the curren

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

Peter Geoghegan writes: > In the case of the collate tests, these are the 17 objects I can see > with ignore_system_indexes=off, once I remove the "\set VERBOSITY > terse" line from the end of collate.sql: > ... > drop cascades to table collate_test23 > drop cascades to table collate_test4 > drop

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

Peter Geoghegan wrote: > In the case of ignore_system_indexes=on, I see the same 17 entries, in > addition to these 3 (20 total): > > drop cascades to table collate_test23 column f1 > drop cascades to table collate_test4 column b > drop cascades to table collate_test5 column b > > Perhaps this m

Re: Proper way to reload config files in backend SIGHUP handler

2018-05-03 19:25 GMT-03:00 Mike Palmiotto : > I am writing a PostgreSQL extension and need to reload certain > extension-managed files whenever the postmaster is reloaded/receives SIGHUP, > but cannot find anything that looks like a standard way to do that. Is there a > hook or recommended method o

Re: unused_oids script is broken with bsd sed

On 5/3/18 15:37, Tom Lane wrote: > I took a quick look into this. It's very easy to do so far as the Perl > code is concerned: I think in order to introduce warts like that, we have to have really great savings. I haven't seen any actual analysis what the current problem is, other than one perso

Re: [HACKERS] Clock with Adaptive Replacement

On Thu, May 3, 2018 at 12:37 PM, Robert Haas wrote: > On Wed, May 2, 2018 at 3:06 PM, Vladimir Sitnikov > wrote: >> Sample output can be seen here: >> https://github.com/vlsi/pgsqlstat/tree/pgsqlio#pgsqlio > > Neat. Not sure what generated this trace, but note this part: > > 3271838881374882

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

On Thu, May 3, 2018 at 4:14 PM, Peter Geoghegan wrote: > On Thu, May 3, 2018 at 3:18 PM, Tom Lane wrote: >> Indeed, that seems weird. Maybe tweak the test scripts so you can see >> all the objects cascaded to, and then find out what the additional >> object is? (I think also you could look into

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

On Thu, May 3, 2018 at 3:18 PM, Tom Lane wrote: > Peter Geoghegan writes: >> Why should the drop cascade to 63 objects rather than 62 because I've >> set ignore_system_indexes=on? > > Indeed, that seems weird. Maybe tweak the test scripts so you can see > all the objects cascaded to, and then fi

Re: A few warnings on Windows

On Fri, May 4, 2018 at 2:46 AM, Peter Eisentraut wrote: > On 5/3/18 10:18, Tom Lane wrote: >> Christian Ullrich writes: Thomas Munro writes: > Does anyone know what line 174 of pyconfig.h happens to say? >> >>> typedef _W64 int ssize_t; >>> , in a "not for 64-bit" block. >>>

Proper way to reload config files in backend SIGHUP handler

All, I am writing a PostgreSQL extension and need to reload certain extension-managed files whenever the postmaster is reloaded/receives SIGHUP, but cannot find anything that looks like a standard way to do that. Is there a hook or recommended method or something else I am missing? Thanks, -- M

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

Peter Geoghegan writes: > Why should the drop cascade to 63 objects rather than 62 because I've > set ignore_system_indexes=on? Indeed, that seems weird. Maybe tweak the test scripts so you can see all the objects cascaded to, and then find out what the additional object is? (I think also you c

Re: Python 3.7 support

Peter Eisentraut writes: > I have committed this now, since the release of Python 3.7 is soon. > I'll let the build farm have a pass at it, then backport it for the > upcoming minor releases. gaur/pademelon (Python 2.5) not very happy :-(. Let me know if you'd like me to try anything particular

ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

Setting ignore_system_indexes=off in postgresql.conf has the effect of making almost all regression tests fail during a "make installcheck". This is unsurprising, since warnings are emitted all over the place. However, some of the specific ways in which it fails *are* surprising. I see the followi

MSYS2 and pg_upgrade testing

I've been getting an Msys2 environment working, and will soon document how to build with this environment. There are several nice things about it, including a modern version of perl and proper support for the mingw-w64 compilers. So far the only thing I have found that needs to be changed for us t

Re: unused_oids script is broken with bsd sed

Alvaro Herrera writes: > Tom Lane wrote: >> However, RenameTempFile is also used by Gen_fmgrtab.pl, and having the >> same sort of no-touch semantics for fmgroids.h and friends would have some >> additional fallout. The makefiles would think they have to keep >> re-running Gen_fmgrtab.pl if fmgro

Re: unused_oids script is broken with bsd sed

On Thu, May 3, 2018 at 3:37 PM, Tom Lane wrote: > > Anyway, I'm happy to go make this happen; it looks like only another hour > or so's worth of work to fix the makefiles. But I wonder if anyone will > say this is too much churn for post-feature-freeze and we should shelve > it till v12. > I thi

Re: unused_oids script is broken with bsd sed

Tom Lane wrote: > However, RenameTempFile is also used by Gen_fmgrtab.pl, and having the > same sort of no-touch semantics for fmgroids.h and friends would have some > additional fallout. The makefiles would think they have to keep > re-running Gen_fmgrtab.pl if fmgroids.h is older than the mod t

Re: unused_oids script is broken with bsd sed

Robert Haas writes: > On Thu, Apr 26, 2018 at 11:29 AM, Tom Lane wrote: >> Personally, I use ccache which doesn't seem to care too much, but I agree >> than in some usages, extra touches of headers would be costly. Perhaps >> it's worth adding logic to avoid overwriting an existing output file >

Re: [HACKERS] Clock with Adaptive Replacement

On Wed, May 2, 2018 at 3:06 PM, Vladimir Sitnikov wrote: > Sample output can be seen here: > https://github.com/vlsi/pgsqlstat/tree/pgsqlio#pgsqlio Neat. Not sure what generated this trace, but note this part: 32718388813748820500 16631638516604 0 327184097

Moving libpg{common,port,feutils}.a to pkglibdir?

Debian's PostgreSQL packages allow installation of several server versions in parallel, but only one libpq and libpq-dev package is provided, i.e. libpq is always from the latest stable branch. There are separate postgresql-server-dev-NN packages for the version-specific header files. So far, postg

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Wed, May 2, 2018 at 9:17 AM, Ashutosh Bapat wrote: > On Wed, May 2, 2018 at 11:56 AM, Amit Langote > wrote: >> But one could very well argue that BEFORE ROW triggers on the >> parent should run before performing the tuple routing and not be cloned to >> individual partitions, in which case the

Re: unused_oids script is broken with bsd sed

On Thu, Apr 26, 2018 at 11:29 AM, Tom Lane wrote: > Personally, I use ccache which doesn't seem to care too much, but I agree > than in some usages, extra touches of headers would be costly. Perhaps > it's worth adding logic to avoid overwriting an existing output file > unless it changed? I'm n

Re: Python 3.7 support

On 2/14/18 21:25, Peter Eisentraut wrote: > On 2/13/18 21:45, Michael Paquier wrote: >> On Tue, Feb 13, 2018 at 04:17:13PM -0500, Peter Eisentraut wrote: >>> A small patch to tweak the tests to support output differences with >>> Python 3.7 (currently in beta). >> >> Wouldn't it be better to wait f

Re: Global snapshots

> On 3 May 2018, at 18:28, Masahiko Sawada wrote: > > On Wed, May 2, 2018 at 1:27 AM, Stas Kelvich wrote: >> 1) To achieve commit atomicity of different nodes intermediate step is >> introduced: at first running transaction is marked as InDoubt on all nodes, >> and only after that each nod

Re: FinishPreparedTransaction missing HOLD_INTERRUPTS section

Thank you, pushed! Stas Kelvich wrote: Hello. It seems that during COMMIT PREPARED FinishPreparedTransaction() doesn't hold interrupts around writing to wal and cleaning up ProcArray and GXact entries. At least RemoveTwoPhaseFile (which is called in between) can print a warning with ereport(),

Re: lazy detoasting

> "Peter" == Peter Eisentraut writes: Peter> The attached test fixes this issue by flattening the toast Peter> values before storing them into PL/pgSQL variables. It could use Peter> another check to see if there are other code paths that need Peter> similar adjustments, but I think it's

Re: Built-in connection pooling

On Fri, Apr 27, 2018 at 4:43 PM, Merlin Moncure wrote: > What _I_ (maybe not others) want is a > faster pgbouncer that is integrated into the database; IMO it does > everything exactly right. I have to admit that I find that an amazing statement. Not that pgbouncer is bad technology, but saying

Re: FPW stats?

On Wed, May 2, 2018 at 7:10 AM, Michael Paquier wrote: > Your patch adds a new field to PgStat_StatDBEntry? Wouldn't you > increase the bottleneck of deployments with many databases? What's > actually your use case? I'm a little doubtful about whether this particular thing is generally useful b

Re: lazy detoasting

On 5/1/18 19:56, Andrew Gierth wrote: > Peter> insert into test1 values (1, repeat('foo', 2000)); > > That value is no good because it's too compressible; it'll be left > inline in the main table rather than being externalized, so the value of > 'x' in the DO-block is still self-contained (though

Re: GSoC 2018: thrift encoding format

>Personally I think raw data bytes are OK if functions for getting all keys and values from this data are provided What is the purpose of using Thrift "encoding" if it turns out to be a simple wrapper for existing binary data? Do you mean the goal is to have "get/set" functions to fetch data out

Re: Optimize Arm64 crc32c implementation in Postgresql

Andrew Gierth writes: > "Tom" == Tom Lane writes: > Tom> I also noticed that we'd been sloppy about making the file safe to > Tom> compile for both frontend and backend, so I cleaned that up. > In a frontend, wouldn't it be more kosher to restore the previous SIGILL > handler rather than uncon

Re: Optimize Arm64 crc32c implementation in Postgresql

> "Tom" == Tom Lane writes: Tom> I also noticed that we'd been sloppy about making the file safe to Tom> compile for both frontend and backend, so I cleaned that up. In a frontend, wouldn't it be more kosher to restore the previous SIGILL handler rather than unconditionally reset it to SIG

Re: Optimize Arm64 crc32c implementation in Postgresql

Thomas Munro writes: > Let me try that again with that stupid typo (crc2) fixed... I didn't like that too much as-is, because it was capable of calling elog(ERROR) without having reset the SIGILL trap first. That's just trouble waiting to happen, so I rearranged to avoid it. I also noticed that

Re: Global snapshots

On Wed, May 2, 2018 at 1:27 AM, Stas Kelvich wrote: > 1) To achieve commit atomicity of different nodes intermediate step is >introduced: at first running transaction is marked as InDoubt on all nodes, >and only after that each node commit it and stamps with a given GlobalCSN. >All rea

Re: documentation is now XML

Hi Peter, Thank you for taking your time to comment on this long-discussed topic. I was not around when all the great work was done to implement XSLT transforms in branch 10 and convert branch 11 to XML, but judging by this thread (https://www.postgresql.org/message-id/flat/4ffd72d6-8ab6-37c6-

Re: A few warnings on Windows

On 5/3/18 10:18, Tom Lane wrote: > Christian Ullrich writes: >>> Thomas Munro writes: Does anyone know what line 174 of pyconfig.h happens to say? > >> typedef _W64 int ssize_t; >> , in a "not for 64-bit" block. >> , 3.6.3 is >>

Re: A few warnings on Windows

Christian Ullrich writes: >> Thomas Munro writes: >>> Does anyone know what line 174 of pyconfig.h happens to say? > typedef _W64 int ssize_t; > , in a "not for 64-bit" block. > , 3.6.3 is > the installed version on whelk. Thanks. N

Re: Issues while building PG in MS Windows, using MSYS2 and MinGW-w64

Hello, insaf.k. You wrote: ik> Hello, ik> I am trying to build PG from source, in MS Windows using MSYS2 ik> and MinGW-w64. I've tried to build PG 10.0 as wells as 10.3. Just checked. All compiled in a sane way. I suppose you have some environmental problems. This is my step-by-step guide:

Re: pgsql: Clean up warnings from -Wimplicit-fallthrough.

On 5/1/18 23:33, Tom Lane wrote: > Andres Freund writes: >> On 2018-05-01 23:35:18 +, Tom Lane wrote: >>> Clean up warnings from -Wimplicit-fallthrough. > >> I found one more oddity with the current committed state: ... >> It seems that gcc gets confused by the #ifdef ECONNRESET. > > Yeah, t

Re: Anyone keep mirrors of old packages from apt.postgresql.org?

On 3 May 2018 at 16:54, Craig Ringer wrote: > On 3 May 2018 at 16:48, Sergei Kornilov wrote: >> Hello >> Do you know http://atalia.postgresql.org/morgue/ repository? > > Ugh. It's in the README. I'm blind. So sorry for the noise. I don't see anything similar for yum.postgresql.org though. Devri

Re: Is there a memory leak in commit 8561e48?

On 5/2/18 20:11, Michael Paquier wrote: > On Wed, May 02, 2018 at 07:03:21PM -0400, Tom Lane wrote: >> It's only ~100 bytes per stack level. I think under normal loads >> nobody would notice. If you're worried about cross-transaction >> memory consumption, our various caches tend to be a lot wors

ParseDateTime in src/backend/utils/adt/datetime.c

Hi guys, https://stackoverflow.com/questions/50153122/how-come-this-postgres-query-is-working Above guys are surprised by "ignore other punctuation but use as delimiter" part. Do you think this should be mentioned in docs? E.g. https://www.postgresql.org/docs/current/static/functions-formatting.htm

Re: GSOC 2018

Hi, Joshua! > 2 мая 2018 г., в 22:05, Joshua D. Drake написал(а): > > Who is coordinating GSOC this year? Stephen Frost is org admin. Best regards, Andrey Borodin.

Re: GSoC 2018: thrift encoding format

Hello Charles, > Thanks for your confirm Aleksander! > Also I am thinking of how to deal with complex > data structure like map, list, or set. I guess one possible > solution is to get raw data bytes for these data structure? > Otherwise it could be hard to wrap into a Datum. Personally I think r

Re: Anyone keep mirrors of old packages from apt.postgresql.org?

On 3 May 2018 at 16:48, Sergei Kornilov wrote: > Hello > Do you know http://atalia.postgresql.org/morgue/ repository? Ugh. It's in the README. I'm blind. So sorry for the noise. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Serv

Re: Anyone keep mirrors of old packages from apt.postgresql.org?

Hello Do you know http://atalia.postgresql.org/morgue/ repository? > copy of postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb anywhere Here is http://atalia.postgresql.org/morgue/p/postgresql-9.6/postgresql-9.6-dbg_9.6.5-1.pgdg80%2b1_amd64.deb regards, Sergei

Anyone keep mirrors of old packages from apt.postgresql.org?

Hi all I'm trying to assemble a mirror of old package versions from apt.postgresql.org and yum.postgresql.org, as I'm encountering more and more cases where I really need debuginfo for a package but the user hasn't installed it. The repos only keep the last couple of builds, so it quickly becomes

Re: Is a modern build system acceptable for older platforms

Hello, Yuriy. You wrote: YZ>  (2) it might make things easier on Windows, YZ> which could be a sufficiently good reason but I don't think I've seen YZ> anyone explain exactly how much easier it will make things and in what YZ> ways. YZ> 1. You can remove tools/msvc folder because all your

Re: A few warnings on Windows

* Tom Lane wrote: Thomas Munro writes: One more problem. whelk builds against Python 3.6 and says: c:\users\pgbf\appdata\local\programs\python\python36-32\include\pyconfig.h(174): warning C4142: benign redefinition of type (src/pl/plpython/plpy_elog.c) [C:\buildfarm\buildenv\HEAD\pgsql.bui