[HACKERS] 9.6beta subplan target list bug

[Jeff Janes]

Found a bug:

create table foo as select x, floor(random()*100)::int as y,
floor(random()*100)::int as z from generate_series(1,100) f(x);

explain select count(y) filter (where y<0.01) from foo where x < 1;

ERROR:  variable not found in subplan target list
STATEMENT:  select count(y) filter (where y<0.01) from foo where x < 1;

Bisects to:

commit 59a3795c2589a0e6dfe4d9a886de9423b3f8b057
Author: Tom Lane 
Date:   Sun Jun 26 12:08:12 2016 -0400

Simplify planner's final setup of Aggrefs for partial aggregation.

Cheers,

Jeff


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

Re: [HACKERS] 9.6beta subplan target list bug

[Tom Lane]

Jeff Janes  writes:
> Found a bug:

Fixed, thanks.

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] Reviewing freeze map code

[Amit Kapila]

On Mon, Jul 18, 2016 at 2:03 PM, Andres Freund  wrote:
> On 2016-07-18 10:02:52 +0530, Amit Kapila wrote:
>> >
>>
>> Consider the below scenario.
>>
>> Vacuum
>> a. acquires a cleanup lock for page - 10
>> b. busy in checking visibility of tuples
>> --assume, here it takes some time and in the meantime Session-1
>> performs step (a) and (b) and start waiting in step- (c)
>> c. marks the page as all-visible (PageSetAllVisible)
>> d. unlockandrelease the buffer
>>
>> Session-1
>> a. In heap_lock_tuple(), readbuffer for page-10
>> b. check PageIsAllVisible(), found page is not all-visible, so didn't
>> acquire the visbilitymap_pin
>> c. LockBuffer in ExlusiveMode  - here it will wait for vacuum to
>> release the lock
>> d. Got the lock, but now the page is marked as all-visible, so ideally
>> need to recheck the page and acquire the visibilitymap_pin
>
> So, I've tried pretty hard to reproduce that. While the theory above is
> sound, I believe the relevant code-path is essentially dead for SQL
> callable code, because we'll always hold a buffer pin before even
> entering heap_update/heap_lock_tuple.
>

It is possible that we don't hold any buffer pin before entering
heap_update() and or heap_lock_tuple().  For heap_update(), it is
possible when it enters via simple_heap_update() path.  For
heap_lock_tuple(), it is possible for ON CONFLICT DO Update statement
and may be others as well.  Let me also try to explain with a test for
both the cases, if above is not clear enough.

Case-1 for heap_update()
---
Session-1
Create table t1(c1 int);
Alter table t1 alter column c1 set default 10;  --via debugger stop at
StoreAttrDefault()/heap_update, while you are in heap_update(), note
down the block number

Session-2
vacuum (DISABLE_PAGE_SKIPPING) pg_attribute; -- In lazy_scan_heap(),
stop at line (if (all_visible && !all_visible_according_to_vm))) for
block number noted in Session-1.

Session-1
In debugger, proceed and let it wait at lockbuffer, note that it will
not pin the visibility map.

Session-2
Set the visibility flag and complete the operation.

Session-1
You will notice that it will attempt to unlock the buffer, pin the
visibility map, lock the buffer again.


Case-2 for heap_lock_tuple()

Session-1
Create table i_conflict(c1 int, c2 char(100));
Create unique index idx_u on i_conflict(c1);

Insert into i_conflict values(1,'aaa');
Insert into i_conflict values(1,'aaa') On Conflict (c1) Do Update Set
c2='bbb';  -- via debugger, stop at line 385 in nodeModifyTable.c (In
ExecInsert(), at
if (onconflict == ONCONFLICT_UPDATE)).

Session-2
-
vacuum (DISABLE_PAGE_SKIPPING) i_conflict --stop before setting the
all-visible flag

Session-1
--
In debugger, proceed and let it wait at lockbuffer, note that it will
not pin the visibility map.

Session-2
---
Set the visibility flag and complete the operation.

Session-1
--
PANIC:  wrong buffer passed to visibilitymap_clear  --this is problematic.


