Shmem queue is not flushed if receiver is not yet attached

problem for me. On another note, `shm_mq.h` declares `shm_mq_flush()`, but I don't see it being implemented. Maybe just a leftover from the previous work? Though it seems useful to implement that API. Thanks, Pavan -- Pavan Deolasee EnterpriseDB: https://www.enterprisedb..com 0001-Flush-the-

Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits

gets into the tree. Thanks, Pavan -- Pavan Deolasee EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: some pointless HeapTupleHeaderIndicatesMovedPartitions calls

Hi Alvaro, On Tue, Sep 29, 2020 at 10:14 PM Alvaro Herrera wrote: > Hello > > Pavan Deolasee recently noted that a few of the > HeapTupleHeaderIndicatesMovedPartitions calls added by commit > 5db6df0c0117 are useless, since they are done after comparing t_self > with t_cti

Re: [HACKERS] MERGE SQL Statement for PG11

On Fri, Mar 23, 2018 at 10:00 AM, Amit Langote < langote_amit...@lab.ntt.co.jp> wrote: > On 2018/03/23 3:42, Pavan Deolasee wrote: > > A slightly improved version attached. Apart from doc cleanup based on > > earlier feedback, fixed one assertion failure based on Rahila&#

Re: Changing WAL Header to reduce contention during ReserveXLogInsertLocation()

Master: == #clients #tps 1 24.128004 2 12.326135 4 8.334143 8 16.035699 16 8.502794 So that's pretty good improvement across the spectrum. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services pg_reduce_wal_contention_v2.patch Description: Binary data

Re: Faster inserts with mostly-monotonically increasing values

a few regression lines that should be sure > to exercise the code path and leave it at that. > > I changed the regression tests to include a few more scenarios, basically using multi-column indexes in different ways and they querying rows by ordering rows in different ways. I did not take aw

Re: [HACKERS] MERGE SQL Statement for PG11

is because it increases my overall > confidence in the design. If it was genuinely hard to add WITH clause > support, then that would probably tell us something about the overall > design that likely creates problems elsewhere. It's easy to say that > it isn't worth hold

Re: [HACKERS] MERGE SQL Statement for PG11

On Fri, Mar 23, 2018 at 4:43 AM, Peter Geoghegan wrote: > On Thu, Mar 22, 2018 at 11:42 AM, Pavan Deolasee > wrote: > > A slightly improved version attached. > > You still need to remove this change: > > > diff --git a/src/include/miscadmin.h b/src/include/misca

Re: [HACKERS] MERGE SQL Statement for PG11

hat the CTE itself is assigned plan_id 1 and the SubPlan then gets plan_id 2. I can investigate further, but given that we see a similar behaviour with regular UPDATE, I don't think it's worth. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Changing WAL Header to reduce contention during ReserveXLogInsertLocation()

but it would > at least alleviate the spinlock. This gives us the same level of guarantee that xl_prev used to offer, yet help us use atomic operations. I'll be happy if we can look at that particular change and see if there are any holes there. Thanks, Pavan -- Pavan Deolasee

Re: PATCH: Exclude unlogged tables from base backups

; and thus fails the test. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Faster inserts with mostly-monotonically increasing values

On Sun, Mar 25, 2018 at 6:00 AM, Andrew Dunstan < andrew.duns...@2ndquadrant.com> wrote: > On Fri, Mar 23, 2018 at 8:27 PM, Pavan Deolasee > wrote: > >> > >> > >> I would probably just have a few regression lines that should be sure > >>

Re: PATCH: Exclude unlogged tables from base backups

On Mon, Mar 26, 2018 at 1:03 PM, Pavan Deolasee wrote: > On Fri, Mar 23, 2018 at 9:51 PM, David Steele wrote: > >> On 3/23/18 12:14 PM, Teodor Sigaev wrote: >> > >> > Thank you, pushed >> >> > Is it just me or the newly added test in 010_pg_basebacku

Re: PATCH: Exclude unlogged tables from base backups

