Re: [HACKERS] PATCH: index-only scans with partial indexes

[Kyotaro HORIGUCHI]

Hello,

At Fri, 09 Oct 2015 16:32:31 +0200, Tomas Vondra  
wrote in <5617cfff.10...@2ndquadrant.com>
> Hello,
> 
> On 10/09/2015 02:59 AM, Kyotaro HORIGUCHI wrote:
> >>> The cause of this seeming mismatch would be the place to hold
> >>> indexrinfos. It is determined only by baserestrictinfo and
> >>> indpred. Any other components are not involved. So IndexClauseSet
> >>> is found not to be the best place after all, I suppose.
> >>>
> >>> Instead, I came to think that the better place is
> >>> IndexOptInfo. Partial indexes are examined in check_partial_index
> >>> and it seems to be the most proper place to check this so far.
> >>
> >> AFAIK there's only one IndexOptInfo instance per index, so I'm not
> >> sure how would that work with queries that use the index in multiple
> >> places?
> >
> > No matter if the index is used multiple places, indexrinfos is
> > determined only with baserestrictinfos of the owner relation and
> > itself's indpred, which are invariant through the following steps.
> 
> I'm probably missing something, but let's say we have a table like
> this:

You might be missing the fact that a table could represented as
multiple relation(RelOptInfo)s in PlannerInfo or PlannerGlobal.

> CREATE TABLE t (a INT, b INT, c INT);
> CREATE INDEX aidx ON t(c) WHERE a = 1;
> CREATE INDEX bidx ON t(c) WHERE b = 2;
> 
> and then a trivial query (where each part perfectly matches one of the
> indexes to allow IOS)
> 
> SELECT c FROM t WHERE a=1
> UNION ALL
> SELECT c FROM t WHERE b=2;
> 
> Now, let's say we move indexrinfos to IndexOptInfo - how will that
> look like for each index? There's only a single IndexOptInfo for each
> index, so it will have to work with union of all baserestrictinfos.

Needless to say about IndexOptInfo, the two t's in the two
component SELECTS are represented as two different subquery rels
having different baserestrictinfo. So it will correctly be
planned as the following with my previous patch.

Append  (cost=0.12..64.66 rows=20 width=4)
   ->  Index Only Scan using aidx on t  (cost=0.12..32.23 rows=10 width=4)
   ->  Index Only Scan using bidx on t t_1  (cost=0.12..32.23 rows=10 width=4)
(3 rows)

The table t is referred to twice by different (alias) names
(though the diferrence is made by EXPLAIN, it shows that they are
different rels in plantree).

> So we'll have these indexrinfos:
> 
> aidx: {b=2}
> bidx: {a=1}

Yes, but each of them belongs to different rels. So,

> which makes index only scans unusable.

The are usable.

> I think we effectively need a separate list of "not implied" clauses
> per index-baserel combination.
> Maybe IndexClauseSet is not the right
> place, but I don't see how IndexOptInfo could work.

Does this make sense?

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center



-- 
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] Release of CVEs

[Gavin Flower]

On 14/10/15 18:19, Tom Lane wrote:

I wrote:

Michael Paquier  writes:

On Mon, Oct 12, 2015 at 2:54 AM, Josh Berkus wrote:

I don't know that there's anything the PostgreSQL project can do about
it.  If anyone on this list is connected with MITRE, please ask them
what they need to be more prompt.

http://cve.mitre.org/ has a "Contact Us" tab linking to the address I
mentioned. That may be a start as at this state this is far more than
6 weeks.

I'm inclined to start by asking the Red Hat security guys, from whom
we obtained all these CVE numbers to begin with.  Will check into it
tomorrow.

According to the Red Hat guys, the fundamental problem is that Mitre like
to research and write up the official CVE descriptions themselves ...
which would be fine if they had adequate resources to do it in a timely
fashion, but they don't really.  Apparently, most of our bugs are of low
enough severity to be way down their priority list.  (Maybe we should
consider that a good thing.)

However, Red Hat did also point out a possible alternative: instead of
linking to the Mitre website, we could link to Red Hat's own repository
of CVE descriptions at
   https://access.redhat.com/security/cve/
for example
   https://access.redhat.com/security/cve/CVE-2015-5289

This is not as unofficial as it might seem, because for several years now
Mitre has officially delegated responsibility for initial assignment of
CVE numbers for all open-source issues to Red Hat.  (It's just final
wording of the descriptions that they're insisting on doing themselves.)

A quick browse through some of the relevant items says that this is at
least as good as cve.mitre.org in terms of the descriptions of the
security issues, but it is a bit Red-Hat-centric in that there's info
about which Red Hat package releases include a fix, but not about package
releases from other vendors such as Ubuntu.

As a former wearer of the red fedora, I'm not going to pretend to have
an unbiased opinion on whether we should switch our security-page links
to point to Red Hat's entries instead of Mitre's.  But it's something
worth considering, given that we're seeing as much as a year's lag in
Mitre's pages.

regards, tom lane


Would be be possibly to link to the Red Hat pages, and (at least semi) 
automate their replacement by the official pages when they become available?



Cheers,
Gavin


--
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] Dangling Client Backend Process

[Amit Kapila]

On Tue, Oct 13, 2015 at 3:54 PM, Rajeev rastogi 
wrote:

> On 12th October 2015 20:45, Rajeev Rastogi Wrote:
>
>
>
> >>> I observed one strange behavior today that if postmaster process gets
> crashed/killed, then it kill all background processes but not the client
> backend process.
>
>
>
> >> This is a known behaviour and there was some discussion on this
>
> >> topic [1] previously as well.
>
>
>
> > Now as it is confirmed to be valid issue, I will spend some time on this
> to find if there is something more appropriate solution.
>
>
>
> I checked the latest code and found Heikki has already added code for
> secure_read using the latch mechanism (using WaitLatchOrSocket). It
> currently waits for two events i.e. WL_LATCH_SET and WL_SOCKET_READABLE.
>
> Commit id: 80788a431e9bff06314a054109fdea66ac538199
>
>
>
> If we add the event WL_POSTMASTER_DEATH also, client backend process
> handling will become same as other backend process. So postmaster death can
> be detected in the same way.
>
>
>
> But I am not sure if WL_POSTMASTER_DEATH event was not added intentionally
> for some reason. Please confirm.
>
> Also is it OK to add this even handling in generic path of Libpq?
>
>
>
> Please let me know if I am missing something?
>
>
I feel this is worth investigation, example for what kind of cases libpq is
used for non-blocking sockets, because for such cases above idea
will not work.

Here, I think the bigger point is that, Tom was not in favour of
this proposal (making backends exit on postmaster death ) at that time,
not sure whether he has changed his mind.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] Release of CVEs

[Tom Lane]

I wrote:
> Michael Paquier  writes:
>> On Mon, Oct 12, 2015 at 2:54 AM, Josh Berkus wrote:
>>> I don't know that there's anything the PostgreSQL project can do about
>>> it.  If anyone on this list is connected with MITRE, please ask them
>>> what they need to be more prompt.

>> http://cve.mitre.org/ has a "Contact Us" tab linking to the address I
>> mentioned. That may be a start as at this state this is far more than
>> 6 weeks.

> I'm inclined to start by asking the Red Hat security guys, from whom
> we obtained all these CVE numbers to begin with.  Will check into it
> tomorrow.

According to the Red Hat guys, the fundamental problem is that Mitre like
to research and write up the official CVE descriptions themselves ...
which would be fine if they had adequate resources to do it in a timely
fashion, but they don't really.  Apparently, most of our bugs are of low
enough severity to be way down their priority list.  (Maybe we should
consider that a good thing.)

However, Red Hat did also point out a possible alternative: instead of
linking to the Mitre website, we could link to Red Hat's own repository
of CVE descriptions at
  https://access.redhat.com/security/cve/
for example
  https://access.redhat.com/security/cve/CVE-2015-5289

This is not as unofficial as it might seem, because for several years now
Mitre has officially delegated responsibility for initial assignment of
CVE numbers for all open-source issues to Red Hat.  (It's just final
wording of the descriptions that they're insisting on doing themselves.)

A quick browse through some of the relevant items says that this is at
least as good as cve.mitre.org in terms of the descriptions of the
security issues, but it is a bit Red-Hat-centric in that there's info
about which Red Hat package releases include a fix, but not about package
releases from other vendors such as Ubuntu.

As a former wearer of the red fedora, I'm not going to pretend to have
an unbiased opinion on whether we should switch our security-page links
to point to Red Hat's entries instead of Mitre's.  But it's something
worth considering, given that we're seeing as much as a year's lag in
Mitre's pages.

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] Support for N synchronous standby servers - take 2

[Michael Paquier]

On Wed, Oct 14, 2015 at 3:02 AM, Masahiko Sawada wrote:
> On Sat, Oct 10, 2015 at 4:35 AM, Robert Haas wrote:
>> On Fri, Oct 9, 2015 at 12:00 AM, Amit Kapila wrote:
>>> Sounds like both the approaches have some pros and cons, also there are
>>> some people who prefer mini-language and others who prefer JSON.  I think
>>> one thing that might help, is to check how other databases support this
>>> feature or somewhat similar to this feature (mainly with respect to User
>>> Interface), as that can help us in knowing what users are already familiar
>>> with.
>>
>> +1!

Thanks for having a look at that!

> For example, MySQL 5.7 has similar feature, but it doesn't support
> quorum commit, and is simpler than postgresql attempting feature.
> There is one configuration parameter in MySQL 5.7 which indicates the
> number of sync replication node.
> The primary server commit when the primary server receives the
> specified number of ACK from standby server regardless name of standby
> server.

Hm. This is not much helpful in the case we especially mentioned
upthread at some point with 2 data centers, first one has the master
and a sync standby, and second one has a set of standbys. We need to
be sure that the standby in DC1 acknowledges all the time, and we
would only need to wait for one or more of them in DC2. I still
believe that this is the main use case for this feature to ensure a
proper failover without data loss if one data center blows away with a
meteorite.

> And IIRC, Oracle database also doesn't support the quorum commit as well.
> The settings standby server sync or async is specified per standby
> server in configuration parameter in primary node.

And I guess that they manage standby nodes using a system catalog
then, being able to change the state of a node from async to sync with
something at SQL level? Is that right?

> I thought that this feature for postgresql should be simple at first
> implementation.

And extensible.

> It would be good even if there are some restriction such as the
> nesting level, the group setting.
> The another new approach that I came up with is,
> * Add new parameter synchronous_replication_method (say s_r_method)
> which can have two names: 'priority', 'quorum'
> * If s_r_method = 'priority', the value of s_s_names (e.g. 'n1,n2,n3')
> is handled using priority. It's same as '[n1,n2,n3]' in dedicated
> language.
> * If s_r_method = 'quorum', the value of s_s_names is handled using
> quorum commit, It's same as '(n1,n2,n3)' in dedicated language.
> * Setting of synchronous_standby_names is same as today. That is, the
> storing the nesting value is not supported.
> * If we want to support more complex syntax like what we are
> discussing, we can add the new value to s_r_method, for example
> 'complex', 'json'.

If we go that path, I think that we still would need an extra
parameter to control the number of nodes that need to be taken from
the set defined in s_s_names whichever of quorum or priority is used.
Let's not forget that in the current configuration the first node
listed in s_s_names and *connected* to the master will be used to
acknowledge the commit.
-- 
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] Performance improvement for joins where outer side is unique

[Pavel Stehule]

2015-10-13 23:28 GMT+02:00 David Rowley :

> On 4 September 2015 at 04:50, Robert Haas  wrote:
>
>>
>> Also: very nice performance results.
>>
>>
> Thanks.
>
> On following a thread in [General] [1] it occurred to me that this patch
> can give a massive improvement on Merge joins where the mark and restore
> causes an index scan to have to skip over many filtered rows again and
> again.
>
> I mocked up some tables and some data from the scenario on the [General]
> thread:
>
> create table customers (id bigint, group_id bigint not null);
> insert into customers select x.x,x.x%27724+1 from
> generate_series(1,473733) x(x);
> alter table customers add constraint customer_pkey primary key (id);
> create table balances (id bigint, balance int not null, tracking_number
> int not null, customer_id bigint not null);
> insert into balances select x.x, 100, 12345, x.x % 45 + 1 from
> generate_Series(1,16876) x(x);
> create index balance_customer_id_index on balances (customer_id);
> create index balances_customer_tracking_number_index on balances
> (customer_id,tracking_number);
> analyze;
>
> Unpatched I get:
>
> test=# explain analyze SELECT ac.* FROM balances ac join customers o ON
> o.id = ac.customer_id WHERE o.group_id = 45;
>
>  QUERY PLAN
>
> 
>  Merge Join  (cost=164.87..1868.70 rows=1 width=24) (actual
> time=6.110..1491.408 rows=375 loops=1)
>Merge Cond: (ac.customer_id = o.id)
>->  Index Scan using balance_customer_id_index on balances ac
>  (cost=0.29..881.24 rows=16876 width=24) (actual time=0.009..5.206
> rows=16876 loops=1)
>->  Index Scan using customer_pkey on customers o  (cost=0.42..16062.75
> rows=17 width=8) (actual time=0.014..1484.382 rows=376 loops=1)
>  Filter: (group_id = 45)
>  Rows Removed by Filter: 10396168
>  Planning time: 0.207 ms
>  Execution time: 1491.469 ms
> (8 rows)
>
> Patched:
>
> test=# explain analyze SELECT ac.* FROM balances ac join customers o ON
> o.id = ac.customer_id WHERE o.group_id = 45;
>
>  QUERY PLAN
>
> 
>  Merge Join  (cost=164.87..1868.70 rows=1 width=24) (actual
> time=6.037..11.528 rows=375 loops=1)
>Merge Cond: (ac.customer_id = o.id)
>->  Index Scan using balance_customer_id_index on balances ac
>  (cost=0.29..881.24 rows=16876 width=24) (actual time=0.009..4.978
> rows=16876 loops=1)
>->  Index Scan using customer_pkey on customers o  (cost=0.42..16062.75
> rows=17 width=8) (actual time=0.015..5.141 rows=2 loops=1)
>  Filter: (group_id = 45)
>  Rows Removed by Filter: 27766
>  Planning time: 0.204 ms
>  Execution time: 11.575 ms
> (8 rows)
>
> Now it could well be that the merge join costs need a bit more work to
> avoid a merge join in this case, but as it stands as of today, this is your
> performance gain.
>
> Regards
>