Attached patch fixes the problem for me.  Note, I have not tried to
reproduce the problem for heap_lock_updated_tuple_rec(), but I think
if you are convinced with above cases, then we should have a similar
check in it as well.


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


pin_vm_lock_tuple-v1.patch
Description: Binary data

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

Re: [HACKERS] One question about transformation ANY Sublinks into joins

[Armor]

After we pull up this sublink as semi join , when make join rel for semi join, 
the optimizer will take hash join method into account if a unique path can be 
created with the RHS, for detail please check make_join_rel in 
src/backend/optimizer/path/joinrels.c. 
For this case, the cost of  hash join is cheaper than semi join, so you can see 
the planner chose the hash join rather than semi join.


--
Jerry Yu
https://github.com/scarbrofair


 




-- Original --
From:  "Robert Haas";;
Date:  Fri, Jul 22, 2016 00:23 AM
To:  "Armor"; 
Cc:  "pgsql-hackers"; 
Subject:  Re: [HACKERS] One question about transformation ANY Sublinks into 
joins



On Sun, Jul 17, 2016 at 5:33 AM, Armor  wrote:
> Hi
> I run a simple SQL with latest PG??
> postgres=# explain select * from t1 where id1 in (select id2 from t2 where
> c1=c2);
>  QUERY PLAN
> 
>  Seq Scan on t1  (cost=0.00..43291.83 rows=1130 width=8)
>Filter: (SubPlan 1)
>SubPlan 1
>  ->  Seq Scan on t2  (cost=0.00..38.25 rows=11 width=4)
>Filter: (t1.c1 = c2)
> (5 rows)
>
> and the table schema are as following:
>
> postgres=# \d t1
>   Table "public.t1"
>  Column |  Type   | Modifiers
> +-+---
>  id1| integer |
>  c1 | integer |
>
> postgres=# \d t2
>   Table "public.t2"
>  Column |  Type   | Modifiers
> +-+---
>  id2| integer |
>  c2 | integer |
>
>  I find PG decide not to pull up this sublink because the whereClauses
> in this sublink refer to the Vars of parent query, for detail please check
> the function named convert_ANY_sublink_to_join in
> src/backend/optimizer/plan/subselect.c.
>  However, for such simple sublink which has no agg, no window function,
> no limit, may be we can carefully pull up the predicates in whereCluase
> which refers to the Vars of parent query, then pull up this sublink and
> produce a query plan as following:
>
> postgres=# explain select * from t1 where id1 in (select id2 from t2 where
> c1=c2);
>QUERY PLAN
> 
>  Hash Join  (cost=49.55..99.23 rows=565 width=8)
>Hash Cond: ((t1.id1 = t2.id2) AND (t1.c1 = t2.c2))
>->  Seq Scan on t1  (cost=0.00..32.60 rows=2260 width=8)
>->  Hash  (cost=46.16..46.16 rows=226 width=8)
>  ->  HashAggregate  (cost=43.90..46.16 rows=226 width=8)
>Group Key: t2.id2, t2.c2
>->  Seq Scan on t2  (cost=0.00..32.60 rows=2260 width=8)

It would need to be a Hash Semi Join rather than a Hash Join, wouldn't it?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [HACKERS] bug in citext's upgrade script for parallel aggregates

[David Rowley]

On 14 July 2016 at 02:00, Andreas Karlsson  wrote:
> On 07/09/2016 05:42 AM, David Rowley wrote:
>> On 30 June 2016 at 03:49, Robert Haas  wrote:
>>> Since we've already released beta2, I think we need to do a whole new
>>> extension version.  We treated beta1 as a sufficiently-significant
>>> event to mandate a version bump, so we should do the same here.
>>
>>
>> Ok, good point. Patch attached.
>
>
> Thanks!
>
> I tested the patch and it looks good.

Thanks for looking.

I've added this to the open items list so that it does not get forgotten about.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


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

Re: [HACKERS] AdvanceXLInsertBuffer vs. WAL segment compressibility

[Amit Kapila]

On Sat, Jul 23, 2016 at 3:32 AM, Chapman Flack  wrote:
>
> Would it then be possible to go back to the old behavior (or make
> it selectable) of not overwriting the full 16 MB every time?
>