On Mon, Mar 26, 2018 at 5:16 PM, Masahiko Sawada wrote: > On Mon, Mar 26, 2018 at 4:52 PM, Pavan Deolasee > > >> > > > > This one-liner patch fixes it for me. > > > > Isn't this issue already fixed by commit > d0c0c894533f906b13b79813f02b2982ac6

Re: [HACKERS] MERGE SQL Statement for PG11

On Tue, Mar 27, 2018 at 1:54 PM, Simon Riggs wrote: > On 26 March 2018 at 17:06, Simon Riggs wrote: > > On 26 March 2018 at 15:39, Pavan Deolasee > wrote: > > > > > That's all I can see so far. > > * change comment “once to” to “once” in src/include/nodes/

Re: [HACKERS] A design for amcheck heapam verification

olding OldestXmin back. Is there anything we can do to lessen that burden like telling other backends to ignore our xmin while computing OldestXmin (like vacuum does)? Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Changing WAL Header to reduce contention during ReserveXLogInsertLocation()

ase, we might read a WAL segment extra before we find the checkpoint record. That's not ideal but not too bad given that only pg_rewind needs this and that too only once. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Changing WAL Header to reduce contention during ReserveXLogInsertLocation()

what we do today. The idea is to just use a different mechanism to find the prior checkpoint. But we should surely find the latest prior checkpoint. In the rare scenario that Tom showed, we should just throw an error and fix the patch if it's not doing that already. Thanks, Pavan -- Pavan Deol

Re: [HACKERS] MERGE SQL Statement for PG11

On Wed, Mar 28, 2018 at 8:28 AM, Peter Geoghegan wrote: > On Tue, Mar 27, 2018 at 2:28 AM, Pavan Deolasee > wrote: > > (Version 26) > > I have some feedback on this version: > > * ExecMergeMatched() needs to determine tuple lock mode for > EvalPlanQual() in a way tha

Re: [HACKERS] A design for amcheck heapam verification

On Wed, Mar 28, 2018 at 2:48 AM, Peter Geoghegan wrote: > On Tue, Mar 27, 2018 at 6:48 AM, Pavan Deolasee > wrote: > > + * When index-to-heap verification is requested, a Bloom filter is used > to > > + * fingerprint all tuples in the target index, as the index is > tra

Re: [HACKERS] A design for amcheck heapam verification

e IndexBuildHeapScan() does that internally. I stumbled upon that while looking for any potential leaks. I know at least one other caller of IndexBuildHeapScan() doesn't bother to say anything either, but it's helpful. FWIW I also looked at the 0001 patch and it looks fine to me. Tha

Re: [HACKERS] MERGE SQL Statement for PG11

TCHED processing, thus inserting it into the table again. I am not saying there is no scope for improvement. But we need to be careful about what can be pushed down to the join and what must be applied after the join. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] MERGE SQL Statement for PG11

uff which was necessitated when we added support for partitioned table. We discussed that at some length, with your inputs and agreed that it's not necessarily a bad thing and probably the only way to deal with partitioned tables. Personally, I don't see why an internal join is bad. That&

Re: Missing parse_merge.h?

to add new files in that commit. I am trying to get in touch with him so that he can add the missing files and correct the situation. Sorry for the trouble. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: pgsql: New files for MERGE

f your review comments), but got side tracked by some high priority customer escalation. I shall respond soon. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: pgsql: New files for MERGE