it is great

Pavel


>
> David Rowley
>
> [1]
> http://www.postgresql.org/message-id/caczyddiaxeam2rkmhbmuhwvcm4txh+5e3hqgggyzfzbn-pn...@mail.gmail.com
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
> 
>  PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [HACKERS] Support for N synchronous standby servers - take 2

[Michael Paquier]

On Wed, Oct 14, 2015 at 3:28 AM, Masahiko Sawada wrote:
> The draft patch of replication using priority is already implemented
> by Michael, so I need to implement simple quorum commit logic and
> merge them.

The last patch in date I know of is this one:
http://www.postgresql.org/message-id/CAB7nPqRFSLmHbYonra0=p-x8mj-xtl7oxjp_qxdjgsjpvwr...@mail.gmail.com
It would surely need a rebase.
-- 
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] Postgres service stops when I kill client backend on Windows

[Amit Kapila]

On Tue, Oct 13, 2015 at 8:57 PM, Tom Lane  wrote:
>
> Michael Paquier  writes:
> > On Tue, Oct 13, 2015 at 5:35 AM, Tom Lane  wrote:
> >> After poking around a bit more, I propose the attached patch.  I've
> >> checked that this is happy with an EXEC_BACKEND Unix build, but I'm not
> >> able to test it on Windows ... would somebody do that?
>
> > Looking at the patch, clearly +1 for the additional routine in both
> > win32_shmem.c and sysv_shmem.c to clean up the shmem state at backend
> > level. I have played as well with the patch on Windows and it behaves
> > as expected: without the patch a process killed with taskkill /f stops
> > straight the server even if restart_on_crash is on. With the patch the
> > server restarts correctly.
>
> OK, pushed with some additional comment-smithing.
>
> I noticed while looking at this that for subprocesses that aren't supposed
> to be attached to shared memory, we do pgwin32_ReserveSharedMemoryRegion()
> anyway in internal_forkexec(), and then that's never undone anywhere,
> so that that segment of the subprocess's memory space remains reserved.
> I'm not sure if this is worth changing, but if it is, we could do so now
> by calling VirtualFree() in PGSharedMemoryNoReAttach().
>

I think it is worth doing, as it can save the memory for processes which
don't attach to shared memory.  Another thing is that we do allocate
handles (by using duplicate handle) in save_backend_variables() which
I am not sure are required for all the processes, anyway this doesn't
seem worth the trouble.


> BTW, I am suspicious that the DSM stuff may have related issues --- do
> we use inheritable mapping handles for DSM segments on Windows?
>

Not by default, there is an API dsm_pin_segment() which Duplicates the
handle for Postmaster process to retain the shared memory segment
till Postmaster shutdown.  In general, I don't see such issues for DSM,
but please point me if you see anything problematic.



With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] Foreign join pushdown vs EvalPlanQual

[Kouhei Kaigai]

> -Original Message-
> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Etsuro Fujita
> Sent: Thursday, October 08, 2015 7:56 PM
> To: Kyotaro HORIGUCHI; robertmh...@gmail.com
> Cc: Kaigai Kouhei(海外 浩平); pgsql-hackers@postgresql.org;
> shigeru.han...@gmail.com
> Subject: Re: [HACKERS] Foreign join pushdown vs EvalPlanQual
> 
> On 2015/10/07 15:39, Etsuro Fujita wrote:
> > On 2015/10/07 15:06, Kyotaro HORIGUCHI wrote:
> >> At Wed, 7 Oct 2015 00:24:57 -0400, Robert Haas 
> >> wrote
> >>> I think it rather requires *replacing* two resjunk columns by one new
> >>> one.  The whole-row references for the individual foreign tables are
> >>> only there to support EvalPlanQual; if we instead have a column to
> >>> populate the foreign scan's slot directly, then we can use that column
> >>> for that purpose directly and there's no remaining use for the
> >>> whole-row vars on the baserels.
> 
> >> It is what I had in mind.
> 
> > OK  I'll investigate this further.
> 
> I noticed that the approach using a column to populate the foreign
> scan's slot directly wouldn't work well in some cases.  For example,
> consider:
> 
> SELECT * FROM verysmall v LEFT JOIN (bigft1 JOIN bigft2 ON bigft1.x =
> bigft2.x) ON v.q = bigft1.q AND v.r = bigft2.r FOR UPDATE OF v;
> 
> The best plan is presumably something like this as you said before:
> 
> LockRows
> -> Nested Loop
> -> Seq Scan on verysmall v
> -> Foreign Scan on bigft1 and bigft2
>  Remote SQL: SELECT * FROM bigft1 JOIN bigft2 ON bigft1.x =
> bigft2.x AND bigft1.q = $1 AND bigft2.r = $2
> 
> Consider the EvalPlanQual testing to see if the updated version of a
> tuple in v satisfies the query.  If we use the column in the testing, we
> would get the wrong results in some cases.
>
In this case, does ForeignScan have to be reset prior to ExecProcNode()?
Once ExecReScanForeignScan() gets called by ExecNestLoop(), it marks EPQ
slot is invalid. So, more or less, ForeignScan needs to kick the remote
join again based on the new parameter come from the latest verysmall tuple.
Please correct me, if I don't understand correctly.
In case of unparametalized ForeignScan case, the cached join-tuple work
well because it is independent from verysmall.

Once again, if FDW driver is responsible to construct join-tuple from
the base relation's tuple cached in EPQ slot, this case don't need to
kick remote query again, because all the materials to construct join-
tuple are already held locally. Right?

Thanks,
--
NEC Business Creation Division / PG-Strom Project
KaiGai Kohei 



-- 
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] DIAGNOSTICS = SKIPPED_ROW_COUNT

[dinesh kumar]

On Tue, Oct 13, 2015 at 5:53 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

>
>>> > Using this attribute, we can have more control on parallel operations
>>> like,
>>>
>>> > IF SKIPPED_ROW_COUNT =0 THEN
>>> > <>
>>> > ELSE
>>> > <>
>>> > END IF;
>>>
>>> Um ... so what?  This is not a use-case.
>>>
>>>
>> In my view, "How one can be sure that, he obtained all the tuples with
>> SKIP LOCKED". If the end user has this counter value, he may proceed with a
>> different approach with partially locked tuples.
>>
>>
> ​Can you be more specific?  In most cases I can come up with (queues,
> basically) where skipped locked is running the processing performing the
> query is going to re-query the database on the next tick regardless of
> whether they thought they say only some or all of the potential rows on the
> prior pass.
>
>
Sure,

In an existing wait policies like WAIT(default) and NO WAIT,
one can be sure to determine(Using ROW_COUNT daignostics counter),
how many required tuples he processed in a transaction.
But this is not case when it comes to SKIP LOCKED. My concern is,
how we come to know that, our SKIP LOCKED missed few tuples due to lock, OR
it's processed all the tuples.
I understood that, the name itself defining SKIP the LOCKED rows, but we
are not measuring it.

I wrote the patch and it's working as below. But I haven't extended this to
other planners yet.

postgres=# DO
$$
DECLARE
rc INT;
BEGIN
PERFORM * FROM test WHERE mod(t,2)=0 FOR UPDATE SKIP LOCKED;
GET DIAGNOSTICS rc=SKIPPED_ROW_COUNT;
RAISE NOTICE 'Skipped : %', rc;
GET DIAGNOSTICS rc=ROW_COUNT;
RAISE NOTICE 'Processed : %', rc;
END;
$$
;
NOTICE:  Skipped : 2
NOTICE:  Processed : 3
DO

Once we measured the SKIP LOCKED, then we can only consider
to re-process the Skipped !=0 transactions rather doing every transaction
again.

In my view, SKIP LOCKED is a nice feature, which gives only the available
OR unlocked tuples.
But those are not the complete required tuples for the given SQL statement.
Isn't it ?!

Let me know, if I am still not clear about this.

David J.
>
>


-- 

Regards,
Dinesh
manojadinesh.blogspot.com

[HACKERS] remaining open items

[Robert Haas]

We've got a few open items remaining at
https://wiki.postgresql.org/wiki/PostgreSQL_9.5_Open_Items - we should
try to get rid of them.  Of the 8 items there, 3 are documentation
issues.  It looks to me like one of those is for Stephen to deal with,
one for Andres, and one for Alvaro.  Is there any reason we can't get
those taken care of and move on?

On the remaining five issues:

- Refactoring speculative insertion with unique indexes a little.
Andres seems to think this shouldn't be an open issue, while Peter
thinks maybe it should be, or at least that's my imperfect executive
summary.  Heikki isn't sure he agrees with all of the changes.  I'm
not very clear on whether this is a new feature, a bug fix,
beautification, or something else.  What would happen if we didn't do
anything at all?

- Oversize item computation needs more testing (c.f. ereport(ERROR)
calls in brin_getinsertbuffer).  This is pretty vague, and there's no
thread linked.  If there's a stability issue here, presumably it falls
to Alvaro to fix it.  But I don't know who added this item or what
really needs to be done.

- DDL deparsing testing module should have detected that transforms
were not supported, but it failed to notice that.  There's no thread
linked to this one either, but the description of the issue seems
clear enough.  Alvaro, any chance that you can, as the comment says,
whack it until it does?

- Foreign join pushdown vs EvalPlanQual.  Attempting to use the new
foreign join pushdown support can crash the server if the query has
locking clauses (or possibly if it's an UPDATE localtab FROM remotetab
type of query, though I don't have a test case for that).  There's
been a lot of discussion about how to fix this, but unless Tom gets
involved, I don't see this getting fixed in time for 9.5, because I
don't know exactly how to fix it and I don't think anyone else does
either.  I have some ideas and I think those ideas are slowly getting
better, but the reality is that if I'd realized this issue existed, I
would not have committed the join pushdown support to 9.5.  I didn't,
and that was a screw-up on my part.   I don't know what to recommend
here: the choices seem to be (a) rip out all the foreign join pushdown
support from 9.5, or (b) leave it in and accept that trying to use it
may fail in some cases.  Since postgres_fdw doesn't have any support
for this anyway, it's not actively hurting anyone.  But it's still bad
that it's broken like this.

- Strange behavior on track_commit_timestamp.  As I've said on the
thread, I think that the idea that the redo routines care about the
value of the GUC at the time redo is performed (rather than at the
time redo is generated), is broken.  Fujii's latest post provides some
examples of how this creates weird results.  I really think this
should be changed.

-- 
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] Eclipse Help

[dinesh kumar]

On Tue, Oct 13, 2015 at 3:49 AM, Praveen M  wrote:

> Hi All,
>
> I was able to follow the debugging of the child process using this link,
> https://wiki.postgresql.org/wiki/Working_with_Eclipse
>
> As per the notes , I was able to set breakpoints and everything seem to be
> working (hopefully). However I am not able to see the debug messages in the
> eclipse console (for the attached process) . Please help
>
> When I check on the console in eclipse , this is the last message I see.
>
> 0x773fad48 in poll () from /lib/x86_64-linux-gnu/libc.so.6
>
> I added a 2 lines in pl_exec.c and kept breakpoints for these lines. The
> breakpoints work fine but I am not able to see the console log.
> I was able to use the log message "ereport(LOG, (errmsg("test here
> started")));" in autovaccum.c line 414 and see the message in the console.
> But this message is from the main process. I am having trouble seeing the
> console log only for the attached process.
>
> pl_exec.c :
>
> Line 310 :
>  ereport(LOG,
> (errmsg("test here started")));
>
> Line 311 :
> elog(ERROR,"test here");
>
>
Not sure what could be the problem. It may be due to your log settings in
postgresql.conf file, OR may the content not flushed yet.

BTW, did you check the log file for these entries.

PS

*I prefer netbeans rather eclipse for local development. If you have enough
resources in your machine, it's worth to give a try latest netbeans.*



> Thanks
> Praveen
>
>
>
>


-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] Foreign join pushdown vs EvalPlanQual

[Kyotaro HORIGUCHI]

Hi,

At Fri, 9 Oct 2015 18:18:52 +0530, Jeevan Chalke 
 wrote in 

> I have noticed that, this thread started saying we are getting a crash
> with the given steps with foreign_join_v16.patch, I am correct?

Your're correct. The immediate cause of the crash is an assertion
failure that EvalPlanQualNext doesn't find a tuple to examine for
a "foreign join" changed into a ForeignScan as the result of
foreign join pushdown.

> Then there are various patches which trying to fix this,
> fdw-eval-plan-qual-*.patch
> 
> I have tried applying foreign_join_v16.patch, which was good. And tried
> reproducing the crash. But instead of crash I am getting following error.
> 
> ERROR:  could not serialize access due to concurrent update
> CONTEXT:  Remote SQL command: SELECT a FROM public.foo FOR UPDATE
> Remote SQL command: SELECT a FROM public.tab FOR UPDATE

It is because you took wrong steps.

FDW runs a transaction in the isolation level above REPEATABLE
READ. You updated a value locally while the fdw is locking the
same tuple in REPEATABLE READ transaction. You should map
different table as the foreign tables from the locally-modified
table.