I don't see going back to old behaviour is an improvement, because as
as you pointed out above that it helps to improve the compression
ratio of WAL files for tools like gzip and it doesn't seem advisable
to loose that capability.  I think providing an option to select that
behaviour could be one choice, but use case seems narrow to me
considering there are tools (pglesslog) to clear the tail.  Do you
find any problems with that tool which makes you think that it is not
reliable?

> Or did the 9.4 changes also change enough other logic that stuff
> would now break if that isn't done?
>

The changes related to the same seems to be isolated (mainly in
CopyXLogRecordToWAL()) and doesn't look to impact other parts of
system, although some more analysis is needed to confirm the same, but
I think the point to make it optional doesn't seem convincing to me.

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


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

Re: [HACKERS] [PROPOSAL] Temporal query processing with range types

[Anton Dignös]

On Sat, Jul 23, 2016 at 12:01 AM, David Fetter  wrote:
> On Fri, Jul 22, 2016 at 01:15:17PM +0200, Anton Dignös wrote:
>> Hi hackers,
>>
>> we are a group of researches that work on temporal databases.  Our
>> main focus is the processing of data with time intervals, such as
>> the range types in PostgreSQL.
>
> Thanks for your hard work so far!
>
> [Explanation and examples elided]
>
> To what extent, if any, are you attempting to follow the SQL:2011
> standard?
>
> http://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf

The querying in the SQL:2011 standard is based on simple SQL range restrictions
and period predicates (OVERLAP, PRECEDES, FOR SYSTEM_TIME AS OF, etc) that
functionality-wise in PostgreSQL are already covered by the operators and
functions on range types.

Operations such as aggregation, outer joins, set-operations on ranges
(mentioned in
Section 2.5 "Future directions" in the above paper) are not yet part of the
standard. These are the operations that require the adjustment (or splitting) of
ranges.

Best,

Anton


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

[HACKERS] LWLocks in DSM memory

[Thomas Munro]

Hi,

As already noted[1], LWLocks don't currently work in DSM segments,
because they use dlist for the list of waiters.  Even though all of
the waiter nodes are in PGPROC and therefore have stable addresses,
the dlist code internally constructs a circular list including
pointers to a special sentinel node inside the dlist_head object, and
those pointers may be invalid in other backends.

One solution could be to provide a non-circular variant of the dlist
interface that uses NULL list termination.  I've attached a quick
sketch of something like that which seems to work correctly.  It is
only lightly tested so far and probably buggy, but shows the general
idea.

Any thoughts on this approach, or better ways to solve this problem?
How valuable is the branch-free property of the circular push and
delete operations?

[1] 
https://www.postgresql.org/message-id/ca+tgmobjia49ccj0zazbwavv7nkgyt+1zo5cwxkh9aahgn2...@mail.gmail.com

-- 
Thomas Munro
http://www.enterprisedb.com


lwlocks-in-dsm.patch
Description: Binary data

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

[HACKERS] Problem in PostgresSQL Configuration with YII 1 & Wordpress

[Jyoti Sharma]

Hi Team,

Currently we have a project running with MySQL with YII & Wordpress, Now i
want to change the Database from MYSQL to PostgreSQL.

Please put your inline comments below.
*[JS:] *Is there any way we can convert MYSQL queries to PostreSQL Queries?

*Your Comment : ?*


*[JS:] *Is there any way we can insert serialize data with junked character
in PostgreSQL?


*Your Comment : ?*


*Can we have a meeting with any of your technical spokesperson.*

*Our Frameworks & software version we are using.*
1. YII 1.1.1.4
2. PostgreSQL 9.3
3. Wordpress
4. MYSQL
5. OS : Ubuntu


--
Thanks
Jyoti Sharma

-- 
 



*This e-mail and all attachments are intended solely for use by the 
intended recipient and may contain confidential / proprietary information 
of KiwiTech, LLC, subject to important disclaimers and conditions including 
restrictions on the use, disclosure, transfer or export of such 
information.* *If you have received this message in error or are not the 
named recipient(s), please immediately notify the sender at the telephone 
number stated above or by reply e-mail and delete this e-mail from your 
computer*