Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Michael Paquier
On Mon, Jun 13, 2016 at 2:42 PM, Tatsuro Yamada wrote: > I got mistake to write an e-mail to -hackers on last week. :-< > I should have written this. > > The bug has not fixed by Tom Lane's patch: commit aeb9ae6. > Because I got the same error using tpc-h. This

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Amit Kapila
On Mon, Jun 13, 2016 at 11:05 AM, David Rowley wrote: > > On 13 June 2016 at 15:39, Thomas Munro wrote: > > What is going on here? > > ... > > > > > postgres=# set max_parallel_workers_per_gather = 2; > > SET > > postgres=# explain

Re: [HACKERS] Rename max_parallel_degree?

2016-06-13 Thread Julien Rouhaud
On 13/06/2016 03:16, Robert Haas wrote: > On Sat, Jun 11, 2016 at 6:24 PM, Julien Rouhaud > wrote: >> On 11/06/2016 23:37, Julien Rouhaud wrote: >>> On 09/06/2016 16:04, Robert Haas wrote: There remains only the task of adding max_parallel_degree as a

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Tatsuro Yamada
Hi, I applied your patch and run tpc-h. Then I got new errors on Q4,12,17 and 19. ERROR: Aggref found in non-Agg plan node. See bellow, -- postgres=# \i queries/4.explain.sql ERROR: Aggref found in non-Agg plan node STATEMENT: explain analyze select

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Tatsuro Yamada
On 2016/06/13 15:52, Michael Paquier wrote: On Mon, Jun 13, 2016 at 2:42 PM, Tatsuro Yamada wrote: I got mistake to write an e-mail to -hackers on last week. :-< I should have written this. The bug has not fixed by Tom Lane's patch: commit aeb9ae6. Because

[HACKERS] proposal: integration bloat tables (indexes) to core

2016-06-13 Thread Pavel Stehule
Hi There are lot of useful queries (views), that are on our wiki. Some queries are necessary for maintenance, and I am thinking these queries should be integrated part of Postgres. Mainly queries for detecting table bloat, index bloat, But some queries over pg_locks should be useful too. Notes,

Re: [HACKERS] Reviewing freeze map code

2016-06-13 Thread Robert Haas
On Sat, Jun 11, 2016 at 5:00 PM, Robert Haas wrote: >> How about changing the return tuple of heap_prepare_freeze_tuple to >> a bitmap? Two flags: "Freeze [not] done" and "[No] more freezing >> needed" > > Yes, I think something like that sounds about right. Here's a

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Tom Lane
I wrote: > Amit Kapila writes: >> It is slightly tricky to write a reproducible parallel-query test, but >> point taken and I think we should try to have a test unless such a test is >> really time consuming. > BTW, decent regression tests could be written without

Re: [HACKERS] Bug in to_timestamp().

2016-06-13 Thread Tom Lane
amul sul writes: > It's look like bug in to_timestamp() function when format string has more > whitespaces compare to input string, see below: No, I think this is a case of "input doesn't match the format string". As a rule of thumb, using to_timestamp() for input that

Re: [HACKERS] proposal: integration bloat tables (indexes) to core

2016-06-13 Thread Robert Haas
On Mon, Jun 13, 2016 at 3:23 AM, Pavel Stehule wrote: > There are lot of useful queries (views), that are on our wiki. Some queries > are necessary for maintenance, and I am thinking these queries should be > integrated part of Postgres. > > Mainly queries for detecting

[HACKERS] Bug in to_timestamp().

2016-06-13 Thread amul sul
Hi, It's look like bug in to_timestamp() function when format string has more whitespaces compare to input string, see below: Ex.1: Two white spaces before HH24 whereas one before input time string postgres=# SELECT TO_TIMESTAMP('2016-06-13 15:43:36', '/MM/DD HH24:MI:SS'); to_timestamp

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Robert Haas
On Mon, Jun 13, 2016 at 11:02 AM, Tom Lane wrote: > Amit Kapila writes: >> It is slightly tricky to write a reproducible parallel-query test, but >> point taken and I think we should try to have a test unless such a test is >> really time

Re: [HACKERS] Bug in to_timestamp().