- create table tab (a int, b int);
- create foreign table foo (a int) server myserver options(table_name 'tab');
- create foreign table bar (a int) server myserver options(table_name 'tab');
+ create table tab (a int, b int);
+ create table lfb (a int, b int);
+ create foreign table foo (a int) server myserver options(table_name 'lfb);
+ create foreign table bar (a int) server myserver options(table_name 'lfb');

And you'll get the following assertion failure.

| TRAP: FailedAssertion("!(scanrelid > 0)", File: "execScan.c", Line: 52)
| LOG:  unexpected EOF on client connection with an open transaction
| LOG:  server process (PID 16738) was terminated by signal 6: Aborted
| DETAIL:  Failed process was running: explain (verbose, analyze) select t1.* 
from t1, ft2, ft2_2 where t1.a = ft2.a and ft2.a = ft2_2.a for update;
| LOG:  terminating any other active server proces

> Then I have applied fdw-eval-plan-qual-3.0.patch on top of it. It was not
> getting applied cleanly (may be due to some other changes meanwhile).
> I fixed the conflicts and the warnings to make it compile.

The combination won't work because the patch requires
postgres_fdw to put alternative path as subpath to
create_foreignscan_path. AFAICS no corresponding forign-join
patch has shown in this thread. This thread continues to discuss
the desirable join pushdown API for FDW.

> When I run same sql sequence, I am getting crash in terminal 2 at EXPLAIN
> it self.
> 
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !>
> 
> Following sql statement I am using:
> 
> create table tab (a int, b int);
> create foreign table foo (a int) server myserver options(table_name 'tab');
> create foreign table bar (a int) server myserver options(table_name 'tab');
> 
> insert into tab values (1, 1);
> insert into foo values (1);
> insert into bar values (1);
> 
> analyze tab;
> analyze foo;
> analyze bar;
> 
> 
> Run the example:
> 
> [Terminal 1]
> begin;
> update tab set b = b + 1 where a = 1;
> 
> [Terminal 2]
> explain verbose select tab.* from tab, foo, bar where tab.a =
> foo.a and foo.a = bar.a for update;
> 
> 
> Am I missing something here?
> Do I need to apply any other patch from other mail-threads?
> 
> Do you have sample test-case explaining the issue and fix?
> 
> With these simple questions, I might have taking the thread slightly off
> from the design considerations, please excuse me for that.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center



-- 
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] Parallel Seq Scan

[Noah Misch]

On Mon, Oct 12, 2015 at 11:46:08AM -0400, Robert Haas wrote:
> plpgsql_param_fetch() assumes that it can detect whether it's being
> called from copyParamList() by checking whether params !=
> estate->paramLI.  I don't know why this works, but I do know that this

It works because PL/pgSQL creates an unshared list whenever copyParamList() is
forthcoming.  (This in turn relies on intimate knowledge of how the rest of
the system processes param lists.)  The comments at setup_param_list() and
setup_unshared_param_list() are most pertinent.

> test fails to detect the case where it's being called from
> SerializeParamList(), which causes failures in exec_eval_datum() as
> predicted.  Calls from SerializeParamList() need the same treatment as
> calls from copyParamList() because it, too, will try to evaluate every
> parameter in the list.  Here, I've taken the approach of making that
> check unconditional, which seems to work, but I'm not sure if some
> other approach would be better, such as adding an additional Boolean
> (or enum context?) argument to ParamFetchHook.  I *think* that
> skipping this check is merely a performance optimization rather than
> anything that affects correctness, and bms_is_member() is pretty
> cheap, so perhaps the way I've done it is OK.

Like you, I don't expect bms_is_member() to be expensive relative to the task
at hand.  However, copyParamList() and SerializeParamList() copy non-dynamic
params without calling plpgsql_param_fetch().  Given the shared param list,
they will copy non-dynamic params the current query doesn't use.  That cost is
best avoided, not being well-bounded; consider the case of an unrelated
variable containing a TOAST pointer to a 1-GiB value.  One approach is to have
setup_param_list() copy the paramnos pointer to a new ParamListInfoData field:

Bitmapset  *paramMask;  /* if non-NULL, ignore params lacking a 1-bit */

Test it directly in copyParamList() and SerializeParamList().  As a bonus,
that would allow use of the shared param list for more cases involving
cursors.  Furthermore, plpgsql_param_fetch() would never need to test
paramnos.  A more-general alternative is to have a distinct "paramIsUsed"
callback, but I don't know how one would exploit the extra generality.


-- 
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: pg_confcheck - syntactic & semantic validation of postgresql configuration files

[Amir Rohan]

On 10/14/2015 01:16 AM, Andres Freund wrote:
> On October 13, 2015 11:14:19 PM GMT+02:00, Alvaro Herrera 
>  wrote:
>> Amir Rohan wrote:
>>
>>> I've been considering that. Reusing the parser would ensure no errors
>>> are introduces by having a different implementation, but on the other
>>> hand involving the pg build in installation what's intended as a
>>> lightweight, independent tool would hurt.
>>> Because it's dubious whether this will end up in core, I'd like
>>> "pip install pg_confcheck" to be all that is required.
>>
>> Maybe just compile a single file in a separate FRONTEND environment?
> 
> Maybe I'm missing something here - but doesn't the posted binary do nearly 
> all of this for you already? There's the option to display the value of a 
> config option, and that checks the validity of the configuration. Might need 
> to add an option to validate hba.conf et al as well and allow to display all 
> values. That should be pretty simple?
> 

Andres, please see upthread for quite a bit on what it doesn't do, and
why having it in the server is both an advantages and a shortcoming.




-- 
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: pg_confcheck - syntactic & semantic validation of postgresql configuration files

[Amir Rohan]

On 10/14/2015 01:12 AM, Alvaro Herrera wrote:
> Amir Rohan wrote:
>> On 10/14/2015 12:14 AM, Alvaro Herrera wrote:
>>> Amir Rohan wrote:
>>>
 I've been considering that. Reusing the parser would ensure no errors
 are introduces by having a different implementation, but on the other
 hand involving the pg build in installation what's intended as a
 lightweight, independent tool would hurt.
 Because it's dubious whether this will end up in core, I'd like
 "pip install pg_confcheck" to be all that is required.
>>>
>>> Maybe just compile a single file in a separate FRONTEND environment?
>>
>> You mean refactoring the postgres like rhass means? could you elaborate?
>>
>> I believe most people get pg as provided by their distro or PaaS,
>> and not by compiling it.
> 
> I mean the utility would be built by using a file from the backend
> source, just like pg_xlogdump does.  We have several such cases.
> I don't think this is impossible to do outside core, either.
> 

I've considered "vendoring", but it seems like enough code surgery
be involved to make this very dubious "reuse". The language is simple
enough that writing a parser from scratch isn't a big deal hard, and
there doesn't seem much room for divergent parsing either.
So, the only question is whether reusing the existing parser will
brings along some highly useful functionality beyond an AST and
a battle-tested validator for bools, etc'. I'm not ruling anything
out yet, though.

Amir



-- 
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 v3] GSSAPI encryption support

[Robbie Harwood]

Alright, here's v3.  As requested, it's one patch now.  Other things
addressed herein include:

 - postgres.h/assert.h ordering fix
 - spacing around casts
 - leaking of GSS buffer in be_gss_inplace_decrypt
 - libpq-be.h not having a conditional internal include
 - always exposing guc veriable gss_encrypt
 - copyright/description headers on all new files
 - movement of GSSAPI methods from fe-auth.c and auth.c to fe-gss.c and
   be-gss.c respectively
 - renaming GSSAPI files to fe-gss.c and be-gss.c (drops -secure)

Andres, one thing you mentioned as "feels rather wrong" was the
GSSAPI-specific code in pqcomm.c; while looking at that again, I have a
slightly better explanation than what I said previously.

Essentially, the problem is that socket_putmessage_noblock() needs to
know the size of the message to put in the buffer but we can't know
that until we've encrypted the message.  socket_putmessage_noblock()
calls socket_putmessage() after ensuring the call will not block;
however, other code paths simply call directly into socket_putmessage()
and so socket_putmessage() needs to have a path to encryption as well.

If you have other potential solutions to this problem, I would love to
hear them; right now though I don't see a better way.

Patch follows.  Thanks!
From 6710d5ad0226ea3a5ea8e35d6dc54b4500f1d3e0 Mon Sep 17 00:00:00 2001
From: "Robbie Harwood (frozencemetery)" 
Date: Mon, 8 Jun 2015 19:27:45 -0400
Subject: [PATCH] GSSAPI encryption support

Encryption is opportuinistic by default for backward compatability, but can be
forced using a server HBA parameter or a client connection URI parameter.
---
 configure   |   2 +
 configure.in|   1 +
 doc/src/sgml/client-auth.sgml   |  19 +-
 doc/src/sgml/libpq.sgml |  12 ++
 doc/src/sgml/protocol.sgml  |  82 +++-
 doc/src/sgml/runtime.sgml   |  20 +-
 src/Makefile.global.in  |   1 +
 src/backend/libpq/Makefile  |   4 +
 src/backend/libpq/auth.c| 338 +-
 src/backend/libpq/be-gss.c  | 397 
 src/backend/libpq/hba.c |   9 +
 src/backend/libpq/pqcomm.c  |  39 
 src/backend/tcop/postgres.c |  30 ++-
 src/backend/utils/init/postinit.c   |   7 +-
 src/backend/utils/misc/guc.c|  30 +++
 src/include/libpq/auth.h|   2 +
 src/include/libpq/hba.h |   1 +
 src/include/libpq/libpq-be.h|  26 +++
 src/include/libpq/libpq.h   |   2 +
 src/interfaces/libpq/Makefile   |   4 +
 src/interfaces/libpq/fe-auth.c  | 182 -
 src/interfaces/libpq/fe-connect.c   |  56 -
 src/interfaces/libpq/fe-gss.c   | 280 +
 src/interfaces/libpq/fe-misc.c  |   5 +
 src/interfaces/libpq/fe-protocol3.c |  60 ++
 src/interfaces/libpq/libpq-int.h|  16 ++
 26 files changed, 1097 insertions(+), 528 deletions(-)
 create mode 100644 src/backend/libpq/be-gss.c
 create mode 100644 src/interfaces/libpq/fe-gss.c

diff --git a/configure b/configure
index b771a83..a542577 100755
--- a/configure
+++ b/configure
@@ -712,6 +712,7 @@ with_uuid
 with_selinux
 with_openssl
 krb_srvtab
+with_gssapi
 with_python
 with_perl
 with_tcl
@@ -5488,6 +5489,7 @@ $as_echo "$with_gssapi" >&6; }
 
 
 
+
 #
 # Kerberos configuration parameters
 #
diff --git a/configure.in b/configure.in
index b5868b0..fccf542 100644
--- a/configure.in
+++ b/configure.in
@@ -636,6 +636,7 @@ PGAC_ARG_BOOL(with, gssapi, no, [build with GSSAPI support],
   krb_srvtab="FILE:\$(sysconfdir)/krb5.keytab"
 ])
 AC_MSG_RESULT([$with_gssapi])
+AC_SUBST(with_gssapi)
 
 
 AC_SUBST(krb_srvtab)
diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml
index 3b2935c..e6456a1 100644
--- a/doc/src/sgml/client-auth.sgml
+++ b/doc/src/sgml/client-auth.sgml
@@ -913,9 +913,10 @@ omicron bryanh  guest1
 GSSAPI with Kerberos
 authentication according to RFC 1964. GSSAPI
 provides automatic authentication (single sign-on) for systems
-that support it. The authentication itself is secure, but the
-data sent over the database connection will be sent unencrypted unless
-SSL is used.
+that support it. The authentication itself is secure, and GSSAPI can be
+used for connection encryption as well (see the
+require_encrypt parameter below); SSL
+can also be used for connection security.

 

@@ -1046,6 +1047,18 @@ omicron bryanh  guest1

   
  
+
+ 
+  require_encrypt
+  
+   
+Whether to require GSSAPI encryption.  Default is off, which causes
+GSSAPI encryption to be enabled if available and requested for
+compatibility with old clients.  It is recommended to set this unless
+old clients are present.
+   
+  
+ 
 

   
diff --git a/doc/src/sgml/libpq.sgml b/doc/

Re: [HACKERS] Proposal: pg_confcheck - syntactic & semantic validation of postgresql configuration files

[Andres Freund]

On October 13, 2015 11:14:19 PM GMT+02:00, Alvaro Herrera 
 wrote:
>Amir Rohan wrote:
>
>> I've been considering that. Reusing the parser would ensure no errors
>> are introduces by having a different implementation, but on the other
>> hand involving the pg build in installation what's intended as a
>> lightweight, independent tool would hurt.
>> Because it's dubious whether this will end up in core, I'd like
>> "pip install pg_confcheck" to be all that is required.
>
>Maybe just compile a single file in a separate FRONTEND environment?

Maybe I'm missing something here - but doesn't the posted binary do nearly all 
of this for you already? There's the option to display the value of a config 
option, and that checks the validity of the configuration. Might need to add an 
option to validate hba.conf et al as well and allow to display all values. That 
should be pretty simple?

--- 
Please excuse brevity and formatting - I am writing this on my mobile phone.


-- 
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: pg_confcheck - syntactic & semantic validation of postgresql configuration files

[Alvaro Herrera]

Amir Rohan wrote:
> On 10/14/2015 12:14 AM, Alvaro Herrera wrote:
> > Amir Rohan wrote:
> > 
> >> I've been considering that. Reusing the parser would ensure no errors
> >> are introduces by having a different implementation, but on the other
> >> hand involving the pg build in installation what's intended as a
> >> lightweight, independent tool would hurt.
> >> Because it's dubious whether this will end up in core, I'd like
> >> "pip install pg_confcheck" to be all that is required.
> > 
> > Maybe just compile a single file in a separate FRONTEND environment?
> 
> You mean refactoring the postgres like rhass means? could you elaborate?
> 
> I believe most people get pg as provided by their distro or PaaS,
> and not by compiling it.

I mean the utility would be built by using a file from the backend
source, just like pg_xlogdump does.  We have several such cases.
I don't think this is impossible to do outside core, either.

-- 
Á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] Proposal: pg_confcheck - syntactic & semantic validation of postgresql configuration files

[Amir Rohan]

On 10/14/2015 12:14 AM, Alvaro Herrera wrote:
> Amir Rohan wrote:
> 
>> I've been considering that. Reusing the parser would ensure no errors
>> are introduces by having a different implementation, but on the other
>> hand involving the pg build in installation what's intended as a
>> lightweight, independent tool would hurt.
>> Because it's dubious whether this will end up in core, I'd like
>> "pip install pg_confcheck" to be all that is required.
> 
> Maybe just compile a single file in a separate FRONTEND environment?
> 

