Re: [HACKERS] pgbench is broken on strict-C89 compilers
On 2014-05-18 00:36:34 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2014-05-17 19:15:15 -0400, Tom Lane wrote: ... It appears to me that the compiler is within its rights to refuse a nonconstant expression for an inner initializer according to C89, though I don't see any such restriction in C99. Yea, I've complained about it in http://www.postgresql.org/message-id/20140403152834.gg17...@awork2.anarazel.de Ah. I'd sort of ignored that patch because it didn't seem too relevant to the issues we were discussing at the time. Well, I wanted to start a animal that quickly warns about the nameless union stuff. That'd require a sensibly clean build. That piece code is also confused about using static vs. const. For a lot longer though... Well, static is also a good thing here because it eliminates the need for runtime initialization of a function-local array variable. But yeah, the code is way under-const-ified as well. Well, const alone would do that too; without requiring checks on every function invocation. The const variant should end up in the readonly part of the binary... I'd just duplicated the ddl structs. Seemed to be the least ugly thing I could come up with. For from pretty tho. It works, anyway. If I don't think of something better, I'll do a bit more polishing and commit that tomorrow. Ok, thanks. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] btree_gist macaddr valgrind woes
On 05/18/2014 12:23 AM, Tom Lane wrote: A larger issue is that we evidently have no buildfarm animals that are picky about alignment, or at least none that are running a modern-enough buildfarm script to be running the contrib/logical_decoding test. That seems like a significant gap. I don't want to volunteer to run a critter on my HPPA box: it's old enough, and eats enough electricity, that I no longer want to leave it on 24x7. Plus a lot of the time its response to a bus error is to lock up in a tight loop rather than report an error, so a failure wouldn't get reported usefully by the buildfarm anyway. Does anyone have an ARM or PPC box where they can configure the kernel not to mask misaligned fetches? I did echo 4 /proc/cpu/alignment on chipmunk - let's see what it crops up. In quick testing with a little test program, it looks like an unaligned access to a 32-bit int still works without error. But an unaligned access to a 64-bit long long causes a SIGBUS now. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] vacuumdb --all --analyze-in-stages - wrong order?
Hello I am looking on --analyze-in-stages option. If I understand well, motivation for this option is a get some minimal statistic for databases in minimal time. But when I tested, I found so iterations are per databases, not per stages - some first database get a maximum statistics and second has zero statistics. Isn't it unpractical? Now: DB a stage 1, stage2, stage3 DB b stage 1, stage2, stage3 should be: Stage1 DB a, DB b ... Stage 2 DB a, DB b ... Stage 3 DB a, DB b, .. Regards Pavel
Re: [HACKERS] 9.4 beta1 crash on Debian sid/i386
Re: Tom Lane 2014-05-18 9058.1400385...@sss.pgh.pa.us Christoph Berg c...@df7cb.de writes: Re: Tom Lane 2014-05-14 1357.1400028...@sss.pgh.pa.us It would appear that something is wrong with check_stack_depth(), and/or getrlimit(RLIMIT_STACK) is lying to us about the available stack. ulimit -s is 8192 (kB); max_stack_depth is 2MB. check_stack_depth looks right, max_stack_depth_bytes there is 2097152 and I can see stack_base_ptr - stack_top_loc grow over repeated invocations of the function (stack_depth itself is optimized out). Still, it never enters if (stack_depth max_stack_depth_bytes...). Hm. Did you check that stack_base_ptr is non-NULL? If it were somehow not getting set, that would disable the error report. But on most architectures that would also result in silly values for the pointer difference, so I doubt this is the issue. stack_base_ptr was non-NULL. The stack size started around 3 or 5kB (don't remember exactly), and grew by something like a few 100B in each iteration, so this looked sane. Interestingly, the Debian buildd managed to run the testsuite for i386, while I could reproduce the problem on the pgapt build machine and on my notebook, so there must be some system difference. Possibly the reason is these two machines are running a 64bit kernel and I'm building in a 32bit chroot, though that hasn't been a problem before. I'm suspicious that something has changed in your build environment, because that stack-checking logic hasn't changed since these commits: It's something in the combination of build and runtime environment. I can reproduce the problem in the package that the Debian i386/experimental buildd has compiled, including passing the regression tests there. Possibly a change in libc there. I'll try to ask some kernel/libc people if they have an idea. My current bet is on the gcc hardening flags we are using. The lack of reports from the buildfarm or other users is also evidence against there being a widespread issue here. The only animal running Debian testing/unstable I can see is dugong, which is ia64 - which has been removed from Debian some months ago. I guess I should look into setting up a new animal for this. A different thought: I have heard of environments in which the available stack depth is much less than what ulimit would suggest because the ulimit space gets split up for multiple per-thread stacks. That should not be happening in a Postgres backend, since we don't do threading, but I'm running out of ideas to investigate ... I've done some builds now and there's no clear picture yet when the problem is occurring. Still trying... Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 beta1 crash on Debian sid/i386
On 2014-05-18 11:08:34 +0200, Christoph Berg wrote: Interestingly, the Debian buildd managed to run the testsuite for i386, while I could reproduce the problem on the pgapt build machine and on my notebook, so there must be some system difference. Possibly the reason is these two machines are running a 64bit kernel and I'm building in a 32bit chroot, though that hasn't been a problem before. I'm suspicious that something has changed in your build environment, because that stack-checking logic hasn't changed since these commits: It's something in the combination of build and runtime environment. I can reproduce the problem in the package that the Debian i386/experimental buildd has compiled, including passing the regression tests there. Possibly a change in libc there. I'll try to ask some kernel/libc people if they have an idea. My current bet is on the gcc hardening flags we are using. As another datapoint: I don't see the problem on 32bit packages build with a 64bit gcc with -m32 on debian unstable on my laptop. Didn't try hard though. Did you measure how large the stack actually was when you got the SIGBUS? Should be possible to determine that by computing the offset using some local stack variable in one of the depeest stack frames. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Allowing join removals for more join types
On Sat, May 17, 2014 at 8:57 PM, David Rowley dgrowle...@gmail.com wrote: I'm currently in the early stages of looking into expanding join removals. Currently left outer joins can be removed if none of the columns of the table are required for anything and the table being joined is a base table that contains a unique index on all columns in the join clause. The case I would like to work on is to allow sub queries where the query is grouped by or distinct on all of the join columns. Take the following as an example: CREATE TABLE products (productid integer NOT NULL, code character varying(32) NOT NULL); CREATE TABLE sales (saleid integer NOT NULL, productid integer NOT NULL, qty integer NOT NULL); CREATE VIEW product_sales AS SELECT p.productid, p.code, s.qty FROM (products p LEFT JOIN ( SELECT sales.productid, sum(sales.qty) AS qty FROM sales GROUP BY sales.productid) s ON ((p.productid = s.productid))); If a user does: SELECT productid,code FROM product_sales; Then, if I'm correct, the join on sales can be removed. Attached is a patch which implements this. It's still a bit rough around the edges and some names could likely do with being improved, but it at least seems to work with all of the test cases that I've thrown at it so far. Comments are welcome, but the main purpose of the email is so I can register the patch for the June commitfest. Regards David Rowley subquery_leftjoin_removal_v0.5.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] buildfarm: strange OOM failures on markhor (running CLOBBER_CACHE_RECURSIVELY)
Andres Freund and...@2ndquadrant.com writes:
On 2014-05-17 22:55:14 +0200, Tomas Vondra wrote:
And another memory context stats for a session executing CREATE INDEX,
while having allocated The interesting thing is there are ~11k lines
that look exactly like this:
pg_namespace_oid_index: 1024 total in 1 blocks; 88 free (0 chunks); 936 used
Heh. That certainly doesn't look right. I bet it's the contexts from
RelationInitIndexAccessInfo().
Looks like there generally are recursion 'troubles' with system
indexes. RelationClearRelation() will mark them as invalid causing any
further lookup to do RelationBuildDesc() calls. Which will again rebuild
the same relation if it's used somewhere inside RelationBuildDesc() or
RelationInitIndexAccessInfo(). From a quick look it looks like it should
resolve itself after some time. Even freeing the superflous memory
contexts. But I am not sure if there scenarios where that won't
happen...
I've identified a couple of memory leakage scenarios in relcache.c:
1. The RelationCacheInsert macro just blithely ignores the possibility
that hash_search(HASH_ENTER) returns found = true. There's a comment
/* used to give notice if found -- now just keep quiet */
which AFAICT was there when we got the code from Berkeley. Well, it
turns out that that's a can-happen scenario, at least for a few system
catalogs and indexes that are referenced during relcache build. The
scenario is that we come in through RelationIdGetRelation, don't find
a cache entry for, say, pg_index, and set about building one in
RelationBuildDesc. Somewhere in the guts of that we have need to read
pg_index, whereupon there's a recursive call of RelationIdGetRelation,
which still doesn't find the entry, so we again call RelationBuildDesc
which builds an entirely separate Relation structure. The recursion
does terminate thanks to the recursion-stopping provisions in relcache.c,
so the inner call finishes and enters its completed Relation structure
into the RelationIdCache hashtable. Control returns out to the outer
invocation of RelationBuildDesc, which finishes, and enters its completed
Relation structure into the RelationIdCache hashtable --- overwriting the
link to the Relation made by the inner invocation. That Relation and
subsidiary data are now unreferenced and permanently leaked in
CacheMemoryContext. If it's an index you can see its leaked subsidiary
rd_indexcxt in a memory dump, which is what we're looking at above.
AFAICT, the inner invocation's Relation should always have zero reference
count by the time we get back to the outer invocation. Therefore it
should be possible for RelationCacheInsert to just delete the
about-to-be-unreachable Relation struct. I'm experimenting with a patch
that adds logic like this to RelationCacheInsert:
if (found)
{
Relation oldrel = idhentry-reldesc;
idhentry-reldesc = RELATION;
if (RelationHasReferenceCountZero(oldrel))
RelationDestroyRelation(oldrel, false);
else
elog(WARNING, leaking still-referenced duplicate relation);
}
and so far it looks good.
2. There's a much smaller leak in AttrDefaultFetch: it doesn't
bother to pfree the result of TextDatumGetCString(). This leakage
occurs in the caller's context not CacheMemoryContext, so it's only
query lifespan not session lifespan, and it would not ordinarily be
significant --- but with the CLOBBER_CACHE logic enabled, we rebuild
some relcache entries a damn lot of times within some queries, so the
leak adds up.
With both of these things fixed, I'm not seeing any significant memory
bloat during the first parallel group of the regression tests. I don't
think I'll have the patience to let it run much further than that
(the uuid and enum tests are still running after an hour :-().
BTW, it strikes me that we could probably improve the runtime of the
CLOBBER tests noticeably if we were to nail AttrDefaultIndexId,
IndexIndrelidIndexId, and ConstraintRelidIndexId into cache. I see
no very good reason not to do that; it should help performance a bit
in normal cases too.
While I'm at it: I could not help noticing RememberToFreeTupleDescAtEOX,
which was not there last time I looked at this code. Isn't that broken
by design? It's basically a deliberately induced transaction-lifespan
memory leak, and AFAICS if it does anything at all, it's supporting
incorrect calling code. There should *never* be any situation where it's
not okay to free a tupledesc with zero refcount. And the comment
justifying it is insane on its face:
* If we Rebuilt a relcache entry during a transaction then its
* possible we did that because the TupDesc changed as the result of
* an ALTER TABLE that ran at less than AccessExclusiveLock. It's
* possible someone copied that TupDesc, in which case the copy would
* point to free'd memory. So if we rebuild an entry we keep the
If someone copied the
Re: [HACKERS] 9.4 release notes
On 05/05/2014 07:26 PM, Andrew Dunstan wrote: On 05/05/2014 07:16 PM, Bruce Momjian wrote: Current text is: Add structured (non-text) data type (JSONB) for storing JSON data (Oleg Bartunov, Teodor Sigaev, Alexander Korotkov, Peter Geoghegan, and Andrew Dunstan) This allows for faster access to values in the JSON document and faster and more useful indexing of JSON. JSONB values are also typed as appropriate scalar SQL types. Is that OK? No. If you must say something then start the last sentence with Scalar values in JSONB documents are typed I still think we should make this change. Does anyone object if I do? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] %d in log_line_prefix doesn't work for bg/autovacuum workers
Andres Freund wrote: On 2014-05-17 23:35:43 +0200, Christoph Berg wrote: Fwiw, this wasn't the first time I've heard of that idea, it also doesn't sound too far-fetched for me. I guess people usually go damn, I can't rename active dbs, let's try something else instead of complaining on the mailing lists in that case. Hm. http://www.postgresql.org/message-id/1305688547-sup-7...@alvh.no-ip.org -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 beta1 crash on Debian sid/i386
Re: Andres Freund 2014-05-18 20140518091445.gu23...@alap3.anarazel.de Did you measure how large the stack actually was when you got the SIGBUS? Should be possible to determine that by computing the offset using some local stack variable in one of the depeest stack frames. Looking at /proc/*/maps, the stack is ffb38000-ffd1e000 = 1944kB for a process that just got SIGBUS. This seems to be in line with stack_base_ptr = 0xffd1c317 and the fcinfo address in #0 hashname (fcinfo=fcinfo@entry=0xffb38024) at /build/postgresql-9.4-4lNBaG/postgresql-9.4-9.4~beta1/build/../src/backend/access/hash/hashfunc.c:143 (Things work fine when I set max_stack_depth = '1900kB'.) Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 beta1 crash on Debian sid/i386
Christoph Berg c...@df7cb.de writes: Re: Andres Freund 2014-05-18 20140518091445.gu23...@alap3.anarazel.de Did you measure how large the stack actually was when you got the SIGBUS? Should be possible to determine that by computing the offset using some local stack variable in one of the depeest stack frames. Looking at /proc/*/maps, the stack is ffb38000-ffd1e000 = 1944kB for a process that just got SIGBUS. This seems to be in line with stack_base_ptr = 0xffd1c317 and the fcinfo address in OK, so the problem is that getrlimit(RLIMIT_STACK) is lying to us about the available stack depth. I'd classify that as a kernel bug. I wonder if it's a different manifestation of this issue: https://bugzilla.redhat.com/show_bug.cgi?id=952946 A different line of thought is that if ulimit -s is 8192, why are we not getting 8MB of stack? But in any case, if we're only going to get 1944kB, getrlimit ought to tell us that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 beta1 crash on Debian sid/i386
On 2014-05-18 17:41:17 -0400, Tom Lane wrote: Christoph Berg c...@df7cb.de writes: Re: Andres Freund 2014-05-18 20140518091445.gu23...@alap3.anarazel.de Did you measure how large the stack actually was when you got the SIGBUS? Should be possible to determine that by computing the offset using some local stack variable in one of the depeest stack frames. Looking at /proc/*/maps, the stack is ffb38000-ffd1e000 = 1944kB for a process that just got SIGBUS. This seems to be in line with stack_base_ptr = 0xffd1c317 and the fcinfo address in OK, so the problem is that getrlimit(RLIMIT_STACK) is lying to us about the available stack depth. I'd classify that as a kernel bug. I wonder if it's a different manifestation of this issue: https://bugzilla.redhat.com/show_bug.cgi?id=952946 That'd explain why I couldn't reproduce it. And I seme to recall some messages about the hardening stuff in debian accidentally being lost some time ago. So if that got re-introduced into 9.4... The CFLAGS certainly indicate that -pie is getting used. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 beta1 crash on Debian sid/i386
Andres Freund and...@2ndquadrant.com writes: On 2014-05-18 17:41:17 -0400, Tom Lane wrote: OK, so the problem is that getrlimit(RLIMIT_STACK) is lying to us about the available stack depth. I'd classify that as a kernel bug. I wonder if it's a different manifestation of this issue: https://bugzilla.redhat.com/show_bug.cgi?id=952946 That'd explain why I couldn't reproduce it. And I seme to recall some messages about the hardening stuff in debian accidentally being lost some time ago. So if that got re-introduced into 9.4... The CFLAGS certainly indicate that -pie is getting used. Yeah. Re-reading the Red Hat bug, it seems like an exact match for this issue. The dependency on ASLR means that the identical run might sometimes work and sometimes crash, which would explain why Christoph was getting less-than-consistent results. The bad news is that the kernel guys have been ignoring the issue for over a year. Dunno if some pressure from the Debian camp would help raise their priority for this. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 beta1 crash on Debian sid/i386
On 2014-05-18 23:52:32 +0200, Andres Freund wrote: On 2014-05-18 17:41:17 -0400, Tom Lane wrote: Christoph Berg c...@df7cb.de writes: Re: Andres Freund 2014-05-18 20140518091445.gu23...@alap3.anarazel.de Did you measure how large the stack actually was when you got the SIGBUS? Should be possible to determine that by computing the offset using some local stack variable in one of the depeest stack frames. Looking at /proc/*/maps, the stack is ffb38000-ffd1e000 = 1944kB for a process that just got SIGBUS. This seems to be in line with stack_base_ptr = 0xffd1c317 and the fcinfo address in OK, so the problem is that getrlimit(RLIMIT_STACK) is lying to us about the available stack depth. I'd classify that as a kernel bug. I wonder if it's a different manifestation of this issue: https://bugzilla.redhat.com/show_bug.cgi?id=952946 That'd explain why I couldn't reproduce it. And I seme to recall some messages about the hardening stuff in debian accidentally being lost some time ago. So if that got re-introduced into 9.4... The CFLAGS certainly indicate that -pie is getting used. Indeed. If I add -pie to my 32bit vpath's configure invocation it crashes, too. Not that that helps much to resolve the bug, given it's been sedentary for a long while :(. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 beta1 crash on Debian sid/i386
On 2014-05-18 17:56:48 -0400, Tom Lane wrote: The bad news is that the kernel guys have been ignoring the issue for over a year. Dunno if some pressure from the Debian camp would help raise their priority for this. I guess we should forward the bug to the lkml/linux-mm lists. I think a fair number of people involved in those areas won't read RH bugzilla without pointed towards it, err, pointedly. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] cosmetic fixes
Hi,
Here are some more trivial fixes in pg_recvlogical message style.
--
Euler Taveira Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
From 0bbf437b490a92afa4b14e4903188bcf795f8e47 Mon Sep 17 00:00:00 2001
From: Euler Taveira eu...@timbira.com
Date: Sun, 18 May 2014 20:26:00 -0300
Subject: [PATCH] Style fixes.
Lowercase help statements. Also, use an existing statement to reduce the
number of strings to be translated.
---
src/bin/pg_basebackup/pg_recvlogical.c | 6 +++---
1 file changed, 3 insertions(+), 3 deletions(-)
diff --git a/src/bin/pg_basebackup/pg_recvlogical.c b/src/bin/pg_basebackup/pg_recvlogical.c
index a585303..651cc40 100644
--- a/src/bin/pg_basebackup/pg_recvlogical.c
+++ b/src/bin/pg_basebackup/pg_recvlogical.c
@@ -83,13 +83,13 @@ usage(void)
printf(_( -F --fsync-interval=SECS\n
frequency of syncs to the output file (default: %d)\n), (fsync_interval / 1000));
printf(_( -o, --option=NAME[=VALUE]\n
- Specify option NAME with optional value VALUE, to be passed\n
+ specify option NAME with optional value VALUE, to be passed\n
to the output plugin\n));
printf(_( -P, --plugin=PLUGINuse output plugin PLUGIN (default: %s)\n), plugin);
printf(_( -s, --status-interval=SECS\n
time between status packets sent to server (default: %d)\n), (standby_message_timeout / 1000));
printf(_( -S, --slot=SLOTuse existing replication slot SLOT instead of starting a new one\n));
- printf(_( -I, --startpos=PTR Where in an existing slot should the streaming start\n));
+ printf(_( -I, --startpos=PTR where in an existing slot should the streaming start\n));
printf(_(\nAction to be performed:\n));
printf(_( --create create a new replication slot (for the slotname see --slot)\n));
printf(_( --startstart streaming in a replication slot (for the slotname see --slot)\n));
@@ -935,7 +935,7 @@ main(int argc, char **argv)
if (sscanf(PQgetvalue(res, 0, 1), %X/%X, hi, lo) != 2)
{
fprintf(stderr,
- _(%s: could not parse log location \%s\\n),
+ _(%s: could not parse transaction log location \%s\\n),
progname, PQgetvalue(res, 0, 1));
disconnect_and_exit(1);
}
--
2.0.0.rc0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \db+ lack of size column
On Fri, May 16, 2014 at 2:03 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: Hi all, Are there some reason to don't show the tablespace size in the \db+ psql command? The attached patch show tablespace size in \db+ psql command. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 951b7ee..282cd43 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -176,6 +176,11 @@ describeTablespaces(const char *pattern, bool verbose) ,\n spcoptions AS \%s\, gettext_noop(Options)); + if (verbose pset.sversion = 90200) + appendPQExpBuffer(buf, + ,\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \%s\, + gettext_noop(Size)); + if (verbose pset.sversion = 80200) appendPQExpBuffer(buf, ,\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \%s\, -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [GENERAL] Is it typo in pg_stat_replication column name in PG 9.4 ?
On 18-05-2014 05:40, Raghavendra wrote: Hi, PostgreSQL 9.4 document for pg_stat_replication view mentions column name as backend_xid, whereas when a view described it shows column name as backend_xmin. http://www.postgresql.org/docs/devel/static/monitoring-stats.html#PG-STAT-REPLICATION-VIEW postgres=# select version(); -[ RECORD 1 ] version | PostgreSQL 9.4beta1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit postgres=# \d pg_stat_replication View pg_catalog.pg_stat_replication Column | Type | Modifiers --+--+--- pid | integer | usesysid | oid | usename | name | application_name | text | client_addr | inet | client_hostname | text | client_port | integer | backend_start| timestamp with time zone | * backend_xmin* | xid | state| text | sent_location| pg_lsn | write_location | pg_lsn | flush_location | pg_lsn | replay_location | pg_lsn | sync_priority| integer | sync_state | text | Is it a typo in pg_stat_replication view column ? Hi, I think this is an incorrect list... moving to pgsql-hackers And yes, this seems to be a typo. Patch attached! Regards, -- Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index ffdf0c5..1c05243 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -1494,7 +1494,7 @@ postgres: replaceableuser/ replaceabledatabase/ replaceablehost/ re /entry /row row - entrystructfieldbackend_xid/structfield/entry + entrystructfieldbackend_xmin/structfield/entry entrytypexid/type/entry entryThis standby's literalxmin/ horizon reported by xref linkend=guc-hot-standby-feedback./entry -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 release notes
On Fri, May 16, 2014 at 02:10:40AM +0200, Andres Freund wrote: On 2014-05-04 08:46:07 -0400, Bruce Momjian wrote: I have completed the initial version of the 9.4 release notes. You can view them here: http://www.postgresql.org/docs/devel/static/release-9-4.html I will be adding additional markup in the next few days. Feedback expected and welcomed. I expect to be modifying this until we release 9.4 final. I have marked items where I need help with question marks. This time I started reading from the end. I think I've fixed most of the questionable things (i.e. ? or FIXMEs) left. I adjusted your patch and applied it. Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + diff --git a/doc/src/sgml/release-9.4.sgml b/doc/src/sgml/release-9.4.sgml new file mode 100644 index 3070d0b..91a586f *** a/doc/src/sgml/release-9.4.sgml --- b/doc/src/sgml/release-9.4.sgml *** *** 30,36 listitem para link linkend=logicaldecodingLogical decoding/link allows database !changes to be streamed out in customizable format /para /listitem --- 30,36 listitem para link linkend=logicaldecodingLogical decoding/link allows database !changes to be streamed out in a customizable format /para /listitem *** *** 298,303 --- 298,309 /para /listitem + listitem + para + commandDISCARD ALL/ now also discards the state of sequences. + /para + /listitem + /itemizedlist /sect2 *** *** 1005,1011 /para para - !-- FIXME: drop? -- This was added so views that select from a table with zero columns can be dumped correctly. /para --- 1011,1016 *** *** 1028,1034 /para para - !-- FIXME: compatibility break entry? -- commandDISCARD ALL/ will now also discard such information. /para /listitem --- 1033,1038 *** *** 1199,1204 --- 1203,1213 AGGREGATE//link to supply the size of the aggregate's transition state data (Hadi Moshayedi) /para + +para + This allows the optimizer to better estimate how much memory will be + used by aggregates. +/para /listitem /itemizedlist *** *** 1218,1224 listitem para ! Allow the changing of foreign key constraint via link linkend=SQL-ALTERTABLEcommandALTER TABLE//link ... literalALTER CONSTRAINT/ (Simon Riggs) /para --- 1227,1233 listitem para ! Allow changing foreign key constraint deferrability via link linkend=SQL-ALTERTABLEcommandALTER TABLE//link ... literalALTER CONSTRAINT/ (Simon Riggs) /para *** *** 1254,1260 listitem para ! Fully-implement the link linkend=datatype-linetypeline//link data type (Peter Eisentraut) /para --- 1263,1269 listitem para ! Fully implement the link linkend=datatype-linetypeline//link data type (Peter Eisentraut) /para *** *** 1472,1478 para Add function link linkend=functions-admin-dblocationfunctionpg_filenode_relation()//link ! to allow for more efficient filenode to relation lookups (Andres Freund) /para /listitem --- 1481,1487 para Add function link linkend=functions-admin-dblocationfunctionpg_filenode_relation()//link ! to allow for more efficient lookup of relation names from filenodes (Andres Freund) /para /listitem *** *** 1543,1552 /listitem listitem -!-- FIXME -- para Allow polymorphic aggregates to have non-polymorphic state data ! types ? (Tom Lane) /para /listitem --- 1552,1564 /listitem listitem para Allow polymorphic aggregates to have non-polymorphic state data ! types (Tom Lane) !/para !para ! This allows the declaration of aggregates like the built-in ! aggregate functionarray_agg()/ in SQL. /para /listitem *** *** 1772,1778 listitem para ! Allow field wrapping to applicationpsql/'s extended mode (Sergey Muraviov) /para /listitem --- 1784,1791 listitem para ! Add ability to wrap long lines in applicationpsql/'s ! literalexpanded/ mode by using command\pset
Re: [HACKERS] 9.4 release notes
On Fri, May 16, 2014 at 02:10:40AM +0200, Andres Freund wrote: I am not really sure how to rewrite the notes for the logical decoding stuff into a more appropriate format for the release notes. Currently it seems to describe too many details and not enough overview. It's also probably too long. How about letting it keep it's sect4 but remove the itemizedlist and put a short explanation about the individual parts into a following para or two? That'd require a name after a sect4 which normally isn't done... I am not sure how to improve it either. The features adds config variable changes, a new table option, and new binary --- I think listing those separately is good. I am not sure it can be improved without making it appear disjointed. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 release notes
On Thu, May 15, 2014 at 06:08:47PM -0700, David G Johnston wrote:
Some errors and suggestions - my apologizes for the format as I do not have
a proper patching routine setup.
Patch Review - Top to Bottom (mostly, I think...)
I have made your suggested adjustments in the attached applied patch.
Add ROWS FROM() syntax to allow horizontal concatenation of set-returning
functions in the FROM-clause (Andrew Gierth)
- Maybe a note about using this to avoid least-common-multiple expansion?
Sorry, I do not understand this.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/release-9.4.sgml b/doc/src/sgml/release-9.4.sgml
new file mode 100644
index 91a586f..41256b7
*** a/doc/src/sgml/release-9.4.sgml
--- b/doc/src/sgml/release-9.4.sgml
***
*** 89,95
linkend=functions-formatting-tablefunctionto_timestamp()//link
and functionto_date()/ format strings to consume a corresponding
number of characters in the input string (whitespace or not), then
! conditionally additional adjacent whitespace if not in literalFX/
mode (Jeevan Chalke)
/para
--- 89,95
linkend=functions-formatting-tablefunctionto_timestamp()//link
and functionto_date()/ format strings to consume a corresponding
number of characters in the input string (whitespace or not), then
! conditionally consume adjacent whitespace if not in literalFX/
mode (Jeevan Chalke)
/para
***
*** 98,104
format string behaved like a single whitespace character and consumed
all adjacent whitespace in the input string. For example, previously
format string space-space-space would consume only the first space in
! ' 12', while it will not consume all three characters.
/para
/listitem
--- 98,104
format string behaved like a single whitespace character and consumed
all adjacent whitespace in the input string. For example, previously
format string space-space-space would consume only the first space in
! ' 12', while it will now consume all three characters.
/para
/listitem
***
*** 122,128
/para
para
! Previously empty arrays were returned as one-dimensional empty arrays
whose text representation looked the same as zero-dimensional arrays
(literal{}/). applicationintarray/'s behavior in this area
now matches the built-in array operators.
--- 122,128
/para
para
! Previously, empty arrays were returned as one-dimensional empty arrays
whose text representation looked the same as zero-dimensional arrays
(literal{}/). applicationintarray/'s behavior in this area
now matches the built-in array operators.
***
*** 131,139
listitem
para
! NULL link
linkend=xfunc-sql-variadic-functionsliteralVARIADIC//link
! function arguments are now disallowed (Pavel Stehule)
/para
para
--- 131,139
listitem
para
! Disallow NULL link
linkend=xfunc-sql-variadic-functionsliteralVARIADIC//link
! function arguments (Pavel Stehule)
/para
para
***
*** 300,306
listitem
para
! commandDISCARD ALL/ now also discards the state of sequences.
/para
/listitem
--- 300,306
listitem
para
! commandDISCARD ALL/ now also discards sequence state.
/para
/listitem
***
*** 366,372
listitem
para
! During immediate shutdown, send uncatchable termination signals
to child processes that have not already shutdown (MauMau,
Aacute;lvaro Herrera)
/para
--- 366,372
listitem
para
! During immediate shutdown send uncatchable termination signals
to child processes that have not already shutdown (MauMau,
Aacute;lvaro Herrera)
/para
***
*** 740,746
para
In contrast
! to link linkend=guc-local-preload-librariesvarnamelocal_preload_libraries//link,
this parameter can load any shared library, not just those in
the filename$libdir/plugins/ directory.
/para
--- 740,746
para
In contrast
! to link linkend=guc-local-preload-librariesvarnamelocal_preload_libraries//link
this parameter can load any shared library, not just those in
the filename$libdir/plugins/ directory.
/para
***
*** 789,796
listitem
para
Have Windows acronymASCII/-encoded databases and server process
! (e.g. link linkend=app-postmasterpostmaster)
Re: [HACKERS] 9.4 release notes
On Sun, May 18, 2014 at 04:08:41PM -0400, Andrew Dunstan wrote: On 05/05/2014 07:26 PM, Andrew Dunstan wrote: On 05/05/2014 07:16 PM, Bruce Momjian wrote: Current text is: Add structured (non-text) data type (JSONB) for storing JSON data (Oleg Bartunov, Teodor Sigaev, Alexander Korotkov, Peter Geoghegan, and Andrew Dunstan) This allows for faster access to values in the JSON document and faster and more useful indexing of JSON. JSONB values are also typed as appropriate scalar SQL types. Is that OK? No. If you must say something then start the last sentence with Scalar values in JSONB documents are typed I still think we should make this change. Does anyone object if I do? OK, I have adjusted it with the attached applied patch. Feel free to adjust it yourself as well. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + diff --git a/doc/src/sgml/release-9.4.sgml b/doc/src/sgml/release-9.4.sgml new file mode 100644 index 41256b7..2913944 *** a/doc/src/sgml/release-9.4.sgml --- b/doc/src/sgml/release-9.4.sgml *** *** 1327,1334 para This allows for faster access to values in the typeJSON/ document and faster and more useful indexing of typeJSON/. ! typeJSONB/ values are also typed as appropriate scalar ! SQL types. /para /listitem --- 1327,1334 para This allows for faster access to values in the typeJSON/ document and faster and more useful indexing of typeJSON/. ! Scalar values in typeJSONB/ documents are typed as appropriate ! scalar SQL types. /para /listitem -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Allowing join removals for more join types
On 18 May 2014 16:38 David Rowley Wrote Sound like a good idea to me.. I have one doubt regarding the implementation, consider the below query Create table t1 (a int, b int); Create table t2 (a int, b int); Create unique index on t2(b); select x.a from t1 x left join (select distinct t2.a a1, t2.b b1 from t2) as y on x.a=y.b1; (because of distinct clause subquery will not be pulled up) In this case, Distinct clause is used on t2.a, but t2.b is used for left Join (t2.b have unique index so this left join can be removed). So I think now when you are considering this join removal for subqueries then this can consider other case also like unique index inside subquery, because in attached patch unique index is considered only if its RTE_RELATION + if (innerrel-rtekind == RTE_RELATION + relation_has_unique_index_for(root, innerrel, clause_list, NIL, NIL)) return true; Correct me if I am missing something.. CREATE TABLE products (productid integer NOT NULL, code character varying(32) NOT NULL); CREATE TABLE sales (saleid integer NOT NULL, productid integer NOT NULL, qty integer NOT NULL); CREATE VIEW product_sales AS SELECT p.productid, p.code, s.qty FROM (products p LEFT JOIN ( SELECT sales.productid, sum(sales.qty) AS qty FROM sales GROUP BY sales.productid) s ON ((p.productid = s.productid))); If a user does: SELECT productid,code FROM product_sales; Then, if I'm correct, the join on sales can be removed. Attached is a patch which implements this. It's still a bit rough around the edges and some names could likely do with being improved, but it at least seems to work with all of the test cases that I've thrown at it so far. Comments are welcome, but the main purpose of the email is so I can register the patch for the June commitfest.