On Thu, Apr 5, 2018 at 12:16 AM, Andres Freund wrote: > Hi, > > On 2018-04-05 00:02:06 +0530, Pavan Deolasee wrote: > > Apologies from my end. Simon checked with me regarding your referenced > > email. I was in the middle of responding to it (with a add-on patch to > take

Re: [HACKERS] MERGE SQL Statement for PG11

* Do basic expression transformation (same as a > ROW() > +* expr, but allow SetToDefault at top level) > + */ > + exprList = transformExpressionList(pstate, > +

Re: [HACKERS] Restrict concurrent update/delete with UPDATE of partition key

ort ON CONFLICT DO UPDATE to move a row to a different partition, but otherwise it works now. See 555ee77a9668e3f1b03307055b5027e13bf1a715. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Add support for printing/reading MergeAction nodes

more confident that we should have got the outfuncs.c support ok. debug_print_parse=on debug_print_rewritten=on debug_print_plan=on Also, I am now running tests with -DCOPY_PARSE_PLAN_TREES -DRAW_EXPRESSION_COVERAGE_TEST since the buildfarm had earlier uncovered some issues with those flags. No p

Re: [HACKERS] MERGE SQL Statement for PG11

pile that way. We should either rewrite that assertion or put it inside a #ifdef ASSERT_CHECKING block or simple remove that assertion because we already check for relkind in parse_merge.c. Will check. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] MERGE SQL Statement for PG11

n move ExecInsert/Update etc to a new file as I suggested, but still use the ModifyTable to run Merge. There are many things common between them. ModifyTable executes all DMLs and MERGE is just another DML which can run all three. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] MERGE SQL Statement for PG11

On Fri, Apr 6, 2018 at 1:30 AM, Andres Freund wrote: > Hi, > > On 2018-04-05 11:31:48 +0530, Pavan Deolasee wrote: > > > +/*- > > > > > > + * > > > + * nodeMerge.c > > >

Re: pgsql: New files for MERGE

th March: On Thu, Mar 29, 2018 at 3:20 PM, Simon Riggs wrote: > On 28 March 2018 at 12:00, Pavan Deolasee > wrote: > > > v27 attached, though review changes are in > > the add-on 0005 patch. > > This all looks good now, thanks for making all of those changes. >

Bugs in TOAST handling, OID assignment and redo recovery

gs exist there too. In fact, these bugs probably existed forever, though I did not check very old releases. Attached is a simple reproducer and a proposed fix to address both the bugs. We should consider backpatching it all supported releases. Thanks, Pavan -- Pavan Deolasee htt

Re: Bugs in TOAST handling, OID assignment and redo recovery

" tuples. > > Agreed. With nextXid, we advance ShmemVariableCache->nextXid if the value > in the online checkpoint record is greater than > ShmemVariableCache->nextXid. But we don't have such a wraparound-aware > concept of "greater than" for OIDs. I

Re: Faster inserts with mostly-monotonically increasing values

Yes, looks like an oversight :-( I will fix it along with the other changes that Peter requested. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Bugs in TOAST handling, OID assignment and redo recovery

On Tue, Apr 10, 2018 at 7:24 PM, Pavan Deolasee wrote: > Hi Heikki, > > On Tue, Apr 10, 2018 at 7:07 PM, Heikki Linnakangas > wrote: > >> >>> >> It would seem more straightforward to add a snapshot parameter to >> GetNewOidWithIndex(), so that th

Re: Bugs in TOAST handling, OID assignment and redo recovery

On Wed, Apr 11, 2018 at 8:20 PM, Tom Lane wrote: > Pavan Deolasee writes: > > Or may be we simply err on the side of caution and scan the toast table > > with SnapshotAny while looking for a duplicate? That might prevent us > from > > reusing an OID for a known-dead tup

Re: Bugs in TOAST handling, OID assignment and redo recovery

x27;t find a case that is broken. I even tried a few test cases with DDLs etc. But I think what you did is fine and more bullet proof. So +1 to that. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Bugs in TOAST handling, OID assignment and redo recovery

s a very specialised test case written after getting a full grasp on the bug. And it just tests the thing that I knew is broken based on code reading. Also, with OID duplicate issue fixed, hitting more bugs in this area is going to be even more difficult. Thanks, Pavan -- Pavan Deolasee

Re: Bugs in TOAST handling, OID assignment and redo recovery

On Thu, Apr 12, 2018 at 5:53 AM, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 4/10/18 06:29, Pavan Deolasee wrote: > > One of our 2ndQuadrant support customers recently reported a sudden rush > > of TOAST errors post a crash recovery, nearly causing an ou

Re: VM map freeze corruption

pg_check_frozen('t');" | $p # See if a vacuum freeze scanning all pages corrects the problem echo "vacuum (verbose, freeze, disable_page_skipping) t;" | $p echo "select pg_check_frozen('t');" | $p Thanks, Pavan [1] https://www.postgresql.org/m

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

heuristic based on available free space in the table prior to the truncation point? Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

K blocks, remember those K buffers, discard those K buffers, truncate the relation and then try for next K blocks. If another backend requests lock on the table, we give up or retry after a while. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: VM map freeze corruption

ttached. Not fully polished (and there is a XXX that I left for comments), but hopefully enough to tell what I am thinking. Do you think it's any improvement or actually makes the problem worse? Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Developme

Re: Toast issues with OldestXmin going backwards

How does this guard against the case when the same OID gets inserted in the toast table again, with matching lengths etc? Rare but seems possible, no? I think we should look for a more complete solution how hard these bugs are to detect and fix. Thanks, Pavan > -- > Andrew (irc:R

Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits

l-frozen. Since the check is performed immediately after the page becomes full and only once per page, there shouldn't be any additional IO cost and the check should be quite fast. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits

e scan that we are doing in this patch is done on a page that should be in the buffer cache, we will pay a bit in terms of CPU cost, but not anything in terms of IO cost. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Re: A separate table level option to control compression

out negative effects of setting compress_tuple_target lower though, per your suggestion. Also added some details in storage.sgml as recommended by Sawada-san. Hope this helps. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services 0001-Add-a-table-level-option-to-control-compression.patch Description: Binary data

Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits

in time to VACUUM the table. The benefits will only go up if the table is vacuumed much later when most of the pages are already written to the disk and removed from shared buffers and/or kernel cache. I hope this satisfies your doubts regarding performance implications of the patch.

Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits

on't see a harm in keeping the code as is. This code is borrowed from vacuumlazy.c and at some point we can even move it to some common location. > Perhaps we can add some tests for this feature to pg_visibility module. > > That's a good idea. Please see if the tests included i

Re: Faster inserts with mostly-monotonically increasing values

On Sun, Dec 31, 2017 at 4:36 PM, Peter Geoghegan wrote: > On Sun, Dec 31, 2017 at 6:44 AM, Pavan Deolasee > wrote: > > Here is a patch that implements the idea. If the last insert happens to > be > > in the rightmost block of an index, then we cache the block and check >

Re: Faster inserts with mostly-monotonically increasing values

. The next split will happen when the right page is full i.e. when another N/2 entries are added. Thus there will be a split at every N/2 inserts, creating an index with half filled pages. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Faster inserts with mostly-monotonically increasing values

On Thu, Jan 4, 2018 at 6:05 PM, Alvaro Herrera wrote: > Pavan Deolasee wrote: > > On Tue, Jan 2, 2018 at 7:15 PM, Tels > wrote: > > > > > Just a question trying to understand how btree indexes work: > > > > > > If one inserts ever-increasin

Re: Changing WAL Header to reduce contention during ReserveXLogInsertLocation()

ere some concerns about bit-flipping, which may inadvertently SegID stored in the carried over WAL record so that it now matches with the current WAL files' SegID, but TBH I don't see why we can't trust CRC to detect that. Because if we can't, then there would be othe

Re: [HACKERS] MERGE SQL Statement for PG11

need some treatment because when the plan is re-executed, it will expect to the find the updated tuple in the slot of the underlying query's RTE and not in the resultRelation's RTE, which does not participate in the join at all. Anything else I could be missing out completely? Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] MERGE SQL Statement for PG11

patch for v11. > TBH I did not consider partitioning any less complex and it was indeed very complex, requiring at least 3 reworks by me. And from what I understood, it would have been a blocker too. So is subquery handling and RLS. That's why I focused on addressing those items while you and Simon were still debating EPQ semantics. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] MERGE SQL Statement for PG11

On Fri, Feb 9, 2018 at 6:53 AM, Peter Geoghegan wrote: > On Wed, Feb 7, 2018 at 7:51 PM, Pavan Deolasee > wrote: > > I understand getting EPQ semantics right is very important. Can you > please > > (once again) summarise your thoughts on what you think is the *most* > &g

Re: [HACKERS] MERGE SQL Statement for PG11

On Sat, Feb 10, 2018 at 7:19 AM, Tomas Vondra wrote: > Hi, > > On 02/07/2018 10:24 AM, Pavan Deolasee wrote: > > > >if (startWAL < GetXactWALBytes()) >ereport(ERROR, >(errcode(ERRCODE_FEATURE_NOT_SUPPORTED), >

Re: [HACKERS] MERGE SQL Statement for PG11

ons separately, outside the EPQ. The problem arises when the join qual returns a different result with the updated tuple. I listed down those cases in my earlier email in the day. To me (and I assume to Peter and Simon too), those are the more interesting cases. Thanks, Pavan -- Pavan Deolas

Re: Changing WAL Header to reduce contention during ReserveXLogInsertLocation()

renames and still resurrect old data several renames before, then we shall have the same problem. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] MERGE SQL Statement for PG11

milarly, INSERT policies will be applied when MERGE attempts to INSERT a row into the table and error will be thrown if the row does not satisfy INSERT policies. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] MERGE SQL Statement for PG11

issues detected by sqlsmith or otherwise. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits

On Wed, Mar 27, 2019 at 9:47 AM Masahiko Sawada wrote: > > The patch looks good to me. There is no comment from me. > > Thanks for your review! Updated patch attached since patch failed to apply after recent changes in the master. Thanks, Pavan -- Pavan Deolasee

Re: Re: A separate table level option to control compression

ERT INTO testtab VALUES ; Earlier this tuple would not have been toasted, even though toast_tuple_target is set to 256. But with the new code, the tuple will get toasted. So that's a change, but in the right direction as far as I can see. Also, this is a bit unrelated to what this patch is trying to achieve. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits

if major architectural changes are required then it's probably too late to consider this for PG12, even though it's more of a bug fix and a candidate for back-patching too. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits

but not the vice versa. The proposed code does not introduce any new behaviour AFAICS. But I might be missing something. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Caveats from reloption toast_tuple_target

he toast_tuple_target is specified at CREATE TABLE, but we would still have problem with ALTER TABLE, no? But there might be side effects of changing the lower limit for pg_dump/pg_restore. So we would need to think about that too. Thanks, Pavan -- Pavan Deolasee http://www.2nd

Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits

at others think before investing additional time. > > Pavan, are you planning to work on this for v13 CF1? Or have you lost > interest on the topic? > Yes, I plan to work on it. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: MERGE SQL statement for PG12

Hi Tomas, Sorry for a delayed response. On Mon, Oct 29, 2018 at 4:59 PM Tomas Vondra wrote: > Hi Pavan, > > On 10/29/2018 10:23 AM, Pavan Deolasee wrote: > > > > ... > > > > Thanks for keeping an eye on the patch. I've rebased the patch > > against t

Re: [HACKERS] MERGE SQL Statement for PG11

iour of re-evaluating join only when certain columns are updated. It looks to me irrespective of what we choose, our implementation would be much superior to what Oracle offers. BTW I've sent v17a of the patch, which is very close to being complete from my perspective (except some documentation fixes/improvements). The only thing pending is the decision to accept or change the currently implemented concurrency semantics. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] MERGE SQL Statement for PG11

t I am seriously doubtful that we want to go down that path and whether it's even feasible. Our regular UPDATE .. FROM does not do that either. Given that, it seems better to just throw an error (even when no NOT MATCHED action exists) and explain to the users that MERGE will work as long as concurrent updates don't modify the columns used in the join condition. Concurrent deletes should be fine and we may actually even invoke WHEN NOT MATCHED action in that case. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: ON CONFLICT DO UPDATE for partitioned tables

on, after having converted the existing ones to use partition relation's varno? May be that works because missing attributes are already added during planning and expand_targetlist() here only adds dropped columns, which are just NULL constants. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Faster inserts with mostly-monotonically increasing values

dexes are actually stored in the reverse order. I am gonna look at that too. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Restrict concurrent update/delete with UPDATE of partition key

at isolation tests for partition key updates are either missing (I haven't checked) or they need more work. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Restrict concurrent update/delete with UPDATE of partition key

ught. May be it was discussed somewhere else and ruled out. I happened to notice this patch because of the bug I encountered. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Restrict concurrent update/delete with UPDATE of partition key

On Thu, Mar 8, 2018 at 12:31 PM, Amit Kapila wrote: > On Thu, Mar 8, 2018 at 11:04 AM, Pavan Deolasee > wrote: > > > > On Tue, Feb 13, 2018 at 12:41 PM, amul sul wrote: > >> > >> Thanks for the confirmation, updated patch attached. > >> > > &g

Re: [HACKERS] Restrict concurrent update/delete with UPDATE of partition key

But there could be other ideas too. And even if we can't find one, my vote would be to settle for #1 instead of trying to do #2. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Faster inserts with mostly-monotonically increasing values

On Tue, Mar 6, 2018 at 10:10 AM, Pavan Deolasee wrote: > > > On Tue, Mar 6, 2018 at 7:29 AM, Peter Geoghegan wrote: > >> On Mon, Mar 5, 2018 at 5:48 PM, Claudio Freire >> wrote: >> >> > I believe PKs are a prime candidate for this optimization, and

Re: [HACKERS] MERGE SQL Statement for PG11

lan this way. > > Of course, that isn't true of MERGE: The MERGE target *can* be the > nullable side of an outer join. That's probably a big complication for > using one target RTE. Your approach to implementing partitioning [1] > seems to benefit from having two diff

Re: Faster inserts with mostly-monotonically increasing values

On Sat, Mar 10, 2018 at 12:11 AM, Claudio Freire wrote: > On Fri, Mar 9, 2018 at 2:54 PM, Pavan Deolasee > wrote: > > > > > > > > > So yes, the benefits of the patch go down with higher number of clients, > but > > it does not entirely vanish. > &g

Re: [HACKERS] MERGE SQL Statement for PG11

ean we could not re-implement MERGE on the lines of INSERTs, but that would most likely mean complete re-writing of the UPDATEs/DELETEs for partition/inheritance tables. The challenges would just be the same in both cases. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Faster inserts with mostly-monotonically increasing values

On Sun, Mar 11, 2018 at 9:18 PM, Claudio Freire wrote: > On Sun, Mar 11, 2018 at 2:27 AM, Pavan Deolasee > > > > > Yes, I will try that next - it seems like a good idea. So the idea would > be: > > check if the block is still the rightmost block and the insertion-

Re: Faster inserts with mostly-monotonically increasing values

is.. It assumes a presence of a branch named "btree_rightmost" with the patched code. You will need to make necessary adjustments of course. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Servi

Re: Faster inserts with mostly-monotonically increasing values

On Wed, Mar 14, 2018 at 4:53 PM, Simon Riggs wrote: > On 14 March 2018 at 04:36, Pavan Deolasee > wrote: > > I wonder if the shortened code path actually leads to > > heavier contention for EXCLUSIVE lock on the rightmost page, which in > turn > > causes the slowdow

Re: Faster inserts with mostly-monotonically increasing values

On Wed, Mar 14, 2018 at 7:21 PM, Simon Riggs wrote: > On 14 March 2018 at 13:33, Pavan Deolasee > wrote: > > > > > > On Wed, Mar 14, 2018 at 4:53 PM, Simon Riggs < > simon.ri...@2ndquadrant.com> > > wrote: > >> > >> On 14 March 2018 at

Re: [HACKERS] MERGE SQL Statement for PG11

Hi Stephen, On Fri, Mar 16, 2018 at 7:28 AM, Stephen Frost wrote: > Greetings Pavan, all, > > * Pavan Deolasee (pavan.deola...@gmail.com) wrote: > > On 9 March 2018 at 08:29, Peter Geoghegan wrote: > > > My #1 concern has become RLS, and > > > perhaps onl

Re: ON CONFLICT DO UPDATE for partitioned tables

t we follow? Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: ON CONFLICT DO UPDATE for partitioned tables

On Fri, Mar 16, 2018 at 5:13 PM, Etsuro Fujita wrote: > (2018/03/16 19:43), Pavan Deolasee wrote: > >> On Fri, Mar 2, 2018 at 9:06 PM, Alvaro Herrera > <mailto:alvhe...@2ndquadrant.com>> wrote: >> > > @@ -106,6 +120,9 @@ typedef stru

Re: [HACKERS] MERGE SQL Statement for PG11

On Mon, Mar 12, 2018 at 5:43 PM, Pavan Deolasee wrote: > > > On Sun, Mar 11, 2018 at 11:18 AM, Peter Geoghegan wrote: > >> >> >> As you know, there is an ON CONFLICT DO UPDATE + partitioning patch in >> the works from Alvaro. In your explanation about

Re: Faster inserts with mostly-monotonically increasing values

client and 10-15% with larger number of clients. I haven't been able to establish why it's happening, but since it's a different AWS instance (though of the same type), I am inclined to blame it on that for now. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadr

Re: ON CONFLICT DO UPDATE for partitioned tables

ol canSetTag) { HeapTuple tuple; If we move the list of arbiter indexes and the tuple desc to ResultRelInfo, as suggested above, I think we can avoid making any API changes to ExecPrepareTupleRouting and ExecInsert. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Faster inserts with mostly-monotonically increasing values

dated patch. I removed the last offset caching thing completely and integrated your changes for conditional lock access. Some other improvements to test cases too. I realised that we must truncate and re-insert to test index fastpath correctly. Thanks, Pavan -- Pavan Deolasee

Re: ON CONFLICT DO UPDATE for partitioned tables

> ResultRelInfo as I wrote above. > > Thanks. It's looking much better now. I think we can possibly move all ON CONFLICT related members to a separate structure and just copy the pointer to the structure if (map == NULL). That might make the code a bit more tidy. Is there anything that needs to be done for transition tables? I checked and didn't see anything, but please check. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] MERGE SQL Statement for PG11

ROM pg_class), (SELECT relname FROM pg_class LIMIT 1)); MERGE 1 postgres=# SELECT * FROM target; a | b -+ 755 | pgbench_source (1 row) Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Assertion failure while streaming toasted data

nly triggers when data is inserted via COPY (multi-insert). Let me know if anything else is needed to reproduce this. Thanks, Pavan -- Pavan Deolasee EnterpriseDB: https://www.enterprisedb.com stream_toasted_txn_test.patch Description: Binary data

Re: Assertion failure while streaming toasted data

lugin gets NULL values for the toast columns. It's a bit hard to demonstrate that with the test_decoding plugin, but if you have some other mechanism to test that change with an actual downstream node receiving and applying changes, it will be useful to test with that. Thanks, Pavan -- Pa

Re: Assertion failure while streaming toasted data

On Tue, May 25, 2021 at 1:49 PM Dilip Kumar wrote: > On Tue, May 25, 2021 at 1:45 PM Pavan Deolasee > wrote: > > > > I am not entirely sure if it works correctly. I'd tried something > similar, but the downstream node using > > my output plugin gets NULL value

Re: Assertion failure while streaming toasted data

em and run these tests with a much lower value. In fact, that's how I caught the problem in the first place. I had deliberately lowered the value to 1kB so that streaming code kicks in very often and even for small transactions. Thanks, Pavan -- Pavan Deolasee EnterpriseDB: https://www.enterprisedb..com

Re: Assertion failure while streaming toasted data

kicked in. I would suggest running the test without the fix and check if the assertion hits. If so, we are good to go. Thanks, Pavan -- Pavan Deolasee EnterpriseDB: https://www.enterprisedb..com

  1   2   >