Re: [HACKERS] 9.5 feature count
On 27 August 2015 at 23:20, Bruce Momjian br...@momjian.us wrote:
On Fri, Jun 26, 2015 at 11:13:19AM -0700, Peter Geoghegan wrote:
On Fri, Jun 26, 2015 at 11:09 AM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
Maybe there's a reasonable way to measure releases (my 8.0 is bigger
than your 9.1!), but I don't think this is it.
I agree with the sentiment, but I don't think that anyone actually
thinks of it that way. Most people tend to think of a release in terms
of the big, exciting features, or the smaller features that happened
to scratch their particular itch.
I agree. I think the count tells us how focused we are in working on a
few big things or many small things, e.g. when we don't have many big
features in a major release, the count tends to be high as we clean up
previously-released big features.
Anything where Hot Standby == {one line changes in default settings} has
been distilled too far to draw any meaningful conclusions. They simply
reflect the editing style in use at that time, which has changed over time.
--
Simon Riggshttp://www.2ndQuadrant.com/
http://www.2ndquadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] [BUGS] Compile fails on AIX 6.1
On Thu, Aug 27, 2015 at 10:36:46AM -0400, Tom Lane wrote: the problem is that IBM's assembler doesn't understand the local symbol notation supported by the GNU assembler (bne 1f referencing the next occurrence of 1:). So s_lock.h's PowerPC assembly code works if you have gcc configured to use gas as backend, but not if it's configured to use the native AIX assembler. Steve says the latter configuration is pretty common. These days, the latter configuration is all but universal. Per the GCC installation instructions, The GNU Assembler has not been updated to support AIX 6 or AIX 7. So now that we know what is happening, what do we want to do about it? AFAICS there are two plausible ways to fix it: 1. Add a configure-time test to see if the assembler supports local symbols. If not, don't try to use the lwarx assembly stanza, but let it fall through to using _check_lock(). This would be simple but there would presumably be some performance hit. 2. Don't rely on local symbols in the PPC spinlock assembly code. This is a bit ugly, because the only way to do that is to hard-code branch offsets, as in the attached draft patch. If there were any likelihood that we'd be changing the PPC spinlock code in future, I would regard this as unmaintainable ... but really, that code is pretty static. So I think this is a viable alternative. A third option is to use __sync intrinsics, like we do on ARM. I like (2). -- 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 - better support pipe line
On Fri, Aug 28, 2015 at 3:29 AM, Jim Nasby jim.na...@bluetreble.com wrote: On 8/27/15 8:37 AM, Pavel Stehule wrote: I don't think we can detect and remove the default values from this output in a reliable way? This is pretty difficult - any parameter can be important, and hard to identify default values on client side without connect to server side. I don't see any other way, than hold bitmap for identification entered values and default values from the input. That would be nice, but probably not that big a deal. What I think would be more useful is a way to exclude the password if you didn't need it. Good point, and maybe even exclude it by default and provide only if asked for that explicitly. It occurs to me the most flexible thing that could be done here would be providing a libpq function that spits out JSON connection parameters and have psql turn that into a variable. It would be easy to feed that to a SQL statement and do whatever you want with it at that point, including format it to a connection URI. Hm... but that would mean that suddenly psql would need JSON parsing capabilities and URI escaping code would have to be moved there too? So every client that links to libpq and wants to use this feature going as far as reconstructing an URI would need both of the capabilities. Why instead of JSON not spit conninfo format, with proper escaping? That could be a separate library call, e.g. PGgetConnectionString() and a separate backslash command: \conninfo -- Alex
[HACKERS] Multi-column distinctness.
Hello, this patch enables planner to be couscious of inter-column correlation. Sometimes two or more columns in a table has some correlation which brings underestimate, which leads to wrong join method and ends with slow execution. Tomas Vondra is now working on heavily-equipped multivariate statistics for OLAP usage. In contrast, this is a lightly implemented solution which calculates only the ratio between a rows estimated by current method and a actual row number. I think this doesn't conflict with his work except the grammar part. This would apply fewer cases but I suppose still in many cases the correlated colums would be in simple proportional relationship, so this can help the cases. The previous discussion is https://wiki.postgresql.org/wiki/Cross_Columns_Stats http://www.postgresql.org/message-id/4d0ba4d5.8080...@fuzzy.cz This patch is covers only the type A (Discrete values and equality conditions) but I think it is usable in many cases seen in the field. So I'd like to repropose for the latest version of PostgreSQL. - design outline Provide new system catalog pg_mvcoefficient to store the information required to do this. A user can instruct planner to correct the wrong estimation caused by inter-column correlation by registering the columns in pg_mvcoefficient using new DDL ALTER TABLE... ADD STATISTICS. Analyzing of the target table also stores the 'multivariate coefficient' calculated by using the following formula into pg_mvcoefficient. mv_coef(c1, c2, ..) = ndistinct(c1 * c2 * ...) / (ndistinct(c1) * ndistinct(c2) * ...) In clauselist_selectivity, planner corrects the estimate if given clauselist has equivalence-classes-compatible clauses for required columns at the top-level. - Example The attached perl script gentbl.pl generates test data resembles some tables in DBT-3 benchmark. $ perl gentbl.pl | psql postgres =# EXPLAIN ANALYZE SELECT * FROM t1 WHERE a = 1 AND b = 2501; ... Seq Scan on t1 (cost=0.00..653.00 rows=1 width=12) (actual time=0.021..6.348 rows=8 loops=1) This doesn't have no harm but in a join case, =# EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.a = t2.a AND t1.b = t2.b; Hash Join (cost=122.00..855.32 rows=32 width=24) (actual time=2.009..29.208 rows=32000 loops=1) The correlation between a and b makes the estimate too small. Then register correlation setting. =# ALTER TABLE t1 ADD STATISTICS (mvndistinct) ON (a, b); =# ANALYZE t1; Then the estimate will be corrected. =# EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.a = t2.a AND t1.b = t2.b; Hash Join (cost=122.00..855.32 rows=32000 width=24) (actual time=1.907..29.025 rows=32000 loops=1) - Known limitations The coefficient calculated by this feature is applicble only for conjunctions of simple var-exprs on merge-joinable operator. The coefficient is applied regardless of whether the base estimate has been calculated using MCV, so estimates for non-join cases on the columns which has MCV can rather become inaccurate. Uniform correlation is assumed so some extent of correlation ununiformity would lead to wrong estimation. This patch set doesn't contain any document yet. - Patche Files This patch consists of the following files. - 0001-New-system-catalog-pg_mvcoefficient.patch Adds new system catalog pg_mvcoefficient. - 0002-Analyze-part-for-multivariate-coefficient.patch Analyze part of multivariate coefficient. - 0003-Make-use-of-multivariate-coefficeient-in-estimation-.patch Planner part to make it use the multivariate coefficient. - 0004-Syntactical-part-of-multivariate-coefficient.patch Add new DDL to define mv coefficient columns. The four files above are essential. The two following files are experimental patch to add mvcattrs to index columns. One of them adds a new opclass for int2vector of btree but it would be overkill. - 0005-Add-btree-operator-class-for-int2vector.patch Add btree operator class for int2vector. - 0006-Use-modified-index-of-pg_mvcoefficient.patch Use modified index of pg_mvcoefficient. regards, -- Kyotaro Horiguchi NTT Open Source Software Center From ce2beeba56b96af4f9289be53646f83e807637dc Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi horiguchi.kyot...@lab.ntt.co.jp Date: Thu, 6 Aug 2015 16:49:04 +0900 Subject: [PATCH 1/6] New system catalog pg_mvcoefficient. --- src/backend/catalog/Makefile | 3 +- src/backend/utils/cache/syscache.c | 12 + src/include/catalog/indexing.h | 3 ++ src/include/catalog/pg_mvcoefficient.h | 74 ++ src/include/utils/syscache.h | 1 + src/test/regress/expected/sanity_check.out | 1 + 6 files changed, 93 insertions(+), 1 deletion(-) create mode 100644 src/include/catalog/pg_mvcoefficient.h diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile index 25130ec..4ce1653 100644 --- a/src/backend/catalog/Makefile +++
[HACKERS] Multiline-statement and multi-statement for pgbench custom script.
Hi, this is a spin-off patch from Fabien COELHO's backslash-continuations. The major concept of this patch is making usage of psql's scanner to get rid of home-grown scanner of pgbench to make multi-statement feature available for pgbench custom scripts. This patch does the following things. - Modify psqlscan.l so that unnecessary functions of it can be masked when used in other modules like pgbench. - Modify pgbench to use psqlscan.l so that the following features available in pgbench. - multi-statement in custom scripts. - natural continuation of SQL statements in costom scripts. - backslash-continuation for pgbench metacommands in costom scripts. The patch consists of following files. - 0001-Prepare-to-share-psqlscan-with-pgbench.patch Modifies psqlscan.l in psql as the preparation. - 0002-Make-use-of-psqlscan-for-parsing-of-custom-script.patch Modifies pgbench to use psqlscan.l. - 0003-Change-MSVC-Build-script.patch Modify MSVC build script. - 0004-Change-the-way-to-hold-command-list.patch Get rid of double-format of internal command list. This changes the way of holding command list to linked list totally. regards, From c8830544312308b42d9ce7fc5793519c32237ba5 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi horiguchi.kyot...@lab.ntt.co.jp Date: Thu, 23 Jul 2015 20:44:37 +0900 Subject: [PATCH 1/4] Prepare to share psqlscan with pgbench. Eliminate direct usage of pset variables and enable parts unnecessary for other than psql to be disabled by defining OUTSIDE_PSQL. --- src/bin/psql/mainloop.c | 6 ++-- src/bin/psql/psqlscan.h | 14 + src/bin/psql/psqlscan.l | 79 - src/bin/psql/startup.c | 4 +-- 4 files changed, 67 insertions(+), 36 deletions(-) diff --git a/src/bin/psql/mainloop.c b/src/bin/psql/mainloop.c index b6cef94..e98cb94 100644 --- a/src/bin/psql/mainloop.c +++ b/src/bin/psql/mainloop.c @@ -233,7 +233,8 @@ MainLoop(FILE *source) /* * Parse line, looking for command separators. */ - psql_scan_setup(scan_state, line, strlen(line)); + psql_scan_setup(scan_state, line, strlen(line), + pset.db, pset.vars, pset.encoding); success = true; line_saved_in_history = false; @@ -373,7 +374,8 @@ MainLoop(FILE *source) resetPQExpBuffer(query_buf); /* reset parsing state since we are rescanning whole line */ psql_scan_reset(scan_state); - psql_scan_setup(scan_state, line, strlen(line)); + psql_scan_setup(scan_state, line, strlen(line), + pset.db, pset.vars, pset.encoding); line_saved_in_history = false; prompt_status = PROMPT_READY; } diff --git a/src/bin/psql/psqlscan.h b/src/bin/psql/psqlscan.h index 55070ca..4bf8dcb 100644 --- a/src/bin/psql/psqlscan.h +++ b/src/bin/psql/psqlscan.h @@ -11,7 +11,11 @@ #include pqexpbuffer.h #include prompt.h - +#if !defined OUTSIDE_PSQL +#include variables.h +#else +typedef int * VariableSpace; +#endif /* Abstract type for lexer's internal state */ typedef struct PsqlScanStateData *PsqlScanState; @@ -36,12 +40,11 @@ enum slash_option_type OT_NO_EVAL /* no expansion of backticks or variables */ }; - extern PsqlScanState psql_scan_create(void); extern void psql_scan_destroy(PsqlScanState state); -extern void psql_scan_setup(PsqlScanState state, -const char *line, int line_len); +extern void psql_scan_setup(PsqlScanState state, const char *line, int line_len, + PGconn *db, VariableSpace vars, int encoding); extern void psql_scan_finish(PsqlScanState state); extern PsqlScanResult psql_scan(PsqlScanState state, @@ -52,6 +55,7 @@ extern void psql_scan_reset(PsqlScanState state); extern bool psql_scan_in_quote(PsqlScanState state); +#if !defined OUTSIDE_PSQL extern char *psql_scan_slash_command(PsqlScanState state); extern char *psql_scan_slash_option(PsqlScanState state, @@ -60,5 +64,5 @@ extern char *psql_scan_slash_option(PsqlScanState state, bool semicolon); extern void psql_scan_slash_command_end(PsqlScanState state); - +#endif /* if !defined OUTSIDE_PSQL */ #endif /* PSQLSCAN_H */ diff --git a/src/bin/psql/psqlscan.l b/src/bin/psql/psqlscan.l index be059ab..f9a19cd 100644 --- a/src/bin/psql/psqlscan.l +++ b/src/bin/psql/psqlscan.l @@ -43,11 +43,6 @@ #include ctype.h -#include common.h -#include settings.h -#include variables.h - - /* * We use a stack of flex buffers to handle substitution of psql variables. * Each stacked buffer contains the as-yet-unread text from one psql variable. @@ -81,10 +76,12 @@ typedef struct PsqlScanStateData const char *scanline; /* current input line at outer level */ /* safe_encoding, curline, refline are used by emit() to replace FFs */ + PGconn *db;/* active connection */ int encoding; /* encoding being used now */ bool safe_encoding; /* is current encoding safe? */ const char *curline; /* actual flex input string for cur buf */ const char *refline; /* original data for cur
Re: [HACKERS] patch: version_stamp.pl: Add Git commit info to version if 'git' is specified
I've done the same using the output $(git describe --tags --dirty) - which will return something like REL9_5_ALPHA1-330-g8a7d070-dirty. Looks good! That's still not perfect considering plpgsql and such, ISTM That even for plpgsql it could be done, the stamp can be generated when the shared object/dll is generated, and could be made available from some inspection function. but it's pretty helpful. Yep. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] NOTIFY in Background Worker
Hello, I added a NOFITY chan to the SQL arg of an SPI_execute(), (I did it also with just the NOTIFY statement), but the listeners (other workers) don't get the notification until a NOTIFY chan is done for example with pgadmin, They don't get lost, just not emited after the not forgotten call of CommitTransactionCommand(). Is this normal ( i.e. not supported (yet) ), a bug, or did I overlook some doc. (or source code) ?. For now, I will try to emit the NOTIFY via libpq. Jacques K.
Re: [HACKERS] patch: version_stamp.pl: Add Git commit info to version if 'git' is specified
On 2015-08-28 07:48:28 +0200, Fabien COELHO wrote: Salesforce did something similar in their internal build, and TBH I do not find it a good idea. The basic problem is it's completely misleading to equate the last commit with the source you actually built from, because that might not have been an unmodified file set. Indeed. What I've done in an svn-based project is to build the stamp from the Makefile basically when linking, that is really as late as possible. The other good point is that svnversion adds 'M' for modified if the source tree has uncommitted changes. Maybe such an approach could be used with git to have something reliable. I've done the same using the output $(git describe --tags --dirty) - which will return something like REL9_5_ALPHA1-330-g8a7d070-dirty. That is, the last tag, the number of commits since, the commit hash, and whether the current build tree is dirty. That's still not perfect considering plpgsql and such, but it's pretty helpful. 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] Minor code improvements to create_foreignscan_plan/ExecInitForeignScan
On 2015/07/22 15:25, Etsuro Fujita wrote:
On 2015/07/10 21:59, David Rowley wrote:
On 10 July 2015 at 21:40, Etsuro Fujita fujita.ets...@lab.ntt.co.jp
mailto:fujita.ets...@lab.ntt.co.jp wrote:
To save cycles, I modified create_foreignscan_plan so that it detects
whether any system columns are requested if scanning a base relation.
I just glanced at this and noticed that the method for determining if
there's any system columns could be made a bit nicer.
/* Now, are any system columns requested from rel? */
scan_plan-fsSystemCol = false;
for (i = FirstLowInvalidHeapAttributeNumber + 1; i 0; i++)
{
if (bms_is_member(i - FirstLowInvalidHeapAttributeNumber, attrs_used))
{
scan_plan-fsSystemCol = true;
break;
}
}
I think could just be written as:
/* Now, are any system columns requested from rel? */
if (!bms_is_empty(attrs_used)
bms_next_member(attrs_used, -1) -FirstLowInvalidHeapAttributeNumber)
scan_plan-fsSystemCol = true;
else
scan_plan-fsSystemCol = false;
I know you didn't change this, but just thought I'd mention it while
there's an opportunity to fix it.
On second thought, I noticed that there is a case when that fix doesn't
work well; bms_next_member wouldn't be efficient when only the rear
user-columns are requested from a foreign table that has a large number
of user-columns. So, I'm inclined to leave that as-is.
Anyway, I'll add this to the upcoming CF.
Best regards,
Etsuro Fujita
--
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] AcquireRewriteLocks/acquireLocksOnSubLinks vs. rowsecurity
* Dean Rasheed (dean.a.rash...@gmail.com) wrote:
On 27 August 2015 at 13:49, Andres Freund and...@anarazel.de wrote:
The locking around rowsecurity policy expressions seems to be
insufficient:
SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
WARNING: RelationIdGetRelation(247984) without holding lock on the relation
WARNING: relation_open(247984, NoLock) of relation uaccount without
previously held lock
[...]
Istmt that something like
context.for_execute = true;
acquireLocksOnSubLinks((Node *)
securityQuals, context);
acquireLocksOnSubLinks((Node *)
withCheckOptions, context);
needs to be added to that code.
Yes, I think you're right. It needs to happen before fireRIRonSubLink,
and only if hasSubLinks is true.
Attached appears to fix this for the RLS case from my testing.
Any comments?
Barring concerns, I'll push this later today and back-patch to 9.5.
Thanks!
Stephen
From 7ac58a62338103338b6907fc7ea89f9afb9a0e53 Mon Sep 17 00:00:00 2001
From: Stephen Frost sfr...@snowman.net
Date: Fri, 28 Aug 2015 08:10:22 -0400
Subject: [PATCH] Ensure locks are acquired on RLS-added relations
During fireRIRrules(), get_row_security_policies can add to
securityQuals and withCheckOptions. Make sure to lock any relations
added at that point and before firing RIR rules on those expressions.
Back-patch to 9.5 where RLS was added.
---
src/backend/rewrite/rewriteHandler.c | 16
1 file changed, 16 insertions(+)
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 1734e48..fbc0c57 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -1787,6 +1787,8 @@ fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown)
{
if (hasSubLinks)
{
+acquireLocksOnSubLinks_context context;
+
/*
* Recursively process the new quals, checking for infinite
* recursion.
@@ -1799,6 +1801,20 @@ fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown)
activeRIRs = lcons_oid(RelationGetRelid(rel), activeRIRs);
+/*
+ * get_row_security_policies just added to securityQuals and/or
+ * withCheckOptions, and there were SubLinks, so make sure
+ * we lock any relations which were added as a result.
+ */
+context.for_execute = true;
+(void) acquireLocksOnSubLinks((Node *) securityQuals, context);
+(void) acquireLocksOnSubLinks((Node *) withCheckOptions,
+ context);
+
+/*
+ * Now that we have the locks on anything added by
+ * get_row_security_policies, fire any RIR rules for them.
+ */
expression_tree_walker((Node *) securityQuals,
fireRIRonSubLink, (void *) activeRIRs);
--
1.9.1
signature.asc
Description: Digital signature
Re: [HACKERS] AcquireRewriteLocks/acquireLocksOnSubLinks vs. rowsecurity
* Andres Freund (and...@anarazel.de) wrote: On 2015-08-28 08:49:24 -0400, Stephen Frost wrote: + /* +* get_row_security_policies just added to securityQuals and/or +* withCheckOptions, and there were SubLinks, so make sure +* we lock any relations which were added as a result. +*/ Very minor comment: Strictly speaking the quals/wces haven't yet been added to the Query, that happens only few lines down. I think it makes sense to mention that we normally rely on the parser to acquire locks, but that can't work here since sec quals/wces aren't visible to the parser. Ok, I'll add a comment to that effect. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] AcquireRewriteLocks/acquireLocksOnSubLinks vs. rowsecurity
On 2015-08-28 08:49:24 -0400, Stephen Frost wrote: + /* + * get_row_security_policies just added to securityQuals and/or + * withCheckOptions, and there were SubLinks, so make sure + * we lock any relations which were added as a result. + */ Very minor comment: Strictly speaking the quals/wces haven't yet been added to the Query, that happens only few lines down. I think it makes sense to mention that we normally rely on the parser to acquire locks, but that can't work here since sec quals/wces aren't visible to the parser. 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] [BUGS] Compile fails on AIX 6.1
Noah Misch n...@leadboat.com writes: On Thu, Aug 27, 2015 at 10:36:46AM -0400, Tom Lane wrote: the problem is that IBM's assembler doesn't understand the local symbol notation supported by the GNU assembler (bne 1f referencing the next occurrence of 1:). So s_lock.h's PowerPC assembly code works if you have gcc configured to use gas as backend, but not if it's configured to use the native AIX assembler. Steve says the latter configuration is pretty common. These days, the latter configuration is all but universal. Per the GCC installation instructions, The GNU Assembler has not been updated to support AIX 6 or AIX 7. Ouch. I'm surprised we've not gotten more complaints. 2. Don't rely on local symbols in the PPC spinlock assembly code. This is a bit ugly, because the only way to do that is to hard-code branch offsets, as in the attached draft patch. If there were any likelihood that we'd be changing the PPC spinlock code in future, I would regard this as unmaintainable ... but really, that code is pretty static. So I think this is a viable alternative. A third option is to use __sync intrinsics, like we do on ARM. I like (2). I've been waiting to hear confirmation from Steve that the proposed patch works with IBM's assembler. (For all I know, it uses * rather than ., or some other randomness.) He's not responded yet though. Are you in a position to test the patch? 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] AcquireRewriteLocks/acquireLocksOnSubLinks vs. rowsecurity
* Andres Freund (and...@anarazel.de) wrote: On 2015-08-28 08:49:24 -0400, Stephen Frost wrote: + /* +* get_row_security_policies just added to securityQuals and/or +* withCheckOptions, and there were SubLinks, so make sure +* we lock any relations which were added as a result. +*/ Very minor comment: Strictly speaking the quals/wces haven't yet been added to the Query, that happens only few lines down. I think it makes sense to mention that we normally rely on the parser to acquire locks, but that can't work here since sec quals/wces aren't visible to the parser. Pushed. Will work on the rewriteTargetView fix. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] AcquireRewriteLocks/acquireLocksOnSubLinks vs. rowsecurity
* Andres Freund (and...@anarazel.de) wrote:
On 2015-08-28 08:49:24 -0400, Stephen Frost wrote:
+ /*
+* get_row_security_policies just added to
securityQuals and/or
+* withCheckOptions, and there were SubLinks,
so make sure
+* we lock any relations which were added as a
result.
+*/
Very minor comment: Strictly speaking the quals/wces haven't yet been
added to the Query, that happens only few lines down. I think it makes
sense to mention that we normally rely on the parser to acquire locks,
but that can't work here since sec quals/wces aren't visible to the
parser.
Better?
Thanks!
Stephen
From 4cd1a52b1a869e2357f7cf0a6573690a89b7 Mon Sep 17 00:00:00 2001
From: Stephen Frost sfr...@snowman.net
Date: Fri, 28 Aug 2015 08:10:22 -0400
Subject: [PATCH] Ensure locks are acquired on RLS-added relations
During fireRIRrules(), get_row_security_policies can add to
securityQuals and withCheckOptions. Make sure to lock any relations
added at that point and before firing RIR rules on those expressions.
Back-patch to 9.5 where RLS was added.
---
src/backend/rewrite/rewriteHandler.c | 19 +++
1 file changed, 19 insertions(+)
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 1734e48..a238cff 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -1787,6 +1787,8 @@ fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown)
{
if (hasSubLinks)
{
+acquireLocksOnSubLinks_context context;
+
/*
* Recursively process the new quals, checking for infinite
* recursion.
@@ -1799,6 +1801,23 @@ fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown)
activeRIRs = lcons_oid(RelationGetRelid(rel), activeRIRs);
+/*
+ * get_row_security_policies just passed back securityQuals
+ * and/or withCheckOptions, and there were SubLinks, make sure
+ * we lock any relations which are referenced.
+ *
+ * These locks would normally be acquired by the parser, but
+ * securityQuals and withCheckOptions are added post-parsing.
+ */
+context.for_execute = true;
+(void) acquireLocksOnSubLinks((Node *) securityQuals, context);
+(void) acquireLocksOnSubLinks((Node *) withCheckOptions,
+ context);
+
+/*
+ * Now that we have the locks on anything added by
+ * get_row_security_policies, fire any RIR rules for them.
+ */
expression_tree_walker((Node *) securityQuals,
fireRIRonSubLink, (void *) activeRIRs);
--
1.9.1
signature.asc
Description: Digital signature
Re: [HACKERS] One question about security label command
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 08/25/2015 06:54 PM, Joe Conway wrote:
On 08/25/2015 06:03 PM, Joe Conway wrote:
I'm arriving late to this party, so maybe everyone else already
knows this, but apparently sepgsql is not compatible with the
version of selinux available on RHEL 6.x. So there doesn't seem
to be much reason for a RHEL 6.x buildfarm animal just for
sepgsql testing as it will always fail ;-)
Just to be clear, I have marked this on the commitfest app as
ready for commit, and plan to commit it soon. Figuring out the
buildfarm animal will be my next task after that.
Here's a synopsis of the state of play with respect to sepgsql
regression tests:
Required
PG Ver RHEL Ver* test w/patchtest w/o patch
- --- --
HEAD 7.x OK NOK
9.57.x OK NOK
9.47.x OK** NOK
9.37.x OK** NOK
9.27.x NOK NOK
9.16.x NOK OK
9.0*** N/A N/A N/A
- --- --
* It is really the version of libselinux.so that matters here. RHEL
7.x has libselinux 2.2.x whereas RHEL 6.x has 2.0.x. The latter lacks
functionality required by sepgsql starting with PG 9.2.
** As noted in an earlier message on this thread by Adam, with PG 9.4
(and 9.3) there is some addition noise coming from differences in
default verbosity or possibly error context hiding added after 9.4,
which causes the regression to fail with Kouhei's patch. Attached is a
slightly modified patch that works for 9.4 and 9.3.
*** sepgsql was introduced in PG 9.1
So given all that, here is what I propose we do:
1.) Commit Kouhei's patch against HEAD and 9.5 (Joe)
2.) Commit my modified patch against 9.4 and 9.3 (Joe)
3.) Rework patch for 9.2 (Kouhei)
4.) Finish standing up the RHEL/CentOS 7.x buildfarm member to
test sepgsql on 9.2 and up. The animal (rhinoceros) is running
already, but still needs some custom scripting. (Joe, Andrew)
5.) Additionally stand up a RHEL/CentOS 6.x buildfarm member to test
sepgsql on 9.1 (no changes) (Joe).
Sound like a plan?
Joe
- --
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, Open Source Development
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.22 (GNU/Linux)
iQIcBAEBAgAGBQJV4IA5AAoJEDfy90M199hlF6wP/1p0xpVORBY4DLjLaM8KzAWt
HxZjtK6vD8yQCG45L1crhYnB2FYIHZoG+71WwP7xSZ6YnOC+g5mFrjh6YdRMxwSe
OSnMIuy7QvVZrGfvSSIG6u4lBivi9jDC6mnFuU5YW9Q3mk6HBdJbErIuwP3z0Bxj
c9yuh6WqWRNghVwIyErkdbp7YqFDeoQZ8iSiKxDghMIQRzFgB4K1egEDM6TGAo/1
/1j0vSLmRoQPZvDnJaLCAsZzw7JozppwCXPwfpwd2Xj6N3h/v9aoflRKaKppvf16
vIqDcHkdbea3Bk/jGS3OBBMBXDsd5lrfjF5iaFVtiBu04VjUaJJ0mHOKNL+xf4Uk
E9C8bjxpR7MEeiR8tE8RTMWg710ITVix3P8I3y+LS0V8GhzaHw2AOKSlGVNlRf/Y
VfoFEcvqcqsnenA3gmUbljSeHI0G3G5w+nTwEvciug28PffnpNyamtjPOn4IEay4
12RrbD/v7IfsXxjnDqhQRLdy1t7tVDjNC6ddjSfT3G64v4pvBoSaT9NQXWJ9jw3A
aM345gguBRVGcKRD/UZfUZ4VBesj5T67g56HPmEqDC+7LlqVBSmKdEJ51RrfRsKF
fd8OZT43h9+XXD4yCuxt0bt38ybiRsOAdjT4eUrTj18GGx0q3P08NNnZI2V0fe4b
/8pM9IlcdxDYGS7e3oPv
=SbW3
-END PGP SIGNATURE-
diff --git a/contrib/sepgsql/expected/alter.out b/contrib/sepgsql/expected/alter.out
index 124f862..e67cc2d 100644
--- a/contrib/sepgsql/expected/alter.out
+++ b/contrib/sepgsql/expected/alter.out
@@ -8,9 +8,9 @@ DROP DATABASE IF EXISTS regtest_sepgsql_test_database;
DROP USER IF EXISTS regtest_sepgsql_test_user;
RESET client_min_messages;
SELECT sepgsql_getcon(); -- confirm client privilege
- sepgsql_getcon
- unconfined_u:unconfined_r:unconfined_t:s0
+ sepgsql_getcon
+--
+ unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0
(1 row)
--
@@ -40,140 +40,136 @@ SET client_min_messages = LOG;
-- owner is not actually changed.
--
ALTER DATABASE regtest_sepgsql_test_database_1 OWNER TO regtest_sepgsql_test_user;
-LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:unconfined_t:s0 tcontext=unconfined_u:object_r:sepgsql_db_t:s0 tclass=db_database name=regtest_sepgsql_test_database_1
+LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_db_t:s0 tclass=db_database name=regtest_sepgsql_test_database_1
ALTER DATABASE regtest_sepgsql_test_database_1 OWNER TO regtest_sepgsql_test_user;
-LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:unconfined_t:s0 tcontext=unconfined_u:object_r:sepgsql_db_t:s0 tclass=db_database name=regtest_sepgsql_test_database_1
+LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0
Re: [HACKERS] WIP: About CMake v2
On 2015-08-28 12:32:45 -0300, Alvaro Herrera wrote: YUriy Zhuravlev wrote: Hello Hackers How would you react if I provided a patch which introduces a CMake build system? What's your motivation for doing so? I definitely can see some advantages. Non-broken dependencies around recursive make being a major one. But I'm also afraid it's a rather large undertaking. There's a fair number of special kind of rules, and we're probably not going to want to break pgxs for extensions. I also have some doubts around the portability of cmake and it's generated makefiles. We do support some odd platforms. 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] What does RIR as in fireRIRrules stand for?
On 2015-08-27 11:12:42 -0400, Tom Lane wrote: Steve Crawford scrawf...@pinpointresearch.com writes: Candidate for Appendix K? Meh ... it's not a user-visible notation, in fact it only appears in rewriteHandler.c AFAICS. Agreed, I don't think we want to distribute that term any wider than that file. I think an explanation in fireRIRrules' header comment would be fine. I was thinking of adding it to the file's header like * NOTES *Some of the terms used in this file are of historic nature: retrieve *was the PostQUEL keyword for what today is SELECT. RIR stands for *Retrieve-Instead-Retrieve, that is an ON SELECT INSTEAD rule (which *has to be unconditional and where only one rule can exist on each *relation). since both retrieve and RIR are used in a bunch of places. Andres -- 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] What does RIR as in fireRIRrules stand for?
Andres Freund and...@anarazel.de writes: On 2015-08-27 11:12:42 -0400, Tom Lane wrote: I think an explanation in fireRIRrules' header comment would be fine. I was thinking of adding it to the file's header like * NOTES * Some of the terms used in this file are of historic nature: retrieve * was the PostQUEL keyword for what today is SELECT. RIR stands for * Retrieve-Instead-Retrieve, that is an ON SELECT INSTEAD rule (which * has to be unconditional and where only one rule can exist on each * relation). since both retrieve and RIR are used in a bunch of places. Seems reasonable. Maybe worth writing out the modern spelling a bit further, an ON SELECT DO INSTEAD SELECT rule, to make the parallel to Retrieve-Instead-Retrieve perfectly clear. 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
[HACKERS] Define two factor authentication for Postgresql Server
Hello, I'm going to create two factor authentication for pgadmin server... I'm using postgresql 9.4 with pgadmin III on Linux Mint 17.2 32bit... I already have 1 password authentication but For better security, I just want to force 2 of them. The authentication factors could be any things(what user has,what user knows,where user is or what user is). for example: The first factor is password(what user knows) and the second is USB device(what user has). I need to force Postgresql to check both for authenticate user and connect him to the server. I send you a screenshot from pgAdmin server authenticate screen and I'm going to implement 2 factor authentication for this. I not talking about OS authentication. All authenticate operation should operate from Postgresql. I already try this for login into pgAdmin through password and USB: I installed pamusb pakages : sudo apt-get install pamusb-tools libpam-usb Although I can add devices on my pamusb config file : pamusb-conf --add-device MyDevice and I can define pamusb users. I added this lines to pamusb config between users tags : user id=postgres deviceMyDevice/device /user My guess : I think I should write module in /etc/pam.d and edit pg_hba.conf file to define login method for local users : local allall pam pamservice=mypam but I don't know how to write module to force both authentication methods for this(both are required). Any help would be appreciated... -- 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] What does RIR as in fireRIRrules stand for?
On 2015-08-28 10:20:58 -0400, Tom Lane wrote: Seems reasonable. Maybe worth writing out the modern spelling a bit further, an ON SELECT DO INSTEAD SELECT rule, to make the parallel to Retrieve-Instead-Retrieve perfectly clear. Makes sense. Pushed that way. Thanks -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WIP: About CMake v2
Hello Hackers How would you react if I provided a patch which introduces a CMake build system? Old thread: http://www.postgresql.org/message-id/200812291325.13354.pete...@gmx.net The main argument against the it's too hard. I'm right? Thanks! -- YUriy Zhuravlev Postgres Professional: http://www.postgrespro.com The Russian Postgres Company -- 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] WIP: About CMake v2
YUriy Zhuravlev wrote: Hello Hackers How would you react if I provided a patch which introduces a CMake build system? What's your motivation for doing so? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Pg_upgrade remote copy
Hi, In pg_upgrade, how about adding a feature to copy data directory over network. That is, we can run pg_upgrade from our new host, where old host will be a remote machine. We can add two parameters - oldhost and if it is used, pg_upgrade will identify the old host as remote and instead of local copy, it will use remote copy. What do you think about it? Thanks.
Re: [HACKERS] Fwd: Core dump with nested CREATE TEMP TABLE
On 8/28/15 8:39 PM, Tom Lane wrote: Michael Paquier michael.paqu...@gmail.com writes: On Sat, Aug 29, 2015 at 5:02 AM, Jim Nasby jim.na...@bluetreble.com wrote: Looks like a 98k file won't get through the list... Is it compressed? Note that we have sometimes larger patches than that, but perhaps those had special permissions by the admins of this list. Messages significantly larger than that go through all the time. Maybe you had it marked with some weird MIME type? Apparently the original email did go through and my MUA search just failed to find it. Sorry for the noise. Original email: http://www.postgresql.org/message-id/55dfaf18.4060...@bluetreble.com -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- 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] One question about security label command
* It is really the version of libselinux.so that matters here. RHEL 7.x has libselinux 2.2.x whereas RHEL 6.x has 2.0.x. The latter lacks functionality required by sepgsql starting with PG 9.2. Yes, that has been my observation as well. So given all that, here is what I propose we do: 1.) Commit Kouhei's patch against HEAD and 9.5 (Joe) 2.) Commit my modified patch against 9.4 and 9.3 (Joe) 3.) Rework patch for 9.2 (Kouhei) 4.) Finish standing up the RHEL/CentOS 7.x buildfarm member to test sepgsql on 9.2 and up. The animal (rhinoceros) is running already, but still needs some custom scripting. (Joe, Andrew) 5.) Additionally stand up a RHEL/CentOS 6.x buildfarm member to test sepgsql on 9.1 (no changes) (Joe). Sound like a plan? I think this makes sense. Getting buildfarm coverage on any level is better than nothing, IMHO. Kind of a bummer that 9.1 is the only version that will work as-is on EL6 but it is what it is for now, I suppose. -Adam -- Adam Brightwell - adam.brightw...@crunchydatasolutions.com Database Engineer - www.crunchydatasolutions.com -- 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] proposal: multiple psql option -c
2015-08-28 22:31 GMT+02:00 David G. Johnston david.g.johns...@gmail.com: On Fri, Aug 28, 2015 at 4:18 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2015-08-28 22:07 GMT+02:00 Jim Nasby jim.na...@bluetreble.com: On 8/26/15 8:15 AM, Pavel Stehule wrote: + and then exit. This is useful in shell scripts. Start-up files + (filenamepsqlrc/filename and filename~/.psqlrc/filename) are + ignored with this option. Sorry if this was discussed and I missed it, but I think this is a bad idea. There's already an option to control this. More important, there's no option to force the rc files to be used, so if -g disables them you'd be stuck with that. I agree that the rc files are a danger when scripting, but if we want to do something about that then it needs to be consistent for ALL non-interactive use. I don't see any problem to load rc files - but should I do it by default? I prefer 1. default - don't read rc 2. possible long option for forcing load rc for -c and -g 3. possible long option for forcing load any file as rc for -c and -g --psqlrc ; read the standard rc files --no-psqlrc ; do not read the standard rc files It belongs in a separate patch, though. sure In this patch -g should disable the reading of the standard rc files. it does Yet another option could be added that allows the user to point to a different set of rc files. Its presence should not cause the include/exclude behavior to change. That way you can setup a psql wrapper function or alias that uses a different rc file while still having control over whether it is included or excluded. The problem here is exploding the logic in order to deal with both a system and a user rc file. I am not against, but it is not neccessary - you can use -g for reading some files and later -g for some special action Regards Pavel This would be yet another patch. My $0.02 David J.
Re: [HACKERS] proposal: multiple psql option -c
2015-08-28 23:01 GMT+02:00 Jim Nasby jim.na...@bluetreble.com: On 8/28/15 3:31 PM, David G. Johnston wrote: --psqlrc ; read the standard rc files --no-psqlrc ; do not read the standard rc files It belongs in a separate patch, though. In this patch -g should disable the reading of the standard rc files. Agreed; I didn't realize -c disabled psqlrc. Yet another option could be added that allows the user to point to a different set of rc files. Its presence should not cause the include/exclude behavior to change. That way you can setup a psql wrapper function or alias that uses a different rc file while still having control over whether it is included or excluded. The problem here is exploding the logic in order to deal with both a system and a user rc file. If we had a \i variation that didn't fail if the file wasn't readable you could use that to pull a system psqlrc in from your custom one. The import any file is not problem with -g command - so special option is not necessary probably psql postgres -g \i somefile -g select xxx -g ... Regards Pavel -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
Re: [HACKERS] [BUGS] Compile fails on AIX 6.1
On Fri, Aug 28, 2015 at 09:58:46AM -0400, Tom Lane wrote: Noah Misch n...@leadboat.com writes: On Thu, Aug 27, 2015 at 10:36:46AM -0400, Tom Lane wrote: So s_lock.h's PowerPC assembly code works if you have gcc configured to use gas as backend, but not if it's configured to use the native AIX assembler. Steve says the latter configuration is pretty common. These days, the latter configuration is all but universal. Per the GCC installation instructions, The GNU Assembler has not been updated to support AIX 6 or AIX 7. Ouch. I'm surprised we've not gotten more complaints. That surprised me, too. Perhaps almost everyone has used either xlc or that IBM-provided gcc you wrote about. 2. Don't rely on local symbols in the PPC spinlock assembly code. A third option is to use __sync intrinsics, like we do on ARM. I like (2). I've been waiting to hear confirmation from Steve that the proposed patch works with IBM's assembler. (For all I know, it uses * rather than ., or some other randomness.) He's not responded yet though. Are you in a position to test the patch? I tested a gcc 64-bit build. Consistent with your followup, b .+12 doesn't build, but b $+12 builds and passes make check. I am attaching the exact diff I tested. On GNU/Linux ppc, I get the same opcodes before and after the change. diff --git a/src/include/storage/s_lock.h b/src/include/storage/s_lock.h index ef66644..b7567c1 100644 --- a/src/include/storage/s_lock.h +++ b/src/include/storage/s_lock.h @@ -447,6 +447,12 @@ typedef unsigned int slock_t; * NOTE: per the Enhanced PowerPC Architecture manual, v1.0 dated 7-May-2002, * an isync is a sufficient synchronization barrier after a lwarx/stwcx loop. * On newer machines, we can use lwsync instead for better performance. + * + * Ordinarily, we'd code the branches here using GNU-style local symbols, that + * is 1f referencing 1: and so on. But some people run gcc on AIX with + * IBM's assembler as backend, and IBM's assembler doesn't do local symbols. + * So hand-code the branch offsets; fortunately, all PPC instructions are + * exactly 4 bytes each, so it's not too hard to count. */ static __inline__ int tas(volatile slock_t *lock) @@ -461,20 +467,18 @@ tas(volatile slock_t *lock) lwarx %0,0,%3 \n #endif cmpwi %0,0\n - bne 1f \n + bne $+16\n /* branch to li %1,1 */ addi%0,%0,1 \n stwcx. %0,0,%3 \n - beq 2f \n -1:li %1,1\n - b 3f \n -2:\n + beq $+12\n /* branch to lwsync/isync */ + li %1,1\n + b $+12\n /* branch to end of asm sequence */ #ifdef USE_PPC_LWSYNC lwsync \n #else isync \n #endif li %1,0\n -3:\n : =r(_t), =r(_res), +m(*lock) : r(lock) -- 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] WIP: About CMake v2
On Friday 28 August 2015 13:28:49 Alvaro Herrera wrote: Andres Freund wrote: On 2015-08-28 12:32:45 -0300, Alvaro Herrera wrote: YUriy Zhuravlev wrote: Hello Hackers How would you react if I provided a patch which introduces a CMake build system? What's your motivation for doing so? I definitely can see some advantages. Non-broken dependencies around recursive make being a major one. But I'm also afraid it's a rather large undertaking. There's a fair number of special kind of rules, and we're probably not going to want to break pgxs for extensions. I also have some doubts around the portability of cmake and it's generated makefiles. We do support some odd platforms. If it allows us to get rid of our custom MSVC scripts, it's a huge benefit, for sure -- that has been a huge pain in the neck since day one. As a user, I suffer a bit with the current build system when I try to build libpq in MinGW. So, if moving to CMake means the build is more 'decoupled', i.e. less inter-dependent modules, it would be great. -- 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] [patch] Proposal for \rotate in psql
2015-08-29 0:48 GMT+02:00 Daniel Verite dan...@manitou-mail.org: Hi, This is a reboot of my previous proposal for pivoting results in psql, with a new patch that generalizes the idea further through a command now named \rotate, and some examples. So the concept is: having an existing query in the query buffer, the user can specify two column numbers C1 and C2 (by default the 1st and 2nd) as an argument to a \rotate command. The query results are then displayed in a 2D grid such that each tuple (vx, vy, va, vb,...) is shown as |va vb...| in a cell at coordinates (vx,vy). The values vx,xy come from columns C1,C2 respectively and are represented in the output as an horizontal and a vertical header. A cell may hold several columns from several rows, growing horizontally and vertically (\n inside the cell) if necessary to show all results. The examples below should be read with a monospaced font as in psql, otherwise they will look pretty bad. 1. Example with only 2 columns, querying login/group membership from the catalog. Query: SELECT r.rolname as username,r1.rolname as groupname FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m ON (m.member = r.oid) LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid) WHERE r.rolcanlogin ORDER BY 1 Sample results: username | groupname +--- daniel | mailusers drupal | dv | admin dv | common extc | readonly extu | foobar | joel | mailreader | readonly manitou| mailusers manitou| admin postgres | u1 | common u2 | mailusers zaz| mailusers Applying \rotate gives: Rotated query results username | admin | common | mailusers | readonly +---++---+-- daniel | || X | drupal | || | dv | X | X | | extc | || | X extu | || | foobar | || | joel | || | mailreader | || | X manitou| X || X | postgres | || | u1 | | X | | u2 | || X | zaz| || X | The 'X' inside cells is automatically added as there are only 2 columns. If there was a 3rd column, the content of that column would be displayed instead (as in the next example). What's good in that \rotate display compared to the classic output is that it's more apparent, visually speaking, that such user belongs or not to such group or another. 2. Example with a unicode checkmark added as 3rd column, and unicode linestyle and borders (to be seen with a mono-spaced font): SELECT r.rolname as username,r1.rolname as groupname, chr(10003) FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m ON (m.member = r.oid) LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid) WHERE r.rolcanlogin ORDER BY 1 Rotated query results ┌┬───┬───�” �┬───┬â ��─┐ │ username │ admin │ common │ mailusers │ readonly │ ├┼───┼───�” �┼───┼â ��─┤ │ daniel │ │ │ ✓ ││ │ drupal │ │ │ ││ │ dv │ ✓ │ ✓ │ ││ │ extc │ │ │ │ ✓│ │ extu │ │ │ ││ │ foobar │ │ │ ││ │ joel │ │ │ ││ │ mailreader │ │ │ │ ✓│ │ manitou│ ✓ │ │ ✓ ││ │ postgres │ │ │ ││ │ u1 │ │ ✓ │ ││ │ u2 │ │ │ ✓ ││ │ zaz│ │ │ ✓ ││ └┴───┴───�” �┴───┴â ��─┘ What I like in that representation is that it looks good enough to be pasted directly into a document in a word processor. 3. It can be rotated easily in the other direction, with: \rotate 2 1 (Cut horizontally to fit in a mail, the actual output is 116 chars wide). Rotated query results ┌───┬┬───�” �┬┬──┬──┬─â ��──┬──┬ │ username │ daniel │ drupal │ dv │ extc │ extu │ foobar │ joel │ mai... ├───┼┼───�” �┼┼──┼──┼─â ��──┼──┼ │ mailusers │ ✓│ ││ │ ││ │ │ admin ││ │ ✓ │ │ │ │ │ │ common││ │ ✓ │ │ │ │ │ │ readonly ││ ││ ✓│ │ │ │ ✓ └───┴┴───�”
Re: [HACKERS] Fwd: Core dump with nested CREATE TEMP TABLE
Michael Paquier michael.paqu...@gmail.com writes: On Sat, Aug 29, 2015 at 5:02 AM, Jim Nasby jim.na...@bluetreble.com wrote: Looks like a 98k file won't get through the list... Is it compressed? Note that we have sometimes larger patches than that, but perhaps those had special permissions by the admins of this list. Messages significantly larger than that go through all the time. Maybe you had it marked with some weird MIME type? 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] NOTIFY in Background Worker
On Sat, Aug 29, 2015 at 9:03 AM, Thomas Munro thomas.mu...@enterprisedb.com wrote: On Fri, Aug 28, 2015 at 10:30 PM, jacques klein jacques.k...@googlemail.com wrote: Hello, I added a NOFITY chan to the SQL arg of an SPI_execute(), (I did it also with just the NOTIFY statement), but the listeners (other workers) don't get the notification until a NOTIFY chan is done for example with pgadmin, They don't get lost, just not emited after the not forgotten call of CommitTransactionCommand(). Is this normal ( i.e. not supported (yet) ), a bug, or did I overlook some doc. (or source code) ?. For now, I will try to emit the NOTIFY via libpq. That's because ProcessCompletedNotifies isn't being called. For regular backends it is called inside the top level loop PostgresMain. I think you need to include commands/async.h and add a call to ProcessCompletedNotifies() after your background worker commits to make this work. For the record, Jacques confirmed off-list that this worked, and I also did a couple of tests. Is this expected? If so, should it be documented -- perhaps with something like the attached? Alternatively there may be some way to make CommitTransactionCommand do it, though the comments in ProcessCompletedNotifies explain why that was rejected, at least as far as AtCommit_Notify goes. This made me wonder what happens if a background worker calls LISTEN. NotifyMyFrontEnd simply logs the notifications, since there is no remote libpq to sent a message to. Perhaps a way of delivering to background workers could be developed, though of course there are plenty of other kinds of IPC available already. -- Thomas Munro http://www.enterprisedb.com bgworker-notify-doc.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] Fwd: Core dump with nested CREATE TEMP TABLE
On Sat, Aug 29, 2015 at 11:18 AM, Jim Nasby jim.na...@bluetreble.com wrote: On 8/28/15 8:39 PM, Tom Lane wrote: Michael Paquier michael.paqu...@gmail.com writes: On Sat, Aug 29, 2015 at 5:02 AM, Jim Nasby jim.na...@bluetreble.com wrote: Looks like a 98k file won't get through the list... Is it compressed? Note that we have sometimes larger patches than that, but perhaps those had special permissions by the admins of this list. Messages significantly larger than that go through all the time. Maybe you had it marked with some weird MIME type? Apparently the original email did go through and my MUA search just failed to find it. Sorry for the noise. Original email: http://www.postgresql.org/message-id/55dfaf18.4060...@bluetreble.com Ah, OK, you meant this file... Yes I was able to receive it as well in your original email. I'll try to investigate further later, but Tom may beat me first. He usually does. -- Michael
Re: [HACKERS] Proposal: Implement failover on libpq connect level.
+1 for bringing the jdbc driver URI syntax into libpq, so that all interfaces can be optionally specified this way. This doesn't preclude the use of ipfailover, in fact it might be work well together. If you don't like it, don't use it. +1 Another thought: multiple hosts in URI could be used in simple configuration for read-only clients. I faced with customers which manages two connections in process - to master and to one of several slaves. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- 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] patch: version_stamp.pl: Add Git commit info to version if 'git' is specified
On Fri, Aug 28, 2015 at 1:44 AM, Andres Freund and...@anarazel.de wrote: On 2015-08-28 07:48:28 +0200, Fabien COELHO wrote: Salesforce did something similar in their internal build, and TBH I do not find it a good idea. The basic problem is it's completely misleading to equate the last commit with the source you actually built from, because that might not have been an unmodified file set. Indeed. What I've done in an svn-based project is to build the stamp from the Makefile basically when linking, that is really as late as possible. The other good point is that svnversion adds 'M' for modified if the source tree has uncommitted changes. Maybe such an approach could be used with git to have something reliable. I've done the same using the output $(git describe --tags --dirty) - which will return something like REL9_5_ALPHA1-330-g8a7d070-dirty. That is, the last tag, the number of commits since, the commit hash, and whether the current build tree is dirty. That looks handy. But, why isn't it alpha2 rather than alpha1 ? Cheers, Jeff
Re: [HACKERS] WIP: About CMake v2
Andres Freund wrote: On 2015-08-28 12:32:45 -0300, Alvaro Herrera wrote: YUriy Zhuravlev wrote: Hello Hackers How would you react if I provided a patch which introduces a CMake build system? What's your motivation for doing so? I definitely can see some advantages. Non-broken dependencies around recursive make being a major one. But I'm also afraid it's a rather large undertaking. There's a fair number of special kind of rules, and we're probably not going to want to break pgxs for extensions. I also have some doubts around the portability of cmake and it's generated makefiles. We do support some odd platforms. If it allows us to get rid of our custom MSVC scripts, it's a huge benefit, for sure -- that has been a huge pain in the neck since day one. Like you, I am also afraid it's a huge undertaking, but if Uri wants to tackle it, we have that part covered. Experimentation until we get it all correct is going to waste some of everybody's time, too, I'm sure. I wonder about two other things: one is speed of the build (not that currently it's all that great, given all the mess with recursive make invocations, but perhaps it can be even worse); the other is how ugly the generated files are going to be, and are we going to carry them in our repo -- right now we only have configure, but are we going to keep extra files to cope with builds in systems that don't have cmake installed (as we cope with missing bison and flex)? I sure would oppose something that looks as ugly as Automake makefiles. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] WIP: About CMake v2
On 2015-08-28 13:28:49 -0300, Alvaro Herrera wrote: the other is how ugly the generated files are going to be, and are we going to carry them in our repo -- right now we only have configure, but are we going to keep extra files to cope with builds in systems that don't have cmake installed (as we cope with missing bison and flex)? Apparently it's still unsupported to ship generated files - they contain absolute paths and such. Personally I think that might be the death-knell - I doubt that we want to have a full dependency on cmake on every platform? -- 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] patch: version_stamp.pl: Add Git commit info to version if 'git' is specified
On 2015-08-28 09:13:59 -0700, Jeff Janes wrote: On Fri, Aug 28, 2015 at 1:44 AM, Andres Freund and...@anarazel.de wrote: I've done the same using the output $(git describe --tags --dirty) - which will return something like REL9_5_ALPHA1-330-g8a7d070-dirty. That is, the last tag, the number of commits since, the commit hash, and whether the current build tree is dirty. That looks handy. But, why isn't it alpha2 rather than alpha1 ? I was on master, and master branched after ALPHA1, not ALPHA2. Andres -- 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] WIP: About CMake v2
Alvaro Herrera alvhe...@2ndquadrant.com writes: I wonder about two other things: one is speed of the build (not that currently it's all that great, given all the mess with recursive make invocations, but perhaps it can be even worse); the other is how ugly the generated files are going to be, and are we going to carry them in our repo -- right now we only have configure, but are we going to keep extra files to cope with builds in systems that don't have cmake installed (as we cope with missing bison and flex)? As near as I can tell, the generated files are platform-specific. (They're certainly different for Unix and Windows; the overview I'm looking at doesn't say in so many words whether they can vary at a finer grain, but I bet they do.) So I'm afraid cmake would likely become a build requirement, even for tarball users. That is probably not a show-stopper, but it's a point against the idea. I have no idea whether switching to cmake would be a good thing or not. It's possible that it'd end up being even uglier than our current autoconf+gmake+msvc-scripts mess ... although when phrased that way, that sounds like a pretty low bar to clear. Anyway, if YUriy is willing to do the preliminary investigation, let's see what he comes up with. 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] WIP: About CMake v2
It's broadly interesting, but since it bakes in a build dependency on CMake, there is some risk that the dependencies become an insurmountable problem. (Does CMake run on a VAX 11/780?? :-)) It is probably worth a try, to see what improvements arise, albeit with the need to accept some risk of refusal of the change. The experiment is most likely necessary: we won't know the benefits without trying. If the results represent little improvement, there will be little or no appetite to jump through the dependency hoops needed to get the change accepted. On the other hand, if there are big gains, that encourages pushing thru the dependency issues. On Aug 28, 2015 10:45, YUriy Zhuravlev u.zhurav...@postgrespro.ru wrote: Hello Hackers How would you react if I provided a patch which introduces a CMake build system? Old thread: http://www.postgresql.org/message-id/200812291325.13354.pete...@gmx.net The main argument against the it's too hard. I'm right? Thanks! -- YUriy Zhuravlev Postgres Professional: http://www.postgrespro.com The Russian Postgres Company -- 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] WIP: About CMake v2
Christopher Browne cbbro...@gmail.com writes: (Does CMake run on a VAX 11/780?? :-)) Yeah. I see the two major risks as being: 1. We limit ourselves to platforms that cmake works on. 2. We lose the ability to handle weird special-case tests that are possible (if not necessarily pleasant) with autoconf/gmake. I might be overly worried about #2 --- but the impression I have of cmake is that they've spent lots of time on make easy cases easy and maybe not enough on make hard cases possible. Anyway, we won't know unless somebody tries it. There will certainly be some pluses and some minuses, and we'll have to decide whether the pluses outweigh the minuses. I don't think we can tell that without a fairly detailed attempt at making it work. 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] [BUGS] Compile fails on AIX 6.1
I wrote: Noah Misch n...@leadboat.com writes: On Thu, Aug 27, 2015 at 10:36:46AM -0400, Tom Lane wrote: 2. Don't rely on local symbols in the PPC spinlock assembly code. A third option is to use __sync intrinsics, like we do on ARM. I like (2). I've been waiting to hear confirmation from Steve that the proposed patch works with IBM's assembler. (For all I know, it uses * rather than ., or some other randomness.) He's not responded yet though. Are you in a position to test the patch? Steve got back to me with the news that AIX's assembler thinks that . is an ordinary symbol, not the current location. Some googling says that that assembler likes $ for current location. I did a quick check on my oldest OS X PPC box, and it seems to be happy with $ as well, so maybe we can use that --- though I see nothing about $ in the GNU Assembler manual, which makes me a bit worried about whether it works on all PPC systems. 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] Adding commit details to SGML release notes
Bruce Momjian br...@momjian.us writes: On Fri, Aug 28, 2015 at 05:32:38PM -0400, Tom Lane wrote: Personally I find that truncated output to be pretty unreadable, and would strongly object to changing git_changelog so that that was the only possible format. However, I have no objection to making it an option (git_changelog --short, or so), and that would dodge any concerns about failing on git versions without the feature. Well, my idea would be to change this git_changelog line: Branch: master [01051a987] 2015-07-09 11:38:34 +0300 to output something in the new 'trunc' format, so we have it right next to the release note text and the release notes easily get that comment. Right, I'm merely saying that I want the current format by default, and the trunc format only with a command-line option. 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] Raising our compiler requirements for 9.6
On Wed, Aug 5, 2015 at 03:46:36PM +0200, Andres Freund wrote: On 2015-08-05 15:08:29 +0200, Andres Freund wrote: We might later want to change some of the harder to maintain macros to inline functions, but that seems better done separately. Here's a conversion for fastgetattr() and heap_getattr(). Not only is the resulting code significantly more readable, but the conversion also shrinks the code size: Hey, the fastgetattr() macro was a work of art! ;-) (And more of my hacks disappear.) -- 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] Raising our compiler requirements for 9.6
On Wed, Aug 12, 2015 at 04:47:55PM -0400, Robert Haas wrote: On Wed, Aug 12, 2015 at 4:34 PM, Heikki Linnakangas hlinn...@iki.fi wrote: Andres didn't mention how big the performance benefit he saw with pgbench was, but I bet it was barely distinguishible from noise. But that's OK. In fact, there's no reason to believe this would make any difference to performance. The point is to make the code more readable, and it certainly achieves that. I think that when Bruce macro-ized this ten years ago or whenever it was, he got a significant performance benefit from it; otherwise I don't think he would have done it. (You over-estimate me. ;-) ) What happened is that I was looking at call graph counts and fastgetattr() was called a bazillion times, so I inlined it, and saw a noticeably performance improvement, maybe 2% on an in-memory SELECT-only workload. Same with a few other macros I created in those early years. Frankly, my hacks last a lot longer than I expected. (Did someone say pg_upgrade. :-) ) -- 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] Adding commit details to SGML release notes
On 2015-08-28 17:49:35 -0400, Bruce Momjian wrote: If we _don't_ do that, how do you easily get those lines into the release notes? I can't imagine how hard it was for Andres to add that text to the 9.5 release notes: Formatting the log wasn't the hard part, that was finding out which item in the release notes corresponds to which commits. Sometimes that's not that easy to see. For the formatting I had an emacs macro. 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] Raising our compiler requirements for 9.6
On Wed, Aug 12, 2015 at 10:40:53PM +0200, Andres Freund wrote: You might argue that it's nothing we have touched frequently. And you're right. But I think that's a mistake. We spend far too much time in the various pieces of code dissembling tuples, and I think at some point somebody really needs to spend time on this. Yes, this will need to be addressed some day --- I have heard rumors that we use more CPU than some proprietary relational database for the same workload. Interestingly, we are not necessary slower, just consume more CPU, causing us to max out the CPU sooner. -- 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] Adding commit details to SGML release notes
On Sat, Aug 29, 2015 at 12:23:30AM +0200, Andres Freund wrote: On 2015-08-28 17:49:35 -0400, Bruce Momjian wrote: If we _don't_ do that, how do you easily get those lines into the release notes? I can't imagine how hard it was for Andres to add that text to the 9.5 release notes: Formatting the log wasn't the hard part, that was finding out which item in the release notes corresponds to which commits. Sometimes that's not that easy to see. For the formatting I had an emacs macro. Right, the formatting is easy with any good editor, but having the text there as you are writing the release notes is a huge time-saver, and if we want that text, it should be present in the git_changelog output when creating those release notes. -- 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] [BUGS] Compile fails on AIX 6.1
I wrote: ... that assembler likes $ for current location. I did a quick check on my oldest OS X PPC box, and it seems to be happy with $ as well, so maybe we can use that --- though I see nothing about $ in the GNU Assembler manual, which makes me a bit worried about whether it works on all PPC systems. A look into the current gas sources finds this in config/tc-ppc.h: /* $ is used to refer to the current location. */ #define DOLLAR_DOT so apparently this is indeed standard behavior for gas on PPC. There's no indication that you could turn it off without manually hacking this config header. Source code access to one's tools is so pleasant ... 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
[HACKERS] [patch] Proposal for \rotate in psql
Hi, This is a reboot of my previous proposal for pivoting results in psql, with a new patch that generalizes the idea further through a command now named \rotate, and some examples. So the concept is: having an existing query in the query buffer, the user can specify two column numbers C1 and C2 (by default the 1st and 2nd) as an argument to a \rotate command. The query results are then displayed in a 2D grid such that each tuple (vx, vy, va, vb,...) is shown as |va vb...| in a cell at coordinates (vx,vy). The values vx,xy come from columns C1,C2 respectively and are represented in the output as an horizontal and a vertical header. A cell may hold several columns from several rows, growing horizontally and vertically (\n inside the cell) if necessary to show all results. The examples below should be read with a monospaced font as in psql, otherwise they will look pretty bad. 1. Example with only 2 columns, querying login/group membership from the catalog. Query: SELECT r.rolname as username,r1.rolname as groupname FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m ON (m.member = r.oid) LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid) WHERE r.rolcanlogin ORDER BY 1 Sample results: username | groupname +--- daniel | mailusers drupal | dv | admin dv | common extc | readonly extu | foobar | joel | mailreader | readonly manitou| mailusers manitou| admin postgres | u1 | common u2 | mailusers zaz| mailusers Applying \rotate gives: Rotated query results username | admin | common | mailusers | readonly +---++---+-- daniel | || X | drupal | || | dv | X | X | | extc | || | X extu | || | foobar | || | joel | || | mailreader | || | X manitou| X || X | postgres | || | u1 | | X | | u2 | || X | zaz| || X | The 'X' inside cells is automatically added as there are only 2 columns. If there was a 3rd column, the content of that column would be displayed instead (as in the next example). What's good in that \rotate display compared to the classic output is that it's more apparent, visually speaking, that such user belongs or not to such group or another. 2. Example with a unicode checkmark added as 3rd column, and unicode linestyle and borders (to be seen with a mono-spaced font): SELECT r.rolname as username,r1.rolname as groupname, chr(10003) FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m ON (m.member = r.oid) LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid) WHERE r.rolcanlogin ORDER BY 1 Rotated query results ┌┬───┬───�� �┬───┬� ��─┐ │ username │ admin │ common │ mailusers │ readonly │ ├┼───┼───�� �┼───┼� ��─┤ │ daniel │ │ │ ✓ ││ │ drupal │ │ │ ││ │ dv │ ✓ │ ✓ │ ││ │ extc │ │ │ │ ✓│ │ extu │ │ │ ││ │ foobar │ │ │ ││ │ joel │ │ │ ││ │ mailreader │ │ │ │ ✓│ │ manitou│ ✓ │ │ ✓ ││ │ postgres │ │ │ ││ │ u1 │ │ ✓ │ ││ │ u2 │ │ │ ✓ ││ │ zaz│ │ │ ✓ ││ └┴───┴───�� �┴───┴� ��─┘ What I like in that representation is that it looks good enough to be pasted directly into a document in a word processor. 3. It can be rotated easily in the other direction, with: \rotate 2 1 (Cut horizontally to fit in a mail, the actual output is 116 chars wide). Rotated query results ┌───┬┬───�� �┬┬──┬──┬─� ��──┬──┬ │ username │ daniel │ drupal │ dv │ extc │ extu │ foobar │ joel │ mai... ├───┼┼───�� �┼┼──┼──┼─� ��──┼──┼ │ mailusers │ ✓│ ││ │ ││ │ │ admin ││ │ ✓ │ │ │ │ │ │ common││ │ ✓ │ │ │ │ │ │ readonly ││ ││ ✓│ │ │ │ ✓ └───┴┴───�� �┴┴──┴──┴─� ��──┴──┴ 4. Example with 3 columns and a count as the value to visualize along two axis: date and category. I'm
Re: [HACKERS] Fwd: Core dump with nested CREATE TEMP TABLE
On Sat, Aug 29, 2015 at 5:02 AM, Jim Nasby jim.na...@bluetreble.com wrote: Looks like a 98k file won't get through the list... Is it compressed? Note that we have sometimes larger patches than that, but perhaps those had special permissions by the admins of this list. -- Michael
Re: [HACKERS] Adding commit details to SGML release notes
Bruce Momjian wrote: To simplify the creation of the release note with the commit tag as an SGML comment, I think src/tools/git_changelog should be modified to output this string. The format trunc feature was added in git 1.8.3. Is that old enough for everyone? Hmm, that's pretty new actually, commit edca4152560522a431a51fc0a06147fc680b5b18 Author: Junio C Hamano gits...@pobox.com AuthorDate: Fri May 24 11:34:46 2013 -0700 CommitDate: Fri May 24 11:34:46 2013 -0700 Git 1.8.3 Since it's only needed by people preparing the release notes, I would guess it's okay --- evidently Tom was able to use it. Maybe an option is to use trunc when git 1.8.3 or newer is detected, and plain (no trunc) otherwise? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Adding commit details to SGML release notes
Alvaro Herrera alvhe...@2ndquadrant.com writes: Bruce Momjian wrote: To simplify the creation of the release note with the commit tag as an SGML comment, I think src/tools/git_changelog should be modified to output this string. The format trunc feature was added in git 1.8.3. Is that old enough for everyone? Since it's only needed by people preparing the release notes, I would guess it's okay --- evidently Tom was able to use it. Well, that's only because I'm running a hand-installed git rather than what RHEL6 comes with. Maybe an option is to use trunc when git 1.8.3 or newer is detected, and plain (no trunc) otherwise? Personally I find that truncated output to be pretty unreadable, and would strongly object to changing git_changelog so that that was the only possible format. However, I have no objection to making it an option (git_changelog --short, or so), and that would dodge any concerns about failing on git versions without the feature. 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] Adding commit details to SGML release notes
On Fri, Aug 28, 2015 at 05:32:38PM -0400, Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Bruce Momjian wrote: To simplify the creation of the release note with the commit tag as an SGML comment, I think src/tools/git_changelog should be modified to output this string. The format trunc feature was added in git 1.8.3. Is that old enough for everyone? Since it's only needed by people preparing the release notes, I would guess it's okay --- evidently Tom was able to use it. Well, that's only because I'm running a hand-installed git rather than what RHEL6 comes with. Yes, I am running git 1.7.2.5 on Debian Squeeze, but I will be upgrading in the next month, before the next major release notes are due, so I would avoid the problem that way. :-) Maybe an option is to use trunc when git 1.8.3 or newer is detected, and plain (no trunc) otherwise? Personally I find that truncated output to be pretty unreadable, and would strongly object to changing git_changelog so that that was the only possible format. However, I have no objection to making it an option (git_changelog --short, or so), and that would dodge any concerns about failing on git versions without the feature. Well, my idea would be to change this git_changelog line: Branch: master [01051a987] 2015-07-09 11:38:34 +0300 to output something in the new 'trunc' format, so we have it right next to the release note text and the release notes easily get that comment. If we _don't_ do that, how do you easily get those lines into the release notes? I can't imagine how hard it was for Andres to add that text to the 9.5 release notes: http://www.postgresql.org/message-id/e1za0lx-0006fm...@gemulon.postgresql.org -- 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] Function accepting array of complex type
Jim Nasby jim.na...@bluetreble.com writes: On 8/25/15 6:28 PM, Tom Lane wrote: You need to cast it to some specific record type: regression=# SELECT magsum( array[row(2.1, 2.1), row(2.2,2.2)]::c[] ); Right, I was wondering how hard it would be to improve that, but it's not clear to me where to look at in the code. Does the resolution happen as part of parsing, or is it further down the road? It would possibly make sense to allow coercion of record[] to complex-array types, but there would be a lot of code to be written to support it. See the unimplemented cases referencing RECORDARRAYOID in parse_coerce.c, and compare to corresponding cases for coercing RECORDOID to complex. (Note that the way array[...]::foo[] works is very specific to ARRAY constructs, so it would not handle the general case. OTOH, coerce_record_to_complex doesn't pretend to handle all cases either.) 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] proposal: multiple psql option -c
On 8/28/15 3:31 PM, David G. Johnston wrote: --psqlrc ; read the standard rc files --no-psqlrc ; do not read the standard rc files It belongs in a separate patch, though. In this patch -g should disable the reading of the standard rc files. Agreed; I didn't realize -c disabled psqlrc. Yet another option could be added that allows the user to point to a different set of rc files. Its presence should not cause the include/exclude behavior to change. That way you can setup a psql wrapper function or alias that uses a different rc file while still having control over whether it is included or excluded. The problem here is exploding the logic in order to deal with both a system and a user rc file. If we had a \i variation that didn't fail if the file wasn't readable you could use that to pull a system psqlrc in from your custom one. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- 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] NOTIFY in Background Worker
On Fri, Aug 28, 2015 at 10:30 PM, jacques klein jacques.k...@googlemail.com wrote: Hello, I added a NOFITY chan to the SQL arg of an SPI_execute(), (I did it also with just the NOTIFY statement), but the listeners (other workers) don't get the notification until a NOTIFY chan is done for example with pgadmin, They don't get lost, just not emited after the not forgotten call of CommitTransactionCommand(). Is this normal ( i.e. not supported (yet) ), a bug, or did I overlook some doc. (or source code) ?. For now, I will try to emit the NOTIFY via libpq. That's because ProcessCompletedNotifies isn't being called. For regular backends it is called inside the top level loop PostgresMain. I think you need to include commands/async.h and add a call to ProcessCompletedNotifies() after your background worker commits to make this work. -- Thomas Munro http://www.enterprisedb.com
[HACKERS] Adding commit details to SGML release notes
On Tue, Jun 30, 2015 at 07:00:45PM +, Andres Freund wrote: Improve 9.5 release notes. 1) Add sgml comments referencing commits. This is useful to search for missing items etc. The comments containing the commit notes are an excerpt from: git log --date=short \ --pretty='format:%cd [%h] %(8,trunc)%cN: %(48,trunc)%s%n%n%w(,4,4)%b%n' \ $(git merge-base origin/master upstream/REL9_4_STABLE)..origin/master To simplify the creation of the release note with the commit tag as an SGML comment, I think src/tools/git_changelog should be modified to output this string. The format trunc feature was added in git 1.8.3. Is that old enough for everyone? I am not going to need this until the 9.6 release notes. Should I add it or someone else? -- 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] proposal: multiple psql option -c
On Fri, Aug 28, 2015 at 4:07 PM, Jim Nasby jim.na...@bluetreble.com wrote: On 8/26/15 8:15 AM, Pavel Stehule wrote: + and then exit. This is useful in shell scripts. Start-up files + (filenamepsqlrc/filename and filename~/.psqlrc/filename) are + ignored with this option. Sorry if this was discussed and I missed it, but I think this is a bad idea. There's already an option to control this. More important, there's no option to force the rc files to be used, so if -g disables them you'd be stuck with that. I agree that the rc files are a danger when scripting, but if we want to do something about that then it needs to be consistent for ALL non-interactive use. This ship has already sailed. The behavior described is consistent with -c which -g should rightly conform with. David J.
Re: [HACKERS] proposal: multiple psql option -c
2015-08-28 22:07 GMT+02:00 Jim Nasby jim.na...@bluetreble.com: On 8/26/15 8:15 AM, Pavel Stehule wrote: + and then exit. This is useful in shell scripts. Start-up files + (filenamepsqlrc/filename and filename~/.psqlrc/filename) are + ignored with this option. Sorry if this was discussed and I missed it, but I think this is a bad idea. There's already an option to control this. More important, there's no option to force the rc files to be used, so if -g disables them you'd be stuck with that. I agree that the rc files are a danger when scripting, but if we want to do something about that then it needs to be consistent for ALL non-interactive use. I don't see any problem to load rc files - but should I do it by default? I prefer 1. default - don't read rc 2. possible long option for forcing load rc for -c and -g 3. possible long option for forcing load any file as rc for -c and -g Regards Pavel -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
Re: [HACKERS] proposal: multiple psql option -c
On Fri, Aug 28, 2015 at 4:18 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2015-08-28 22:07 GMT+02:00 Jim Nasby jim.na...@bluetreble.com: On 8/26/15 8:15 AM, Pavel Stehule wrote: + and then exit. This is useful in shell scripts. Start-up files + (filenamepsqlrc/filename and filename~/.psqlrc/filename) are + ignored with this option. Sorry if this was discussed and I missed it, but I think this is a bad idea. There's already an option to control this. More important, there's no option to force the rc files to be used, so if -g disables them you'd be stuck with that. I agree that the rc files are a danger when scripting, but if we want to do something about that then it needs to be consistent for ALL non-interactive use. I don't see any problem to load rc files - but should I do it by default? I prefer 1. default - don't read rc 2. possible long option for forcing load rc for -c and -g 3. possible long option for forcing load any file as rc for -c and -g --psqlrc ; read the standard rc files --no-psqlrc ; do not read the standard rc files It belongs in a separate patch, though. In this patch -g should disable the reading of the standard rc files. Yet another option could be added that allows the user to point to a different set of rc files. Its presence should not cause the include/exclude behavior to change. That way you can setup a psql wrapper function or alias that uses a different rc file while still having control over whether it is included or excluded. The problem here is exploding the logic in order to deal with both a system and a user rc file. This would be yet another patch. My $0.02 David J.
Re: [HACKERS] psql - better support pipe line
On 8/28/15 3:58 AM, Shulgin, Oleksandr wrote: It occurs to me the most flexible thing that could be done here would be providing a libpq function that spits out JSON connection parameters and have psql turn that into a variable. It would be easy to feed that to a SQL statement and do whatever you want with it at that point, including format it to a connection URI. Hm... but that would mean that suddenly psql would need JSON parsing capabilities and URI escaping code would have to be moved there too? So every client that links to libpq and wants to use this feature going as far as reconstructing an URI would need both of the capabilities. Anything that's doing this presumably has connected to the database, which on any recent version means you have plenty of ability to process JSON at the SQL layer. Why instead of JSON not spit conninfo format, with proper escaping? That could be a separate library call, e.g. PGgetConnectionString() and a separate backslash command: \conninfo Do you mean as a URI? The downside to that it's it's more difficult to parse than JSON. Another option might be an array. The other issue is there's no way to capture \conninfo inside of psql and do something with it. If instead this was exposed as a variable, you could handle it in SQL if you wanted to. All that said, the patch already adds significant value and you could always parse the URI if you really needed to. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- 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] Function accepting array of complex type
On 8/25/15 6:28 PM, Tom Lane wrote: Jim Nasby jim.na...@bluetreble.com writes: This works: CREATE TYPE c AS (r float, i float); CREATE FUNCTION mag(c c) RETURNS float LANGUAGE sql AS $$ SELECT sqrt(c.r^2 + c.i^2) $$; SELECT mag( (2.2, 2.2) ); mag -- 3.11126983722081 But this doesn't: CREATE FUNCTION magsum( c c[] ) RETURNS float LANGUAGE sql AS $$ SELECT sum(sqrt(c.r^2 + c.i^2)) FROM unnest(c) c $$; SELECT magsum( array[row(2.1, 2.1), row(2.2,2.2)] ); ERROR: function magsum(record[]) does not exist at character 8 You need to cast it to some specific record type: regression=# SELECT magsum( array[row(2.1, 2.1), row(2.2,2.2)]::c[] ); Right, I was wondering how hard it would be to improve that, but it's not clear to me where to look at in the code. Does the resolution happen as part of parsing, or is it further down the road? -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- 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] proposal: multiple psql option -c
On 8/26/15 8:15 AM, Pavel Stehule wrote: + and then exit. This is useful in shell scripts. Start-up files + (filenamepsqlrc/filename and filename~/.psqlrc/filename) are + ignored with this option. Sorry if this was discussed and I missed it, but I think this is a bad idea. There's already an option to control this. More important, there's no option to force the rc files to be used, so if -g disables them you'd be stuck with that. I agree that the rc files are a danger when scripting, but if we want to do something about that then it needs to be consistent for ALL non-interactive use. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Fwd: Core dump with nested CREATE TEMP TABLE
Looks like a 98k file won't get through the list... Forwarded Message Subject: Core dump with nested CREATE TEMP TABLE Date: Thu, 27 Aug 2015 19:45:12 -0500 From: Jim Nasby jim.na...@bluetreble.com To: Pg Hackers pgsql-hackers@postgresql.org I don't have an independent reproduction yet (though make test in [1] should reproduce this). I haven't actually been able to reproduce by hand yet, but pgtap has something to do with this. This is affecting at least 9.4 and a fairly recent HEAD. -- Bits from top of test/sql/base.sql \i test/helpers/setup.sql SET ROLE = DEFAULT; CREATE ROLE test_role; GRANT USAGE ON SCHEMA tap TO test_role; GRANT test_role TO test_factory__owner; CREATE SCHEMA test AUTHORIZATION test_role; SET ROLE = test_role; SET search_path = test, tap; \i test/helpers/create.sql SELECT tf.register( 'customer' , array[ row( 'insert' , $$INSERT INTO customer VALUES (DEFAULT, 'first', 'last' ) RETURNING *$$ )::tf.test_set , row( 'function' , $$SELECT * FROM customer__add( 'func first', 'func last' )$$ )::tf.test_set ] ); SELECT tf.register( 'invoice' , array[ row( 'base' , $$INSERT INTO invoice VALUES( DEFAULT , (tf.get( NULL::customer, 'insert' )).customer_id , current_date , current_date + 30 ) RETURNING *$$ )::tf.test_set ] ); SELECT no_plan(); SELECT lives_ok($$SELECT * FROM tf.get( NULL::invoice, 'base' )$$); not ok 1 # Failed test 1 # died: 42703: column c_data_table_name does not exist -- Ok, got an error, but no crash. But now... SELECT results_eq( $$SELECT * FROM tf.get( NULL::invoice, 'base' )$$ , $$VALUES( 1, 1, current_date, current_date + 30 )$$ , 'invoice factory output' ); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. lives_ok() and results_eq() are both pgTap functions. Definitions at [2] and [3]. I've attached a full server log from running make test, but the most relevant bit is below: DEBUG: AbortSubTransaction CONTEXT: PL/pgSQL function results_eq(refcursor,refcursor,text) line 11 during exception cleanup PL/pgSQL function results_eq(text,text,text) line 9 at assignment DEBUG: name: unnamed; blockState:INPROGRESS; state: INPROGR, xid/subid/cid: 1980/1/979, nestlvl: 1, children: 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 CONTEXT: PL/pgSQL function results_eq(refcursor,refcursor,text) line 11 during exception cleanup PL/pgSQL function results_eq(text,text,text) line 9 at assignment DEBUG: name: pg_psql_temporary_savepoint; blockState: SUB INPROGRS; state: INPROGR, xid/subid/cid: 2000/34/979, nestlvl: 2, children: CONTEXT: PL/pgSQL function results_eq(refcursor,refcursor,text) line 11 during exception cleanup PL/pgSQL function results_eq(text,text,text) line 9 at assignment DEBUG: name: unnamed; blockState: SUB INPROGRS; state: INPROGR, xid/subid/cid: 2001/35/979, nestlvl: 3, children: CONTEXT: PL/pgSQL function results_eq(refcursor,refcursor,text) line 11 during exception cleanup PL/pgSQL function results_eq(text,text,text) line 9 at assignment TRAP: FailedAssertion(!(rebuild ? !((bool)((relation)-rd_refcnt == 0)) : ((bool)((relation)-rd_refcnt == 0))), File: relcache.c, Line: 2055) [1] https://github.com/BlueTreble/test_factory/tree/crash [2] https://github.com/theory/pgtap/blob/master/sql/pgtap.sql.in#L746 [3] https://github.com/theory/pgtap/blob/master/sql/pgtap.sql.in#L6541 which is being called by https://github.com/theory/pgtap/blob/master/sql/pgtap.sql.in#L6591 -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