You mean refactoring the postgres like rhass means? could you elaborate?

I believe most people get pg as provided by their distro or PaaS,
and not by compiling it. Involving a pg build environment is asking a
whole lot of someone who has pg all installed and who just wants to lint
their conf.

It's also legitimate for someone to be configuring postgres
without having a clue how to compile it.

If this was in core, this wouldn't be an issue because then packages
would also include  the tool. Note I'm not actually pushing for that,
it's that that has implications on what can be assumed as available.






-- 
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] Parallel Seq Scan

[Robert Haas]

On Tue, Oct 13, 2015 at 2:45 AM, Amit Kapila  wrote:
> Attached is rebased patch for partial seqscan support.

Review comments:

- If you're going to pgindent execParallel.c, you need to add some
entries to typedefs.list so it doesn't mangle the formatting.
ExecParallelEstimate's parameter list is misformatted, for example.
Also, I think if we're going to do this we had better extract the
pgindent changes and commit those first.  It's pretty distracting the
way you have it.

- Instead of inlining the work needed by each parallel mode in
ExecParallelEstimate(), I think you should mimic the style of
ExecProcNode and call a node-type specific function that is part of
that node's public interface - here, ExecPartialSeqScanEstimate,
perhaps.  Similarly for ExecParallelInitializeDSM.  Perhaps
ExecPartialSeqScanInitializeDSM.

- I continue to think GetParallelShmToc is the wrong approach.
Instead, each time ExecParallelInitializeDSM or
ExecParallelInitializeDSM calls a nodetype-specific initialized
function (as described in the previous point), have it pass d->pcxt as
an argument.  The node can get the toc from there if it needs it.  I
suppose it could store a pointer to the toc in its scanstate if it
needs it, but it really shouldn't.  Instead, it should store a pointer
to, say, the ParallelHeapScanDesc in the scanstate.  It really should
only care about its own shared data, so once it finds that, the toc
shouldn't be needed any more. Then ExecPartialSeqScan doesn't need to
look up pscan; it's already recorded in the scanstate.

- ExecParallelInitializeDSMContext's new pscan_len member is 100%
wrong.  Individual scan nodes don't get to add stuff to that context
object.  They should store details like this in their own ScanState as
needed.

- The positioning of the new nodes in various lists doesn't seem to
entirely consistent.  nodes.h adds them after SampleScan which isn't
terrible, though maybe immediately after SeqScan would be better, but
_outNode has it right after BitmapOr and the switch in _copyObject has
it somewhere else again.

- Although the changes in parallelpaths.c are in a good direction, I'm
pretty sure this is not yet up to scratch.  I am less sure exactly
what needs to be fixed, so I'll have to give some more thought to
that.

-- 
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] Performance improvement for joins where outer side is unique

[David Rowley]

On 4 September 2015 at 04:50, Robert Haas  wrote:

>
> Also: very nice performance results.
>
>
Thanks.

On following a thread in [General] [1] it occurred to me that this patch
can give a massive improvement on Merge joins where the mark and restore
causes an index scan to have to skip over many filtered rows again and
again.

I mocked up some tables and some data from the scenario on the [General]
thread:

create table customers (id bigint, group_id bigint not null);
insert into customers select x.x,x.x%27724+1 from generate_series(1,473733)
x(x);
alter table customers add constraint customer_pkey primary key (id);
create table balances (id bigint, balance int not null, tracking_number int
not null, customer_id bigint not null);
insert into balances select x.x, 100, 12345, x.x % 45 + 1 from
generate_Series(1,16876) x(x);
create index balance_customer_id_index on balances (customer_id);
create index balances_customer_tracking_number_index on balances
(customer_id,tracking_number);
analyze;

Unpatched I get:

test=# explain analyze SELECT ac.* FROM balances ac join customers o ON o.id
= ac.customer_id WHERE o.group_id = 45;

 QUERY PLAN

 Merge Join  (cost=164.87..1868.70 rows=1 width=24) (actual
time=6.110..1491.408 rows=375 loops=1)
   Merge Cond: (ac.customer_id = o.id)
   ->  Index Scan using balance_customer_id_index on balances ac
 (cost=0.29..881.24 rows=16876 width=24) (actual time=0.009..5.206
rows=16876 loops=1)
   ->  Index Scan using customer_pkey on customers o  (cost=0.42..16062.75
rows=17 width=8) (actual time=0.014..1484.382 rows=376 loops=1)
 Filter: (group_id = 45)
 Rows Removed by Filter: 10396168
 Planning time: 0.207 ms
 Execution time: 1491.469 ms
(8 rows)

Patched:

test=# explain analyze SELECT ac.* FROM balances ac join customers o ON o.id
= ac.customer_id WHERE o.group_id = 45;

 QUERY PLAN

 Merge Join  (cost=164.87..1868.70 rows=1 width=24) (actual
time=6.037..11.528 rows=375 loops=1)
   Merge Cond: (ac.customer_id = o.id)
   ->  Index Scan using balance_customer_id_index on balances ac
 (cost=0.29..881.24 rows=16876 width=24) (actual time=0.009..4.978
rows=16876 loops=1)
   ->  Index Scan using customer_pkey on customers o  (cost=0.42..16062.75
rows=17 width=8) (actual time=0.015..5.141 rows=2 loops=1)
 Filter: (group_id = 45)
 Rows Removed by Filter: 27766
 Planning time: 0.204 ms
 Execution time: 11.575 ms
(8 rows)

Now it could well be that the merge join costs need a bit more work to
avoid a merge join in this case, but as it stands as of today, this is your
performance gain.

Regards

David Rowley

[1]
http://www.postgresql.org/message-id/caczyddiaxeam2rkmhbmuhwvcm4txh+5e3hqgggyzfzbn-pn...@mail.gmail.com

--
 David Rowley   http://www.2ndQuadrant.com/

 PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Proposal: pg_confcheck - syntactic & semantic validation of postgresql configuration files

[Alvaro Herrera]

Amir Rohan wrote:

> I've been considering that. Reusing the parser would ensure no errors
> are introduces by having a different implementation, but on the other
> hand involving the pg build in installation what's intended as a
> lightweight, independent tool would hurt.
> Because it's dubious whether this will end up in core, I'd like
> "pip install pg_confcheck" to be all that is required.

Maybe just compile a single file in a separate FRONTEND environment?

-- 
Á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] Proposal: pg_confcheck - syntactic & semantic validation of postgresql configuration files

[Amir Rohan]

On 10/13/2015 09:20 PM, Robert Haas wrote:
> On Mon, Oct 12, 2015 at 8:01 PM, Amir Rohan  wrote:
>> That wasn't my intention. Perhaps I'm overreacting to a long-standing
>> "Tom Lane's bucket of cold water" tradition. I'm new here.
>> I understand your point and I was only reiterating what in particular
>> makes the conf checker distinctly useful IMO, and what it could
>> provide that pg_settings doesn't.
>>
>> I've looked at parts of the pg_settings implementation and indeed some
>> of that code (and legwork) could be reused so the mundane parts
>> of writing this will be less hassle. I might have missed that if Tom and
>> you hadn't pointed that out.
>>
>> So, Fair, and thanks.
> 
> No worries. I'm not upset with you, and I see where you're coming
> from.  But I since I'm trying to be helpful I thought I would check
> whether it's working.  Sounds like yes, which is nice.
> 
> It would be spiffy if we could use the same config-file parser from
> outside postgres itself, but it seems hard.  I assume the core lexer
> and parser could be adapted to work from libcommon with some
> non-enormous amount of effort, but check-functions are can and do
> assume that they are running in a backend environment; one would lose
> a lot of sanity-checking if those couldn't be executed, 

I've been considering that. Reusing the parser would ensure no errors
are introduces by having a different implementation, but on the other
hand involving the pg build in installation what's intended as a
lightweight, independent tool would hurt.
Because it's dubious whether this will end up in core, I'd like
"pip install pg_confcheck" to be all that is required.

Also, anything short of building the tool in tree with an unmodified
parser amounts to forking the parser code and maintaining it along with
upstream, per version, etc'. I'm not eager to do that.

> and checking GUCs created by loadable modules seems impossible.  Still, even a
> partial move toward making this code accessible in frontend code would
> be welcome from where I sit.
> 

Dumping the output of the pg_settings view into json has already
provided all the type/enum information needed to type-check values and
flag unrecognized GUCs. I don't really see that as involving a heroic
amount of work, the language seems extremely simple.
There aren't that many types, type-checking them isn't that much work,
and once that's written the same checks should apply to all GUCs
registered with the server, assuming the pg_settings view is
exhaustive in that sense.

Any modules outside the main server can provide their own data
in a similar format, if it comes to that. I doubt whether such
a tool has that much appeal, especially if it isn't bundled with
the server.

So, I'll think about this some more, and write up a description of how I
think it's going to look for some feedback. Then I'll code something.

Regards,
Amir




-- 
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] Duda

[Rodolfo Campero]

La lista de correo apropiada para tu consulta es pgsql-es-ayuda
(pgsql-es-ayuda(at)postgresql(dot)org).
Saludos,

El 13 de octubre de 2015, 16:51, Enrique Escobar 
escribió:

> Hola
> Tengo una duda, que tan pesado es poner ssl en una base. (me refiero si es
> mas pesado para el equipo usar esta conexión o es igual a una con ip en hba?
> Mil Gracias
>
>
> --
> Saludos Enrique
>

-- 
Rodolfo Campero

Re: [HACKERS] pam auth - add rhost item

[kolo hhmow]

Yes, sorry. I was in hurry when I posted this message.
I dont understand whay in CheckPAMAuth function only PAM_USER item is
adding to pam information before authenticate?
Wheter it would be a problem to set additional pam information like
PAM_RHOST which is very useful because we can use this item to restrict
access to this ip address.
I hope I'm more specific now and you will understand me.
Sorry, but I'm not native english speaker.
Patch in attachment, and link below to web-view on github:
https://github.com/grzsmp/postgres/commit/5e2b102ec6de27e786d627623dcb187e997609e4

On Tue, Oct 13, 2015 at 7:08 PM, Robert Haas  wrote:

> On Mon, Oct 12, 2015 at 12:01 PM, kolo hhmow  wrote:
> > Wheter it would be a problem to set additional item (rhost) before
> > pam_authentication function in backend/libpq/auth.c?
> > It is very useful because you can restrict access to given ip address
> like
> > in mysql.
> > And this actually utilized in pam-pgsql, wich cannot be used because
> rhost
> > item is empty.
>
> I can't understand what you are suggesting here.  Perhaps you could be
> more specific, or propose a patch.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c
index aca4ffe..1cff899 100644
--- a/src/backend/libpq/auth.c
+++ b/src/backend/libpq/auth.c
@@ -1736,7 +1736,9 @@ CheckPAMAuth(Port *port, char *user, char *password)
 {
 	int			retval;
 	pam_handle_t *pamh = NULL;
-
+	char hostinfo[NI_MAXHOST];
+pg_getnameinfo_all(&port->raddr.addr, port->raddr.salen,
+hostinfo, sizeof(hostinfo), NULL, 0, NI_NUMERICHOST);
 	/*
 	 * We can't entirely rely on PAM to pass through appdata --- it appears
 	 * not to work on at least Solaris 2.6.  So use these ugly static
@@ -1780,6 +1782,16 @@ CheckPAMAuth(Port *port, char *user, char *password)
 		pam_passwd = NULL;		/* Unset pam_passwd */
 		return STATUS_ERROR;
 	}
+	
+	retval = pam_set_item(pamh, PAM_RHOST, hostinfo);
+	if (retval != PAM_SUCCESS)
+	{
+		ereport(LOG,
+(errmsg("pam_set_item(PAM_RHOST) failed: %s",
+	pam_strerror(pamh, retval;
+pam_passwd = NULL;  	/* Unset pam_passwd */
+return STATUS_ERROR;
+	}
 
 	retval = pam_set_item(pamh, PAM_CONV, &pam_passw_conv);
 

-- 
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] SQL function to report log message

[Robert Haas]

This patch is marked as Ready for Committer in the CommitFest
application.  Here is my attempt to summarize the votes upthread:

Tom Lane: plpgsql RAISE is sufficient; we don't need this.
Pavel Stehule: could be replaced by custom function, but not against it.
Robert Haas: plpgsql RAISE is sufficient; we don't need this.
Jim Nasby: A generic wrapper around RAISE is not trivial, so +1.
Andres Freund: I've written this function many times, so let's have it in core.
David Fetter: I've written this function like 20 times, we should have it.

I'm only -0 on this patch, so I won't yell and scream if some other
committer is prepared to step up and get this committed, but I'm not
excited about doing it myself on the strength of a weak consensus in
which I'm not a participant.  Any takers?  I recommend that we allow
this patch 30 days to attract an interested committer, and, if nobody
volunteers in that time, that we mark it Rejected for lack of
interest.

-- 
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] Duda

[Enrique Escobar]

Hola
Tengo una duda, que tan pesado es poner ssl en una base. (me refiero si 
es mas pesado para el equipo usar esta conexión o es igual a una con ip 
en hba?

Mil Gracias


--
Saludos Enrique

Re: [HACKERS] DTrace build dependency rules

[Robert Haas]

On Tue, Aug 18, 2015 at 12:08 PM, Alvaro Herrera
 wrote:
> Robert Haas wrote:
>> On Sat, Aug 15, 2015 at 6:45 PM, Mark Johnston  wrote:
>
>> > The bug is in src/backend/Makefile. probes.o, the dtrace(1)-generated
>> > object file, depends on the objfiles.txt for each of the backend
>> > subdirs. These files depend in turn on the object files themselves; if
>> > objfiles.txt is out of date with respect to one of its object files, the
>> > mtime of objfiles.txt is updated with "touch" (see backend/common.mk).
>> > The problem is that dtrace -G, which runs at the end of the build,
>> > modifies a number of object files (it overwrites their probe sites with
>> > NOPs), thus making their corresponding objfiles.txt out of date. Then,
>> > when "make install" traverses the backend subdirs, it updates
>> > objfiles.txt, which causes probes.o to be rebuilt, resulting in an error
>> > from dtrace(1).
>>
>> Gosh, that's pretty ugly.  I would have thought it would be a real
>> no-no to update the .o file once it got generated.  If nothing else, a
>> modification to the .c file concurrent with a make invocation might
>> lead to the .o not getting rebuilt the next time make is run.
>
> I had the same thought, and wondered for a bit whether we should instead
> have the compilation rules produce some intermediate file (prior to
> dtrace fumbling), then emit the .o from dtrace -G.  OTOH this might be
> more trouble than is worth for a feature that doesn't see a lot of use.

Given the lack of further interest from the PostgreSQL community,
that's my guess.  I've pushed this patch to master; let's see if we
get any complaints.  If it makes life better for FreeBSD without
making life worse for anyone else, I suppose we might as well do it
until something better comes along.

-- 
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] INSERT ... ON CONFLICT documentation clean-up patch

[Robert Haas]

On Sun, Oct 11, 2015 at 8:54 PM, Peter Geoghegan  wrote:
> Attached documentation patch is intended to close-out the INSERT ...
> ON CONFLICT documentation items from the 9.5 open item list. I also
> attach a patch that makes a minor adjustment to an error message
> concerning deferred constraints; the problem came to my attention as I
> worked on the documentation patch (where the same minor inaccuracy is
> corrected).

I have committed 0001 and back-patched it to 9.5.  In the future,
please remember to include changes to the regression test results in
your patch.

I hope someone more familiar with the new upsert feature can review
0002, perhaps ideally Andres. One comment on that patch is that I
think slash-separated words should be avoided in the documentation;
this patch introduces 2 uses of "and/or" and 1 of
"constraints/indexes".  I realize we have other such usages in the
documentation, but I believe it's a style better avoided.

Thanks for your work to tidy up these loose ends.

-- 
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] bugs and bug tracking

[Bruce Momjian]

On Tue, Oct 13, 2015 at 08:39:16AM -0700, Joshua Drake wrote:
> On 10/13/2015 08:15 AM, Tom Lane wrote:
> >Andres Freund  writes:
> >>On 2015-10-13 16:21:54 +0200, �lvaro Hern�ndez Tortosa wrote:
> >>>(50 chars for the commit summary, 72 chars line wrapping)
> >
> >>-1 - imo 50 chars too often makes the commit summary too unspecific,
> >>requiring to read much more.
> >
> >I agree --- I have a hard enough time writing a good summary in 75
> >characters.  50 would be awful.
> 
> The idea of writing a commit message that is useful in a number of
> characters that is less than half a tweet sounds unbearable. The
> idea of trying to discern what the hell a commit actually is in a
> number of characters that is less than half a tweet sounds
> completely ridiculous.
> 
> -1 on that particular aspect.

FYI, I think we already have two limits for the first line summary of
commit messages.  The limits are 64 for commit message subjects and 50
characters for gitweb summary pages --- anything longer is truncated.

My commit template shows me the limits as I am typing the commit text to
remind me of the limits:

-- email subject limit -
-- gitweb summary limit --

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
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

[Masahiko Sawada]

On Wed, Oct 14, 2015 at 3:16 AM, Josh Berkus  wrote:
> On 10/13/2015 11:02 AM, Masahiko Sawada wrote:
>> I thought that this feature for postgresql should be simple at first
>> implementation.
>> It would be good even if there are some restriction such as the
>> nesting level, the group setting.
>> The another new approach that I came up with is,
>> * Add new parameter synchronous_replication_method (say s_r_method)
>> which can have two names: 'priority', 'quorum'
>> * If s_r_method = 'priority', the value of s_s_names (e.g. 'n1,n2,n3')
>> is handled using priority. It's same as '[n1,n2,n3]' in dedicated
>> laguage.
>> * If s_r_method = 'quorum', the value of s_s_names is handled using
>> quorum commit, It's same as '(n1,n2,n3)' in dedicated language.
>
> Well, the first question is: can you implement both of these things for
> 9.6, realistically?
>  If you can implement them, then we can argue about
> configuration format later.  It's even possible that the nature of your
> implementation will enforce a particular syntax.
>
> For example, if your implementation requires sync groups to be named,
> then we have to include group names in the syntax.  If you can't
> implement nesting in the near future, there's no reason to have a syntax
> for it.

Yes, I can implement both without nesting.
The draft patch of replication using priority is already implemented
by Michael, so I need to implement simple quorum commit logic and
merge them.

>> * Setting of synchronous_standby_names is same as today. That is, the
>> storing the nesting value is not supported.
>> * If we want to support more complex syntax like what we are
>> discussing, we can add the new value to s_r_method, for example
>> 'complex', 'json'.
>
> I think having two different syntaxes is a bad idea.  I'd rather have a
> wholly proprietary configuration markup than deal with two alternate ones.
>

I agree, we should choice either.

Regards,

--
Masahiko Sawada


-- 
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: pg_confcheck - syntactic & semantic validation of postgresql configuration files

[Robert Haas]

On Mon, Oct 12, 2015 at 8:01 PM, Amir Rohan  wrote:
> That wasn't my intention. Perhaps I'm overreacting to a long-standing
> "Tom Lane's bucket of cold water" tradition. I'm new here.
> I understand your point and I was only reiterating what in particular
> makes the conf checker distinctly useful IMO, and what it could
> provide that pg_settings doesn't.
>
> I've looked at parts of the pg_settings implementation and indeed some
> of that code (and legwork) could be reused so the mundane parts
> of writing this will be less hassle. I might have missed that if Tom and
> you hadn't pointed that out.
>
> So, Fair, and thanks.

No worries. I'm not upset with you, and I see where you're coming
from.  But I since I'm trying to be helpful I thought I would check
whether it's working.  Sounds like yes, which is nice.

It would be spiffy if we could use the same config-file parser from
outside postgres itself, but it seems hard.  I assume the core lexer
and parser could be adapted to work from libcommon with some
non-enormous amount of effort, but check-functions are can and do
assume that they are running in a backend environment; one would lose
a lot of sanity-checking if those couldn't be executed, and checking
GUCs created by loadable modules seems impossible.  Still, even a
partial move toward making this code accessible in frontend code would
be welcome from where I sit.

-- 
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] Support for N synchronous standby servers - take 2

[Josh Berkus]

On 10/13/2015 11:02 AM, Masahiko Sawada wrote:
> I thought that this feature for postgresql should be simple at first
> implementation.
> It would be good even if there are some restriction such as the
> nesting level, the group setting.
> The another new approach that I came up with is,
> * Add new parameter synchronous_replication_method (say s_r_method)
> which can have two names: 'priority', 'quorum'
> * If s_r_method = 'priority', the value of s_s_names (e.g. 'n1,n2,n3')
> is handled using priority. It's same as '[n1,n2,n3]' in dedicated
> laguage.
> * If s_r_method = 'quorum', the value of s_s_names is handled using
> quorum commit, It's same as '(n1,n2,n3)' in dedicated language.

Well, the first question is: can you implement both of these things for
9.6, realistically?  If you can implement them, then we can argue about
configuration format later.  It's even possible that the nature of your
implementation will enforce a particular syntax.

For example, if your implementation requires sync groups to be named,
then we have to include group names in the syntax.  If you can't
implement nesting in the near future, there's no reason to have a syntax
for it.

> * Setting of synchronous_standby_names is same as today. That is, the
> storing the nesting value is not supported.
> * If we want to support more complex syntax like what we are
> discussing, we can add the new value to s_r_method, for example
> 'complex', 'json'.

I think having two different syntaxes is a bad idea.  I'd rather have a
wholly proprietary configuration markup than deal with two alternate ones.

-- 
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] Support for N synchronous standby servers - take 2

[Masahiko Sawada]

On Sat, Oct 10, 2015 at 4:35 AM, Robert Haas  wrote:
> On Fri, Oct 9, 2015 at 12:00 AM, Amit Kapila  wrote:
>> Sounds like both the approaches have some pros and cons, also there are
>> some people who prefer mini-language and others who prefer JSON.  I think
>> one thing that might help, is to check how other databases support this
>> feature or somewhat similar to this feature (mainly with respect to User
>> Interface), as that can help us in knowing what users are already familiar
>> with.
>
> +1!
>

For example, MySQL 5.7 has similar feature, but it doesn't support
quorum commit, and is simpler than postgresql attempting feature.
There is one configuration parameter in MySQL 5.7 which indicates the
number of sync replication node.
The primary server commit when the primary server receives the
specified number of ACK from standby server regardless name of standby
server.

And IIRC, Oracle database also doesn't support the quorum commit as well.
The settings standby server sync or async is specified per standby
server in configuration parameter in primary node.

I think that the use of JSON format approach and dedicated language
approach are different.
The dedicated language format approach would be useful for simple
configuration such as the one nesting, not using group.
This will allow us to configure replication more simpler and easier.
In contrast, The JSON format approach would be useful for complex configuration.

I thought that this feature for postgresql should be simple at first
implementation.
It would be good even if there are some restriction such as the
nesting level, the group setting.
The another new approach that I came up with is,
* Add new parameter synchronous_replication_method (say s_r_method)
which can have two names: 'priority', 'quorum'
* If s_r_method = 'priority', the value of s_s_names (e.g. 'n1,n2,n3')
is handled using priority. It's same as '[n1,n2,n3]' in dedicated
laguage.
* If s_r_method = 'quorum', the value of s_s_names is handled using
quorum commit, It's same as '(n1,n2,n3)' in dedicated language.
* Setting of synchronous_standby_names is same as today. That is, the
storing the nesting value is not supported.
* If we want to support more complex syntax like what we are
discussing, we can add the new value to s_r_method, for example
'complex', 'json'.

Though?

Regards,

--
Masahiko Sawada


-- 
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] Database schema diff

[Christopher Browne]

On 13 October 2015 at 11:48, Michal Novotny 
wrote:

> Hi guys,
>
> I would like to ask you whether is there any tool to be able to compare
> database schemas ideally no matter what the column order is or to dump
> database table with ascending order of all database columns.
>
> For example, if I have table (called table) in schema A and in schema B
> (the time difference between is 1 week) and I would like to verify the
> column names/types matches but the order is different, i.e.:
>
> Schema A (2015-10-01) |  Schema B (2015-10-07)
>   |
> id int|  id int
> name varchar(64)  |  name varchar(64)
> text text |  description text
> description text  |  text text
>
> Is there any tool to compare and (even in case above) return that both
> tables match? Something like pgdiff or something?
>
> This should work for all schemas, tables, functions, triggers and all
> the schema components?
>
> Also, is there any tool to accept 2 PgSQL dump files (source for
> pg_restore) and compare the schemas of both in the way above?
>
> Thanks a lot!
> Michal


I built a tool I call "pgcmp", which is out on GitHub <
https://github.com/cbbrowne/pgcmp>

The one thing that you mention that it *doesn't* consider is the ordering
of columns.

It would not be difficult at all to add that comparison; as simple as adding
an extra capture of table columns and column #'s.  I'd be happy to consider
adding that in.

Note that pgcmp expects the database to be captured as databases; it pulls
data
from information_schema and such.  In order to run it against a pair of
dumps,
you'd need to load those dumps into databases, first.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

Re: [HACKERS] pam auth - add rhost item

[Robert Haas]

On Mon, Oct 12, 2015 at 12:01 PM, kolo hhmow  wrote:
> Wheter it would be a problem to set additional item (rhost) before
> pam_authentication function in backend/libpq/auth.c?
> It is very useful because you can restrict access to given ip address like
> in mysql.
> And this actually utilized in pam-pgsql, wich cannot be used because rhost
> item is empty.

I can't understand what you are suggesting here.  Perhaps you could be
more specific, or propose a 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] Getting sorted data from foreign server

[Robert Haas]

On Tue, Oct 13, 2015 at 3:29 AM, Ashutosh Bapat
 wrote:
>> - You consider pushing down ORDER BY if any prefix of the query
>> pathkeys satisfy is_foreign_expr(), but that doesn't seem right to me.
>> If the user says SELECT * FROM remotetab ORDER BY a, unsafe(a),
>> ordering the result set by a doesn't help us much.  We've talked a few
>> times about an incremental sort capability that would take a stream of
>> tuples already ordered by one or more columns and sort each group by
>> additional columns, but I don't think we have that currently.  Without
>> that capability, I don't think there's much benefit in sorting by a
>> prefix of the pathkeys.  I suspect that if we can't get them all, it's
>> not worth doing.
>
> I somehow thought, we are using output, which is ordered by prefix of
> pathkeys in Sort nodes. But as you rightly pointed out that's not the case.
> Only complete pathkeys are useful.

A truncated list of pathkeys is useful for merge joins, but not for
toplevel ordering.

>> - Right now, you have this code below the point where we bail out if
>> use_remote_estimate is not set.  If we keep it like that, the comment
>> needs updating.  But I suggest that we consider an ordered path even
>> if we are not using remote estimates.  Estimate the sorted path to
>> cost somewhat more than the unsorted path, so that we only choose that
>> path if the sort actually benefits us.  I don't know exactly how to
>> come up with a principled estimate given that we don't actually know
>> whether the remote side will need an extra sort or not, but maybe a
>> dumb estimate is still better than not trying a sorted path.
>
> I like that idea, although there are two questions
> 1. How can we estimate cost of getting the data sorted? If there is an
> appropriate index on foreign server we can get the data sorted at no extra
> cost. If there isn't the cost of sorting is proportionate to NlogN where N
> is the size of data. It seems unreasonable to arrive at the cost of sorting
> by multiplying with some constant multiplier. Also, the constant multiplier
> to the NlogN estimate depends heavily upon the properties of foreign server
> e.g. size of memory available for sorting, disk and CPU speed etc. The last
> two might have got factored into fdw_tuple_cost and fdw_startup_cost, so
> that's probably taken care of. If the estimate we come up turns out to be
> too pessimistic, we will not get sorted data even if that's the right thing
> to do. If too optimistic, we will incur heavy cost at the time of execution.
> Setting the cost estimate to some constant factor of NlogN would be too
> pessimistic if there is an appropriate index on foreign server. Otherway
> round if there isn't an appropriate index on foreign server.
>
> Even if we leave these arguments aside for a while, the question remains as
> to what should be the constant factor 10% or 20% or 50% or 100% or something
> else on top of the estimate for simple foreign table scan estimates (or
> NlogN of that)? I am unable to justify any of these factors myself. What do
> you say?

