tag:blogger.com,1999:blog-23561373769349645512024-03-13T06:39:02.825-04:00Andrew Dunstan's PostgreSQL and Technical blogAndrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.comBlogger153125tag:blogger.com,1999:blog-2356137376934964551.post-79105537892675415392024-02-12T07:51:00.003-05:002024-02-12T14:30:11.381-05:00Review: PostgreSQL 16 Administration Cookbook<p>This book, published in December 2023 by <a href="https://www.packtpub.com/product/postgresql-16-administration-cookbook/9781835460580">packt</a>, is the latest incarnation of a book originally principally authored by Simon Riggs, and brought up to date for PostgreSQL 16 by my EDB colleagues Gianni Ciolli, Boriss Mejías, Jimmy Angelakos and Vibhor Kumar. It's available in both hard copy and electronic format. For review purposes, the publisher provided me with one of each. DBAs will probably find the electronic format more useful because of the ability to cut and paste from it. And in these days of devops, where just about every developer is their own DBA to some extent, the book should appeal to a wide audience of PostgreSQL users.<br /></p><p>Books like this don't necessarily aim to be comprehensive guides, but rather to provide a toolbox full of useful tips and recipes. And this book is chock full of them. They cover topics relating to pretty much every aspect of PostgreSQL administration, from installation to maintenance to troubleshooting. And the book is pretty reliable. That's not surprising to me, as I know these authors to be fairly meticulous in their attention to detail. When I'm given a book to review I usually start by looking at areas where I have pretty good knowledge already. All too often I find errors that are egregious or just plain careless. I can report that I didn't find any errors beyond an insignificant typo or two. This book is pretty solid, and I'm happy to recommend it.</p><p>I do have a few relatively minor criticisms. First, the layout could be improved. A horizontal bar between recipes would be useful. Simply using a slightly larger font for recipe headings doesn't make it easy enough to see where one topic ends and another starts. Better visual clues would be good. If that increases the book's length, maybe they could also save some by removing the repetitious "Learn more on Discord" Section at the end of each chapter.</p><p>More substantially, some topics are covered in such little detail as to be not very useful. For example, the section of Foreign Data Wrappers is very sparse, as is the section that mentions Prometheus and Grafana. It might be better to have less recipes but with more useful information.</p><p>Finally, it is odd to read things stated in the first person singular in a book with multiple authors. I imagine this is a holdover from earlier incarnations of the book, but it still feels strange. If it's going to be kept then the reader should be told whose voice this is. But in general I think it would be better to use "we" and "our" instead of "I" and "my".</p><p>None of this should deter you from buying the book. If you're a PostgreSQL developer or administrator you are highly likely to find useful gems of information in this book.</p><p> <br /></p><p><br /></p><p><br /></p>Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-20967746202852015112023-08-04T11:32:00.000-04:002023-08-04T11:32:15.211-04:00Announcing Release 17 of the PostgreSQL Buildfarm client<div class="markdown-body my-3" data-pjax="true" data-test-selector="body-content" data-view-component="true"><p>I have pushed Release 17 of the PostgreSQL Buildfarm client. <br /></p><p>Release 17 has two main features:</p>
<ul><li>Modernize the way we do cross-version upgrade tests. Most of the
logic for modifying instances to make them suitable for cross version
upgrade testing has now been migrated to the Postgres core code in <code>src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm</code> The new code simply imports this module and leverages its knowledge.</li><li>Support of building with <code>meson</code>. This is only supported
on version 16 or later of Postgres; older branches will continue to use
the older toolsets. To enable building with <code>meson</code> there are several new settings, illustrated in the sample configuration file:
<ul><li><code>using_meson</code> this must be set to a true value</li><li><code>meson_jobs</code> this controls the degree of parallelism that <code>meson</code> will use</li><li><code>meson_test_timeout</code> this is used to multiply the meson test timeout. The default is 3, 0 turns off timeout</li><li><code>meson_config</code> This is an array of settings for passing to <code>meson setup</code>. Note that all options need to be explicitly given here - the client disables all <code>auto</code> options. This includes use of <code>zlib</code> and <code>readline</code>, which do not default to on, unlike <code>autoconf</code> setups.</li></ul>
</li></ul>
<p>There are also a number of relatively small bug fixes and tweaks (e.g. some improvements in processing typedefs).</p><p>The release is available at <a class="moz-txt-link-rfc2396E" href="https://github.com/PGBuildFarm/client-code/releases">https://github.com/PGBuildFarm/client-code/releases</a> or <a class="moz-txt-link-rfc2396E" href="https://buildfarm.postgresql.org/downloads/latest-client.tgz">https://buildfarm.postgresql.org/downloads/latest-client.tgz</a></p>
<p>Enjoy!</p>
<p><br />
</p><p> </p></div><p></p>Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-55838838983329081152023-01-13T20:21:00.000-05:002023-01-13T20:21:09.829-05:00Announcing Release 16 of the PostgreSQL Buildfarm client<p> </p><div class="markdown-body my-3" data-pjax="true" data-test-selector="body-content" data-view-component="true"><p>Hot on the heels of Release 15 comes Release 16.</p>
<p>This release deals with some issues that have been discovered with the check for update feature of Release 15 and the <code>force_every</code> and <code>trigger_exclude</code> features, so that it now works correctly with those features.</p>
<p>It also features these items:</p>
<ul><li>a new <code>--check-for-work</code> mode of run_branches.pl<br />
This mode doesn't do any work but exits with a zero status if there is
work to do and 1 if there is not. It is intended for use as an<br />
ExecCondition in <code>systemd</code> units</li><li>up to date filtering now works with an explicit list of branches, as well as with key words like <code>ALL</code></li><li>reduce the verbosity of <code>Another process holds the lock</code> messages.<br />
These are now only emitted if the <code>verbose</code> setting is greater than 1</li><li><code>update_personality</code> now has options to change the owner name and owner email<br />
This was in Release 15 but was accidentally omitted from the release
notes. Up to now the only way to change these was by action from<br />
the administrators.</li><li>improve collection of logs in cross version upgrade testing</li></ul><p style="text-align: left;"> The release can be downloaded from
</p><a class="moz-txt-link-rfc2396E" href="https://github.com/PGBuildFarm/client-code/releases/tag/REL_16">https://github.com/PGBuildFarm/client-code/releases/tag/REL_16</a> or </div><div class="markdown-body my-3" data-pjax="true" data-test-selector="body-content" data-view-component="true"><a class="moz-txt-link-rfc2396E" href="https://buildfarm.postgresql.org/downloads">https://buildfarm.postgresql.org/downloads</a></div>Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-48880700452092766762022-12-31T10:15:00.000-05:002022-12-31T10:15:30.909-05:00Announcing Release 15 of the PostgreSQL Buildfarm client<div class="markdown-body my-3" data-pjax="true" data-test-selector="body-content" data-view-component="true"><p>Changes</p>
<ul><li>add a new script <code>manage_alerts.pl</code> that lets the user enable or disable alerts for an animal<br />
This is especially useful in the case of animals that have stopped running for some reason.</li><li>check if a branch is up to date before trying to run it<br />
This only applies if the <code>branches_to_build</code> setting is a keyword rather than a list of branches. It reduces the number of useless calls to <code>git pull</code> to almost zero.</li><li>require Perl version 5.14 or later<br />
This should not be a problem, as it's more than 10 years old.</li><li>add <code>--avoid-ts-collisions</code> command line parameter<br />
This is for specialized uses, and imposes a penalty of a few seconds per run. <code>run_branches.pl</code> already does this, so it's not required for normal operations.</li><li>run TAP tests for <code>src/interfaces</code> subdirectories</li><li>add amcheck and extension upgrade tests to cross version upgrade testing</li><li>adjust to changes in postgres code, file locations, etc.</li><li>assorted minor bug fixes and tweaks</li></ul></div>
<p>The release can be downloaded from
</p><p><a class="moz-txt-link-rfc2396E" href="https://github.com/PGBuildFarm/client-code/releases/tag/REL_15">https://github.com/PGBuildFarm/client-code/releases/tag/REL_15</a> or </p><p><a class="moz-txt-link-rfc2396E" href="https://buildfarm.postgresql.org/downloads">https://buildfarm.postgresql.org/downloads</a></p>
<p>Upgrading is highly recommended.
</p><p> </p>Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-76912796811923130632022-11-12T09:23:00.000-05:002022-11-12T09:23:43.767-05:00Goodbye Windows XP<p>This time for sure (h/t Bullwinkle)</p>
<p>Yesterday I finally turned off my Windows XP instance for good. It's been running for quite a long time, almost 12 years, basically as the host for the buildfarm animals <a href="https://buildfarm.postgresql.org/cgi-bin/show_status.pl?member=currawong&member=frogmouth&member=brolga">currawong, brolga and frogmouth</a>. However, XP itself went out of support on 2014, and in recent years modern Postgres will not build or run on it due to some platform limitations. The latest version of Postgres that could run on it was release 10, which has now reached end of life, so there is no longer a supported version of Postgres that can build on XP, and there is thus no longer a need for these buildfarm animals.</p>
<p>This means we're also probably saying goodbye to Microsoft Visual C 2008, and Msys version 1.</p>Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-50790138773953718022022-08-17T11:47:00.002-04:002022-08-19T15:17:25.645-04:00Taming Buildfarm Logs<p> The output from running a buildfarm step is the output from running a command such as <span style="color: red;"><span style="font-family: courier;">make check </span></span>followed by any relevant log files that the step creates. These log files can get quite large, and in some cases there are quite a lot of them. Up to now the buildfarm server has just show all of this in an undifferentiated text blob. That has made finding out what might have gone wrong not impossible but not as easy as it could be, and there have been complaints about it over the years.<br /></p><p>A few days ago I implemented a new way of presenting this information. You still see the command output, but instead of showing the log file contents you see a list of the log files. Click on one log file and it will show you the contents of that file. Click on it again and they are hidden again. So if, say, a TAP test fails at script number 12, you can quickly locate the <span style="color: red;"><span style="font-family: courier;">regress_log_012_mytest.log</span></span> and associated server logs, without having to scroll through thousand of lines that don't interest you.</p><p>You can still see the old format if you need to - the page for each stage has a link to the raw log. <br /></p><p>I hope this will enhance the usability and usefulness of the buildfarm.</p><p>Thanks to Kori Lane for pointing me at the JavaScript gadget that is used to implement this.</p><p>Update: All issues now resolved.<br /></p>Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-20699409645429887112022-02-06T09:15:00.000-05:002022-02-06T09:15:25.156-05:00Release 14 of the PostgreSQL Buildfarm client<p> This is to announce release 14 of the PostgreSQL Buildfarm client.<br /></p><div class="markdown-body my-3" data-pjax="true" data-test-selector="body-content" data-view-component="true"><p>Significant changes:</p>
<ul><li>don't run TestUpgrade if TAP tests are present in <code>src/bin/pg_upgrade</code></li><li>Add proposed new location of <code>pg_upgrade</code> logs in TestUpgrade module</li><li>Use an HMAC over the whole content as the signature.</li><li>Quote <code>PROVE_FLAGS </code>in case there are multiple settings</li><li>tighten failure detection for cross version upgrade</li><li>be more verbose about git mirror failures</li><li>Support symlinks on Windows where possible in SCM module</li><li>Document <code>rm_worktrees</code> setting and make <code>on</code> the default.</li><li>Add new branches_to_build keywords <code>STABLE</code> and <code>OLD</code></li></ul>Because of the changes to how pg_upgrade logging works, owners are
strongly urged to upgrade to the new release as soon as possible if they
are running the TestUpgrade module. </div><div class="markdown-body my-3" data-pjax="true" data-test-selector="body-content" data-view-component="true"> </div><div class="markdown-body my-3" data-pjax="true" data-test-selector="body-content" data-view-component="true">The meaning of the branches_to_build keywords are as follows: STABLE
means all the live branches other than master/HEAD, while OLD means
those branches older than STABLE that we are now supporting limited
builds for (currently REL9_2_STABLE through REL9_6_STABLE). </div><div class="markdown-body my-3" data-pjax="true" data-test-selector="body-content" data-view-component="true"> </div><div class="markdown-body my-3" data-pjax="true" data-test-selector="body-content" data-view-component="true">Downloads are available at <a class="moz-txt-link-rfc2396E" href="https://github.com/PGBuildFarm/client-code/releases">https://github.com/PGBuildFarm/client-code/releases</a> and <a class="moz-txt-link-rfc2396E" href="https://buildfarm.postgresql.org/downloads">https://buildfarm.postgresql.org/downloads</a></div>Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-41985262090177197862021-08-04T09:41:00.000-04:002021-08-04T09:41:41.746-04:00Release 13.1 of the PostgreSQL BuildFarm client<p> </p>I have just pushed Release 13.1 of the PostgreSQL BuildFarm client.
<p>This update to Release 13 fixes errors that occur from the new default
branch name checking code when used with versions of git that are too
old. This code is now disabled if the git version is not capable of
running it, and in verbose mode a warning is printed. The warning can be
disabled by explicitly setting "skip_git_default_check => 1" in the
config file. In either case, the owner will need to update their git
installation or remove all branch and mirror repositories when the
default branch name changes.
</p><p>Downloads are available at:</p><p><a class="moz-txt-link-rfc2396E" href="https://github.com/PGBuildFarm/client-code/releases">https://github.com/PGBuildFarm/client-code/releases</a> and</p><p><a class="moz-txt-link-rfc2396E" href="https://buildfarm.postgresql.org/downloads">https://buildfarm.postgresql.org/downloads</a>
</p><p></p>Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-33296937759681034632021-08-02T17:40:00.000-04:002021-08-02T17:40:07.773-04:00Release 13 of the PostgreSQL BuildFarm client<p>I have pushed Release 13 of the PostgreSQL BuildFarm client.
</p><p>Change highlights:
</p><ul>
<li> add tests for a few cases that were previously missing
</li><li>more fine-grained control over which TAP test sets run
</li><li>--no-keepall can be specified on the command line
</li><li>repair a repo if it crashed during a copy operation
</li><li>make sure the repo is really clean (including free of ignored files)
</li><li>generate stack traces on CYGWIN
</li><li>collect stack traces for TAP tests
</li><li>Extract MSVC settings at runtime rather than had coding them in theconfig file (see below)
</li><li>cross version upgrade tests now run on Windows, both for msys2 and
MSVC builds
</li><li>add support for inhibit-runs and force-one-run trigger files( see below)
</li><li>add experimental module for running arbitrary out of tree TAP tests
</li><li>Adjust if an upstream repo changes the default branch name (see below)
</li><li>add use_discard_caches caches setting (see below)
</li></ul>
<p>MSVC animals are now very much simpler to set up, and to upgrade to a
new compiler. Using the new mechanism, as shown in the sample config
file, all that's needed is to specify a location where the standard
script vcvarsall.bat can be found. The script will then run that script
and extract the settings and apply them. Tha means that upgrading to a
new version of Visual Studio would entail just a one line change in the
config file.
</p><p>If you put a file called [animalname].inhibit-runs in the build root,
all runs will be stopped until the file is removed. If you put a file
called [animalname].force-one-run in the build root, each configured
branch will forced to run once, and the file will be removed. These only
apply if you use the run_branches.pl script.
</p><p>The client should transparently deal with any change that is made in the
upstream repository's default branch name. This avoids the need for a
flag day when we eventually change the default branch name for
postgresql, as I assume we will do before long. The branch bf_HEAD which
the client creates now refers to the upstream default whatever it might
be, rather than the hardcoded name 'master'. The code of the SCM module
underwent quite a lot of change in order to make this happen; the
checkout code had become quite long and convoluted and I had to refactor
it somewhat before I was able to make and test this change. The changes
have been fairly extensively tested, but I'm still slightly nervous
about them. Owners are asked to report any issues promptly.
</p><p>the use_discard_caches setting reflects a change in the way postgres
handles this - it's now a runtime setting rather than a compile time
setting. On older branches it sets "-DCLOBBER_CACHE_ALWAYS". If you use
this setting don't use that define.
</p><p>Downloads are available at </p><a class="moz-txt-link-rfc2396E" href="https://github.com/PGBuildFarm/client-code/releases">https://github.com/PGBuildFarm/client-code/releases</a> and
<a class="moz-txt-link-rfc2396E" href="https://buildfarm.postgresql.org/downloads">https://buildfarm.postgresql.org/downloads</a>Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-49000771768780893102021-06-19T16:53:00.000-04:002021-06-19T16:53:51.933-04:00Running Standalone TAP Tests<p>Recent discussion set me thinking about what would be involved in setting up standalone TAP tests for Postgres, to do, say, more code coverage than we get from the current core tests. So I started experimenting. And with a tiny bit of Makefile magic it turns out to be absurdly easy.</p>
<p>You need just two things: an entirely boilerplate Makefile and a tiny bit of glue at the top of your TAP test files.</p>
<p>First let's look at the Makefile. Here it is in its entirety:</p>
<blockquote><pre>TAP_TESTS = 1
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
# install_path for PostgreSQL binaries etc
PG_INSTALL_ROOT := $(dir $(shell $(PG_CONFIG) --bindir))
export PG_INSTALL_ROOT
# where to find PostgresNode and friends
PG_NODE_LOC = $(top_srcdir)/src/test/perl
export PG_NODE_LOC
</pre></blockquote>
<p>Then in your TAP test perl file(s) (which should reside in a subdirectory called "t") you put this:</p>
<blockquote><pre>
use lib "$ENV{PG_NODE_LOC}";
use PostgresNode;
use Test::More;
local $ENV{PG_REGRESS} = "/bin/true";
my $node_inst = $ENV{PG_INSTALL_ROOT};
# for pre--release-14 releases you would possibly set LD_LIBRARY_PATH
# based on this. For release 14 and up PostgresNode does that for you
# via the install_path parameter.
my $node = PostgresNode->get_new_node('dummy', install_path => $node_inst);
...
</pre></blockquote>
<p>That's all you need. Given that you can run your TAP tests with just a Postgres installation, as in this example:</p>
<blockquote><pre>
andrew@emma:tests $ make PG_CONFIG=../inst.head.5701/bin/pg_config installcheck
rm -rf '/home/andrew/pgl/tests'/tmp_check
/usr/bin/mkdir -p '/home/andrew/pgl/tests'/tmp_check
cd ./ && TESTDIR='/home/andrew/pgl/tests' PATH="/home/andrew/pgl/inst.head.5701/bin:$PATH" PGPORT='65701' \
top_builddir='/home/andrew/pgl/tests//home/andrew/pgl/inst.head.5701/lib/postgresql/pgxs/src/makefiles/../..' \
PG_REGRESS='/home/andrew/pgl/tests//home/andrew/pgl/inst.head.5701/lib/postgresql/pgxs/src/makefiles/../../src/test/regress/pg_regress' \
REGRESS_SHLIB='/src/test/regress/regress.so' \
/usr/bin/prove -I /home/andrew/pgl/inst.head.5701/lib/postgresql/pgxs/src/makefiles/../../src/test/perl/ -I ./ t/*.pl
t/dummy.pl .. ok
All tests successful.
Files=1, Tests=1, 3 wallclock secs ( 0.04 usr 0.01 sys + 1.22 cusr 0.36 csys = 1.63 CPU)
Result: PASS</pre></blockquote>
<p>The Makefile and a demo TAP test are at <a href="https://gitlab.com/adunstan/dummytap">This Gitlab repo</a></p>
<p>In the next episode, we'll look at how to leverage this so that your standalone tests are run by your buildfarm animal.</p>
Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com1tag:blogger.com,1999:blog-2356137376934964551.post-4764480921635411092021-06-05T12:01:00.004-04:002021-08-04T12:52:10.705-04:00Buildfarm adopts modern git naming<p>In keeping with modern git practice, I have renamed the default branch on both the <a href="https://github.com/PGBuildFarm/client-code">client-code</a> and <a href="https://github.com/PGBuildFarm/server-code">server-code</a> repositories to <span style="font-family: courier;">main</span>. If you have a clone of either of these repositories, you can adapt to the change by doing the following commands in your local repo:</p>
<blockquote><pre>git checkout master
git branch -m main
git fetch
git branch --unset-upstream
git branch -u origin/main
git symbolic-ref refs/remotes/origin/HEAD refs/remotes/origin/main
git fetch -p
</pre></blockquote>
<p>There is still a bunch of work to do to ensure that things run smoothly when source repositories such as <a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=summary">The PostgreSQL Git repository</a> make this change, as will surely happen at some stage. I will be testing this on a small source repository I control - the plan is that when it happens it will be transparent to buildfarm animals.</p><h4 style="text-align: left;"> Update (August 4 2021):</h4><p>I just went to update a couple of machines with very old versions of git. They choked on these lines</p><pre style="margin-left: 40px; text-align: left;">git branch --unset-upstream
git branch -u origin/main</pre><p style="text-align: left;">The solution is instead to edit the <span style="font-family: courier;">.git/config</span> file directly. In these lines change "master" to "main":</p>
<blockquote><pre>[branch "main"]
<span> </span>remote = origin
merge = refs/heads/master</pre></blockquote>
<p>The rest of the recipe worked fine. In some cases you might need a final <span style="font-family: courier;">git pull</span>.<br /></p>Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-75905030982491312222016-08-08T09:55:00.000-04:002016-08-08T09:55:22.985-04:00Using EXPLAIN json format output in plpgsqlIt's possible to use EXPLAIN output in plpgsql. In the default text format, the result comes back as a set of text values, so you can process them in a loop like this:<br />
<blockquote>
<pre></pre>
</blockquote>
<br />
declare<br />
exp text;<br />
begin<br />
for exp in explain myquery<br />
loop<br />
raise notice '%', exp;<br />
end loop;<br />
end;<br />
<br />
<br />
If you use json format output, however, the result comes back as a single json document rather than a set of lines. You still might need a loop - I haven't found another way yet of getting the output from EXPLAIN into a variable - but the loop will only have one iteration. Here is an example taken from a function I wrote the other day that lets you get the estimated number of rows from the plan:<br />
<br />
<blockquote>
<pre></pre>
</blockquote>
<br />
declare<br />
exp json;<br />
begin<br />
for exp in explain (format json) myquery<br />
loop<br />
raise notice 'rows: %', exp#>>'{0,Plan,Plan Rows}';<br />
end loop;<br />
end;<br />
Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com2tag:blogger.com,1999:blog-2356137376934964551.post-67631936281393685102016-06-09T09:03:00.000-04:002016-06-09T09:03:33.410-04:00Removing a Buildfarm client/server perl dependencyWith one very small exception, there is nothing in the buildfarm server that requires the client to be running perl, even though both the server and the standard client are written in perl.<br />
<br />
Andreas Scherbaum has written a new specialized client in python, and he asked me about removing that dependency. Essentially what this requires is that an alternative mechanism be allowed for transmitting the serialized configuration in the client's web transaction. Currently the client uses perl's Storable module to serialize the data, and the server uses the same module to de-serialize it. The obvious alternative candidate is to serialize it as JSON.<br />
<br />
The first problem was to determine if we can sanely distinguish between data serialized by the two mechanisms. And we can. JSON is not allowed to contain any control characters, and a structure serialized using Storable's nfreeze() method is pretty much guaranteed to contain such characters. So I've added a test to the receiving program that looks for such characters and if it doesn't find them assumes that the data is JSON and decodes it accordingly. This has been tested using the client nightjar.<br />
<br />
There are a few wrinkles, though. The main one is that we can't assume that every client has a module available to encode the data as JSON. In fact, nightjar didn't until I installed one. So in order to build this into the client I'll need to find a way to determine at runtime if it's available and fall back to using Storable otherwise.<br />
<br />
We should also look at actually storing the data as JSON rather than as a serialized blob. When the buildfarm was created we didn't have any database support for JSON, but now the sensible thing to do would be to store the data as jsonb, and make it searchable.<br />
<br />
But that's a project for another day.Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-67744125847872065872016-06-04T11:20:00.001-04:002016-06-04T11:20:44.647-04:00Using wal-e with Postgres on a non-standard portI just discovered after a great deal of puzzlement and far too much time that if you run multiple instances of Postgres on a machine, and you're using <a href="https://github.com/wal-e/wal-e">wal-e</a> for continuous backup, you'll need an entry for PGPORT or PGSERVICE in the environment directory for any instance that's not running on port 5432. Otherwise, wal-e will happily start and stop a backup on your port 5432 server and your non-5432 backups will be completely useless since they won't have a backup label.<br />
<br />
Yet another example of the fact that any untested backup is not worth anything. If I hadn't tested this and found the problem we could have been left without a backup when we needed it.Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-80605462809960370412016-05-31T08:47:00.000-04:002016-05-31T08:47:04.364-04:00Indiscriminate use of CTEs considered harmfulCommon Table Expressions are a wonderful thing. Not only are they indespensible for creating recursive queries, but they can be a powerful tool in creating complex queries that are comprehensible. It's very easy to get lost in a fog of sub-sub-sub-queries, so using CTEs as a building block can make things a lot nicer.<br />
<br />
However, there is one aspect of the current implementation of CTEs that should make you pause. Currently CTEs are in effect materialized before they can be used. That is, Postgres runs the query and stashes the data in a temporary store before it can be used in the larger query. There are a number of consequences of this.<br />
<br />
First, this can be a good thing. I have on a number of occasions used this fact to good effect to get around problems with poorly performing query plans. It's more or less the same effect as putting "offset 0" on a subquery.<br />
<br />
However, it can also result in some very inefficient plans. In particular, if CTEs return a lot of rows they can result in some spectacularly poorly performing plans at the point where you come to use them. Note also that any indexes on the underlying tables will be of no help to you at all at this point, since you are no longer querying against those tables but against the intermediate result mentioned above, which has no indexes at all.<br />
<br />
This was brought home to me forcefully on Friday and Saturday when I was looking at a very poorly performing query. After some analysis and testing, the simple act of inlining two CTEs in the query in question resulted in the query running in 4% of the time it had previously taken. Indiscriminate use of CTEs had made the performance of this query 25 times worse.<br />
<br />
So the moral is: be careful in using CTEs. They are not just a convenient tool for abstracting away subqueries.<br />
<br />
There has been some discussion about removing this aspect of the implementation of CTEs. It's not something that is inherent in CTEs, it's simply a part of the way we have implemented them in PostgreSQL. However, for now, you need to be familiar with the optimization effects when using them, or you might face the same problem I was dealing with above.<br />
<br />
<br />Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com6tag:blogger.com,1999:blog-2356137376934964551.post-83819184365120125752016-04-29T08:34:00.000-04:002016-04-29T08:34:00.645-04:00Windows XP - PostgreSQL's long goodbye.I just committed a patch to allow building with Visual Studio 2015. Due to a change in the way we need to detect locales with this compiler, it will not be possible to use binaries built with it on Windows XP. Despite the fact that Microsoft declared Windows XP to be completely out of support well over a year ago, it still lives on in a huge number of installations. My own instance still gets occasional updates from Microsoft. And you can still build and run the very latest PostgreSQL on Windows XP. But you can't use it on Windows XP if it's built with Visual Studio 2015 or later.<br />
<br />
I will keep my instance (whose only job is to run several buildfarm members) running as long as it doesn't require any significant tweaks. But a day will eventually come when it does require such tweaks, or just won't build and run successfully any more, and at that stage I'll shut it down.<br />
<br />
<br />Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-54448433616312298732016-04-17T11:39:00.000-04:002016-04-17T11:39:45.850-04:00Building an extension with VS2015I needed to get a DLL prepared of my tiny <a href="https://bitbucket.org/adunstan/pg-closed-ranges">closed format ranges extension</a>, and so I tried following the <a href="http://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows/">excellent instructions Craig Ringer gave</a> a couple of years ago. I was using Visual Studio 2015, on a Windows 10 box, targeting PostgreSQL 9.4, 64bit.<br />
<br />
But I ran into some problems. I could compile the thing quite cleanly, but Postgres couldn't find the functions when I tried to create the extension. I had a short and very helpful email exchange with Craig, and came up with this solution: the exported functions needed to be declared with a prototype before the call to PG_FUNCTION_INFO_V1, and both the prototype and the function definition needed to be decorated with PGDLLEXPORT. Once I had done that, the DLL loaded when I ran "create extension", and the types worked as expected.<br />
<br />
I don't know if this is required with any earlier versions of Visual Studio, or even of it works with them. When I get a chance I will see if I can find out.<br />
<br />
Based on this, I think it's probably worth trying at some stage to put together a script to build the project and solution files for extensions. I don't have time to do that right now, however.Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com1tag:blogger.com,1999:blog-2356137376934964551.post-7340600323577062872016-03-24T15:08:00.000-04:002016-03-24T15:08:39.081-04:00Weird stuff happensFive days ago, my buildfarm animal jacana suddenly started getting an error while trying to extract typedefs. It had been happily doing this for ages, and suddenly the quite reproducible error started. For now I have disabled its typedef analysis, but I will need to get to the bottom of it. It's bad enough to crash the buildfarm client leaving the build directory dirty and not able to process further builds until I clean it up. I'm assuming it's probably something that changed in the source code, as nothing else has changed at all. These are the commits that took place between a good run and the first appearance of the error.<br />
<br />
<ul>
<li>9a83564 Allow SSL server key file to have group read access if owned by root</li>
<li>6eb2be1 Fix stupid omission in c4901a1e.</li>
<li>07aed46 Fix missed update in _readForeignScan().</li>
<li>ff0a7e6 Use yylex_init not yylex_init_extra().</li>
<li>a3e39f8 Suppress FLEX_NO_BACKUP check for psqlscanslash.l.</li>
<li>0ea9efb Split psql's lexer into two separate .l files for SQL and backslash cases.</li>
</ul>
I don't know for dead certain that any of these has caused an issue, but finding out what the problem is is just one more way for me to spend my copious free time.Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-62623202515869700702016-03-19T17:54:00.000-04:002016-03-19T20:16:51.322-04:00GIN indexing an array of enumsGIN indexes of arrays can be pretty useful. And arrays of enums can also be pretty useful. Unfortunately, there is no built in GIN indexing of arrays of enums, and in fact there is no pseudo-type for arrays of enums. So, while we can declare an operator class for supporting a type of "anyenum", which I did in my recent work on btree_gin and btree_gist support for enums, we can't declare one for support of "anyenum[]".<br />
<br />
However, we can declare one for support of a concrete enum type's array type. And all the pieces are already there.<br />
<br />
After a lot of reading and experimentation, Here's what I found that seems to work just like the builtin GIN array operator classes.<br />
<br />
Given an enum type of animal, you can declare an operator class for its array type like this (note how almost completely generic this is):<br />
<br />
<blockquote><pre>create operator class _animal_ops
default for type public.animal[]
using gin
family array_ops as
function 1 enum_cmp(anyenum,anyenum),
function 2 pg_catalog.ginarrayextract(anyarray, internal),
function 3 ginqueryarrayextract(anyarray, internal, smallint, internal,
internal, internal, internal),
function 4 ginarrayconsistent(internal, smallint, anyarray, integer,
internal, internal, internal, internal),
function 6 ginarraytriconsistent(internal, smallint, anyarray, integer,
internal, internal, internal),
storage oid ;
</pre></blockquote><br />
and here it is working:<br />
<blockquote><pre>andrew=# \d animal_classes
Table "public.animal_classes"
Column | Type | Modifiers
---------+----------+-----------
class | integer |
animals | animal[] |
andrew=# select * from animal_classes where '{lion}' <@ (animals);
class | animals
-------+--------------
1 | {lion,zebra}
andrew=# set enable_seqscan = off;
SET
andrew=# create index animal_classes_animals_idx on animal_classes using gin(animals);
CREATE INDEX
andrew=# explain (costs off) select * from animal_classes where '{lion}' <@ (animals);
QUERY PLAN
-------------------------------------------------------
Bitmap Heap Scan on animal_classes
Recheck Cond: ('{lion}'::animal[] <@ animals)
-> Bitmap Index Scan on animal_classes_animals_idx
Index Cond: ('{lion}'::animal[] <@ animals)
(4 rows)
andrew=# select * from animal_classes where '{lion}' <@ (animals);
class | animals
-------+--------------
1 | {lion,zebra}
(1 row)
andrew=#
</pre>
</blockquote>Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-67631046384213380672016-03-19T10:15:00.000-04:002016-03-19T10:15:35.827-04:00Gist and Gin support for enumsRecently I submitted a patch (too late for release 9.6, unfortunately) to add support for enum data types to the btree_gist and btree_gin additional modules. This came out of a desire to be able to use an enum field in an exclusion constraint. That really only requires GiST support, but since I was adding enum support to btree_gist it seemed a good idea to add it to btree_gin as well, so that, for example, enum fields can be used in multi-column GIN indexes.<br />
<br />
Here's an exclusion constraint example using enums, adapted from the Postgres docs.<br />
<br />
<blockquote><pre>andrew=# create extension btree_gist;
CREATE EXTENSION
andrew=# \dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+-----------------------------------------------
btree_gist | 1.2 | public | support for indexing common datatypes in GiST
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
andrew=# create type animal as enum ('zebra','lion');
CREATE TYPE
andrew=# create table zoo (cage int, exhibit animal, exclude using gist(cage with =, exhibit with <>));
CREATE TABLE
andrew=# INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1
andrew=# INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1
andrew=# INSERT INTO zoo VALUES(123, 'lion');
ERROR: conflicting key value violates exclusion constraint "zoo_cage_exhibit_excl"
DETAIL: Key (cage, exhibit)=(123, lion) conflicts with existing key (cage, exhibit)=(123, zebra).
andrew=#
</pre></blockquote>Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com5tag:blogger.com,1999:blog-2356137376934964551.post-60521685564967682002016-03-08T12:29:00.001-05:002016-03-08T12:29:33.891-05:00Json dates, times, and binary dataIf I had to pick one thing I would change about the JSON spec it's this: they should allow timestamps and dates as primitive values. It's a very common complaint. Another thing they should have provided for is binary data. This gets a bit annoying if you convert a record containing a bytea to json and hand it to the client. They see a string like '\x71951ce4c84c0b4de861377f27a4938a'. If you can, it's probably nicer to encode your bytea before converting it to json, using encode(mybytea,'base64') or encode(mybytea,'hex'). Then your client might see a string for which they have a standard routine to convert to binary, rather than having to strip off the the initial '\x' before converting it.Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-90622994787325755632016-02-20T19:17:00.000-05:002016-02-20T19:17:34.042-05:00Release 4.17 of the PostgreSQL Buildfarm clientI have just cut release 4.17 of the PostgreSQL Buildfarm client. It is available at <a class="moz-txt-link-rfc2396E" href="http://www.pgbuildfarm.org/downloads/latest-client.tgz">http://www.pgbuildfarm.org/downloads/latest-client.tgz</a>. <br />
<br />
Changes of note: <br />
<ul><li>use PGCTLTIMEOUT instead of hardcoded timeout settings</li>
<li>shipped config file is renamed to build-farm.conf.sample to avoid</li>
<li>overwriting a default config file</li>
<li>use configured make command in make_bin_installcheck</li>
<li>restrict restart sleeps to Windows platforms</li>
<li>fix trigger_exclude pattern in build-farm.conf</li>
<li>protect git checkout with a lock if using git_use_workdirs</li>
</ul><br />
For the most part, the thing of significance it the PGCTLTIMEOUT change, which follows up on some changes in core code. The new code defaults this setting to 120, but it can be set in the config file. If you don't need any of the other changes (most people probably won't) then just adding something like <br />
<br />
<blockquote><pre>PGCTLTIMEOUT => '120',</pre></blockquote><br />
to the build_env stanza of your config file should do the trick, and you can happily wait for the next release. Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-9133514069457621222016-01-25T11:33:00.000-05:002016-01-25T11:33:30.597-05:00Fixed length record filesI've had a few requests recently for support for the <a href="https://github.com/adunstan/file_fixed_length_record_fdw">Fixed Length File FDW</a> that I created as an experiment about 5 years ago. Apparently people are still getting data from COBOL programs or some such source. The code has bitrotted some in the meanwhile, so I'll try to grab a little time to update it, add some test cases and docco, etc.<br />
<br />
Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-60865343453707300912016-01-19T09:02:00.000-05:002016-01-19T09:02:53.069-05:00Buildfarm server movingToday we're moving the buildfarm server to a new machine and more modern Postgres. Here is yesterday's (corrrected) announcement:<br />
<br />
<blockquote>Apologies for the late notice. <br />
<br />
Tomorrow, January 19th, at 4.00 pm US East Coast time (UT - 5.0) we will be moving the buildfarm server from its current home at CommandPrompt, where we have been ever since we started, to a machine that is part of the standard core infrastructure. In doing so we will be moving to a) a more modern and supported PostgreSQL version, and b) a machine with more disk space so that our current severe pace shortage will be alleviated. In addition, the community would be much better placed to maintain the buildfarm if either JD or I were to fall under a bus. <br />
<br />
The outage is expected to last about 4 hours or less, and we will sent out notifications when this is complete. <br />
<br />
Buildfarm owners who want to avoid getting reporting failures should disable their animals during that time. We don't have an avalanche of commits right now either, but it might also be nice if committers were to refrain from adding changes in the hours leading up to this and until we announce that we're back online, for the benefit of those owners who don't see this message in time. <br />
<br />
Thanks in advance for your help and understanding. <br />
<br />
And many thanks to CommandPrompt for their constant support over the many years we've been in operation.<br />
</blockquote><br />
In a few hours I will start disabling my 12 buildfarm members.<br />
Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com0tag:blogger.com,1999:blog-2356137376934964551.post-49454510262089631962016-01-15T08:52:00.000-05:002016-01-15T08:52:24.702-05:00Using PostgreSQL 9.5's IMPORT FOREIGN SCHEMAThis is a pretty nice feature in 9.5. The other day I needed to look at some data from a WordPress site that's stored in MySQL. I had a dump of the data, but I'm not terribly proficient at using MySQL tools or their SQL dialect, so I thought I would try out just importing the schema to PostgreSQL using the new IMPORT FOREIGN SCHEMA command. It worked pretty well. First I installed the Postgres rpms from the community repository. Unfortunately, the repository doesn't have the latest mysql foreign data wrapper for 9.5, so I cloned it from <a href="https://github.com/EnterpriseDB/mysql_fdw">github</a> and built and installed it without difficulties. Then I restored the backup into the test database on local mysql instance. Then there was a small glitch. When I tried to import the schema it complained that it didn't know about the type "tinytext". So I created a domain for this that just mapped it to text. and then re-ran the import, and it worked just fine. Here is the whole import session:<br />
<blockquote>
<pre>[andrew@dino mysql_fdw]$ psql wp
psql (9.5.0)
Type "help" for help.
wp=# create extension mysql_fdw;
CREATE EXTENSION
wp=# CREATE SERVER mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '127.0.0.1', port '3306');
CREATE SERVER
wp=# create user mapping for andrew server mysql_server;
CREATE USER MAPPING
wp=# import FOREIGN SCHEMA test from server mysql_server into public;
ERROR: type "tinytext" does not exist
LINE 4: comment_author tinytext NOT NULL,
^
QUERY: CREATE FOREIGN TABLE wp_comments (
"comment_ID" bigint NOT NULL,
"comment_post_ID" bigint NOT NULL,
comment_author tinytext NOT NULL,
comment_author_email varchar NOT NULL,
comment_author_url varchar NOT NULL,
"comment_author_IP" varchar NOT NULL,
comment_date timestamp NOT NULL,
comment_date_gmt timestamp NOT NULL,
comment_content text NOT NULL,
comment_karma int NOT NULL,
comment_approved varchar NOT NULL,
comment_agent varchar NOT NULL,
comment_type varchar NOT NULL,
comment_parent bigint NOT NULL,
user_id bigint NOT NULL
) SERVER mysql_server OPTIONS (dbname 'test', table_name 'wp_comments');
CONTEXT: importing foreign table "wp_comments"
wp=# create domain tinytext as text;
CREATE DOMAIN
wp=# import FOREIGN SCHEMA test from server mysql_server into public;
IMPORT FOREIGN SCHEMA
wp=#
</pre>
</blockquote>
Andrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.com1