2016-06-13 Thread Robert Haas
On Mon, Jun 13, 2016 at 12:12 PM, Tom Lane wrote: > amul sul writes: >> It's look like bug in to_timestamp() function when format string has more >> whitespaces compare to input string, see below: > > No, I think this is a case of "input doesn't match

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Tom Lane
Robert Haas writes: > On Mon, Jun 13, 2016 at 3:18 AM, Amit Kapila wrote: >> In create_grouping_paths(), we are building partial_grouping_path and same >> is used for gather path and other grouping paths (for partial paths). >> However, we don't

Re: [HACKERS] Prepared statements and generic plans

2016-06-13 Thread Albe Laurenz
Bruce Momjian wrote: > Also, is it possible to do an EXPLAIN prepare() with the libpq/wire > protocol? I can't do PREPARE EXPLAIN, but I can do EXPLAIN EXECUTE. > However, I don't see any way to inject EXPLAIN into the libpq/wire > prepare case. Can you specify prepare(EXPLAIN SELECT)? (PREPARE

Re: [HACKERS] WIP: Data at rest encryption

2016-06-13 Thread Peter Eisentraut
On 6/7/16 9:56 AM, Ants Aasma wrote: Similar things can be achieved with filesystem level encryption. However this is not always optimal for various reasons. One of the better reasons is the desire for HSM based encryption in a storage area network based setup. Could you explain this in more

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Amit Kapila
On Mon, Jun 13, 2016 at 7:17 PM, Robert Haas wrote: > > On Mon, Jun 13, 2016 at 3:18 AM, Amit Kapila wrote: > > In create_grouping_paths(), we are building partial_grouping_path and same > > is used for gather path and other grouping paths (for

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Amit Kapila
On Mon, Jun 13, 2016 at 7:51 PM, Tom Lane wrote: > > Robert Haas writes: > > On Mon, Jun 13, 2016 at 3:18 AM, Amit Kapila wrote: > >> In create_grouping_paths(), we are building partial_grouping_path and same > >> is used for

Re: [HACKERS] proposal: integration bloat tables (indexes) to core

2016-06-13 Thread Pavel Stehule
2016-06-13 18:52 GMT+02:00 Robert Haas : > On Mon, Jun 13, 2016 at 3:23 AM, Pavel Stehule > wrote: > > There are lot of useful queries (views), that are on our wiki. Some > queries > > are necessary for maintenance, and I am thinking these queries

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Tom Lane
Robert Haas writes: > On Mon, Jun 13, 2016 at 11:02 AM, Tom Lane wrote: >> BTW, decent regression tests could be written without the need to create >> enormous tables if the minimum rel size in create_plain_partial_paths() >> could be configured to

Re: [HACKERS] New design for FK-based join selectivity estimation

2016-06-13 Thread Tom Lane
Simon Riggs writes: > So a simple change is to make RelationGetFKeyList() only retrieve FKs with > nKeys>1. Rename to RelationGetMultiColumnFKeyList(). That greatly reduces > the scope for increased planning time. FWIW, I don't particularly agree with that. It makes the

Re: [HACKERS] New design for FK-based join selectivity estimation

2016-06-13 Thread Simon Riggs
On 4 June 2016 at 20:44, Tom Lane wrote: > This is a branch of the discussion in > > https://www.postgresql.org/message-id/flat/20160429102531.GA13701%40huehner.biz > but I'm starting a new thread as the original title is getting > increasingly off-topic. > > I complained in

Re: [HACKERS] Reviewing freeze map code

2016-06-13 Thread Andres Freund
On June 13, 2016 11:02:42 AM CDT, Robert Haas wrote: >(Official status update: I'm hoping that senior hackers will carefully >review these patches for defects. If they do not, I plan to commit >the patches anyway neither less than 48 nor more than 60 hours from >now

Re: [HACKERS] proposal: integration bloat tables (indexes) to core

2016-06-13 Thread Tom Lane
Robert Haas writes: > On Mon, Jun 13, 2016 at 3:23 AM, Pavel Stehule > wrote: >> There are lot of useful queries (views), that are on our wiki. Some queries >> are necessary for maintenance, and I am thinking these queries should be >> integrated

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Robert Haas
On Mon, Jun 13, 2016 at 1:06 PM, Tom Lane wrote: > Robert Haas writes: >> On Mon, Jun 13, 2016 at 11:02 AM, Tom Lane wrote: >>> BTW, decent regression tests could be written without the need to create >>> enormous tables if the

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Tom Lane
Amit Kapila writes: > On Mon, Jun 13, 2016 at 7:51 PM, Tom Lane wrote: >> I think the real question here is why the code removed by 04ae11f62 >> was wrong. It was unsafe to use apply_projection_to_path, certainly, >> but using

Re: [HACKERS] WIP: Data at rest encryption

2016-06-13 Thread Ants Aasma
On Mon, Jun 13, 2016 at 5:17 AM, Michael Paquier wrote: > On Sun, Jun 12, 2016 at 4:13 PM, Ants Aasma wrote: >>> I feel separate file is better to include the key data instead of pg_control >>> file. >> >> I guess that would be more flexible.

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Tom Lane
Amit Kapila writes: > It is slightly tricky to write a reproducible parallel-query test, but > point taken and I think we should try to have a test unless such a test is > really time consuming. BTW, decent regression tests could be written without the need to

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Robert Haas
On Mon, Jun 13, 2016 at 3:18 AM, Amit Kapila wrote: > In create_grouping_paths(), we are building partial_grouping_path and same > is used for gather path and other grouping paths (for partial paths). > However, we don't use it for partial path list and sort path due to

Re: [HACKERS] WIP: Data at rest encryption

2016-06-13 Thread Peter Eisentraut
On 6/12/16 3:13 AM, Ants Aasma wrote: 5. Instead of providing passphrase through environmental variable, > better to provide some options to pg_ctl etc. That looks like it would be worse from a security perspective. Integrating a passphrase prompt would be an option, but a way for scripts to

Re: [HACKERS] Why we don't have checksums on clog files

2016-06-13 Thread Robert Haas
On Sun, Jun 12, 2016 at 10:54 PM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Jun 7, 2016 at 10:41 AM, Amit Kapila wrote: >>> I think it will be better if users can have an option to checksum clog >>> pages. However, I

Re: [HACKERS] Prepared statements and generic plans

2016-06-13 Thread br...@momjian.us
On Mon, Jun 13, 2016 at 04:29:26PM -0400, David G. Johnston wrote: > On Mon, Jun 13, 2016 at 3:40 PM, br...@momjian.us wrote: > I am not sure how we can improve things, but I wanted to clarify exactly > what is happening. > > > ‚Äč""" > Comparisons on

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Robert Haas
On Mon, Jun 13, 2016 at 4:46 PM, Tom Lane wrote: > Robert Haas writes: >> In practice, we don't yet have the ability for >> parallel-safe paths from subqueries to affect planning at higher query >> levels, but that's because the pathification stuff

Re: [HACKERS] parallel workers and client encoding

2016-06-13 Thread Robert Haas
On Sun, Jun 12, 2016 at 3:05 AM, Noah Misch wrote: > On Thu, Jun 09, 2016 at 12:04:59PM -0400, Peter Eisentraut wrote: >> On 6/6/16 9:45 PM, Peter Eisentraut wrote: >> >There appears to be a problem with how client encoding is handled in the >> >communication from parallel

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Robert Haas
On Mon, Jun 13, 2016 at 5:27 PM, Robert Haas wrote: > On Mon, Jun 13, 2016 at 4:46 PM, Tom Lane wrote: >> Robert Haas writes: >>> In practice, we don't yet have the ability for >>> parallel-safe paths from subqueries to affect

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Tom Lane
Robert Haas writes: > One problem that I've realized that is related to this is that the way > that the consider_parallel flag is being set for upper rels is almost > totally bogus, which I believe accounts for your complaint at PGCon > that force_parallel_mode was not

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Robert Haas
On Mon, Jun 13, 2016 at 5:46 PM, Tom Lane wrote: > Robert Haas writes: >> One problem that I've realized that is related to this is that the way >> that the consider_parallel flag is being set for upper rels is almost >> totally bogus, which I believe

Re: [HACKERS] [sqlsmith] Failed assertion in postgres_fdw/deparse.c:1116

2016-06-13 Thread Robert Haas
On Fri, Jun 10, 2016 at 4:14 PM, Robert Haas wrote: > Although I have done a bit of review of this patch, it needs more > thought than I have so far had time to give it. I will update again > by Tuesday. I've reviewed this a bit further and have discovered an infelicity.

Re: [HACKERS] [sqlsmith] Failed assertion in postgres_fdw/deparse.c:1116

2016-06-13 Thread Robert Haas
On Mon, Jun 13, 2016 at 5:51 PM, Robert Haas wrote: > On Fri, Jun 10, 2016 at 4:14 PM, Robert Haas wrote: >> Although I have done a bit of review of this patch, it needs more >> thought than I have so far had time to give it. I will update again >>

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Tom Lane
Robert Haas writes: > Oh, one other thing about this: I'm not going to try to defend > whatever confusion between subplans and subqueries appears in that > comment, but prior to upper planner pathification I think we were dead > in the water here anyway, because the

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Robert Haas
On Mon, Jun 13, 2016 at 6:21 PM, Tom Lane wrote: > Robert Haas writes: >> Again, please have a look at the patch and see if it seems unsuitable >> to you for some reason. I'm not confident of my ability to get all of >> this path stuff right without a

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Tom Lane
Robert Haas writes: > Again, please have a look at the patch and see if it seems unsuitable > to you for some reason. I'm not confident of my ability to get all of > this path stuff right without a bit of help from the guy who designed > it. I'm kind of in a bind right

Re: [HACKERS] IsUnderPostmaster with shared_preload_libraries on Windows

2016-06-13 Thread Joe Conway
On 06/13/2016 01:57 PM, Tom Lane wrote: > Joe Conway writes: >> Is it expected that IsUnderPostmaster is true during postmaster startup >> in an extension's _PG_init() when preloading under Windows? On Linux it >> is false at this point AFAICT. > > AFAIK it will be false in

Re: [HACKERS] New design for FK-based join selectivity estimation

2016-06-13 Thread Simon Riggs
On 13 June 2016 at 19:16, Tom Lane wrote: > Simon Riggs writes: > > So a simple change is to make RelationGetFKeyList() only retrieve FKs > with > > nKeys>1. Rename to RelationGetMultiColumnFKeyList(). That greatly reduces > > the scope for increased

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Tom Lane
I wrote: > ... there was also an unexplainable plan change: > *** /home/postgres/pgsql/src/test/regress/expected/aggregates.out Thu Apr > 7 21:13:14 2016 > --- /home/postgres/pgsql/src/test/regress/results/aggregates.out Mon Jun > 13 11:54:01 2016 > *** > *** 577,590

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Robert Haas
On Mon, Jun 13, 2016 at 3:42 PM, Tom Lane wrote: >> I would not be surprised at a change to a parallel-query plan, but there's >> no parallelism here, so what happened? This looks like a bug to me. >> (Also, doing this query without COSTS OFF shows that the newly selected >>

Re: [HACKERS] Prepared statements and generic plans

2016-06-13 Thread 'br...@momjian.us'
On Mon, Jun 13, 2016 at 01:26:04PM +, Albe Laurenz wrote: > Bruce Momjian wrote: > > Also, is it possible to do an EXPLAIN prepare() with the libpq/wire > > protocol? I can't do PREPARE EXPLAIN, but I can do EXPLAIN EXECUTE. > > However, I don't see any way to inject EXPLAIN into the

Re: [HACKERS] New design for FK-based join selectivity estimation

2016-06-13 Thread Tom Lane
Simon Riggs writes: > On 13 June 2016 at 19:16, Tom Lane wrote: >> Another point here is that I'm now unconvinced that restricting the logic >> to consider only multi-column fkeys is really what we want. It looks to >> me like the code can also improve

Re: [HACKERS] 10.0

2016-06-13 Thread Merlin Moncure
On Tue, May 17, 2016 at 12:45 AM, Craig Ringer wrote: > On 14 May 2016 at 02:49, Tom Lane wrote: >> >> * This year's major release will be 9.6.0, with minor updates 9.6.1, >> 9.6.2, etc. It's too late to do otherwise for this release cycle. >> >> *

Re: [HACKERS] Prepared statements and generic plans

2016-06-13 Thread David G. Johnston
On Mon, Jun 13, 2016 at 3:40 PM, br...@momjian.us wrote: > > > > Looking at how the code behaves, it seems custom plans that are _more_ > > > expensive (plus planning cost) than the generic plan switch to the > > > generic plan after five executions, as now documented. Custom

[HACKERS] IsUnderPostmaster with shared_preload_libraries on Windows

2016-06-13 Thread Joe Conway
Is it expected that IsUnderPostmaster is true during postmaster startup in an extension's _PG_init() when preloading under Windows? On Linux it is false at this point AFAICT. Thanks, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, &

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Tom Lane
Robert Haas writes: > In practice, we don't yet have the ability for > parallel-safe paths from subqueries to affect planning at higher query > levels, but that's because the pathification stuff landed too late in > the cycle for me to fully react to it. I wonder if that's

Re: [HACKERS] IsUnderPostmaster with shared_preload_libraries on Windows

2016-06-13 Thread Tom Lane
Joe Conway writes: > Is it expected that IsUnderPostmaster is true during postmaster startup > in an extension's _PG_init() when preloading under Windows? On Linux it > is false at this point AFAICT. AFAIK it will be false in the *postmaster's* execution of _PG_init(). But

Re: [HACKERS] New design for FK-based join selectivity estimation

2016-06-13 Thread Tomas Vondra
Hi, Attached is a reworked patch, mostly following the new design proposal from this thread. I'm not entirely happy with the code, but it's the best I've been able to come up with by now and I won't be able to significantly improve that due to travel over. Inevitably there will be issues in

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-13 Thread Amit Kapila
On Mon, Jun 13, 2016 at 9:37 PM, Tom Lane wrote: > > I wrote: > > Amit Kapila writes: > >> It is slightly tricky to write a reproducible parallel-query test, but > >> point taken and I think we should try to have a test unless such a test is > >>

Re: [HACKERS] WIP: Data at rest encryption

2016-06-13 Thread Haribabu Kommi
On Sun, Jun 12, 2016 at 5:13 PM, Ants Aasma wrote: > On Fri, Jun 10, 2016 at 5:23 AM, Haribabu Kommi > wrote: > >> 2. Instead of depending on a contrib module for the encryption, how >> about integrating pgcrypto contrib in to the core and add that

[HACKERS] Re: Should phraseto_tsquery('simple', 'blue blue') @@ to_tsvector('simple', 'blue') be true ?

2016-06-13 Thread Noah Misch
On Fri, Jun 10, 2016 at 03:10:40AM -0400, Noah Misch wrote: > On Tue, Jun 07, 2016 at 06:05:10PM -0400, Tom Lane wrote: > > Jean-Pierre Pelletier writes: > > > I wanted to test if phraseto_tsquery(), new with 9.6 could be used for > > > matching consecutive words but it

Re: [HACKERS] Rename max_parallel_degree?

2016-06-13 Thread Robert Haas
On Mon, Jun 13, 2016 at 5:42 AM, Julien Rouhaud wrote: > Agreed, and fixed in attached v3. I don't entirely like the new logic in RegisterDynamicBackgroundWorker. I wonder if we can't drive this off of a couple of counters, instead of having the process registering

Re: [HACKERS] parallel workers and client encoding

2016-06-13 Thread Peter Eisentraut
On 6/10/16 2:08 PM, Peter Eisentraut wrote: On 6/6/16 9:45 PM, Peter Eisentraut wrote: Attached is a patch to illustrates how this could be fixed. There might be similar issues elsewhere. The notification propagation in particular could be affected. Tracing the code, NotificationResponse

Re: [HACKERS] parallel workers and client encoding

2016-06-13 Thread Peter Eisentraut
On 6/9/16 7:16 PM, Tatsuo Ishii wrote: Something like SetClientEncoding(GetDatabaseEncoding()) is a Little bit ugly. It would be nice if we could have a switch to turn off the automatic encoding conversion in the future, but for 9.6, I feel I'm fine with your proposed patch. One way to make