I think we want to estimate the cost in such a way that we'll tend to
pick the ordered path if it's useful, but skip it if it's not.  So,
say we pick 10%.  That's definitely enough that we won't pick a remote
sort when it's useless, but it's small enough that if a remote sort is
useful, we will probably choose to do it.  I think that's what we
want.  I believe we should err on the side of a small estimate because
it's generally better to do as much work as possible on the remote
side.  In some cases the sort may turn out to be free at execution
time because the remote server was going to generate the results in
that order anyway, and it may know that because of its own pathkeys,
and thus be able to skip the explicit ordering step.

>> - In the long run, we should probably either add some configuration so
>> that the local side can make better estimates even without
>> use_remote_estimate, or maybe have a way for the FDW to keep a cache
>> of data in the system catalogs that is updated by ANALYZE.  Then,
>> analyzing a foreign table could store information locally about
>> indexes and so forth, instead of starting each query planning cycle
>> with no knowledge about the remote side.  That's not a matter for this
>> patch, I don't think, but it seems like something we should do.
>
> To an extent knowing which indexes are available on the tables on foreign
> server will help. Now, I do understand that not every foreign server will
> have indexes like PostgreSQL, but as long as whatever they have can be
> translated into a language that PostgreSQL can understand it should be fine.
> From that point of view, will it help if we have declarative indexes on
> foreign tables similar to the declarative constraints? Obviously, we will be
> burdening user with extra work of maintaining the declarative indexes in
> sync like we do for constraints. But we might ease the burden when we get to
> fetch tha

Re: [HACKERS] bugs and bug tracking

[Alvaro Herrera]

Joshua D. Drake wrote:
> On 10/13/2015 08:15 AM, Tom Lane wrote:
> >Andres Freund  writes:
> >>On 2015-10-13 16:21:54 +0200, �lvaro Hern�ndez Tortosa wrote:
> >>>(50 chars for the commit summary, 72 chars line wrapping)
> >
> >>-1 - imo 50 chars too often makes the commit summary too unspecific,
> >>requiring to read much more.
> >
> >I agree --- I have a hard enough time writing a good summary in 75
> >characters.  50 would be awful.
> 
> The idea of writing a commit message that is useful in a number of
> characters that is less than half a tweet sounds unbearable. The idea of
> trying to discern what the hell a commit actually is in a number of
> characters that is less than half a tweet sounds completely ridiculous.

There are many commits that can be summarized in small lines; new
features are often like that.  Bug fix summaries are much harder to
write and most of the time they require longer lines.  When you can
achieve 50-char lines it looks better in tooling (gitweb or git log
--oneline), but if you have to make it 75 it's not the end of the world.

http://git.postgresql.org/gitweb/?p=postgresql.git;a=shortlog

I don't care one bit if one or two lines that are part of the _body_ of
the commit messages are longer than 80 chars, particularly if they
provide useful links such as message-ids.  The message-ids or
message-id-based URLs are too handy to ignore.

A month ago I asked sysadm...@pg.org about using
http://postgr.es/m/ as a way to create shorter URLs to use
in commit messages, but the idea wasn't too hotly received so I let it
go.  I'm glad it popped up again.

-- 
Á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] bugs and bug tracking

[Álvaro Hernández Tortosa]

On 13/10/15 17:39, Joshua D. Drake wrote:

On 10/13/2015 08:15 AM, Tom Lane wrote:

Andres Freund  writes:

On 2015-10-13 16:21:54 +0200, �lvaro Hern�ndez Tortosa wrote:

(50 chars for the commit summary, 72 chars line wrapping)



-1 - imo 50 chars too often makes the commit summary too unspecific,
requiring to read much more.


I agree --- I have a hard enough time writing a good summary in 75
characters.  50 would be awful.


The idea of writing a commit message that is useful in a number of 
characters that is less than half a tweet sounds unbearable. The idea 
of trying to discern what the hell a commit actually is in a number of 
characters that is less than half a tweet sounds completely ridiculous.


-1 on that particular aspect.

jD



I'm writing a YC application and they ask you to summarize your 
whole project idea in less than 50 chars. So I guess that a commit 
message can be summarized under 50 chars too ^_^
We even do this with Java commits, and hey, you know, if you include a 
JavaStyleCamelCaseUnnecesarilyVerboseClassName in this summary you're 
screwed up!


But it seems there's clear agreement on *not* restricting it to 50, 
so I have nothing else to add :)


Best,

Álvaro

--
Álvaro Hernández Tortosa


---
8Kdata



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Database schema diff

[Michal Novotny]

Hi guys,

I would like to ask you whether is there any tool to be able to compare
database schemas ideally no matter what the column order is or to dump
database table with ascending order of all database columns.

For example, if I have table (called table) in schema A and in schema B
(the time difference between is 1 week) and I would like to verify the
column names/types matches but the order is different, i.e.:

Schema A (2015-10-01) |  Schema B (2015-10-07)
  |
id int|  id int
name varchar(64)  |  name varchar(64)
text text |  description text
description text  |  text text

Is there any tool to compare and (even in case above) return that both
tables match? Something like pgdiff or something?

This should work for all schemas, tables, functions, triggers and all
the schema components?

Also, is there any tool to accept 2 PgSQL dump files (source for
pg_restore) and compare the schemas of both in the way above?

Thanks a lot!
Michal


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] bugs and bug tracking

[Joshua D. Drake]

On 10/13/2015 08:15 AM, Tom Lane wrote:

Andres Freund  writes:

On 2015-10-13 16:21:54 +0200, �lvaro Hern�ndez Tortosa wrote:

(50 chars for the commit summary, 72 chars line wrapping)



-1 - imo 50 chars too often makes the commit summary too unspecific,
requiring to read much more.


I agree --- I have a hard enough time writing a good summary in 75
characters.  50 would be awful.


The idea of writing a commit message that is useful in a number of 
characters that is less than half a tweet sounds unbearable. The idea of 
trying to discern what the hell a commit actually is in a number of 
characters that is less than half a tweet sounds completely ridiculous.


-1 on that particular aspect.

jD



regards, tom lane





--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
New rule for social situations: "If you think to yourself not even
JD would say this..." Stop and shut your mouth. It's going to be bad.


--
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] Postgres service stops when I kill client backend on Windows

[Tom Lane]

Michael Paquier  writes:
> On Tue, Oct 13, 2015 at 5:35 AM, Tom Lane  wrote:
>> After poking around a bit more, I propose the attached patch.  I've
>> checked that this is happy with an EXEC_BACKEND Unix build, but I'm not
>> able to test it on Windows ... would somebody do that?

> Looking at the patch, clearly +1 for the additional routine in both
> win32_shmem.c and sysv_shmem.c to clean up the shmem state at backend
> level. I have played as well with the patch on Windows and it behaves
> as expected: without the patch a process killed with taskkill /f stops
> straight the server even if restart_on_crash is on. With the patch the
> server restarts correctly.

OK, pushed with some additional comment-smithing.

I noticed while looking at this that for subprocesses that aren't supposed
to be attached to shared memory, we do pgwin32_ReserveSharedMemoryRegion()
anyway in internal_forkexec(), and then that's never undone anywhere,
so that that segment of the subprocess's memory space remains reserved.
I'm not sure if this is worth changing, but if it is, we could do so now
by calling VirtualFree() in PGSharedMemoryNoReAttach().

BTW, I am suspicious that the DSM stuff may have related issues --- do
we use inheritable mapping handles for DSM segments on Windows?

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] Improve the concurency of vacuum full table and select statement on the same relation

[Jinyu]

>>it's approach to this is to summarily kill anything that attempts DDL on a 
>>table being repacked.
Why? I am confused with it.  Could you please explain this?

Jinyu Zhang
thanks


At 2015-10-12 23:46:12, "Jim Nasby"  wrote:
>On 10/11/15 6:55 AM, Jinyu wrote:
>> Are there other solutions to improve the concurency of vacuum
>> full/cluster and select statement on the same relation?
>
>ISTM that if we were going to put effort into this it makes more sense 
>to pull pg_repack into core. BTW, it's approach to this is to summarily 
>kill anything that attempts DDL on a table being repacked.
>-- 
>Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
>Experts in Analytics, Data Architecture and PostgreSQL
>Data in Trouble? Get it in Treble! http://BlueTreble.com

Re: [HACKERS] bugs and bug tracking

[Tom Lane]

Andres Freund  writes:
> On 2015-10-13 16:21:54 +0200, Álvaro Hernández Tortosa wrote:
>> (50 chars for the commit summary, 72 chars line wrapping)

> -1 - imo 50 chars too often makes the commit summary too unspecific,
> requiring to read much more.

I agree --- I have a hard enough time writing a good summary in 75
characters.  50 would be awful.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] bugs and bug tracking

[Álvaro Hernández Tortosa]

On 13/10/15 16:24, Andres Freund wrote:

On 2015-10-13 16:21:54 +0200, Álvaro Hernández Tortosa wrote:

On 13/10/15 04:40, Tom Lane wrote:

I'm with Robert on the idea that commit log entries need to be
limited-width. I personally format them to 75 characters, so that
git_changelog's output is less than 80 characters. regards, tom lane

 Little bit off-topic, but if precisely if we're trying to make the
commits/bug-tracking/whatever system more user-friendly also for non-hacker
users, I'd adhere to the 50/72 "standard" for commit messages, which seems
to be quite extended: http://chris.beams.io/posts/git-commit/#seven-rules

(50 chars for the commit summary, 72 chars line wrapping)

-1 - imo 50 chars too often makes the commit summary too unspecific,
requiring to read much more.

I'm not strong advocate of 50 chars anyway, but if people are 
getting used to this, and probably also tools, I'd try to stick to it. 
And I believe you should be able to describe a commit in 50 chars. But 
we shouldn't of course deviate and start yet another thread on this, so 
it's all up to you :)


Álvaro


--
Álvaro Hernández Tortosa


---
8Kdata



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] bugs and bug tracking

[Andrew Dunstan]

On 10/13/2015 10:21 AM, Álvaro Hernández Tortosa wrote:


On 13/10/15 04:40, Tom Lane wrote:
I'm with Robert on the idea that commit log entries need to be 
limited-width. I personally format them to 75 characters, so that 
git_changelog's output is less than 80 characters. regards, tom lane 


Little bit off-topic, but if precisely if we're trying to make the 
commits/bug-tracking/whatever system more user-friendly also for 
non-hacker users, I'd adhere to the 50/72 "standard" for commit 
messages, which seems to be quite extended: 
http://chris.beams.io/posts/git-commit/#seven-rules


(50 chars for the commit summary, 72 chars line wrapping)





50 is a pretty short for a commit summary. I've often found it quite 
difficult to write sane summaries inside that limit.


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] bugs and bug tracking

[Andres Freund]

On 2015-10-13 16:21:54 +0200, Álvaro Hernández Tortosa wrote:
> 
> On 13/10/15 04:40, Tom Lane wrote:
> >I'm with Robert on the idea that commit log entries need to be
> >limited-width. I personally format them to 75 characters, so that
> >git_changelog's output is less than 80 characters. regards, tom lane
> 
> Little bit off-topic, but if precisely if we're trying to make the
> commits/bug-tracking/whatever system more user-friendly also for non-hacker
> users, I'd adhere to the 50/72 "standard" for commit messages, which seems
> to be quite extended: http://chris.beams.io/posts/git-commit/#seven-rules
> 
> (50 chars for the commit summary, 72 chars line wrapping)

-1 - imo 50 chars too often makes the commit summary too unspecific,
requiring to read much more.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] bugs and bug tracking

[Álvaro Hernández Tortosa]

On 13/10/15 04:40, Tom Lane wrote:
I'm with Robert on the idea that commit log entries need to be 
limited-width. I personally format them to 75 characters, so that 
git_changelog's output is less than 80 characters. regards, tom lane 


Little bit off-topic, but if precisely if we're trying to make the 
commits/bug-tracking/whatever system more user-friendly also for 
non-hacker users, I'd adhere to the 50/72 "standard" for commit 
messages, which seems to be quite extended: 
http://chris.beams.io/posts/git-commit/#seven-rules


(50 chars for the commit summary, 72 chars line wrapping)

Álvaro


--
Álvaro Hernández Tortosa


---
8Kdata



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] bugs and bug tracking

[Magnus Hagander]

On Tue, Oct 13, 2015 at 4:07 PM, Andrew Dunstan  wrote:

>
>
> On 10/12/2015 07:36 PM, Robert Haas wrote:
>
>> On Tue, Oct 6, 2015 at 2:33 PM, Nathan Wagner 
>> wrote:
>>
>>> Two, I think any attempt to tell the developers and committers that they
>>> need to change their workflow to adapt to some system is bound to fail,
>>> so, I have asked, just what changed would you all be willing to actually
>>> *do*?  Tom Lane is pretty good at noting a bug number in his commit
>>> messages, for example.  Would he be willing to modify that slightly to
>>> make it easier to machine parse?  Would you be willing to add a bug
>>> number to your commit messages?  I'm not asking for guarantees.
>>> Actually I'm not really asking for anything, I'm just trying to figure
>>> out what the parameters of a solution might be.  If the answer to that
>>> is "no, I'm not willing to change anything at all", that's fine, it just
>>> colors what might be done and how much automation I or someone else
>>> might be able to write.
>>>
>> I'd personally be willing to put machine-parseable metadata into my
>> commit messages provided that:
>>
>> 1. I'm not the only one doing it - i.e. at least 3 or 4
>> moderately-frequent committers are all doing it consistently and all
>> using the same format.  If Tom buys into it, that's a big plus.
>>
>
> I'll do whatever everybody else agrees on.
>
>
>> 2. Adding the necessary metadata to a commit can be reasonably
>> expected to take no more than 2 minutes in typical cases (preferably
>> less).
>>
>> 3. Adding the metadata doesn't cause lines > 70 characters.  I am not
>> a fan of the "Discussion: Message-ID-Here" format which some
>> committers have begun using, sometimes with just the message ID and
>> sometimes with the full URL, because anything which causes horizontal
>> scrolling makes me sad.
>>
>>
> Perhaps we need some sort of tinyurl gadget?
>

