Re: [HACKERS] KNN-GiST with recheck
On Fri, May 15, 2015 at 2:30 AM, Heikki Linnakangas hlinn...@iki.fi wrote: On 05/15/2015 02:28 AM, Heikki Linnakangas wrote: I think this is now ready for committing, but I'm pretty tired now so I'll read through this one more time in the morning, so that I won't wake up to a red buildfarm. Forgot to attach the latest patch, here you go. Looks good for me. -- With best regards, Alexander Korotkov.
[HACKERS] log bloating with shortlife bgworkers?
Hi I am planning to use short living bg workers. I was little bit surprised so any start and finish does entry in log. Is there any plan to decrease a log level for these purposes? Regards Pavel
Re: [HACKERS] multivariate statistics / patch v6
Hello,
At Thu, 14 May 2015 12:35:50 +0200, Tomas Vondra tomas.von...@2ndquadrant.com
wrote in 55547a86.8020...@2ndquadrant.com
On 05/13/15 10:31, Kyotaro HORIGUCHI wrote:
Hello, this might be somewhat out of place but strongly related
to this patch so I'll propose this here.
This is a proposal of new feature for this patch or asking for
your approval for my moving on this as a different (but very
close) project.
===
Attached is v6 of the multivariate stats, with a number of
improvements:
...
2) fix of pg_proc issues (reported by Jeff)
3) rebase to current master
Unfortunately, the v6 patch suffers some system oid conflicts
with recently added ones. And what more unfortunate for me is
that the code for functional dependencies looks undone:)
I'll fix the OID conflicts once the CF completes, which should be in a
few days I guess. Until then you can apply it on top of master from
about May 6 (that's when the v6 was created, and there should be no
conflicts).
I applied it with further fixing. It wasn't a problem :)
Regarding the functional dependencies - you're right there's room for
improvement. For example it only works with dependencies between pairs
of columns, not multi-column dependencies. Is this what you mean by
incomplete?
No, It overruns dependencies-deps because build_mv_dependencies
stores many elements into dependencies-deps[n] although it
really has a room for only one element. I suppose that you paused
writing it when you noticed that the number of required elements
is unknown before finising walk through all pairs of
values. palloc'ing numattrs^2 is reasonable enough as POC code
for now. Am I looking wrong version of patch?
-dependencies = (MVDependencies)palloc0(sizeof(MVDependenciesData))
+dependencies = (MVDependencies)palloc0(sizeof(MVDependenciesData) +
+sizeof(MVDependency) * numattrs * numattrs);
I mention this because I recently had a issue from strong
correlation between two columns in dbt3 benchmark. Two columns in
some table are in strong correlation but not in functional
dependencies, there are too many values and the distribution of
them is very uniform so MCV is no use for the table (histogram
has nothing to do with equal conditions). As the result, planner
estimates the number of rows largely wrong as expected especially
for joins.
I think the other statistics types (esp. histograms) might be more
useful here, but I assume you haven't tried that because of the
conflicts.
The current patch does not handle joins at all, though.
Well, that's one of the resons. But I understood that any
deterministic estimation cannot be applied for such distribution
when I saw what made the wrong estimation. eqsel and eqsel_join
finally relies on random match assumption on uniform distribution
when the value is not found in MCV list. And functional
dependencies stuff in your old patch (which works) (rightfully)
failed to find such relationship between the problematic
columns. So I tried ndistinct, which is not contained in your
patch to see how it works well.
I, then, had a try calculating the ratio between the product of
distinctness of every column and the distinctness of the set of
the columns, call it multivariate coefficient here, and found
that it looks greately useful for the small storage space, less
calculation, and simple code.
So when you have two columns A and B, you compute this:
ndistinct(A) * ndistinct(B)
---
ndistinct(A,B)
Yes, I used the reciprocal of that, though.
where ndistinc(...) means number of distinct values in the column(s)?
Yes.
The attached first is a script to generate problematic tables.
And the second is a patch to make use of the mv coef on current
master. The patch is a very primitive POC so no syntactical
interfaces involved.
...
Make use of mv coefficient.
=# insert into pg_mvcoefficient values ('t'::regclass, 1, 2, 3, 0);
=# analyze t;
=# explain analyze select * from t where a = 1 and b = 1 and c = 1;
Seq Scan on t (cost=0.00..22906.00 rows=9221 width=12)
(actual time=3.740..242.330 rows=1 loops=1)
Row number estimation was largely improved.
With my patch:
alter table t add statistics (mcv) on (a,b,c);
...
Seq Scan on t (cost=0.00..22906.00 rows=9533 width=12)
Yes, your MV-MCV list should have one third of all possible (set
of) values so it works fine, I guess. But my original problem was
occurred on the condition that (the single column) MCVs contain
under 1% of possible values, MCV would not work for such cases,
but its very uniform distribution helps random assumption to
work.
$ perl gentbl.pl 20 | psql postgres
takes a while..
posttres=# alter table t1 add statistics (mcv true) on (a, b);
postgres=# analyze t1;
postgres=# explain analyze select * from t1 where a = 1 and b = 2501;
Seq Scan on t1
Re: Custom/Foreign-Join-APIs (Re: [HACKERS] [v9.5] Custom Plan API)
2015-05-15 8:43 GMT+09:00 Kouhei Kaigai kai...@ak.jp.nec.com: Regarding of FDW, as Hanada-san mentioned, I'm uncertain whether similar feature is also needed because its join-pushdown feature scan on the result-set of remotely joined relations, thus no need to have local child Path nodes. So, I put this custom_children list on Custom structure only. AFAIS most of FDWs won't need child paths to process their external data. The most possible idea is that a FDW uses output of ForeignScan plan node which is handled by the FDW, but such work should be done by another CSP (or at least via CSP I/F). -- Shigeru HANADA -- 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] Re: [COMMITTERS] pgsql: Map basebackup tablespaces using a tablespace_map file
On Thu, May 14, 2015 at 10:29 PM, Andrew Dunstan and...@dunslane.net wrote: On 05/14/2015 10:52 AM, Robert Haas wrote: On Thu, May 14, 2015 at 12:12 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Thu, May 14, 2015 at 2:10 AM, Andrew Dunstan and...@dunslane.net wrote: How about if we simply abort if we find a non-symlink where we want the symlink to be, and only remove something that is actually a symlink (or a junction point, which is more or less the same thing)? We can do that way and for that I think we need to use rmdir instead of rmtree in the code being discussed (recovery path), OTOH we should try to minimize the errors raised during recovery. I'm not sure I understand this issue in detail, but why would using rmtree() on something you expect to be a symlink ever be a good idea? It seems like if things are the way you expect them to be, it has no benefit, but if they are different from what you expect, you might blow away a ton of important data. Maybe I am just confused. The suggestion is to get rid of using rmtree. Instead, if we find a non-symlink in pg_tblspc we'll make the user clean it up before we can continue. So your instinct is in tune with my suggestion. Find the patch which gets rid of rmtree usage. I have made it as a separate function because the same code is used from create_tablespace_directories() as well. I thought of extending the same API for using it from destroy_tablespace_directories() as well, but due to special handling (especially for ENOENT) in that function, I left it as of now. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com remove_only_symlinks_during_recovery_v1.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
[HACKERS] Minor improvements to alter_foreign_table.sgml
Hi, Here is a patch to improve the ALTER FOREIGN TABLE documentation a bit: (1) fix markup for ADD table_constraint [ NOT VALID ] and (2) remove an unnecessary comma from an example query. Best regards, Etsuro Fujita diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml b/doc/src/sgml/ref/alter_foreign_table.sgml index ace0040..4329d43 100644 --- a/doc/src/sgml/ref/alter_foreign_table.sgml +++ b/doc/src/sgml/ref/alter_foreign_table.sgml @@ -169,7 +169,7 @@ ALTER FOREIGN TABLE [ IF EXISTS ] replaceable class=PARAMETERname/replaceab /varlistentry varlistentry -termliteralADD replaceable class=PARAMETERtable_constraint/replaceable/literal [ NOT VALID ]/term +termliteralADD replaceable class=PARAMETERtable_constraint/replaceable [ NOT VALID ]/literal/term listitem para This form adds a new constraint to a foreign table, using the same @@ -541,7 +541,7 @@ ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL; para To change options of a foreign table: programlisting -ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2, 'value2', DROP opt3 'value3'); +ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3 'value3'); /programlisting/para /refsect1 -- 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 : REINDEX xxx VERBOSE
On Thu, May 14, 2015 at 4:30 PM, Sawada Masahiko sawada.m...@gmail.com wrote: On Thu, May 14, 2015 at 9:58 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, May 13, 2015 at 8:25 PM, Sawada Masahiko sawada.m...@gmail.com wrote: The v15 patch emits a line for each table when reindexing multiple tables, and emits a line for each index when reindexing single table. But v14 patch emits a line for each index, regardless of reindex target. Should I change back to v14 patch? Uh, maybe. What made you change it? I thought that the users who want to reindex multiple tables are interested in the time to reindex whole table takes. But I think it seems sensible to emit a line for each index even when reindex multiple tables. The v16 patch is based on v14 and a few modified is attached. Thanks for updating the patch! The regression test failed because you forgot to remove the trailng period from the verbose message in the expected file of the regression test. I just fixed it and push the patch. Regards, -- Fujii Masao -- 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] KNN-GiST with recheck
On Fri, May 15, 2015 at 2:48 PM, Heikki Linnakangas hlinn...@iki.fi wrote: On 05/15/2015 11:31 AM, Alexander Korotkov wrote: On Fri, May 15, 2015 at 2:30 AM, Heikki Linnakangas hlinn...@iki.fi wrote: On 05/15/2015 02:28 AM, Heikki Linnakangas wrote: I think this is now ready for committing, but I'm pretty tired now so I'll read through this one more time in the morning, so that I won't wake up to a red buildfarm. Forgot to attach the latest patch, here you go. Looks good for me. Ok, pushed after some further minor cleanup. Great! Thank you! -- With best regards, Alexander Korotkov.
Re: [HACKERS] KNN-GiST with recheck
On 05/15/2015 11:31 AM, Alexander Korotkov wrote: On Fri, May 15, 2015 at 2:30 AM, Heikki Linnakangas hlinn...@iki.fi wrote: On 05/15/2015 02:28 AM, Heikki Linnakangas wrote: I think this is now ready for committing, but I'm pretty tired now so I'll read through this one more time in the morning, so that I won't wake up to a red buildfarm. Forgot to attach the latest patch, here you go. Looks good for me. Ok, pushed after some further minor cleanup. - Heikki -- 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 for bug #12845 (GB18030 encoding)
On Thu, May 14, 2015 at 11:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: Robert Haas robertmh...@gmail.com writes: On Wed, May 6, 2015 at 11:13 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Maybe not, but at the very least we should consider getting it fixed in 9.5 rather than waiting a full development cycle. Same as in https://www.postgresql.org/message-id/20150428131549.ga25...@momjian.us I'm not saying we MUST include it in 9.5, but we should at least consider it. If we simply stash it in the open CF we guarantee that it will linger there for a year. Sure, if somebody has the time to put into it now, I'm fine with that. I'm afraid it won't be me, though: even if I had the time, I don't know enough about encodings. I concur that we should at least consider this patch for 9.5. I've added it to https://wiki.postgresql.org/wiki/PostgreSQL_9.5_Open_Items I looked at this patch a bit, and read up on GB18030 (thank you wikipedia). I concur we have a problem to fix. I do not like the way this patch went about it though, ie copying-and-pasting LocalToUtf and UtfToLocal and their supporting routines into utf8_and_gb18030.c. Aside from being duplicative, this means the improved mapping capability isn't available to use with anything except GB18030. (I do not know whether there are any linear mapping ranges in other encodings, but seeing that the Unicode crowd went to the trouble of defining a notation for it in http://www.unicode.org/reports/tr22/, I'm betting there are.) What I think would be a better solution, if slightly more invasive, is to extend LocalToUtf and UtfToLocal to add a callback function argument for a function of signature uint32 translate(uint32). This function, if provided, would be called after failing to find a mapping in the mapping table(s), and it could implement any translation that would be better handled by code than as a boatload of mapping-table entries. If it returns zero then it doesn't know a translation either, so throw error as before. An alternative definition that could be proposed would be to call the function before consulting the mapping tables, not after, on the grounds that the function can probably exit cheaply if the input's not in a range that it cares about. However, consulting the mapping table first wins if you have ranges that mostly work but contain a few exceptions: put the exceptions in the mapping table and then the function need not worry about handling them. Another alternative approach would be to try to define linear mapping ranges in a tabular fashion, for more consistency with what's there now. But that probably wouldn't work terribly well because the bytewise character representations used in this logic have to be converted into code points before you can do any sort of linear mapping. We could hard-wire that conversion for UTF8, but the conversion in the other code space would be encoding-specific. So we might as well just treat the whole linear mapping behavior as a black box function for each encoding. I'm also discounting the possibility that someone would want an algorithmic mapping for cases involving combined codes (ie pairs of UTF8 characters). Of the encodings we support, only EUC_JIS_2004 and SHIFT_JIS_2004 need such cases at all, and those have only a handful of cases; so it doesn't seem popular enough to justify the extra complexity. I also notice that pg_gb18030_verifier isn't even close to strict enough; it basically relies on pg_gb18030_mblen which contains no checks whatsoever on the third and fourth bytes. So that needs to be fixed. The verification tightening would definitely not be something to back-patch, and I'm inclined to think that the additional mapping capability shouldn't be either, in view of the facts that (a) we've had few if any field complaints yet, and (b) changing the signatures of LocalToUtf/UtfToLocal might possibly break third-party code. So I'm seeing this as a HEAD-only patch, but I do want to try to squeeze it into 9.5 rather than wait another year. Barring objections, I'll go make this happen. GB18030 is a special case, because it's a full mapping of all unicode characters, and most of it is algorithmically defined. This makes UtfToLocal a bad choice to implement it. UtfToLocal assumes a sparse array with only the defined characters. It uses binary search to find a character. The 2 tables it uses now are huge (the .so file is 1MB). Adding the rest of the valid characters to this scheme is possible, but would make the problem worse. I think fixing UtfToLocal only for the new characters is not optimal. I think the best solution is to get rid of UtfToLocal for GB18030. Use a specialized algorithm: - For characters U+ use the algorithm from my patch - For charcaters = U+ use special mapping tables to map from/to UTF32. Those tables would be smaller, and the code would be faster (I assume). For example (256 KB):
[HACKERS] Support for N synchronous standby servers - take 2
There was a discussion on support for N synchronous standby servers started by Michael. Refer http://archives.postgresql.org/message-id/cab7npqr9c84ig0zuvhmqamq53vqsd4rc82vyci4dr27pvof...@mail.gmail.com . The use of hooks and dedicated language was suggested, however, it seemed to be an overkill for the scenario and there was no consensus on this. Exploring GUC-land was preferred. Please find attached a patch, built on Michael's patch from above mentioned thread, which supports choosing different number of nodes from each set i.e. k nodes from set 1, l nodes from set 2, so on. The format of synchronous_standby_names has been updated to standby name followed by the required count separated by hyphen. Ex: 'aa-1, bb-3'. The transaction waits for all the specified number of standby in each group. Any extra nodes with the same name will be considered potential. The special entry * for the standby name is also supported. Thanks, Beena Emerson 20150515_multiple_sync_rep.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] Support for N synchronous standby servers - take 2
On Fri, May 15, 2015 at 8:55 PM, Beena Emerson memissemer...@gmail.com wrote:
There was a discussion on support for N synchronous standby servers started
by Michael. Refer
http://archives.postgresql.org/message-id/cab7npqr9c84ig0zuvhmqamq53vqsd4rc82vyci4dr27pvof...@mail.gmail.com
. The use of hooks and dedicated language was suggested, however, it seemed
to be an overkill for the scenario and there was no consensus on this.
Exploring GUC-land was preferred.
Cool.
Please find attached a patch, built on Michael's patch from above mentioned
thread, which supports choosing different number of nodes from each set i.e.
k nodes from set 1, l nodes from set 2, so on.
The format of synchronous_standby_names has been updated to standby name
followed by the required count separated by hyphen. Ex: 'aa-1, bb-3'. The
transaction waits for all the specified number of standby in each group. Any
extra nodes with the same name will be considered potential. The special
entry * for the standby name is also supported.
I don't think that this is going in the good direction, what was
suggested mainly by Robert was to use a micro-language that would
allow far more extensibility that what you are proposing. See for
example ca+tgmobpwoenmmepfx0jwrvqufxvbqrv26ezq_xhk21gxrx...@mail.gmail.com
for some ideas. IMO, before writing any patch in this area we should
find a clear consensus on what we want to do. Also, unrelated to this
patch, we should really get first the patch implementing the... Hum...
infrastructure for regression tests regarding replication and
archiving to be able to have actual tests for this feature (working on
it for next CF).
+if (!SplitIdentifierString(standby_detail, '-', elemlist2))
+{
+/* syntax error in list */
+pfree(rawstring);
+list_free(elemlist1);
+return 0;
+}
At quick glance, this looks problematic to me if application_name has an hyphen.
Regards,
--
Michael
--
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] feature freeze and beta schedule
On 1 May 2015 at 18:05, Simon Riggs si...@2ndquadrant.com wrote: * TABLESAMPLE clause Doesn't seem very far from being done. Some questions about including (or not) DDL and contrib modules seem to remain. Will commit this soon OK, completely happy with this now and will commit today. It's finally ready now, but I have a meeting, so don't want to turn buildfarm red and not have time to fix. Will be committing in about 5-6 hours time. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
[HACKERS] Re: [COMMITTERS] pgsql: Allow GiST distance function to return merely a lower-bound.
On 05/15/2015 03:17 PM, Heikki Linnakangas wrote: On 05/15/2015 03:05 PM, Fujii Masao wrote: Seems this patch causes the regression test of pg_trgm fail. The regression diff that I got is: *** /home/postgres/pgsql/head/contrib/pg_trgm/expected/pg_trgm.out 2013-07-23 16:46:22.212488785 +0900 --- /home/postgres/pgsql/head/contrib/pg_trgm/results/pg_trgm.out 2015-05-15 20:59:16.574926732 +0900 *** *** 2332,2343 (3 rows) select t - 'q0987wertyu0988', t from test_trgm order by t - 'q0987wertyu0988' limit 2; ! ?column? | t ! --+- ! 0.411765 | qwertyu0988 ! 0.5 | qwertyu0987 ! (2 rows) ! drop index trgm_idx; create index trgm_idx on test_trgm using gin (t gin_trgm_ops); set enable_seqscan=off; --- 2332,2338 (3 rows) select t - 'q0987wertyu0988', t from test_trgm order by t - 'q0987wertyu0988' limit 2; ! ERROR: index returned tuples in wrong order drop index trgm_idx; create index trgm_idx on test_trgm using gin (t gin_trgm_ops); set enable_seqscan=off; Hmm, OK. pg_trgm works for me, but I'll take a look. (rover_firefly also went red, due to rounding differences in the regression test) There are two issues here: 1. I forgot to initialize the recheck variable before calling the distance function. pg_trgm's distance function is never lossy, and it doesn't set recheck to anything. If 'recheck' happens to be true, because it's uninitialized, the executor node will try to reorder the tuples. 2. There is confusion on the datatype of the distance. pg_trgm's - operator returns float4, but the GIST code and pg_trgm's GIST distance function always returns a float8. Gist thus returns the distance as a float8, but the executor node re-calculates it as a float4, and then tries to compare the two using float8 operator. The immediate problem goes away if we fix 1. and initialize the variable, as the executor won't try to re-calculate or compare the ORDER BY expressions if the index never returns a lossy tuple, but the confusion on the datatypes is still real. It's not very nice that gist has a hardcoded assumption that the distance is always measured as a float8. It would be better to support whatever datatype the distance function returns, and use the type's comparison functions. But as a quick fix, I think we should add a special case for float4. Gist should check if the datatype of the original ordering operator is float4, and convert the float8 used internally to float4 before returning it. If the datatype is anything other than float4 or float8, throw an error. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 9.5 open items
I have processed all the open email items I can through mid-March, though I do have two pg_upgrade fixes pending application today. I will continue processing doc fixes and major bug fixes for 9.5, but everything else I do will be for 9.6. -- 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] best place for rtree strategy numbers
Alvaro Herrera alvaro.herr...@2ndquadrant.com writes: So here's a patch for this. Looks reasonable to me (though I only eyeballed it, not tested). Do we want to push this into 9.5, or wait for 9.6? 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 : REINDEX xxx VERBOSE
On Sun, May 10, 2015 at 2:23 AM, Sawada Masahiko sawada.m...@gmail.com wrote:
On Sat, May 9, 2015 at 4:26 AM, Fabrízio de Royes Mello
fabriziome...@gmail.com wrote:
On Fri, May 8, 2015 at 4:23 PM, Fabrízio de Royes Mello
fabriziome...@gmail.com wrote:
On Thu, May 7, 2015 at 7:55 PM, Sawada Masahiko sawada.m...@gmail.com
wrote:
On 5/7/15, Sawada Masahiko sawada.m...@gmail.com wrote:
On Wed, May 6, 2015 at 5:42 AM, Robert Haas robertmh...@gmail.com
javascript:; wrote:
On Tue, May 5, 2015 at 11:10 AM, Sawada Masahiko
sawada.m...@gmail.com
javascript:; wrote:
On Fri, May 1, 2015 at 9:04 PM, Robert Haas robertmh...@gmail.com
javascript:; wrote:
On Thu, Apr 30, 2015 at 11:05 PM, Sawada Masahiko
sawada.m...@gmail.com
javascript:; wrote:
VACUUM has both syntax: with parentheses and without parentheses.
I think we should have both syntax for REINDEX like VACUUM does
because it would be pain to put parentheses whenever we want to do
REINDEX.
Are the parentheses optional in REINDEX command?
No. The unparenthesized VACUUM syntax was added back before we
realized that that kind of syntax is a terrible idea. It requires
every option to be a keyword, and those keywords have to be in a
fixed
order. I believe the intention is to keep the old VACUUM syntax
around for backward-compatibility, but not to extend it. Same for
EXPLAIN and COPY.
REINDEX will have only one option VERBOSE for now.
Even we're in a situation like that it's not clear to be added newly
additional option to REINDEX now, we should need to put parenthesis?
In my opinion, yes. The whole point of a flexible options syntax is
that we can add new options without changing the grammar. That
involves some compromise on the syntax, which doesn't bother me a
bit.
Our previous experiments with this for EXPLAIN and COPY and VACUUM
have worked out quite well, and I see no reason for pessimism here.
I agree that flexible option syntax does not need to change grammar
whenever we add new options.
Attached patch is changed based on your suggestion.
And the patch for reindexdb is also attached.
Please feedbacks.
Also I'm not sure that both implementation and documentation
regarding
VERBOSE option should be optional.
I don't know what this means.
Sorry for confusing you.
Please ignore this.
Sorry, I forgot attach files.
I applied the two patches to master and I got some errors when compile:
tab-complete.c: In function ‘psql_completion’:
tab-complete.c:3338:12: warning: left-hand operand of comma expression has
no effect [-Wunused-value]
{TABLE, INDEX, SYSTEM, SCHEMA, DATABASE, NULL};
^
tab-complete.c:3338:21: warning: left-hand operand of comma expression has
no effect [-Wunused-value]
{TABLE, INDEX, SYSTEM, SCHEMA, DATABASE, NULL};
^
tab-complete.c:3338:31: warning: left-hand operand of comma expression has
no effect [-Wunused-value]
{TABLE, INDEX, SYSTEM, SCHEMA, DATABASE, NULL};
^
tab-complete.c:3338:41: warning: left-hand operand of comma expression has
no effect [-Wunused-value]
{TABLE, INDEX, SYSTEM, SCHEMA, DATABASE, NULL};
^
tab-complete.c:3338:53: warning: left-hand operand of comma expression has
no effect [-Wunused-value]
{TABLE, INDEX, SYSTEM, SCHEMA, DATABASE, NULL};
^
tab-complete.c:3338:5: warning: statement with no effect [-Wunused-value]
{TABLE, INDEX, SYSTEM, SCHEMA, DATABASE, NULL};
^
tab-complete.c:3338:59: error: expected ‘;’ before ‘}’ token
{TABLE, INDEX, SYSTEM, SCHEMA, DATABASE, NULL};
^
tab-complete.c:3340:22: error: ‘list_REINDEX’ undeclared (first use in
this function)
COMPLETE_WITH_LIST(list_REINDEX);
^
tab-complete.c:169:22: note: in definition of macro ‘COMPLETE_WITH_LIST’
completion_charpp = list; \
^
tab-complete.c:3340:22: note: each undeclared identifier is reported only
once for each function it appears in
COMPLETE_WITH_LIST(list_REINDEX);
^
tab-complete.c:169:22: note: in definition of macro ‘COMPLETE_WITH_LIST’
completion_charpp = list; \
^
make[3]: *** [tab-complete.o] Error 1
make[3]: *** Waiting for unfinished jobs
make[2]: *** [install-psql-recurse] Error 2
make[2]: *** Waiting for unfinished jobs
make[1]: *** [install-bin-recurse] Error 2
make: *** [install-src-recurse] Error 2
Looking at the code I think you remove one line accidentally from
tab-complete.c:
$ git diff src/bin/psql/tab-complete.c
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 750e29d..55b0df5 100644
--- a/src/bin/psql/tab-complete.c
+++
Re: [HACKERS] ERROR: cannot GetMultiXactIdMembers() during recovery
Hi hackers, Any chance to get this fixed in time for 9.1.16? .m -- 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] Providing catalog view to pg_hba.conf file - Patch submission
* Haribabu Kommi (kommi.harib...@gmail.com) wrote: On Tue, May 5, 2015 at 6:48 AM, Peter Eisentraut pete...@gmx.net wrote: On 5/1/15 12:33 PM, Andres Freund wrote: On 2015-04-08 19:19:29 +0100, Greg Stark wrote: I'm not sure what the best way to handle the hand-off from patch contribution to reviewer/committer. If I start tweaking things then you send in a new version it's actually more work to resolve the conflicts. I think at this point it's easiest if I just take it from here. Are you intending to commit this? It still looks quite dubious to me. The more I test this, the more fond I grow of the idea of having this information available in SQL. But I'm also growing more perplexed by how this the file is mapped to a table. It just isn't a good match. For instance: What is keyword_databases? Why is it an array? Same for keyword_users. How can I know whether a given database or user matches a keyword? What is compare_method? (Should perhaps be keyword_address?) Why is compare method set to mask when a hostname is set? (Column order is also a bit confusing here.) I'd also like options to be jsonb instead of a text array. Thanks for your suggestion. I am not sure how to use jsonb here, i will study the same and provide a patch for the next version. Regarding next version- are you referring to 9.6 and therefore we should go ahead and bounce this to the next CF, or were you planning to post a next version of the patch today? This is certainly a capability which I'd like to see, though I share Peter's concerns regarding the splitting up of the keywords rather than keeping the same structure as what's in the actual pg_hba.conf. That strikes me as confusing. It'd be neat if we were able to change pg_hba.conf to make more sense and then perhaps the SQL version wouldn't look so different but I don't think there's any way to do that. I discussed the patch briefing with Greg over IM, who pointed out that keeping things just exactly as they are in the config file would mean implementing, essentially, a pg_hba.conf parser in SQL. I can understand that perspective, but I don't think there's really much hope in users being able to use this view directly without a lot of effort, regardless. We need to provide a function which takes the arguments that our pg_hba lookup does (database, user-to-login-as, maybe system user for pg_ident checks, optionally an IP, etc) and then returns the record that matches. Apologies for not being able to provide more feedback earlier. I'll be happy to help with all of the above and review the patch. Independently, I'd love to see an SQL interface to pg_ident.conf too, where, I expect anyway, it'll be a lot simpler, though I'm not sure that it's very useful until we also have pg_hba.conf available through SQL. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] KNN-GiST with recheck
On Fri, May 15, 2015 at 02:48:29PM +0300, Heikki Linnakangas wrote: On 05/15/2015 11:31 AM, Alexander Korotkov wrote: On Fri, May 15, 2015 at 2:30 AM, Heikki Linnakangas hlinn...@iki.fi wrote: On 05/15/2015 02:28 AM, Heikki Linnakangas wrote: I think this is now ready for committing, but I'm pretty tired now so I'll read through this one more time in the morning, so that I won't wake up to a red buildfarm. Forgot to attach the latest patch, here you go. Looks good for me. Ok, pushed after some further minor cleanup. Great! That PostGIS workaround they had to use for accurate distances with CTEs and LIMIT 100 was an ugly hack. -- 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] Changes to backup.sgml
On Fri, May 15, 2015 at 08:15:51AM -0700, Joshua Drake wrote: On 05/15/2015 07:42 AM, Bruce Momjian wrote: 3. Push the rsync paragraph (and edit where appropriate) within the continuous archiving section. 3a. Add information about robocopy (windows rsync) Oh, yes, we should mention robocopy. I had never heard of that. 4. Move continuous archiving up above everything except pg_dump. Perhaps change time to Online Backup. Uh, I don't like Online Backup because pg_dump is an online backup, no? Online File Backup? Uh, yeah, kind of, though that starts to sound like file system backup. -- 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] Triaging the remaining open commitfest items
On 2015-05-14 23:28:33 +0200, Andres Freund wrote: I've removed the use of GroupedVars and Andrew is right now working on structural changes. I'm not ready at this point to make a judgement. Andrew worked really hard and addressed the voiced concerns with the way chaining was done. In my last read through I found a bunch of stylistic quibbles and a question about behaviour where reading the spec confirmed that the current implementation is actually correct ( grouping sets + functional dependencies = weird). I plan to post a squashed patches from what's in git now in a couple hours and then, unless something major (issues, protest) comes up, push PDT late afternoon. -- 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] i feel like compelled !
On Wed, May 13, 2015 at 7:05 PM, Gianni nasus.maxi...@gmail.com wrote: Oh well... then, THANKS GUYS!!! I'm not the original poster, btw. I felt a bit 'abandoned' a while back, since I started using Interbase/Firebird since, like, ~2000. But since Firebird never really took off, I felt I had to look for better solutions. I worked with Oracle for a bit, and then MySQL. But I found Oracle to be expensive (obviously) and too intrusive into my OS (Linux, many flavours, but mostly RedHat-based). What I really liked about Firebird, and then Postgres made me feel right at home, was standards-compliance with SQL and great feature set. I find myself most-often-than-not guessing how something ought to work in Postgres, based on past experiences, and finding that it works exactly (mostly) like it 'should'. Plus, I found many new things that I loved and changed the way I think about stuff, like using Python for SP, JSON fields and RegEx in WHERE. And a special mention to the Async NOTIFY stuff which finally works like it 'should' in a DB (Firebird had something like that, but with no payload). Also, how postgres is easy to deploy really helps. For example, I use it with a Qt App, which is compiled in MinGW. So I recompiled libpq with the same compiler, thus avoiding extra DLLs. Thanks for the kind words ... and for your thoughts on why you like PostgreSQL. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WALWriteLock contention
WALWriteLock contention is measurable on some workloads. In studying the problem briefly, a couple of questions emerged: 1. Doesn't it suck to rewrite an entire 8kB block every time, instead of only the new bytes (and maybe a few bytes following that to spoil any old data that might be there)? I mean, the OS page size is 4kB on Linux. If we generate 2kB of WAL and then flush, we're likely to dirty two OS blocks instead of one. The OS isn't going to be smart enough to notice that one of those pages didn't really change, so we're potentially generating some extra disk I/O. My colleague Jan Wieck has some (inconclusive) benchmark results that suggest this might actually be hurting us significantly. More research is needed, but I thought I'd ask if we've ever considered NOT doing that, or if we should consider it. 2. I don't really understand why WALWriteLock is set up to prohibit two backends from flushing WAL at the same time. That seems unnecessary. Suppose we've got two backends that flush WAL one after the other. Assume (as is not unlikely) that the second one's flush position is ahead of the first one's flush position. So the first one grabs WALWriteLock and does the flush, and then the second one grabs WALWriteLock for its turn to flush and has to wait for an entire spin of the platter to complete before its fsync() can be satisfied. If we'd just let the second guy issue his fsync() right away, odds are good that the disk would have satisfied both in a single rotation. Now it's possible that the second request would've arrived too late for that to work out, but AFAICS in that case we're no worse off than we are now. And if it does work out we're better off. The only reasons I can see why we might NOT want to do this are (1) if we're trying to compensate for some OS-level bugginess, which is a horrifying thought, or (2) if we think the extra system calls will cost more than we save by piggybacking the flushes more efficiently. Thoughts? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Changes to backup.sgml
On 05/15/2015 07:42 AM, Bruce Momjian wrote: 3. Push the rsync paragraph (and edit where appropriate) within the continuous archiving section. 3a. Add information about robocopy (windows rsync) Oh, yes, we should mention robocopy. I had never heard of that. 4. Move continuous archiving up above everything except pg_dump. Perhaps change time to Online Backup. Uh, I don't like Online Backup because pg_dump is an online backup, no? Online File Backup? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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 for bug #12845 (GB18030 encoding)
On Fri, May 15, 2015 at 4:10 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Arjen Nienhuis a.g.nienh...@gmail.com writes:
GB18030 is a special case, because it's a full mapping of all unicode
characters, and most of it is algorithmically defined.
True.
This makes UtfToLocal a bad choice to implement it.
I disagree with that conclusion. There are still 3+ characters
that need to be translated via lookup table, so we still need either
UtfToLocal or a clone of it; and as I said previously, I'm not on board
with cloning it.
I think the best solution is to get rid of UtfToLocal for GB18030. Use
a specialized algorithm:
- For characters U+ use the algorithm from my patch
- For charcaters = U+ use special mapping tables to map from/to
UTF32. Those tables would be smaller, and the code would be faster (I
assume).
I looked at what wikipeda claims is the authoritative conversion table:
http://source.icu-project.org/repos/icu/data/trunk/charset/data/xml/gb-18030-2000.xml
According to that, about half of the characters below U+ can be
processed via linear conversions, so I think we ought to save table
space by doing that. However, the remaining stuff that has to be
processed by lookup still contains a pretty substantial number of
characters that map to 4-byte GB18030 characters, so I don't think
we can get any table size savings by adopting a bespoke table format.
We might as well use UtfToLocal. (Worth noting in this connection
is that we haven't seen fit to sweat about UtfToLocal's use of 4-byte
table entries for other encodings, even though most of the others
are not concerned with characters outside the BMP.)
It's not about 4 vs 2 bytes, it's about using 8 bytes vs 4. UtfToLocal
uses a sparse array:
map = {{0, x}, {1, y}, {2, z}, ...}
v.s.
map = {x, y, z, ...}
That's fine when not every code point is used, but it's different for
GB18030 where almost all code points are used. Using a plain array
saves space and saves a binary search.
Gr. Arjen
--
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] Triaging the remaining open commitfest items
On 05/15/2015 12:32 PM, Josh Berkus wrote: Note that I am not proposing a general delay in feature freeze. I am specifically proposing an additional week for Grouping Sets and *only* for Grouping Sets. Core is in charge of releases. I believe like the other semi and formal organizations around this community it makes sense for Core to present a motion and for that motion to be voted upon. The vote can take place publicly (and there is an argument that it should) but it is a vote for core not hackers or general. In short, Josh, Bruce, you are both core members. If you want to call a vote, do so. Something like this should suffice: WHEREAS 1. The Core Committee of PGDG is responsible for releases of PostgreSQL 2. The feature Grouping Sets is a major feature for the upcoming release of PostgreSQL THE CORE COMMITTEE RESOLVES THAT 3. In an effort to produce a fair and equitable return for the efforts put in by contributors of the Grouping Sets patch, the core committee will extend feature freeze for the Grouping Sets patch for exactly 7 days. 4. This extension is for the Grouping Sets patch and the Grouping Sets patch only. 5. Should a committable patch not be produced within 7 days, the patch shall be pushed back into the queue for the production release of PostgreSQL. Sincerely, JD P.S. Note that I have seen the final patch that hit the list about 45 minutes ago. -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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] brin regression test intermittent failures
Andrew Dunstan wrote: There's something odd about the brin regression tests. They seem to generate intermittent failures, which suggests some sort of race condition or ordering failure. See for example http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=fulmardt=2015-05-15%2001%3A02%3A28 and http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=sittelladt=2015-05-15%2021%3A08%3A38 Yeah it's pretty odd. I guess the way to figure out what is going on is to get the test to print out the index contents in case of failure. I guess I could do something with \gset. (The way to print out the index is to use the pageinspect functions. One problem is that at the time the brin test is run we don't have pageinspect) Of course, if I could reproduce the issue locally, this would be a lot easier. -- Á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] WALWriteLock contention
On Fri, May 15, 2015 at 9:06 AM, Robert Haas robertmh...@gmail.com wrote: WALWriteLock contention is measurable on some workloads. In studying the problem briefly, a couple of questions emerged: ... 2. I don't really understand why WALWriteLock is set up to prohibit two backends from flushing WAL at the same time. That seems unnecessary. Suppose we've got two backends that flush WAL one after the other. Assume (as is not unlikely) that the second one's flush position is ahead of the first one's flush position. So the first one grabs WALWriteLock and does the flush, and then the second one grabs WALWriteLock for its turn to flush and has to wait for an entire spin of the platter to complete before its fsync() can be satisfied. If we'd just let the second guy issue his fsync() right away, odds are good that the disk would have satisfied both in a single rotation. Now it's possible that the second request would've arrived too late for that to work out, but AFAICS in that case we're no worse off than we are now. And if it does work out we're better off. The only reasons I can see why we might NOT want to do this are (1) if we're trying to compensate for some OS-level bugginess, which is a horrifying thought, or (2) if we think the extra system calls will cost more than we save by piggybacking the flushes more efficiently. I implemented this 2-3 years ago, just dropping the WALWriteLock immediately before the fsync and then picking it up again immediately after, and was surprised that I saw absolutely no improvement. Of course it surely depends on the IO stack, but from what I saw it seemed that once a fsync landed in the kernel, any future ones on that file were blocked rather than consolidated. Alas I can't find the patch anymore, I can make more of an effort to dig it up if anyone cares. Although it would probably be easier to reimplement it than it would be to find it and rebase it. I vaguely recall thinking that the post-fsync bookkeeping could be moved to a spin lock, with a fair bit of work, so that the WALWriteLock would not need to be picked up again, but the whole avenue didn't seem promising enough for me to worry about that part in detail. My goal there was to further improve group commit. When running pgbench -j10 -c10, it was common to see fsyncs that alternated between flushing 1 transaction, and 9 transactions. Because the first one to the gate would go through it and slam it on all the others, and it would take one fsync cycle for it reopen. Cheers, Jeff
[HACKERS] brin regression test intermittent failures
There's something odd about the brin regression tests. They seem to generate intermittent failures, which suggests some sort of race condition or ordering failure. See for example http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=fulmardt=2015-05-15%2001%3A02%3A28 and http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=sittelladt=2015-05-15%2021%3A08%3A38 cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] brin regression test intermittent failures
Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: Just from reading the documentation, couldn't the symptom we're seeing arise from autovacuum having hit the table right before brin_summarize_new_values got called? Well, I added a autovacuum_enabled=off to that table recently precisely because that was my hypothesis. It didn't work though, so it must be sometihng else. Ah. Not having noticed that, I'd locally added a pg_sleep(60) right before the brin_summarize_new_values call, and failed to reproduce any problem. So it's not AV doing something, but it sure smells like something close to that. Is there a good reason why we need to exercise brin_summarize_new_values as such here, rather than just doing a manual VACUUM on the table? And if there is, do we really need to verify its result value? I mean, even without whatever sort of race condition we're talking about, that expected result of 5 looks pretty darn phase-of-the-moon-dependent to me. 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] brin regression test intermittent failures
Alvaro Herrera alvhe...@2ndquadrant.com writes: Andrew Dunstan wrote: There's something odd about the brin regression tests. They seem to generate intermittent failures, which suggests some sort of race condition or ordering failure. See for example http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=fulmardt=2015-05-15%2001%3A02%3A28 and http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=sittelladt=2015-05-15%2021%3A08%3A38 Yeah it's pretty odd. Oooh. I saw the sittella failure and assumed it was triggered by the latest BRIN additions, but that fulmar failure is from before those hit. Just from reading the documentation, couldn't the symptom we're seeing arise from autovacuum having hit the table right before brin_summarize_new_values got called? 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] brin regression test intermittent failures
Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Andrew Dunstan wrote: There's something odd about the brin regression tests. They seem to generate intermittent failures, which suggests some sort of race condition or ordering failure. See for example http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=fulmardt=2015-05-15%2001%3A02%3A28 and http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=sittelladt=2015-05-15%2021%3A08%3A38 Yeah it's pretty odd. Oooh. I saw the sittella failure and assumed it was triggered by the latest BRIN additions, but that fulmar failure is from before those hit. Just from reading the documentation, couldn't the symptom we're seeing arise from autovacuum having hit the table right before brin_summarize_new_values got called? Well, I added a autovacuum_enabled=off to that table recently precisely because that was my hypothesis. It didn't work though, so it must be sometihng else. -- Á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] Problems with question marks in operators (JDBC, ECPG, ...)
On 05/15/2015 04:35 PM, Robert Haas wrote:
I guess JDBC has the same problem as Perl and JavaScript here: ?
signals a bind variable. The next question is, why isn't there some
escaping mechanism for that, like writing ?? or \? or something?
FTR, Perl's DBD::Pg lets you do this:
$dbh-{pg_placeholder_dollaronly} = 1; # disable ? placeholders
$sth = $dbh-prepare(q{SELECT * FROM mytable WHERE lseg1 ?# lseg2
AND name = $1});
$sth-execute('segname');
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] i feel like compelled !
On 5/13/15 6:05 PM, Gianni wrote: What I really liked about Firebird, and then Postgres made me feel right at home, was standards-compliance with SQL and great feature set. I find myself most-often-than-not guessing how something ought to work in Postgres, based on past experiences, and finding that it works exactly (mostly) like it 'should'. Plus, I found many new things that I loved and changed the way I think about stuff, like using Python for SP, JSON fields and RegEx in WHERE. And a special mention to the Async NOTIFY stuff which finally works like it 'should' in a DB (Firebird had something like that, but with no payload). FWIW, numerous people made similar comments about Postgres at this month's AustinPUG meeting when someone mentioned they haven't used it yet. Basically, they said he'd love using it. :) -- Jim Nasby, Data Architect, Blue Treble Consulting 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] KNN-GiST with recheck
On 5/14/15 6:30 PM, Heikki Linnakangas wrote: On 05/15/2015 02:28 AM, Heikki Linnakangas wrote: I think this is now ready for committing, but I'm pretty tired now so I'll read through this one more time in the morning, so that I won't wake up to a red buildfarm. If anyone feels motivated to fix, there's a typo in the comment for IndexNextWithReorder (s/his/this/): + * Like IndexNext, but his version can also re-check any -- Jim Nasby, Data Architect, Blue Treble Consulting 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] trust authentication behavior
2015-05-16 5:13 GMT+09:00 Robert Haas robertmh...@gmail.com:
On Thu, May 14, 2015 at 3:52 PM, David G. Johnston
david.g.johns...@gmail.com wrote:
On Thu, May 14, 2015 at 12:22 PM, Denis Kirjanov k...@itsirius.su wrote:
Yeah, but the idea is to do that without the pg_hba.conf
You may want to try describing the problem and not just ask if the chosen
solution is possible - of which I am doubtful but I have never used selinux
or studied it in any depth. pg_hba.conf is the chosen tool for this kind of
thing so pointing out why it cannot be used is a much more useful first
step.
In mandatory access control systems like SE-Linux, the system security
policy is supposed to centralize all security decisions, and it should
be possible to enforce any necessary access control rule by modifying
that policy. At least that's my understanding. sepgsql lets the
kernel's mandatory access control policies filter down into access
control decisions that PostgreSQL makes. sepgsql consults the
operating system policy when faced with an access control decision of
a type that it supports, and accepts or rejects the connect based on
that.
So the question is whether the sepgsql integration points include
anything that can block a connection, rather than, say, allowing the
connection but blocking access to particular tables. Looking at the
code, it appears that it vaguely contemplates a db_database:{access}
permission, which sounds like about the right thing, and it's also
mentioned at
https://wiki.postgresql.org/wiki/SEPostgreSQL/Permissions#Connection
as maybe being the right thing, but I can't find anyplace that it is
actually enforce. That's rather disappointing...
KaiGai, any thoughts?
I'd like to understand what Denis Kirjanov actually wants to do
first of all.
If he wants to control accesses whole of the PostgreSQL instances
according to the credential on operating system, it is a configuration
on operating system side.
He can write up self security policy module that allows someone_t
domain to connect PostgreSQL instance, not per database basis.
It is permission around the pair of someone_t and postgresql_t, than
can be wrapped up by postgresql_stream_connect() in selinux's policy.
If he wants to control accesses per database basis based on selinux
policy, it is a right choice to consider sepgsql module.
However, some of permissions are not still implemented, like
db_database:{access} because of priority of permissions (and I had to
focus on GPU acceleration infrastructure in v9.5 cycle...).
If he wants to control accesses based on the credential of operating
system, not limited to selinux, IDENT method is available, isn't it?
Also, he may additionally needs labeled networking configuration,
if he wants to carry security label information over the TCP/IP
connection. It is a point to be considered for his requirement.
Thanks,
--
KaiGai Kohei kai...@kaigai.gr.jp
--
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] KNN-GiST with recheck
On Fri, May 15, 2015 at 2:49 PM, Alexander Korotkov aekorot...@gmail.com wrote: On Fri, May 15, 2015 at 2:48 PM, Heikki Linnakangas hlinn...@iki.fi wrote: On 05/15/2015 11:31 AM, Alexander Korotkov wrote: On Fri, May 15, 2015 at 2:30 AM, Heikki Linnakangas hlinn...@iki.fi wrote: On 05/15/2015 02:28 AM, Heikki Linnakangas wrote: I think this is now ready for committing, but I'm pretty tired now so I'll read through this one more time in the morning, so that I won't wake up to a red buildfarm. Forgot to attach the latest patch, here you go. Looks good for me. Ok, pushed after some further minor cleanup. Great! Thank you! BTW, I found that now IndexScan node lackof copy and output support for indexorderbyops. Attached patch fixes that. Copy and output functions assume that indexorderbyops has the same length as indexorderby. In order to make this more evident I move check for best_path-path.pathkeys in create_plan from if into assertion. AFAICS, pathkeys should always present where there are indexorderby. -- With best regards, Alexander Korotkov. fix-indexscan-node.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] Triaging the remaining open commitfest items
On 5/13/15 7:46 PM, Kouhei Kaigai wrote: * ctidscan as an example of custom-scan This basically hasn't gotten any attention, which may mean nobody cares enough to justify putting it in the tree. We need to either push it to next CF or reject altogether. Agreed. I was fine with never committing this. I don't think we have a requirement that every hook or bit of functionality we expose at the C level must have an example in core. But other people (you? Simon?) seemed to want a demonstration in the core repository. If that's still a priority, I am willing to work on it more for 9.6, but there is not time now. If no other people required it again, I don't think this module should be kept in core and also I'm not favor to push ctidscan to v9.6 development cycle. It intends to demonstrate custom-scan interface, however, it is not certain an example always needs to be in-core. FWIW, having TIDGreaterOperator would be very useful for anyone trying to un-bloat a table, so it'd be nice if this was at least available as a PGXN extension. -- Jim Nasby, Data Architect, Blue Treble Consulting 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] Triaging the remaining open commitfest items
On 5/14/15 5:48 PM, Tom Lane wrote: True, but I have problems with leaders acting in a way that is unfair to those with less power. Have you considered how demoralizing it is to work in an unfair environment? Unfairness happens, but as leaders, we are supposed to try to avoid it, not cause it. TBH, every time somebody beats me up about not having dropped everything else to spend a month on this patch, it just makes me want to back away further. I haven't had the time, and I really could do without accusations of that being unfair. FWIW, I don't think that's what people are expressing an issue with. Rather, while you were marked as committer no one else was working on it even thought they might have had you not been marked. I think (or at least hope) people understand that things happen, especially when $JOB intervenes. OTOH, once you were no longer marked as committer I don't think it's fair to hold you accountable for people not stepping back up. -- Jim Nasby, Data Architect, Blue Treble Consulting 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] Patch for bug #12845 (GB18030 encoding)
Arjen Nienhuis a.g.nienh...@gmail.com writes: GB18030 is a special case, because it's a full mapping of all unicode characters, and most of it is algorithmically defined. True. This makes UtfToLocal a bad choice to implement it. I disagree with that conclusion. There are still 3+ characters that need to be translated via lookup table, so we still need either UtfToLocal or a clone of it; and as I said previously, I'm not on board with cloning it. I think the best solution is to get rid of UtfToLocal for GB18030. Use a specialized algorithm: - For characters U+ use the algorithm from my patch - For charcaters = U+ use special mapping tables to map from/to UTF32. Those tables would be smaller, and the code would be faster (I assume). I looked at what wikipeda claims is the authoritative conversion table: http://source.icu-project.org/repos/icu/data/trunk/charset/data/xml/gb-18030-2000.xml According to that, about half of the characters below U+ can be processed via linear conversions, so I think we ought to save table space by doing that. However, the remaining stuff that has to be processed by lookup still contains a pretty substantial number of characters that map to 4-byte GB18030 characters, so I don't think we can get any table size savings by adopting a bespoke table format. We might as well use UtfToLocal. (Worth noting in this connection is that we haven't seen fit to sweat about UtfToLocal's use of 4-byte table entries for other encodings, even though most of the others are not concerned with characters outside the BMP.) 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] best place for rtree strategy numbers
Tom Lane wrote: Alvaro Herrera alvaro.herr...@2ndquadrant.com writes: So here's a patch for this. Looks reasonable to me (though I only eyeballed it, not tested). Do we want to push this into 9.5, or wait for 9.6? My intention is to push this now, before pushing brin inclusion. -- Á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] Changes to backup.sgml
On Thu, May 14, 2015 at 09:53:01AM -0700, Joshua Drake wrote: -hackers, After my brain flatulence last week on backups, I decided to read the docs again. There are some improvements that I would like to make and wanted some feedback: 1. File System Level Backup The section should be a note within the larger document. It is largely a legacy section from before 8.3. Uh, file system snapshots on VMs make this feature still useful, I think. 2. Rework the paragraph about consistent snapshots into its own section OK. 3. Push the rsync paragraph (and edit where appropriate) within the continuous archiving section. 3a. Add information about robocopy (windows rsync) Oh, yes, we should mention robocopy. I had never heard of that. 4. Move continuous archiving up above everything except pg_dump. Perhaps change time to Online Backup. Uh, I don't like Online Backup because pg_dump is an online backup, no? 4a. I want to do some general rewording, there are some places where the documentation is not clear. I can just do this and then let the reviewers have their say. Sure. -- 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] Triaging the remaining open commitfest items
On 2015-05-13 11:38:27 -0400, Tom Lane wrote: Looking at what remains open in the current commitfest: As of now the remaining items !bugfix entries are: * GIN fillfactor I'd like to put this one on Heikki's plate as well, since he's touched the GIN code more than anyone else lately. While sad, I think this is going to have to be moved. * Additional role attributes Is this ready to commit? Stephen's call. This was still being discussed/spec'ed recently, so I think it's not too bad to move this now. * catalog view to pg_hba.conf file Greg Stark is marked as committer of record on this. A bit sad again. I think we can close the commitfest now? Moving these three entries to the next one? 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] Providing catalog view to pg_hba.conf file - Patch submission
On Fri, May 15, 2015 at 11:24 PM, Stephen Frost sfr...@snowman.net wrote: * Haribabu Kommi (kommi.harib...@gmail.com) wrote: On Tue, May 5, 2015 at 6:48 AM, Peter Eisentraut pete...@gmx.net wrote: It still looks quite dubious to me. The more I test this, the more fond I grow of the idea of having this information available in SQL. But I'm also growing more perplexed by how this the file is mapped to a table. It just isn't a good match. For instance: What is keyword_databases? Why is it an array? Same for keyword_users. How can I know whether a given database or user matches a keyword? What is compare_method? (Should perhaps be keyword_address?) Why is compare method set to mask when a hostname is set? (Column order is also a bit confusing here.) I'd also like options to be jsonb instead of a text array. Thanks for your suggestion. I am not sure how to use jsonb here, i will study the same and provide a patch for the next version. Regarding next version- are you referring to 9.6 and therefore we should go ahead and bounce this to the next CF, or were you planning to post a next version of the patch today? Yes, for 9.6 version. This is certainly a capability which I'd like to see, though I share Peter's concerns regarding the splitting up of the keywords rather than keeping the same structure as what's in the actual pg_hba.conf. That strikes me as confusing. It'd be neat if we were able to change pg_hba.conf to make more sense and then perhaps the SQL version wouldn't look so different but I don't think there's any way to do that. I discussed the patch briefing with Greg over IM, who pointed out that keeping things just exactly as they are in the config file would mean implementing, essentially, a pg_hba.conf parser in SQL. I can understand that perspective, but I don't think there's really much hope in users being able to use this view directly without a lot of effort, regardless. We need to provide a function which takes the arguments that our pg_hba lookup does (database, user-to-login-as, maybe system user for pg_ident checks, optionally an IP, etc) and then returns the record that matches. Thanks for details. I will try to come up with a view and a function by considering all the above for the next commitfest. Apologies for not being able to provide more feedback earlier. I'll be happy to help with all of the above and review the patch. Independently, I'd love to see an SQL interface to pg_ident.conf too, where, I expect anyway, it'll be a lot simpler, though I'm not sure that it's very useful until we also have pg_hba.conf available through SQL. Yes, Definitely I look into pg_ident also along with pg_hba. Regards, Hari Babu Fujitsu Australia -- 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] Final Patch for GROUPING SETS
On 2015-05-16 00:06:12 +0200, Andres Freund wrote: Andrew (and I) have been working on this since. Here's the updated and rebased patch. It misses a decent commit message and another beautification readthrough. I've spent the last hour going through the thing again and all I hit was a disturbing number of newline errors and two minor comment additions. And committed. Thanks Andrew, everyone. Despite some unhappiness all around I do think the patch has improved due to the discussions in this thread. -- 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] Triaging the remaining open commitfest items
On Sat, May 16, 2015 at 11:00 AM, Andres Freund wrote: On 2015-05-13 11:38:27 -0400, Tom Lane wrote: * GIN fillfactor I'd like to put this one on Heikki's plate as well, since he's touched the GIN code more than anyone else lately. While sad, I think this is going to have to be moved. Yeah, I was rather confident in what Alexander did here. But life is life, and so is deadline. * Additional role attributes Is this ready to commit? Stephen's call. This was still being discussed/spec'ed recently, so I think it's not too bad to move this now. Moved to next CF. * catalog view to pg_hba.conf file Greg Stark is marked as committer of record on this. A bit sad again. Moved to next CF. I think we can close the commitfest now? Moving these three entries to the next one? And CF closed. -- Michael -- 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] Triaging the remaining open commitfest items
Andres Freund and...@anarazel.de writes: I think we can close the commitfest now? Moving these three entries to the next one? Yeah, I don't think any of the remaining entries are committable. 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] Triaging the remaining open commitfest items
On 05/14/2015 03:58 PM, Bruce Momjian wrote: On Thu, May 14, 2015 at 06:57:24PM -0400, Tom Lane wrote: Stephen Frost sfr...@snowman.net writes: * Bruce Momjian (br...@momjian.us) wrote: I will call for a vote that the freeze deadline be changed if this patch is rejected to due to time. I might lose the vote, but I am going to try because if we lose our reputation for fairness, we have lost a lot more than a week/month of release time. I'm guessing the vote is core-only, but +1 from me in any case. I fully agree that this patch has had a serious measure of effort put behind it from the author and is absolutely a capability we desire and need to have in core. I should think we'd have learned by now what happens when we delay a release date to get in some extra feature. It hasn't worked well in the past and I see no reason to believe the results would be any more desirable this time. Right, the importance of the feature is not a reason to delay the feature freeze. It has nothing to do with the importance of the feature. It has everything to do with fairness. Regardless of what Tom did or didn't do, what we have here is a major feature patch which was submitted in a timely fashion, and then *not reviewed* for multiple commitfests, and now in danger of being bounced because it's late. Considering that many other things have been committed which were submitted significantly later than Grouping Sets, including some which have been committed with the acknowledgement that there is more work do do during beta, this would have the appearance of being prejudicial against Gierth. Grouping Sets has been working, at least in demo form, since November. I really don't think we can, as a project, afford to have the appearance of prejudice in the review process. Things are bad enough already; if contributors feel that the review process is blatantly unfair, they will resort to underhanded means to get their patches in, and things will break down completely. We're only holding stuff together despite short resources because contributors believe in the inherent fairness of the process and the committers and that scarce resources will be allocated evenly. Note that I am not proposing a general delay in feature freeze. I am specifically proposing an additional week for Grouping Sets and *only* for Grouping Sets. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] PATCH: adaptive ndistinct estimator v4
On 05/15/2015 11:30 AM, Robert Haas wrote: Once we enter beta (or even feature freeze), it's too late to whack around the algorithm heavily. We're pretty much committed to releasing and supporting whatever we have got at that point. I guess we could revert it if it doesn't work out, but that's about the only option at that point. We have more flexibility during the main part of the development cycle. But your point is certainly valid and I don't mean to dispute it. I will finally have a customer workload available to test this on this weekend. That's been rather delayed by the availability of customer hardware,because I'm not allowed to copy out the database. However, this is a database which suffers from multiple ndistinct estimation issues in production, so I should be able to get a set of stats back by Monday which would show how much of a general improvement it is. I realize that's after the deadline, but there wasn't much I could do about it. I've tried to simulate the kind of estimation issues I've seen, but they don't simulate well. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] broken documentation: BackgroundWorkerInitializeConnection(NULL, NULL);
On Thu, May 14, 2015 at 8:25 AM, Pavel Stehule pavel.steh...@gmail.com wrote: The documentation (or this feature) is broken still If dbname is NULL or dboid is InvalidOid, the session is not connected to any particular database, but shared catalogs can be accessed. If username is NULL or useroid is InvalidOid, the process will run as the superuser created during initdb. A background worker can only call one of these two functions, and only once. It is not possible to switch databases. But it fails with error: FATAL: database 0 does not exist Ugh. I think that's a bug. Patch attached. The test code I used to verify that this works is also attached. If there are no objections, I will commit and back-patch. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] WALWriteLock contention
On 05/15/2015 09:06 AM, Robert Haas wrote: 2. I don't really understand why WALWriteLock is set up to prohibit two backends from flushing WAL at the same time. That seems unnecessary. Suppose we've got two backends that flush WAL one after the other. Assume (as is not unlikely) that the second one's flush position is ahead of the first one's flush position. So the first one grabs WALWriteLock and does the flush, and then the second one grabs WALWriteLock for its turn to flush and has to wait for an entire spin of the platter to complete before its fsync() can be satisfied. If we'd just let the second guy issue his fsync() right away, odds are good that the disk would have satisfied both in a single rotation. Now it's possible that the second request would've arrived too late for that to work out, but AFAICS in that case we're no worse off than we are now. And if it does work out we're better off. The only This is a bit out of my depth but it sounds similar to (from a user perspective) the difference between synchronous and asynchronous commit. If we are willing to trust that PostgreSQL/OS will do what it is supposed to do, then it seems logical that what you describe above would definitely be a net win. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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] ERROR: cannot GetMultiXactIdMembers() during recovery
On 15 May 2015 at 19:03, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Andres Freund wrote: Alternatively we could make MultiXactIdIsRunning() return false 9.3 when in recovery. I think that'd end up fixing things, but it seems awfully fragile to me. Hm, why fragile? It seems a pretty decent answer -- pre-9.3, it's not possible for a tuple to be locked in recovery, is it? I mean, in the standby you can't lock it nor update it; the only thing you can do is read (select), and that is not affected by whether there is a multixact in it. It can't return true and won't ever change for 9.3 so I don't see what the objection is. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] WALWriteLock contention
On Fri, May 15, 2015 at 1:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: WALWriteLock contention is measurable on some workloads. In studying the problem briefly, a couple of questions emerged: 1. Doesn't it suck to rewrite an entire 8kB block every time, instead of only the new bytes (and maybe a few bytes following that to spoil any old data that might be there)? It does, but it's not clear how to avoid torn-write conditions without that. Can you elaborate? I don't understand how repeatedly overwriting the same bytes with themselves accomplishes anything at all. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] PATCH: adaptive ndistinct estimator v4
On Wed, May 13, 2015 at 5:07 PM, Jeff Janes jeff.ja...@gmail.com wrote: With the warning it is very hard to correlate the discrepancy you do see with which column is causing it, as the warnings don't include table or column names (Assuming of course that you run it on a substantial database--if you just run it on a few toy cases then the warning works well). Presumably the warning is going to go away before we actually commit this thing. If we want to have an explicitly experimental patch which we want people with interesting real-world databases to report back on, what kind of patch would it have to be to encourage that to happen? Or are we never going to get such feedback no matter how friendly we make it? Another problem is that you really need to have the gold standard to compare them to, and getting that is expensive (which is why we resort to sampling in the first place). I don't think there is much to be done on that front other than bite the bullet and just do it--perhaps only for the tables which have discrepancies. If we stick with the idea of a GUC to control the behavior, then somebody can run ANALYZE, save the ndistinct estimates, run ANALYZE again, and compare. They can also run SQL queries against the tables themselves to check the real value. We could even provide a script for all of that. I think that would be quite handy. It can't hurt, but how effective will it be? Will developers know or care whether ndistinct happened to get better or worse while they are working on other things? I would think that problems will be found by focused testing, or during beta, and probably not by accidental discovery during the development cycle. It can't hurt, but I don't know how much it will help. Once we enter beta (or even feature freeze), it's too late to whack around the algorithm heavily. We're pretty much committed to releasing and supporting whatever we have got at that point. I guess we could revert it if it doesn't work out, but that's about the only option at that point. We have more flexibility during the main part of the development cycle. But your point is certainly valid and I don't mean to dispute it. I agree with the experimental GUC. That way if hackers do happen to see something suspicious, they can just turn it off and see what difference it makes. If they have to reverse out a patch from 6 months ago in an area of the code they aren't particularly interested in and then recompile their code and then juggle two different sets of binaries, they will likely just shrug it off without investigation. Yep. Users, too. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Changes to backup.sgml
On Thu, May 14, 2015 at 12:53 PM, Joshua D. Drake j...@commandprompt.com wrote: 1. File System Level Backup The section should be a note within the larger document. It is largely a legacy section from before 8.3. I agree. I think this section is just plain weird at this point. Most people would assume that you can copy or move the database files when the database server is shut down, but few people would consider that a usable backup strategy. I'm not sure exactly how this should be refactored, but I think something should be done. Maybe the title should be something like Moving or Copying Database Files instead of File System Level Backup, and the content could be adjusted to fit that theme. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Disabling trust/ident authentication configure option
Yes, I'd like to know if Alvaros suggestion would in deed achieve consensus (possibly with Andrews addition). It looks like the most general solution but might be some work using autoconf ... Best regards, Volker On Wed, May 13, 2015 at 2:18 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, May 13, 2015 at 8:01 AM, Volker Aßmann volker.assm...@gmail.com wrote: Even in this case it still means that any breach in any of the network services running on your application server would immediately own your database, or at least everything your application can access. This applies even to totally unrelated services running with restricted permissions. Using password or certificate based authentication at least gives you the additional security of local filesystem access controls and is not much harder to setup. M2M authentication is always a difficult topic as the authentication tokens have to be secured but I would agree that a more specific / secure method than disable-all-authentication would be preferable. Sure, opinions on the best way to do any given thing are going to vary, and nobody's trying to prevent you from configuring your instances of PostgreSQL however you like. The email to which I was responding was suggesting limiting MY ability to set up MY instances of PostgreSQL the way I like. And I'm opposed to that. All of this is fairly far afield from the original topic of this thread, which was whether a configure option disabling trust + ident authentication would be a good idea. I said no. Then we had a bunch of counter-proposals: Alvaro: Support a configure switch whose value is a comma-separated list of authentication methods to disable. Peter: Generalized hardening facility. Andrew: Like what Alvaro said, but require at least one of trust + peer to remain enabled so people can't hose themselves. Andrew, v2: Rip out RFC1413 ident authentication completely. Stephen: Require a command-line option to use trust auth. There's clearly no consensus on any of these proposals, and most of them don't address your original requirement anyway, though Alvaro's would. I guess the point is that nothing is going to get changed here on one person's say-so if other people don't agree, so if you want to get something done, you're going to need to pick something that can achieve consensus and then implement that. Also, anything you want to get done is certainly going to be in 9.6 at the earliest, because the time for 9.5 proposals has already come and gone. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] ERROR: cannot GetMultiXactIdMembers() during recovery
Andres Freund wrote: Alternatively we could make MultiXactIdIsRunning() return false 9.3 when in recovery. I think that'd end up fixing things, but it seems awfully fragile to me. Hm, why fragile? It seems a pretty decent answer -- pre-9.3, it's not possible for a tuple to be locked in recovery, is it? I mean, in the standby you can't lock it nor update it; the only thing you can do is read (select), and that is not affected by whether there is a multixact in it. -- Á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] Missing importing option of postgres_fdw
On Thu, May 14, 2015 at 6:37 AM, Etsuro Fujita fujita.ets...@lab.ntt.co.jp wrote: On second thought, I noticed that as for this option, we cannot live without allowing IMPORT FOREIGN SCHEMA to return ALTER FOREIGN TABLE statements because we cannot declare the convalidated information in the CREATE FOREIGN TABLE statement. So, I think we shoould also allow it to return ALTER FOREIGN TABLE statements. Am I right? Isn't convalidated utterly meaningless for constraints on foreign tables? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] PATCH: adaptive ndistinct estimator v4
On Fri, May 15, 2015 at 3:35 PM, Josh Berkus j...@agliodbs.com wrote: On 05/15/2015 11:30 AM, Robert Haas wrote: Once we enter beta (or even feature freeze), it's too late to whack around the algorithm heavily. We're pretty much committed to releasing and supporting whatever we have got at that point. I guess we could revert it if it doesn't work out, but that's about the only option at that point. We have more flexibility during the main part of the development cycle. But your point is certainly valid and I don't mean to dispute it. I will finally have a customer workload available to test this on this weekend. That's been rather delayed by the availability of customer hardware,because I'm not allowed to copy out the database. However, this is a database which suffers from multiple ndistinct estimation issues in production, so I should be able to get a set of stats back by Monday which would show how much of a general improvement it is. I realize that's after the deadline, but there wasn't much I could do about it. I've tried to simulate the kind of estimation issues I've seen, but they don't simulate well. This is clearly 9.6 material at this point, and has been for a while. The patch - at least the last version I looked at - didn't store anything different in pg_statistic. It just logged what it would have stored. So testing is good, but there's not a question of pushing this into 9.5. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] PATCH: adaptive ndistinct estimator v4
On 05/15/2015 12:58 PM, Robert Haas wrote: On Fri, May 15, 2015 at 3:35 PM, Josh Berkus j...@agliodbs.com wrote: On 05/15/2015 11:30 AM, Robert Haas wrote: Once we enter beta (or even feature freeze), it's too late to whack around the algorithm heavily. We're pretty much committed to releasing and supporting whatever we have got at that point. I guess we could revert it if it doesn't work out, but that's about the only option at that point. We have more flexibility during the main part of the development cycle. But your point is certainly valid and I don't mean to dispute it. I will finally have a customer workload available to test this on this weekend. That's been rather delayed by the availability of customer hardware,because I'm not allowed to copy out the database. However, this is a database which suffers from multiple ndistinct estimation issues in production, so I should be able to get a set of stats back by Monday which would show how much of a general improvement it is. I realize that's after the deadline, but there wasn't much I could do about it. I've tried to simulate the kind of estimation issues I've seen, but they don't simulate well. This is clearly 9.6 material at this point, and has been for a while. The patch - at least the last version I looked at - didn't store anything different in pg_statistic. It just logged what it would have stored. So testing is good, but there's not a question of pushing this into 9.5. I'm personally OK with that. The last thing we want to do is make query costing changes *in haste*. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] broken documentation: BackgroundWorkerInitializeConnection(NULL, NULL);
On Fri, May 15, 2015 at 4:07 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Robert Haas wrote: On Fri, May 15, 2015 at 3:53 PM, Robert Haas robertmh...@gmail.com wrote: The test code I used to verify that this works is also attached. If there are no objections, I will commit and back-patch. Oops. Really attached this time. We have spi_worker in src/test/modules now -- I think it makes sense to add this one there too in master. Really? I was thinking of the test code as throwaway. I just wanted to fix the bug. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] trust authentication behavior
On Thu, May 14, 2015 at 3:52 PM, David G. Johnston
david.g.johns...@gmail.com wrote:
On Thu, May 14, 2015 at 12:22 PM, Denis Kirjanov k...@itsirius.su wrote:
Yeah, but the idea is to do that without the pg_hba.conf
You may want to try describing the problem and not just ask if the chosen
solution is possible - of which I am doubtful but I have never used selinux
or studied it in any depth. pg_hba.conf is the chosen tool for this kind of
thing so pointing out why it cannot be used is a much more useful first
step.
In mandatory access control systems like SE-Linux, the system security
policy is supposed to centralize all security decisions, and it should
be possible to enforce any necessary access control rule by modifying
that policy. At least that's my understanding. sepgsql lets the
kernel's mandatory access control policies filter down into access
control decisions that PostgreSQL makes. sepgsql consults the
operating system policy when faced with an access control decision of
a type that it supports, and accepts or rejects the connect based on
that.
So the question is whether the sepgsql integration points include
anything that can block a connection, rather than, say, allowing the
connection but blocking access to particular tables. Looking at the
code, it appears that it vaguely contemplates a db_database:{access}
permission, which sounds like about the right thing, and it's also
mentioned at
https://wiki.postgresql.org/wiki/SEPostgreSQL/Permissions#Connection
as maybe being the right thing, but I can't find anyplace that it is
actually enforce. That's rather disappointing...
KaiGai, any thoughts?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Problems with question marks in operators (JDBC, ECPG, ...)
Not sure what the point of this is: as you indicated the ship has sailed so
to speak
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On 15 May 2015 at 15:14, Bruno Harbulot br...@distributedmatter.net wrote:
Hello,
I've been trying to use the new JSONB format using JDBC, and ran into
trouble with the question mark operators (?, ?| and ?).
I realise there has already been a discussion about this (actually, it was
about hstore, not jsonb, but that's more or less the same problem):
- http://www.postgresql.org/message-id/51114165.4070...@abshere.net
-
http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/48.html
From what I gather, the JDBC team seems to think that using ? in operators
is not in line with the SQL standards, but the outcome on the PostgreSQL
list team suggested that a fix could be implemented in the PostgreSQL JDBC
driver anyway.
I think this problem might actually affect a number of other places,
unfortunately. I must admit I don't know the SQL specifications very well
(a quick look at a draft seemed to suggest the question mark was indeed a
reserved character, but this is probably out of context), and this isn't
about finding out who is right or who is wrong, but from a practical point
of view, this also seemed to affect other kinds of clients, for example:
- Perl:
http://blog.endpoint.com/2015/01/dbdpg-escaping-placeholders-with.html
- JavaScript: https://github.com/tgriesser/knex/issues/519
Of course, there can be workarounds in some cases, but even if they work,
they can be quite awkward, especially if they differ from one language to
another (in particular if you want to be able to re-use the same query from
multiple languages).
As far, as I can tell, question mark operators are also incompatible with
PostgreSQL's ECPG when using dynamic SQL.
http://www.postgresql.org/docs/current/static/ecpg-dynamic.html
(I'm pasting an example at the end of this message, tried with a
PostgreSQL 9.4 server.)
I realise it's a bit late to raise this concern, considering that these
operators have been around for a few versions now (at least as far as
hstore), but wouldn't it be better to provide official alternative
notations altogether, something that is less likely to conflict with most
client implementations? Perhaps a function or a notation similar to what
'CAST(x AS y)' is to 'x::y' would be suitable if other symbols aren't
better (although I think a short operator would still be preferable).
Best wishes,
Bruno.
ECPG test output:
** Using query: SELECT ('{key1:123,key2:Hello}'::jsonb -
?::text)::text
Result should be 123 for 'key1': 123
Result should be empty for 'key3':
** Using query: SELECT ('{key1:123,key2:Hello}'::jsonb ?
?::text)::text
SQL error: syntax error at or near $1 on line 52
SQL error: invalid statement name mystmt3 on line 55
Result should be true for 'key1':
SQL error: invalid statement name mystmt3 on line 59
Result should be false for 'key3':
SQL error: invalid statement name mystmt3 on line 62
ECPG test code:
#include stdio.h
#include stdlib.h
int main()
{
EXEC SQL BEGIN DECLARE SECTION;
char* target = unix:postgresql://localhost/mydatabase;
char result1[2048];
int result1_ind;
char *key1_str = key1;
char *key3_str = key3;
char *stmt2 = SELECT ('{\key1\:123,\key2\:\Hello\}'::jsonb
- ?::text)::text;
char *stmt3 = SELECT ('{\key1\:123,\key2\:\Hello\}'::jsonb
? ?::text)::text;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL CONNECT TO :target AS testdb;
printf(\n\n** Using query: %s\n\n, stmt2);
EXEC SQL PREPARE mystmt2 FROM :stmt2;
result1[0] = 0;
EXEC SQL EXECUTE mystmt2 INTO :result1 :result1_ind USING :key1_str;
printf(Result should be 123 for 'key1': %s\n, result1);
result1[0] = 0;
EXEC SQL EXECUTE mystmt2 INTO :result1 :result1_ind USING :key3_str;
printf(Result should be empty for 'key3': %s\n, result1);
EXEC SQL DEALLOCATE PREPARE mystmt2;
printf(\n\n** Using query: %s\n\n, stmt3);
EXEC SQL PREPARE mystmt3 FROM :stmt3;
result1[0] = 0;
EXEC SQL EXECUTE mystmt3 INTO :result1_ind USING :key1_str;
printf(Result should be true for 'key1': %s\n, result1);
result1[0] = 0;
EXEC SQL EXECUTE mystmt3 INTO :result1_ind USING :key3_str;
printf(Result should be false for 'key3': %s\n, result1);
EXEC SQL DEALLOCATE PREPARE mystmt3;
EXEC SQL DISCONNECT ALL;
return 0;
}
Re: [HACKERS] broken documentation: BackgroundWorkerInitializeConnection(NULL, NULL);
On Fri, May 15, 2015 at 4:15 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Really? I was thinking of the test code as throwaway. I just wanted to fix the bug. Oh, that's fine then. I thought you wanted to push it. Nah, sorry, I shoulda been more clear about that. That was just so I could actually be sure I had the fix right. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Problems with question marks in operators (JDBC, ECPG, ...)
On Fri, May 15, 2015 at 4:13 PM, Dave Cramer p...@fastcrypt.com wrote: Not sure what the point of this is: as you indicated the ship has sailed so to speak Well, if we were to agree this was a problem, we could introduce new, less-problematic operator names and then eventually deprecate the old ones. Personally, it wouldn't take a lot to convince me that if a certain set of operator names is problematic for important connectors, we should avoid using those and switch to other ones. I expect others on this mailing list to insist that if the connectors don't work, that's the connector drivers fault for coding their connectors wrong. And maybe that's the right answer, but on the other hand, maybe it's a little myopic. I think the discussion is worth having. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Problems with question marks in operators (JDBC, ECPG, ...)
As far, as I can tell, question mark operators are also incompatible with PostgreSQL's ECPG when using dynamic SQL. http://www.postgresql.org/docs/current/static/ecpg-dynamic.html (I'm pasting an example at the end of this message, tried with a PostgreSQL 9.4 server.) Indeed it is. The question mark is used in ecpg to denote a variable to be filled-in by the process. I'm not completely sure if this was in the standard or only implemented because several (not sure if all) other precompiler used it as well. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael.meskes at gmail dot com VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- 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] Problems with question marks in operators (JDBC, ECPG, ...)
On 15 May 2015 at 16:41, Robert Haas robertmh...@gmail.com wrote: On Fri, May 15, 2015 at 4:38 PM, Dave Cramer p...@fastcrypt.com wrote: I don't really want to take a violently strong position on this without understanding what's really going on here. Well our solution was to use ?? but that does mean we have to do some extra parsing which in a perfect world wouldn't be necessary. So what about strings quoted with '' or $$ or $something$ - how would you handle those? We parse for strings; the ?? just adds to the parsing load which we really try to avoid. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
Re: [HACKERS] broken documentation: BackgroundWorkerInitializeConnection(NULL, NULL);
On Fri, May 15, 2015 at 3:53 PM, Robert Haas robertmh...@gmail.com wrote:
On Thu, May 14, 2015 at 8:25 AM, Pavel Stehule pavel.steh...@gmail.com
wrote:
The documentation (or this feature) is broken still
If dbname is NULL or dboid is InvalidOid, the session is not connected to
any particular database, but shared catalogs can be accessed. If username is
NULL or useroid is InvalidOid, the process will run as the superuser created
during initdb. A background worker can only call one of these two functions,
and only once. It is not possible to switch databases.
But it fails with error:
FATAL: database 0 does not exist
Ugh. I think that's a bug.
Patch attached.
The test code I used to verify that this works is also attached.
If there are no objections, I will commit and back-patch.
Oops. Really attached this time.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index debadf0..28a4966 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -827,7 +827,7 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,
/* take database name from the caller, just for paranoia */
strlcpy(dbname, in_dbname, sizeof(dbname));
}
- else
+ else if (OidIsValid(dboid))
{
/* caller specified database by OID */
HeapTuple tuple;
@@ -847,6 +847,18 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,
if (out_dbname)
strcpy(out_dbname, dbname);
}
+ else
+ {
+ /*
+ * If this is a background worker not bound to any particular
+ * database, we're done now. Everything that follows only makes
+ * sense if we are bound to a specific database. We do need to
+ * close the transaction we started before returning.
+ */
+ if (!bootstrap)
+ CommitTransactionCommand();
+ return;
+ }
/* Now we can mark our PGPROC entry with the database ID */
/* (We assume this is an atomic store so no lock is needed) */
diff --git a/contrib/no_db_worker/Makefile b/contrib/no_db_worker/Makefile
new file mode 100644
index 000..2085c95
--- /dev/null
+++ b/contrib/no_db_worker/Makefile
@@ -0,0 +1,18 @@
+# contrib/no_db_worker
+
+MODULES = no_db_worker
+
+EXTENSION = no_db_worker
+DATA = no_db_worker--1.0.sql
+PGFILEDESC = no_db_worker - background worker without database
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/no_db_worker
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/no_db_worker/no_db_worker--1.0.sql b/contrib/no_db_worker/no_db_worker--1.0.sql
new file mode 100644
index 000..a38ec63
--- /dev/null
+++ b/contrib/no_db_worker/no_db_worker--1.0.sql
@@ -0,0 +1,7 @@
+/* contrib/no_db_worker/no_db_worker--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use CREATE EXTENSION no_db_worker to load this file. \quit
+
+CREATE FUNCTION no_db_worker_launch() RETURNS pg_catalog.int4 STRICT
+AS 'MODULE_PATHNAME' LANGUAGE C;
diff --git a/contrib/no_db_worker/no_db_worker.c b/contrib/no_db_worker/no_db_worker.c
new file mode 100644
index 000..2a09bc4
--- /dev/null
+++ b/contrib/no_db_worker/no_db_worker.c
@@ -0,0 +1,103 @@
+/* -
+ *
+ * no_db_worker.c
+ * A database worker that does not connect to any particular database.
+ *
+ * Copyright (C) 2015, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/no_db_worker/no_db_worker.c
+ *
+ * -
+ */
+#include postgres.h
+
+#include access/relscan.h
+#include access/xact.h
+#include catalog/pg_database.h
+#include fmgr.h
+#include miscadmin.h
+#include postmaster/bgworker.h
+#include storage/ipc.h
+#include utils/rel.h
+#include utils/snapmgr.h
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(no_db_worker_launch);
+
+extern void no_db_worker_main(Datum main_arg);
+
+void
+no_db_worker_main(Datum main_arg)
+{
+ Relation rel;
+ HeapScanDesc scan;
+ HeapTuple tup;
+
+ BackgroundWorkerInitializeConnection(NULL, NULL);
+
+ StartTransactionCommand();
+ (void) GetTransactionSnapshot();
+
+ rel = heap_open(DatabaseRelationId, AccessShareLock);
+ scan = heap_beginscan_catalog(rel, 0, NULL);
+
+ while (HeapTupleIsValid(tup = heap_getnext(scan, ForwardScanDirection)))
+ {
+ Form_pg_database pgdatabase = (Form_pg_database) GETSTRUCT(tup);
+
+ elog(LOG, found database with OID %u and name \%s\,
+ HeapTupleGetOid(tup), NameStr(pgdatabase-datname));
+ }
+
+ elog(LOG, done scanning pg_database);
+
+ heap_endscan(scan);
+ heap_close(rel, AccessShareLock);
+
+ proc_exit(1);
+}
+
+/*
+ * Dynamically launch an SPI worker.
+ */
+Datum
+no_db_worker_launch(PG_FUNCTION_ARGS)
+{
+ BackgroundWorker worker;
+
Re: [HACKERS] Patch for bug #12845 (GB18030 encoding)
On Fri, May 15, 2015 at 3:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: However, I'm not that excited about changing it. We have not heard field complaints about these converters being too slow. What's more, there doesn't seem to be any practical way to apply the same idea to the other conversion direction, which means if you do feel there's a speed problem this would only halfway fix it. Half a loaf is better than none. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] broken documentation: BackgroundWorkerInitializeConnection(NULL, NULL);
Robert Haas wrote: On Fri, May 15, 2015 at 3:53 PM, Robert Haas robertmh...@gmail.com wrote: The test code I used to verify that this works is also attached. If there are no objections, I will commit and back-patch. Oops. Really attached this time. We have spi_worker in src/test/modules now -- I think it makes sense to add this one there too in master. -- Á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] log bloating with shortlife bgworkers?
On Fri, May 15, 2015 at 2:04 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I am planning to use short living bg workers. I was little bit surprised so any start and finish does entry in log. Is there any plan to decrease a log level for these purposes? Parallel query is going to hit that issue, too. I imagine we'll do something about it at some point. Maybe add a flag that reduces the log level to DEBUG1 or DEBUG2. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] broken documentation: BackgroundWorkerInitializeConnection(NULL, NULL);
Robert Haas wrote: On Fri, May 15, 2015 at 4:07 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Robert Haas wrote: On Fri, May 15, 2015 at 3:53 PM, Robert Haas robertmh...@gmail.com wrote: The test code I used to verify that this works is also attached. If there are no objections, I will commit and back-patch. Oops. Really attached this time. We have spi_worker in src/test/modules now -- I think it makes sense to add this one there too in master. Really? I was thinking of the test code as throwaway. I just wanted to fix the bug. Oh, that's fine then. I thought you wanted to push it. -- Á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] Problems with question marks in operators (JDBC, ECPG, ...)
On 15 May 2015 at 16:21, Robert Haas robertmh...@gmail.com wrote: On Fri, May 15, 2015 at 4:13 PM, Dave Cramer p...@fastcrypt.com wrote: Not sure what the point of this is: as you indicated the ship has sailed so to speak Well, if we were to agree this was a problem, we could introduce new, less-problematic operator names and then eventually deprecate the old ones. Personally, it wouldn't take a lot to convince me that if a certain set of operator names is problematic for important connectors, we should avoid using those and switch to other ones. I expect others on this mailing list to insist that if the connectors don't work, that's the connector drivers fault for coding their connectors wrong. And maybe that's the right answer, but on the other hand, maybe it's a little myopic. I think the discussion is worth having. In that case my vote is new operators. This has been a sore point for the JDBC driver Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Fri, May 15, 2015 at 4:23 PM, Dave Cramer p...@fastcrypt.com wrote:
Well, if we were to agree this was a problem, we could introduce new,
less-problematic operator names and then eventually deprecate the old
ones. Personally, it wouldn't take a lot to convince me that if a
certain set of operator names is problematic for important connectors,
we should avoid using those and switch to other ones. I expect others
on this mailing list to insist that if the connectors don't work,
that's the connector drivers fault for coding their connectors wrong.
And maybe that's the right answer, but on the other hand, maybe it's a
little myopic. I think the discussion is worth having.
In that case my vote is new operators. This has been a sore point for the
JDBC driver
I guess JDBC has the same problem as Perl and JavaScript here: ?
signals a bind variable. The next question is, why isn't there some
escaping mechanism for that, like writing ?? or \? or something?
I ask because, you know, suppose you write this:
INSERT INTO foo VALUES ('How many pickled peppers did Peter Piper pick?');
Or alternatively this:
INSERT INTO foo VALUES ($$If Peter piper picked a peck of pickled
peppers, where's the peck of pickled peppers Peter Piper picked?$$);
Those have also got question marks in them. Do they also get
interpreted as bind variables?
I don't really want to take a violently strong position on this
without understanding what's really going on here.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Problems with question marks in operators (JDBC, ECPG, ...)
On 15 May 2015 at 16:35, Robert Haas robertmh...@gmail.com wrote:
On Fri, May 15, 2015 at 4:23 PM, Dave Cramer p...@fastcrypt.com wrote:
Well, if we were to agree this was a problem, we could introduce new,
less-problematic operator names and then eventually deprecate the old
ones. Personally, it wouldn't take a lot to convince me that if a
certain set of operator names is problematic for important connectors,
we should avoid using those and switch to other ones. I expect others
on this mailing list to insist that if the connectors don't work,
that's the connector drivers fault for coding their connectors wrong.
And maybe that's the right answer, but on the other hand, maybe it's a
little myopic. I think the discussion is worth having.
In that case my vote is new operators. This has been a sore point for the
JDBC driver
I guess JDBC has the same problem as Perl and JavaScript here: ?
signals a bind variable. The next question is, why isn't there some
escaping mechanism for that, like writing ?? or \? or something?
I ask because, you know, suppose you write this:
INSERT INTO foo VALUES ('How many pickled peppers did Peter Piper pick?');
Or alternatively this:
INSERT INTO foo VALUES ($$If Peter piper picked a peck of pickled
peppers, where's the peck of pickled peppers Peter Piper picked?$$);
Those have also got question marks in them. Do they also get
interpreted as bind variables?
I don't really want to take a violently strong position on this
without understanding what's really going on here.
Well our solution was to use ?? but that does mean we have to do some
extra parsing which in a perfect world wouldn't be necessary.
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Fri, May 15, 2015 at 4:38 PM, Dave Cramer p...@fastcrypt.com wrote: I don't really want to take a violently strong position on this without understanding what's really going on here. Well our solution was to use ?? but that does mean we have to do some extra parsing which in a perfect world wouldn't be necessary. So what about strings quoted with '' or $$ or $something$ - how would you handle those? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Problems with question marks in operators (JDBC, ECPG, ...)
On 15 May 2015 at 16:44, Dave Cramer p...@fastcrypt.com wrote: On 15 May 2015 at 16:41, Robert Haas robertmh...@gmail.com wrote: On Fri, May 15, 2015 at 4:38 PM, Dave Cramer p...@fastcrypt.com wrote: I don't really want to take a violently strong position on this without understanding what's really going on here. Well our solution was to use ?? but that does mean we have to do some extra parsing which in a perfect world wouldn't be necessary. So what about strings quoted with '' or $$ or $something$ - how would you handle those? We parse for strings; the ?? just adds to the parsing load which we really try to avoid. The ?? is just harder to deal with because ? is part of the JDBC spec as a placeholder Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Fri, May 15, 2015 at 9:41 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, May 15, 2015 at 4:38 PM, Dave Cramer p...@fastcrypt.com wrote: I don't really want to take a violently strong position on this without understanding what's really going on here. Well our solution was to use ?? but that does mean we have to do some extra parsing which in a perfect world wouldn't be necessary. So what about strings quoted with '' or $$ or $something$ - how would you handle those? I hadn't realised that the JDBC driver allowed the ? operator to be escaped as ??. It seems to work indeed (at least with version 9.4-1201 of the JDBC driver). $$?$$ also works. I guess the JDBC drivers tries to parse literals first and escapes them accordingly. That said, I'd still suggest providing new operators and deprecating the ones containing a question mark if possible. (There are 8 distinct operator names like this: ?-, ?, ?, ?#, ?||, ?-|, ?| and ?.) I think it would be nicer to have a single mechanism that can be used consistently across multiple languages (?? doesn't work for ECPG, for example), considering that ? as a placeholder seems quite common. Best wishes, Bruno.
Re: [HACKERS] Minor improvement to create_foreign_table.sgml
On Thu, May 14, 2015 at 4:07 AM, Etsuro Fujita fujita.ets...@lab.ntt.co.jp wrote: The attached patch adds missing NO INHERIT to the CHECK clause in the synopsis section in the reference page on CREATE FOREIGN TABLE. Good catch. Committed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Patch for bug #12845 (GB18030 encoding)
Arjen Nienhuis a.g.nienh...@gmail.com writes:
On Fri, May 15, 2015 at 4:10 PM, Tom Lane t...@sss.pgh.pa.us wrote:
According to that, about half of the characters below U+ can be
processed via linear conversions, so I think we ought to save table
space by doing that. However, the remaining stuff that has to be
processed by lookup still contains a pretty substantial number of
characters that map to 4-byte GB18030 characters, so I don't think
we can get any table size savings by adopting a bespoke table format.
We might as well use UtfToLocal. (Worth noting in this connection
is that we haven't seen fit to sweat about UtfToLocal's use of 4-byte
table entries for other encodings, even though most of the others
are not concerned with characters outside the BMP.)
It's not about 4 vs 2 bytes, it's about using 8 bytes vs 4. UtfToLocal
uses a sparse array:
map = {{0, x}, {1, y}, {2, z}, ...}
v.s.
map = {x, y, z, ...}
That's fine when not every code point is used, but it's different for
GB18030 where almost all code points are used. Using a plain array
saves space and saves a binary search.
Well, it doesn't save any space: if we get rid of the additional linear
ranges in the lookup table, what remains is 30733 entries requiring about
256K, same as (or a bit less than) what you suggest.
The point about possibly being able to do this with a simple lookup table
instead of binary search is valid, but I still say it's a mistake to
suppose that we should consider that only for GB18030. With the reduced
table size, the GB18030 conversion tables are not all that far out of line
with the other Far Eastern conversions:
$ size utf8*.so | sort -n
textdata bss dec hex filename
1880 512 162408 968 utf8_and_ascii.so
2394 528 162938 b7a utf8_and_iso8859_1.so
6674 512 1672021c22 utf8_and_cyrillic.so
24318 904 16 252386296 utf8_and_win.so
28750 968 16 297347426 utf8_and_iso8859.so
121110 512 16 121638 1db26 utf8_and_euc_cn.so
123458 512 16 123986 1e452 utf8_and_sjis.so
133606 512 16 134134 20bf6 utf8_and_euc_kr.so
185014 512 16 185542 2d4c6 utf8_and_sjis2004.so
185522 512 16 186050 2d6c2 utf8_and_euc2004.so
212950 512 16 213478 341e6 utf8_and_euc_jp.so
221394 512 16 221922 362e2 utf8_and_big5.so
274772 512 16 275300 43364 utf8_and_johab.so
26 512 16 278304 43f20 utf8_and_uhc.so
332262 512 16 332790 513f6 utf8_and_euc_tw.so
350640 512 16 351168 55bc0 utf8_and_gbk.so
496680 512 16 497208 79638 utf8_and_gb18030.so
If we were to get excited about reducing the conversion time for GB18030,
it would clearly make sense to use similar infrastructure for GBK, and
perhaps the EUC encodings too.
However, I'm not that excited about changing it. We have not heard field
complaints about these converters being too slow. What's more, there
doesn't seem to be any practical way to apply the same idea to the other
conversion direction, which means if you do feel there's a speed problem
this would only halfway fix it.
So my feeling is that the most practical and maintainable answer is to
keep GB18030 using code that is mostly shared with the other encodings.
I've committed a fix that does it that way for 9.5. If you want to
pursue the idea of a faster conversion using direct lookup tables,
I think that would be 9.6 material at this point.
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] Problems with question marks in operators (JDBC, ECPG, ...)
Hello,
I've been trying to use the new JSONB format using JDBC, and ran into
trouble with the question mark operators (?, ?| and ?).
I realise there has already been a discussion about this (actually, it was
about hstore, not jsonb, but that's more or less the same problem):
- http://www.postgresql.org/message-id/51114165.4070...@abshere.net
-
http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/48.html
From what I gather, the JDBC team seems to think that using ? in operators
is not in line with the SQL standards, but the outcome on the PostgreSQL
list team suggested that a fix could be implemented in the PostgreSQL JDBC
driver anyway.
I think this problem might actually affect a number of other places,
unfortunately. I must admit I don't know the SQL specifications very well
(a quick look at a draft seemed to suggest the question mark was indeed a
reserved character, but this is probably out of context), and this isn't
about finding out who is right or who is wrong, but from a practical point
of view, this also seemed to affect other kinds of clients, for example:
- Perl:
http://blog.endpoint.com/2015/01/dbdpg-escaping-placeholders-with.html
- JavaScript: https://github.com/tgriesser/knex/issues/519
Of course, there can be workarounds in some cases, but even if they work,
they can be quite awkward, especially if they differ from one language to
another (in particular if you want to be able to re-use the same query from
multiple languages).
As far, as I can tell, question mark operators are also incompatible with
PostgreSQL's ECPG when using dynamic SQL.
http://www.postgresql.org/docs/current/static/ecpg-dynamic.html
(I'm pasting an example at the end of this message, tried with a PostgreSQL
9.4 server.)
I realise it's a bit late to raise this concern, considering that these
operators have been around for a few versions now (at least as far as
hstore), but wouldn't it be better to provide official alternative
notations altogether, something that is less likely to conflict with most
client implementations? Perhaps a function or a notation similar to what
'CAST(x AS y)' is to 'x::y' would be suitable if other symbols aren't
better (although I think a short operator would still be preferable).
Best wishes,
Bruno.
ECPG test output:
** Using query: SELECT ('{key1:123,key2:Hello}'::jsonb -
?::text)::text
Result should be 123 for 'key1': 123
Result should be empty for 'key3':
** Using query: SELECT ('{key1:123,key2:Hello}'::jsonb ?
?::text)::text
SQL error: syntax error at or near $1 on line 52
SQL error: invalid statement name mystmt3 on line 55
Result should be true for 'key1':
SQL error: invalid statement name mystmt3 on line 59
Result should be false for 'key3':
SQL error: invalid statement name mystmt3 on line 62
ECPG test code:
#include stdio.h
#include stdlib.h
int main()
{
EXEC SQL BEGIN DECLARE SECTION;
char* target = unix:postgresql://localhost/mydatabase;
char result1[2048];
int result1_ind;
char *key1_str = key1;
char *key3_str = key3;
char *stmt2 = SELECT ('{\key1\:123,\key2\:\Hello\}'::jsonb
- ?::text)::text;
char *stmt3 = SELECT ('{\key1\:123,\key2\:\Hello\}'::jsonb ?
?::text)::text;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL CONNECT TO :target AS testdb;
printf(\n\n** Using query: %s\n\n, stmt2);
EXEC SQL PREPARE mystmt2 FROM :stmt2;
result1[0] = 0;
EXEC SQL EXECUTE mystmt2 INTO :result1 :result1_ind USING :key1_str;
printf(Result should be 123 for 'key1': %s\n, result1);
result1[0] = 0;
EXEC SQL EXECUTE mystmt2 INTO :result1 :result1_ind USING :key3_str;
printf(Result should be empty for 'key3': %s\n, result1);
EXEC SQL DEALLOCATE PREPARE mystmt2;
printf(\n\n** Using query: %s\n\n, stmt3);
EXEC SQL PREPARE mystmt3 FROM :stmt3;
result1[0] = 0;
EXEC SQL EXECUTE mystmt3 INTO :result1_ind USING :key1_str;
printf(Result should be true for 'key1': %s\n, result1);
result1[0] = 0;
EXEC SQL EXECUTE mystmt3 INTO :result1_ind USING :key3_str;
printf(Result should be false for 'key3': %s\n, result1);
EXEC SQL DEALLOCATE PREPARE mystmt3;
EXEC SQL DISCONNECT ALL;
return 0;
}
Re: [HACKERS] WALWriteLock contention
Robert Haas robertmh...@gmail.com writes: WALWriteLock contention is measurable on some workloads. In studying the problem briefly, a couple of questions emerged: 1. Doesn't it suck to rewrite an entire 8kB block every time, instead of only the new bytes (and maybe a few bytes following that to spoil any old data that might be there)? It does, but it's not clear how to avoid torn-write conditions without that. 2. I don't really understand why WALWriteLock is set up to prohibit two backends from flushing WAL at the same time. That seems unnecessary. Hm, perhaps so. 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] multivariate statistics / patch v6
Hello, On 05/15/15 08:29, Kyotaro HORIGUCHI wrote: Hello, Regarding the functional dependencies - you're right there's room for improvement. For example it only works with dependencies between pairs of columns, not multi-column dependencies. Is this what you mean by incomplete? No, It overruns dependencies-deps because build_mv_dependencies stores many elements into dependencies-deps[n] although it really has a room for only one element. I suppose that you paused writing it when you noticed that the number of required elements is unknown before finising walk through all pairs of values. palloc'ing numattrs^2 is reasonable enough as POC code for now. Am I looking wrong version of patch? -dependencies = (MVDependencies)palloc0(sizeof(MVDependenciesData)) +dependencies = (MVDependencies)palloc0(sizeof(MVDependenciesData) + +sizeof(MVDependency) * numattrs * numattrs); Ah! That's clearly a bug. Thanks for noticing that, will fix in the next version of the patch. I mention this because I recently had a issue from strong correlation between two columns in dbt3 benchmark. Two columns in some table are in strong correlation but not in functional dependencies, there are too many values and the distribution of them is very uniform so MCV is no use for the table (histogram has nothing to do with equal conditions). As the result, planner estimates the number of rows largely wrong as expected especially for joins. I think the other statistics types (esp. histograms) might be more useful here, but I assume you haven't tried that because of the conflicts. The current patch does not handle joins at all, though. Well, that's one of the resons. But I understood that any deterministic estimation cannot be applied for such distribution when I saw what made the wrong estimation. eqsel and eqsel_join finally relies on random match assumption on uniform distribution when the value is not found in MCV list. And functional dependencies stuff in your old patch (which works) (rightfully) failed to find such relationship between the problematic columns. So I tried ndistinct, which is not contained in your patch to see how it works well. Yes, that's certainly true. I think you're right that mv coefficient might be quite useful in some cases. With my patch: alter table t add statistics (mcv) on (a,b,c); ... Seq Scan on t (cost=0.00..22906.00 rows=9533 width=12) Yes, your MV-MCV list should have one third of all possible (set of) values so it works fine, I guess. But my original problem was occurred on the condition that (the single column) MCVs contain under 1% of possible values, MCV would not work for such cases, but its very uniform distribution helps random assumption to work. Actually, I think the MCV list should contain all the items, as it decides the sample contains all the values from the data. The usual 1D MCV list uses the same logic. But you're right that on a data set with more MCV items and mostly uniform distribution, this won't work. $ perl gentbl.pl 20 | psql postgres takes a while.. posttres=# alter table t1 add statistics (mcv true) on (a, b); postgres=# analyze t1; postgres=# explain analyze select * from t1 where a = 1 and b = 2501; Seq Scan on t1 (cost=0.00..124319.00 rows=1 width=8) (actual time=0.051..1250.773 rows=8 loops=1) The estimate rows=1 is internally 2.4e-11, 3.33e+11 times smaller than the real number. This will result in roughly the same order of error for joins. This is because MV-MCV holds too small part of the domain and then calculated using random assumption. This won't be not saved by increasing statistics_target to any sane amount. Yes, the MCV lists don't do work well with data sets like this. alter table t drop statistics all; alter table t add statistics (histogram) on (a,b,c); ... Seq Scan on t (cost=0.00..22906.00 rows=9667 width=12) So both the MCV list and histogram do quite a good work here, I understand how you calculate selectivity for equality clauses using histogram. And it calculates the result rows as 2.3e-11, which is almost same as MV-MCV, and this comes the same cause with it then yields the same result for joins. but there are certainly cases when that does not work and the mvcoefficient works better. +1 The condition mv-coef is effective where, as metioned above, MV-MCV or MV-HISTO cannot hold sufficient part of the domain. The appropriate combination of MV-MCV and mv-coef would be the same as va_eq_(non_)const/eqjoinsel_inner for single column, which is, applying mv-coef on the part of selectivity corresponding to values not in MV-MCV. I have no idea to combinate it with MV-HISTOGRAM right now. The current patch does not handle joins, but it's one of the TODO items. Yes, but the result on the very large tables can be deduced from the discussion above. I think the result above shows that the multivariate coefficient is significant to
Re: [HACKERS] multivariate statistics / patch v6
Hello,
On 05/15/15 08:29, Kyotaro HORIGUCHI wrote:
Hello,
At Thu, 14 May 2015 12:35:50 +0200, Tomas Vondra
tomas.von...@2ndquadrant.com wrote in 55547a86.8020...@2ndquadrant.com
...
Regarding the functional dependencies - you're right there's room for
improvement. For example it only works with dependencies between pairs
of columns, not multi-column dependencies. Is this what you mean by
incomplete?
No, It overruns dependencies-deps because build_mv_dependencies
stores many elements into dependencies-deps[n] although it
really has a room for only one element. I suppose that you paused
writing it when you noticed that the number of required elements
is unknown before finising walk through all pairs of
values. palloc'ing numattrs^2 is reasonable enough as POC code
for now. Am I looking wrong version of patch?
-dependencies = (MVDependencies)palloc0(sizeof(MVDependenciesData))
+dependencies = (MVDependencies)palloc0(sizeof(MVDependenciesData) +
+sizeof(MVDependency) * numattrs * numattrs);
Actually, looking at this a bit more, I think the current behavior is
correct. I assume the line is from build_mv_dependencies(), but the
whole block looks like this:
if (dependencies == NULL)
{
dependencies = (MVDependencies)palloc0(sizeof(MVDependenciesData));
dependencies-magic = MVSTAT_DEPS_MAGIC;
}
else
dependencies = repalloc(dependencies,
offsetof(MVDependenciesData, deps) +
sizeof(MVDependency) * (dependencies-ndeps + 1));
which allocates space for a single element initially, and then extends
that when other dependencies are added.
--
Tomas Vondra http://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] Triaging the remaining open commitfest items
On 2015-05-15 18:00:49 +0200, Andres Freund wrote: On 2015-05-14 23:28:33 +0200, Andres Freund wrote: I've removed the use of GroupedVars and Andrew is right now working on structural changes. I'm not ready at this point to make a judgement. Andrew worked really hard and addressed the voiced concerns with the way chaining was done. In my last read through I found a bunch of stylistic quibbles and a question about behaviour where reading the spec confirmed that the current implementation is actually correct ( grouping sets + functional dependencies = weird). I plan to post a squashed patches from what's in git now in a couple hours and then, unless something major (issues, protest) comes up, push PDT late afternoon. Here's why I think it's somewhat important that we make progress on the issue, and why I want to go ahead with this: In my eye Tom has, I'll assume unintentionally, stalled progress on this patch for nearly half a year. Due to Tom's profile it's unlikely that somebody else is going to seriously tackle a nontrivial patch that Tom has laid claims to. Especially if fundamental objections have been made, without also painting a way forward. In addition, by commenting on various triage emails that he'll get to it at some point, Tom in my view essentially has cemented that claim. Due to that I think the issue can't be characterized, as done nearby, as one of unduly laying claims to Tom's time, which obviously is his own to manage. The way it turned out people were forced to do that. I do think that part of the problem is that Andrew (Gierth, not Dunstan) isn't always easy to work with. Particularly there seems to be a very unfortunate dynamic between both Tom and Andrew. But if one is annoyed about someone's communication style I think it's much better to ignore that person or publically lash out about it. Essentially blocking progress of a patch for months is in my opinion a poor way of handling it. If Tom had said a couple months, or even weeks, ago that he doesn't have time to look into the patch in the 9.5 cycle, I'd not even think about pressing forward with the patch at this time of the cycle after it had just undergone significant changes. I like to think that it would have already gotten in at that point, but who knows. But either way, we're not in normal circumstances with regard to this patch. Our community has a reputation, and increasingly so, of being very painful to work with. Given the growth in adoption, without a corresponding growth in experienced long term contributors, I don't think we can afford feeding that reputation with more justified causes. We have difficulties keeping up even today. If the patch were in a bad shape I wouldn't even consider pressing ahead. But I don't think it is anymore. It's also not a patch that has the danger to destabilize postgres in the longer term. The code is fairly specific to grouping sets. Doesn't change the disk format. If we in hindsight discover the implementation wasn't using the right approach we can just change it. The worst that will happen is that explain output changes. I think many, much more dangerous, patches have been integreated into 9.5 with less review. 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] Changes to backup.sgml
On 05/15/2015 10:03 AM, Robert Haas wrote: On Thu, May 14, 2015 at 12:53 PM, Joshua D. Drake j...@commandprompt.com wrote: 1. File System Level Backup The section should be a note within the larger document. It is largely a legacy section from before 8.3. I agree. I think this section is just plain weird at this point. Most people would assume that you can copy or move the database files when the database server is shut down, but few people would consider that a usable backup strategy. I'm not sure exactly how this should be refactored, but I think something should be done. Maybe the title should be something like Moving or Copying Database Files instead of File System Level Backup, and the content could be adjusted to fit that theme. In looking at this further, we really need to think about this in a different light. The first page needs to immediately mention we don't need to take the database offline for a backup. 24.1. SQL Dump I think we should remove any references to redirection and use only appropriate flags instead. Any admin worth their salt will realize you can use redirection and those who aren't worth their salt are just going to be confused. For example: Instead of pg_dump dbname file use pg_dump -d dbname -f dbname.sql I also think we really need to encourage the use of -Fd or -Fc 24.1.1. Restoring the Dump In conjunction with my suggestions for SQL Dump, I think we need to focus on using pg_restore. In short, -Fd and -Fc get precedence (although we will discuss the sql dump) and thus pg_restore will also get precedence. 24.1.2. Using pg_dumpall We need to give larger precedence to pg_dumpall for the sake of globals. I also recommend that we eliminate referencing pg_dumpall as the way to backup the cluster, (except for small databases) and instead focus on the required -g option. 24.1.3. Handling Large Databases The idea of a large database is completely different now from then. This reads almost identical to what it said in 8.0. 24.2. File System Level Backup We already discussed this 24.3. Continuous Archiving and Point-in-Time Recovery (PITR) List robocopy (available since server 2003) 24.3.1. Setting Up WAL Archiving 24.3.2. Making a Base Backup Better discussion of pg_basebackup needs to be had. 24.3.3. Making a Base Backup Using the Low Level API Already discussed this 24.3.4. Recovering Using a Continuous Archive Backup Remove any files present in pg_xlog/; these came from the file system backup and are therefore probably obsolete rather than current. If you didn't archive pg_xlog/ at all, then recreate it with proper permissions, being careful to ensure that you re-establish it as a symbolic link if you had it set up that way before. Is that actually needed? Won't PostgreSQL just ignore them or spit them out? 24.3.5. Timelines I need to read through this again 24.3.6. Tips and Examples I will probably add some to this. 24.3.7. Caveats -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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] Problems with question marks in operators (JDBC, ECPG, ...)
On Fri, May 15, 2015 at 1:45 PM, Dave Cramer p...@fastcrypt.com wrote: On 15 May 2015 at 16:44, Dave Cramer p...@fastcrypt.com wrote: On 15 May 2015 at 16:41, Robert Haas robertmh...@gmail.com wrote: On Fri, May 15, 2015 at 4:38 PM, Dave Cramer p...@fastcrypt.com wrote: I don't really want to take a violently strong position on this without understanding what's really going on here. Well our solution was to use ?? but that does mean we have to do some extra parsing which in a perfect world wouldn't be necessary. So what about strings quoted with '' or $$ or $something$ - how would you handle those? We parse for strings; the ?? just adds to the parsing load which we really try to avoid. The ?? is just harder to deal with because ? is part of the JDBC spec as a placeholder Whenever I ponder this I always come back to the idea of having a driver (or driver mode) that integrates with the Java API that JDBC specifies but whose parsing implementation adheres to libpq. This would, intentionally, be a driver that could not be used with portable source code but would allow people who are OK with binding tightly with PostgreSQL to talk in its native language. As for alternative operators maybe pgJDBC should put one or more extensions out on PGXN that would be considered an official compatibility mode that developers can write against and setup as dependency. Avoids each application developing its own mapping rules and the resultant problems that could result in doing so. At worse it at least makes the issue more visible if done fully. I'm not particularly in favor of deprecating the existing operators though I haven't given it that much thought either. Since using them results in syntax errors the harm in allowing them seems fairly minimal. The use of ? as an operator is normally done for solid reasons and clarity is not something to be discarded for everyone when only a subset are affected. David J.
Re: [HACKERS] BRIN range operator class
Emre Hasegeli wrote: I pushed patches 04 and 07, as well as adopting some of the changes to the regression test in 06. I'm afraid I caused a bit of merge pain for you -- sorry about that. No problem. I rebased the remaining ones. Thanks, pushed. There was a proposed change by Emre to renumber operator -|- to 17 for range types (from 6 I think). I didn't include that as I think it should be a separate commit. Also, we're now in debt of the test strategy for the union procedure. I will work with Emre in the coming days to get that sorted out. I'm now thinking that something in src/test/modules is the most appropriate. -- Á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] Problems with question marks in operators (JDBC, ECPG, ...)
Bruno Harbulot br...@distributedmatter.net wrote:
On Fri, May 15, 2015 at 9:41 PM, Robert Haas robertmh...@gmail.com wrote:
On Fri, May 15, 2015 at 4:38 PM, Dave Cramer p...@fastcrypt.com wrote:
Well our solution was to use ?? but that does mean we have to
do some extra parsing which in a perfect world wouldn't be
necessary.
It seems like maybe we missed a trick when we dealt with this; the
Java Specification (the language spec, not the API spec) seems to
say that curly braces should be used for this sort of thing. So
The Java Way would seem to be to have used {?} or {question_mark}
or some such as our product-specific way of dealing with this.
That probably would reduce the JDBC parsing overhead, since it
must look for curly braces for the standard escapes, anyway (like
a date literal being {d '2015-05-15'}).
That would be kinda ugly, since if you wanted to use the ?||
operator you would need to write that in your prepared statement as
{?}||. That seems only moderately more confusing than the current
need to write it as ??||, though.
But the opportunity to do that up-front was missed and, besides, we
have other connectors to worry about.
So what about strings quoted with '' or $$ or $something$ - how
would you handle those?
I hadn't realised that the JDBC driver allowed the ? operator to
be escaped as ??. It seems to work indeed (at least with version
9.4-1201 of the JDBC driver).
$$?$$ also works. I guess the JDBC drivers tries to parse
literals first and escapes them accordingly.
Yeah; regardless of what escape is used, the JDBC driver still
needs to deal with finding literals and treating them differently.
That said, I'd still suggest providing new operators and
deprecating the ones containing a question mark if possible.
(There are 8 distinct operator names like this: ?-, ?, ?,
?#, ?||, ?-|, ?| and ?.)
That would lower the burden on every connector to do something
about this.
I think it would be nicer to have a single mechanism that can be
used consistently across multiple languages (?? doesn't work for
ECPG, for example), considering that ? as a placeholder seems
quite common.
I don't know how practical it would be for all connectors to use
the same escape syntax. They all need to have some way to do it if
they want to allow the operators containing a question mark to be
used, but if we're going to allow it in SQL operators it may be
more sane to allow each connector to figure out what is the best
escape.
I lean toward deprecating those operators in favor of ones without
the problem character, and some years down the line dropping the
old (deprecated) operators.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Problems with question marks in operators (JDBC, ECPG, ...)
Bruno Harbulot br...@distributedmatter.net writes: That said, I'd still suggest providing new operators and deprecating the ones containing a question mark if possible. (There are 8 distinct operator names like this: ?-, ?, ?, ?#, ?||, ?-|, ?| and ?.) There are more in contrib ... 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