We have one - postgr.es. Right now it's only really used by the API from
planet postgres, but we could certainly make that possible. But that adds
yet another step, doesn't it, making it take longer? And one more system
dependency.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [HACKERS] bugs and bug tracking

[Andrew Dunstan]

On 10/12/2015 07:36 PM, Robert Haas wrote:

On Tue, Oct 6, 2015 at 2:33 PM, Nathan Wagner  wrote:

Two, I think any attempt to tell the developers and committers that they
need to change their workflow to adapt to some system is bound to fail,
so, I have asked, just what changed would you all be willing to actually
*do*?  Tom Lane is pretty good at noting a bug number in his commit
messages, for example.  Would he be willing to modify that slightly to
make it easier to machine parse?  Would you be willing to add a bug
number to your commit messages?  I'm not asking for guarantees.
Actually I'm not really asking for anything, I'm just trying to figure
out what the parameters of a solution might be.  If the answer to that
is "no, I'm not willing to change anything at all", that's fine, it just
colors what might be done and how much automation I or someone else
might be able to write.

I'd personally be willing to put machine-parseable metadata into my
commit messages provided that:

1. I'm not the only one doing it - i.e. at least 3 or 4
moderately-frequent committers are all doing it consistently and all
using the same format.  If Tom buys into it, that's a big plus.


I'll do whatever everybody else agrees on.



2. Adding the necessary metadata to a commit can be reasonably
expected to take no more than 2 minutes in typical cases (preferably
less).

3. Adding the metadata doesn't cause lines > 70 characters.  I am not
a fan of the "Discussion: Message-ID-Here" format which some
committers have begun using, sometimes with just the message ID and
sometimes with the full URL, because anything which causes horizontal
scrolling makes me sad.



Perhaps we need some sort of tinyurl gadget?

BTW, a very quick look at my pg mailbox shows that message IDs are 
overwhelmingly 68 chars or less, including the surrounding <>. 68 seems 
to be a fixed width for gmail generated IDs - almost everybody else's 
message IDs are a lot smaller than 68.


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] Postgres service stops when I kill client backend on Windows

[Tom Lane]

Andrew Dunstan  writes:
> On 10/12/2015 04:35 PM, Tom Lane wrote:
>> BTW, it appears from this that Cygwin builds have been broken right along
>> in a different way: according to the code in sysv_shmem's
>> PGSharedMemoryReAttach, Cygwin does cause a re-attach to occur, which we
>> were not undoing for putatively-not-connected-to-shmem child processes.
>> That's a robustness problem because it breaks the postmaster's expectation
>> that it's safe to not reinitialize shmem after a crash of one of those
>> processes.  I believe this patch fixes that problem as well, though if
>> anyone can test it on Cygwin that wouldn't be a bad thing either.

> OK, I can test it. But it's not quite clear to me from your description 
> how I should test Cygwin.

The point is that I think that right now, the logging collector subprocess
remains connected to shared memory, which it should not (and won't, if my
patch is doing the right thing).  I do not know if there's an easy way to
inspect the process state to verify that on Windows.

If nothing else, you could put a bogus access to some shared-memory data
structure into the syslogger loop, and check that it succeeds now and
crashes after applying the patch ...

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Postgres service stops when I kill client backend on Windows

[Andrew Dunstan]

On 10/12/2015 04:35 PM, Tom Lane wrote:

I wrote:

This is kind of a mess :-(.  But it does look like what we want is
for SubPostmasterMain to do more than nothing when it chooses not to
reattach.  Probably that should include resetting UsedShmemSegAddr to
NULL, as well as closing the handle.

After poking around a bit more, I propose the attached patch.  I've
checked that this is happy with an EXEC_BACKEND Unix build, but I'm not
able to test it on Windows ... would somebody do that?

BTW, it appears from this that Cygwin builds have been broken right along
in a different way: according to the code in sysv_shmem's
PGSharedMemoryReAttach, Cygwin does cause a re-attach to occur, which we
were not undoing for putatively-not-connected-to-shmem child processes.
That's a robustness problem because it breaks the postmaster's expectation
that it's safe to not reinitialize shmem after a crash of one of those
processes.  I believe this patch fixes that problem as well, though if
anyone can test it on Cygwin that wouldn't be a bad thing either.





OK, I can test it. But it's not quite clear to me from your description 
how I should test Cygwin.



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

[HACKERS] Re: [COMMITTERS] pgsql: Cause TestLib.pm to define $windows_os in all branches.

[Andrew Dunstan]

On 10/12/2015 10:45 PM, Michael Paquier wrote:

On Tue, Oct 13, 2015 at 11:31 AM, Tom Lane wrote:

Hmm, well, why wasn't that back-patched?  We expect these tests to run
on Windows don't we?

The message related to this particular commit is here:
http://www.postgresql.org/message-id/55b90161.5090...@iki.fi
I recall that we discussed about back-patching more such things to
improve the buildfarm coverage but I guess it fell from other's radar.
Would you consider pushing any sync-up patch for 9.5 and 9.4 I could
send? At quick glance, I think that's basically a combination of
adb4950, 13d856e1, 690ed2b and ff85fc8. Andrew, Noah, Heikki, and
others feel free to object of course if you think that's an utterly
bad idea.



In general I think we can be a good deal more liberal about backpatching 
the testing regime than we are with production code, where we are always 
cautious, and the caution has paid big dividends in our reputation for 
stability.


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] pg_ctl/pg_rewind tests vs. slow AIX buildfarm members

[Tom Lane]

Michael Paquier  writes:
> On Tue, Oct 13, 2015 at 7:41 AM, Tom Lane  wrote:
>> I'm not sure if this will completely fix our problems with "pg_ctl start"
>> related buildfarm failures on very slow critters.  It does get rid of the
>> hard wired 5-second timeout, but the 60-second timeout could still be an
>> issue.  I think Noah was considering a patch to allow that number to be
>> raised.  I'd be in favor of letting pg_ctl accept a default timeout length
>> from an environment variable, and then the slower critters could be fixed
>> by adjusting their buildfarm configurations.

> Being able to pass that as a command-line parameter (master-only
> change) would be welcome as well IMO.

Huh?  The --timeout parameter has always been there.  I'm just expressing
an opinion that modifying all the makefiles and test scripts to pass
through a timeout setting would be too messy.

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] DIAGNOSTICS = SKIPPED_ROW_COUNT

[David G. Johnston]

>
>
>> > Using this attribute, we can have more control on parallel operations
>> like,
>>
>> > IF SKIPPED_ROW_COUNT =0 THEN
>> > <>
>> > ELSE
>> > <>
>> > END IF;
>>
>> Um ... so what?  This is not a use-case.
>>
>>
> In my view, "How one can be sure that, he obtained all the tuples with
> SKIP LOCKED". If the end user has this counter value, he may proceed with a
> different approach with partially locked tuples.
>
>
​Can you be more specific?  In most cases I can come up with (queues,
basically) where skipped locked is running the processing performing the
query is going to re-query the database on the next tick regardless of
whether they thought they say only some or all of the potential rows on the
prior pass.

David J.

[HACKERS] Re: Memory prefetching while sequentially fetching from SortTuple array, tuplestore

[Amir Rohan]

On 10/11/2015 03:20 AM, Peter Geoghegan wrote:
> On Thu, Sep 3, 2015 at 5:35 PM, David Rowley
>  wrote:
>> My test cases are:
> 
> Note that my text caching and unsigned integer comparison patches have
> moved the baseline down quite noticeably. I think that my mobile
> processor out-performs the Xeon you used for this, which seems a
> little odd even taken the change in baseline performance into account.
> 

To add a caveat not yet mentioned, the idea behind  prefetching is to
scarifice spare memory bandwidth for performance. That can be
a winning bet on a quiet box (the one we benchmark on), but can backfire
on production db when the extra memory pressure can degrade all running
queries.  Something to test for, or at least keep in mind.

>> set work_mem ='1GB';
>> create table t1 as select md5(random()::text) from
>> generate_series(1,1000);
>>
>> Times are in milliseconds. Median and average over 10 runs.
>>
>> Test 1
>

I am the reluctant owner of outmoded hardware. Namely a core2 from
around 2007 on plain spinning metal. My results (linux 64bit):

--
Test 1
--
set work_mem ='1GB';
select count(distinct md5) from t1;

== Master ==
42771.040 ms <- outlier?
41704.570 ms
41631.660 ms
41421.877 ms

== Patch ==
42469.911 ms  <- outlier?
41378.556 ms
41375.870 ms
41118.105 ms
41096.283 ms
41095.705 ms

--
Test 2
--
select sum(rn) from (select row_number() over (order by md5) rn from t1) a;

== Master ==
44186.775 ms
44137.154 ms
44111.271 ms
44061.896 ms
44109.122 ms

== Patch ==
44592.590 ms
44403.076 ms
44276.170 ms


very slight difference in an ambiguous direction, but also no perf
catastrophe.

> It's worth considering that for some (possibly legitimate) reason, the
> built-in function call is ignored by your compiler, since GCC has
> license to do that. You might try this on both master and patched
> builds:
> 
> ~/postgresql/src/backend/utils/sort$ gdb -batch -ex 'file tuplesort.o'
> -ex 'disassemble tuplesort_gettuple_common' > prefetch_disassembly.txt
> 
> ...
> 
> Notably, there is a prefetchnta instruction here.
> 

I have verified the prefetech is emitted in the disassembly.

An added benefit of owning outmoded hardware is that the MSR for this
generation is public and I can disable individual prefetcher units by
twiddeling a bit.

Disabling the "HW prefetch" or the "DCU prefetch" units on a pacthed
version gave results that look relatively unchanged, which seems promising.
Disabling them both at once on an unpatched version shows a slowdown of
5-6% in test1 (43347.181, 43898.705, 43399.428). That gives an
indication of maximum potential gains in this direction, for this box
at least.

Finally, I notice my results are 4x slower than everyone else's.
That can be very tough on a man's pride, let me tell you.

Amir



-- 
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] Getting sorted data from foreign server

[Ashutosh Bapat]

On Tue, Oct 13, 2015 at 1:48 PM, Jeevan Chalke <
jeevan.cha...@enterprisedb.com> wrote:

>
> On Thu, Oct 8, 2015 at 9:39 PM, Robert Haas  wrote:
>>
>>>
>>> In the interest of full disclosure, I asked Ashutosh to work on this
>>> patch and have discussed the design with him several times.  I believe
>>> that this is a good direction for PostgreSQL to be going.  It's
>>> trivially easy right now to write a query against an FDW that performs
>>> needlessly easy, because a join, or a sort, or an aggregate is
>>> performed on the local server rather than the remote one.   I, and
>>> EnterpriseDB, want that to get fixed.  However, we also want it to get
>>> fixed in the best possible way, and not to do anything unless there is
>>> consensus on it.  So, if anyone has opinions on this topic, please
>>> jump in.
>>>
>>
>
> Are we planning to push sorting on foreign server with parametrized
> foreign path?
>
> We might get a parametrized path when local table is small enough and
> foreign table is bigger, like, for this query
> SELECT big_ft.x FROM big_ft, small_lt WHERE big_ft.x = small_lt.y;
> we might end up getting parametrized foreign path with remote query
> like:
> SELECT big_ft.x FROM big_ft WHERE big_ft.x = $1;
>
> And with this, if we have an ORDER BY clause like "ORDER BY big_ft.x"
> we will get remote query like:
> SELECT big_ft.x FROM big_ft WHERE big_ft.x = $1 ORDER BY big_ft.x;
>
> Is this possible???
>
> If yes, then don't we need to sort again on local server?
>
> Assume each row on local server matches three rows from foreign table,
> then for each $1, we will have 3 rows returned from the foreign server,
> of-course sorted. But then all these fetched rows in batch of 3, needs
> to be re-sorted on local server, isn't it?
> If yes, this will be much more costly than fetching unsorted rows and
> sorting then locally only once.
>
> Or am I missing something here?
>
>
Thanks a lot for the catch. Per add_path() prologue
368   * ... First, we treat all parameterized paths
 369  *as having NIL pathkeys, so that they cannot win comparisons on
the
 370  *basis of sort order.

So, anyway those paths were getting eliminated by add_path().

I will take care of this one in the next version of patch.


> --
> Jeevan B Chalke
> Principal Software Engineer, Product Development
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
>
>


-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

[HACKERS] Eclipse Help

[Praveen M]

Hi All,

I was able to follow the debugging of the child process using this link,
https://wiki.postgresql.org/wiki/Working_with_Eclipse

As per the notes , I was able to set breakpoints and everything seem to be
working (hopefully). However I am not able to see the debug messages in the
eclipse console (for the attached process) . Please help

When I check on the console in eclipse , this is the last message I see.

0x773fad48 in poll () from /lib/x86_64-linux-gnu/libc.so.6

I added a 2 lines in pl_exec.c and kept breakpoints for these lines. The
breakpoints work fine but I am not able to see the console log.
I was able to use the log message "ereport(LOG, (errmsg("test here
started")));" in autovaccum.c line 414 and see the message in the console.
But this message is from the main process. I am having trouble seeing the
console log only for the attached process.

pl_exec.c :

Line 310 :
 ereport(LOG,
(errmsg("test here started")));

Line 311 :
elog(ERROR,"test here");

Thanks
Praveen

Re: [HACKERS] Dangling Client Backend Process

[Rajeev rastogi]

On 12th October 2015 20:45, Rajeev Rastogi Wrote:

>>> I observed one strange behavior today that if postmaster process gets 
>>> crashed/killed, then it kill all background processes but not the client 
>>> backend process.

>> This is a known behaviour and there was some discussion on this
>> topic [1] previously as well.

> Now as it is confirmed to be valid issue, I will spend some time on this to 
> find if there is something more appropriate solution.

I checked the latest code and found Heikki has already added code for 
secure_read using the latch mechanism (using WaitLatchOrSocket). It currently 
waits for two events i.e. WL_LATCH_SET and WL_SOCKET_READABLE.
Commit id: 80788a431e9bff06314a054109fdea66ac538199

If we add the event WL_POSTMASTER_DEATH also, client backend process handling 
will become same as other backend process. So postmaster death can be detected 
in the same way.

But I am not sure if WL_POSTMASTER_DEATH event was not added intentionally for 
some reason. Please confirm.
Also is it OK to add this even handling in generic path of Libpq?

Please let me know if I am missing something?

Thanks and Regards,
Kumar Rajeev Rastogi

Re: [HACKERS] Getting sorted data from foreign server

[Jeevan Chalke]

> On Thu, Oct 8, 2015 at 9:39 PM, Robert Haas  wrote:
>
>>
>> In the interest of full disclosure, I asked Ashutosh to work on this
>> patch and have discussed the design with him several times.  I believe
>> that this is a good direction for PostgreSQL to be going.  It's
>> trivially easy right now to write a query against an FDW that performs
>> needlessly easy, because a join, or a sort, or an aggregate is
>> performed on the local server rather than the remote one.   I, and
>> EnterpriseDB, want that to get fixed.  However, we also want it to get
>> fixed in the best possible way, and not to do anything unless there is
>> consensus on it.  So, if anyone has opinions on this topic, please
>> jump in.
>>
>

Are we planning to push sorting on foreign server with parametrized
foreign path?

We might get a parametrized path when local table is small enough and
foreign table is bigger, like, for this query
SELECT big_ft.x FROM big_ft, small_lt WHERE big_ft.x = small_lt.y;
we might end up getting parametrized foreign path with remote query
like:
SELECT big_ft.x FROM big_ft WHERE big_ft.x = $1;

And with this, if we have an ORDER BY clause like "ORDER BY big_ft.x"
we will get remote query like:
SELECT big_ft.x FROM big_ft WHERE big_ft.x = $1 ORDER BY big_ft.x;

Is this possible???

If yes, then don't we need to sort again on local server?

Assume each row on local server matches three rows from foreign table,
then for each $1, we will have 3 rows returned from the foreign server,
of-course sorted. But then all these fetched rows in batch of 3, needs
to be re-sorted on local server, isn't it?
If yes, this will be much more costly than fetching unsorted rows and
sorting then locally only once.

Or am I missing something here?

-- 
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Re: [HACKERS] Postgres service stops when I kill client backend on Windows

[Michael Paquier]

On Tue, Oct 13, 2015 at 5:35 AM, Tom Lane  wrote:
> I wrote:
>> This is kind of a mess :-(.  But it does look like what we want is
>> for SubPostmasterMain to do more than nothing when it chooses not to
>> reattach.  Probably that should include resetting UsedShmemSegAddr to
>> NULL, as well as closing the handle.
>
> After poking around a bit more, I propose the attached patch.  I've
> checked that this is happy with an EXEC_BACKEND Unix build, but I'm not
> able to test it on Windows ... would somebody do that?
>
> BTW, it appears from this that Cygwin builds have been broken right along
> in a different way: according to the code in sysv_shmem's
> PGSharedMemoryReAttach, Cygwin does cause a re-attach to occur, which we
> were not undoing for putatively-not-connected-to-shmem child processes.
> That's a robustness problem because it breaks the postmaster's expectation
> that it's safe to not reinitialize shmem after a crash of one of those
> processes.  I believe this patch fixes that problem as well, though if
> anyone can test it on Cygwin that wouldn't be a bad thing either.

I don't have a Cygwin environment at hand. That's unfortunate..

Looking at the patch, clearly +1 for the additional routine in both
win32_shmem.c and sysv_shmem.c to clean up the shmem state at backend
level. I have played as well with the patch on Windows and it behaves
as expected: without the patch a process killed with taskkill /f stops
straight the server even if restart_on_crash is on. With the patch the
server restarts correctly.

(Sorry, I should have mentioned that my last patch was untested and
*surely broken*, that was the result of a 3-min guess to make the
cleanup more generic for child processes that do not need to be
attached to shmem).
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] Parallel Aggregate

[Haribabu Kommi]

On Tue, Oct 13, 2015 at 5:53 PM, David Rowley
 wrote:
> On 13 October 2015 at 17:09, Haribabu Kommi 
> wrote:
>>
>> On Tue, Oct 13, 2015 at 12:14 PM, Robert Haas 
>> wrote:
>> > Also, I think the path for parallel aggregation should probably be
>> > something like FinalizeAgg -> Gather -> PartialAgg -> some partial
>> > path here.  I'm not clear whether that is what you are thinking or
>> > not.
>>
>> No. I am thinking of the following way.
>> Gather->partialagg->some partial path
>>
>> I want the Gather node to merge the results coming from all workers,
>> otherwise
>> it may be difficult to merge at parent of gather node. Because in case
>> the partial
>> group aggregate is under the Gather node, if any of two workers are
>> returning
>> same group key data, we need to compare them and combine it to make it a
>> single group. If we are at Gather node, it is possible that we can
>> wait till we get
>> slots from all workers. Once all workers returns the slots we can compare
>> and merge the necessary slots and return the result. Am I missing
>> something?
>
>
> My assumption is the same as Robert's here.
> Unless I've misunderstood, it sounds like you're proposing to add logic into
> the Gather node to handle final aggregation? That sounds like a modularity
> violation of the whole node concept.
>
> The handling of the final aggregate stage is not all that different from the
> initial aggregate stage. The primary difference is just that your calling
> the combine function instead of the transition function, and the values

Yes, you are correct, till now i am thinking of using transition types as the
approach, because of that reason only I proposed it as Gather node to handle
the finalize aggregation.

> being aggregated are aggregates states rather than the type of the values
> which were initially aggregated. The handling of GROUP BY is all the same,
> yet you only apply the HAVING clause during final aggregation. This is why I
> ended up implementing this in nodeAgg.c instead of inventing some new node
> type that's mostly a copy and paste of nodeAgg.c [1]

After going through your Partial Aggregation / GROUP BY before JOIN patch,
Following is my understanding of parallel aggregate.

Finalize [hash] aggregate
-> Gather
  -> Partial [hash] aggregate

The data that comes from the Gather node contains the group key and
grouping results.
Based on these we can generate another hash table in case of hash aggregate at
finalize aggregate and return the final results. This approach works
for both plain and
hash aggregates.

For group aggregate support of parallel aggregate, the plan should be
as follows.

Finalize Group aggregate
->sort
-> Gather
  -> Partial group aggregate
   ->sort

The data that comes from Gather node needs to be sorted again based on
the grouping key,
merge the data and generates the final grouping result.

With this approach, we no need to change anything in Gather node. Is
my understanding correct?

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] Getting sorted data from foreign server

[Ashutosh Bapat]

On Thu, Oct 8, 2015 at 9:39 PM, Robert Haas  wrote:

> On Tue, Oct 6, 2015 at 6:46 AM, Ashutosh Bapat
>  wrote:
> > standard_qp_callback() sets root->query_pathkeys to pathkeys on which the
> > result of query_planner are expected be sorted upon (see the function for
> > more details). The patch checks if any prefix of these pathkeys can be
> > entirely evaluated using the foreign relation and at the foreign server
> > under consideration. If yes, it gets estimates of costs involved and adds
> > paths with those pathkeys. There can be multiple pathkeyless paths added
> for
> > a given base relation. For every such path one path with pathkeys is
> added.
> > If there is an index matching on the foreign server, getting the data
> sorted
> > from foreign server improves execution time as seen from the results. The
> > patch adds this functionality entirely in postgres_fdw.
>
> In the interest of full disclosure, I asked Ashutosh to work on this
> patch and have discussed the design with him several times.  I believe
> that this is a good direction for PostgreSQL to be going.  It's
> trivially easy right now to write a query against an FDW that performs
> needlessly easy, because a join, or a sort, or an aggregate is
> performed on the local server rather than the remote one.   I, and
> EnterpriseDB, want that to get fixed.  However, we also want it to get
> fixed in the best possible way, and not to do anything unless there is
> consensus on it.  So, if anyone has opinions on this topic, please
> jump in.
>
> That having been said, here are some review comments on this patch:
>
> - You consider pushing down ORDER BY if any prefix of the query
> pathkeys satisfy is_foreign_expr(), but that doesn't seem right to me.
> If the user says SELECT * FROM remotetab ORDER BY a, unsafe(a),
> ordering the result set by a doesn't help us much.  We've talked a few
> times about an incremental sort capability that would take a stream of
> tuples already ordered by one or more columns and sort each group by
> additional columns, but I don't think we have that currently.  Without
> that capability, I don't think there's much benefit in sorting by a
> prefix of the pathkeys.  I suspect that if we can't get them all, it's
> not worth doing.
>

I somehow thought, we are using output, which is ordered by prefix of
pathkeys in Sort nodes. But as you rightly pointed out that's not the case.
Only complete pathkeys are useful.


>
> - Right now, you have this code below the point where we bail out if
> use_remote_estimate is not set.  If we keep it like that, the comment
> needs updating.  But I suggest that we consider an ordered path even
> if we are not using remote estimates.  Estimate the sorted path to
> cost somewhat more than the unsorted path, so that we only choose that
> path if the sort actually benefits us.  I don't know exactly how to
> come up with a principled estimate given that we don't actually know
> whether the remote side will need an extra sort or not, but maybe a
> dumb estimate is still better than not trying a sorted path.
>

I like that idea, although there are two questions
1. How can we estimate cost of getting the data sorted? If there is an
appropriate index on foreign server we can get the data sorted at no extra
cost. If there isn't the cost of sorting is proportionate to NlogN where N
is the size of data. It seems unreasonable to arrive at the cost of sorting
by multiplying with some constant multiplier. Also, the constant multiplier
to the NlogN estimate depends heavily upon the properties of foreign server
e.g. size of memory available for sorting, disk and CPU speed etc. The last
two might have got factored into fdw_tuple_cost and fdw_startup_cost, so
that's probably taken care of. If the estimate we come up turns out to be
too pessimistic, we will not get sorted data even if that's the right thing
to do. If too optimistic, we will incur heavy cost at the time of
execution. Setting the cost estimate to some constant factor of NlogN would
be too pessimistic if there is an appropriate index on foreign server.
Otherway round if there isn't an appropriate index on foreign server.

Even if we leave these arguments aside for a while, the question remains as
to what should be the constant factor 10% or 20% or 50% or 100% or
something else on top of the estimate for simple foreign table scan
estimates (or NlogN of that)? I am unable to justify any of these factors
myself. What do you say?

Given that we save on the local resources required for sorting if we get
the data sorted from the foreign server, it might be better to always get
it sorted from the foreign server, but our costing model doesn't account
for that benefit today.

We might be able to do a good job there if we know more things about the
foreign server/table e.g. indexes on the foreign table, memory available
for sorting etc. that leads to your next comment.


>
> - In the long run, we should probably either add some configuration so
> that 

Re: [HACKERS] Parallel Aggregate

[Simon Riggs]

On 13 October 2015 at 02:14, Robert Haas  wrote:

> On Sun, Oct 11, 2015 at 10:07 PM, Haribabu Kommi
>  wrote:
> > Parallel aggregate is the feature doing the aggregation job parallel
> > with the help of Gather and
> > partial seq scan nodes. The following is the basic overview of the
> > parallel aggregate changes.
> >
> > Decision phase:
> >
> > Based on the following conditions, the parallel aggregate plan is
> generated.
> >
> > - check whether the below plan node is Gather + partial seq scan only.
> >
> > This is because to check whether the plan nodes that are present are
> > aware of parallelism or not?
>
> This is really not the right way of doing this.  We should do
> something more general.  Most likely, parallel aggregate should wait
> for Tom's work refactoring the upper planner to use paths.  But either
> way, it's not a good idea to limit ourselves to parallel aggregation
> only in the case where there is exactly one base table.
>

What we discussed at PgCon was this rough flow of work

* Pathify upper Planner (Tom) WIP
* Aggregation push down (David) Prototype
* Parallel Aggregates

Parallel infrastructure is also required for aggregation, though that
dependency looks further ahead than the above at present.

Parallel aggregates do look like they can make it into 9.6, but there's not
much slack left in the critical path.


> One of the things I want to do pretty early on, perhaps in time for
> 9.6, is create a general notion of partial paths.  A Partial Seq Scan
> node creates a partial path.  A Gather node turns a partial path into
> a complete path.  A join between a partial path and a complete path
> creates a new partial path.  This concept lets us consider,
> essentially, pushing joins below Gather nodes.  That's quite powerful
> and could make Partial Seq Scan applicable to a much broader variety
> of use cases.  If there are worthwhile partial paths for the final
> joinrel, and aggregation of that joinrel is needed, we can consider
> parallel aggregation using that partial path as an alternative to
> sticking a Gather node on there and then aggregating.


Some form of partial plan makes sense. A better word might be "strand".


> > - Set the single_copy mode as true, in case if the below node of
> > Gather is a parallel aggregate.
>
> That sounds wrong.  Single-copy mode is for when we need to be certain
> of running exactly one copy of the plan.  If you're trying to have
> several workers aggregate in parallel, that's exactly what you don't
> want.
>
> Also, I think the path for parallel aggregation should probably be
> something like FinalizeAgg -> Gather -> PartialAgg -> some partial
> path here.  I'm not clear whether that is what you are thinking or
> not.
>

Yes, but not sure of names.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services