Re: Feature: temporary materialized views

ws. Tests test that this really works, that refreshing of such views work, and that refreshing can also work from a trigger. Mitar On Thu, Dec 27, 2018 at 5:15 AM Alvaro Herrera wrote: > > On 2018-Dec-27, Mitar wrote: > > > Hi! > > > > I made a new version of

Re: Feature: triggers on materialized views

script to run before the timed part of the script.) Mitar On Mon, Dec 24, 2018 at 12:59 PM Mitar wrote: > > Hi! > > Based on discussion about observing changes on an open query in a > reactive manner (to support reactive web applications) [1], I > identified that one critical

Re: Feature: temporary materialized views

Hi! One more version of the patch with more deterministic tests. Mitar On Thu, Dec 27, 2018 at 10:35 AM Mitar wrote: > > Hi! > > Thanks, I did it. > > I am attaching a new version of the patch with few more lines added to tests. > > I noticed that there is no good sum

Re: Feature: triggers on materialized views

Hi! Hm, why in commitfest it does not display the latest patch? https://commitfest.postgresql.org/21/1953/ It does display correctly the latest e-mail, but not the link to the patch. :-( Mitar On Thu, Dec 27, 2018 at 11:51 PM Mitar wrote: > > Hi! > > One more version

Re: Feature: triggers on materialized views

Hi! One more version of the patch with slightly more deterministic tests. Mitar On Thu, Dec 27, 2018 at 11:43 PM Mitar wrote: > > Hi! > > I have made an updated version of the patch, added tests and > documentation changes. This is my view now a complete patch. Please > pr

Re: Feature: triggers on materialized views

rg/message-id/flat/20181227215726.4d166b4874f8983a641123f5%40sraoss.co.jp [2] https://www.postgresql.org/message-id/flat/fc784a9f-f599-4dcc-a45d-dbf6fa582...@qqdd.eu Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Adding a concept of TEMPORARY TABLESPACE for the use in temp_tablespaces

Hi! On Sun, Jan 6, 2019 at 11:01 AM Mitar wrote: > I have experimented a bit and performance really improves if /dev/shm > is used. I have experimented with creating temporary tables inside a > regular (SSD backed) tablespace /dev/shm and I have seen at least 2x > improvement in t

Adding a concept of TEMPORARY TABLESPACE for the use in temp_tablespaces

temporary objects are allowed to be created in a TEMPORARY TABLESPACE, to make sure user does not make a mistake. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Feature: temporary materialized views

factoring this myself this weekend. Hopefully > it is not too involved. That would be great! I can afterwards update the patch accordingly. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materializ

Re: [PATCH] Allow UNLISTEN during recovery

e that it works for you and then I can do another review. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Feature: temporary materialized views

Hi! On Thu, Jan 17, 2019 at 9:53 AM Andreas Karlsson wrote: > > What is the stumbling block to just leaving that alone? > > I think the issue Mitar ran into is that the temporary materialized view > is created in the rStartup callback of the receiver whic

Re: Feature: temporary materialized views

esides tab autocompletion? It is OK to remove that security check? If I understand correctly, there are some general refactoring of code Tom is proposing, but I am not sure if I am able to do that/understand that. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Feature: triggers on materialized views

Hi! I did a bit of benchmarking. It seems my version with UPDATE takes even slightly less time (~5%). Mitar On Mon, Dec 24, 2018 at 6:17 PM Mitar wrote: > > Hi! > > I made another version of the patch. This one does UPDATEs for changed > row instead of DELETE/INSERT. &

Re: Feature: triggers on materialized views

ny for your new feature, which is not good. Yes, I have not yet done that. I want first to also add calling triggers for non-concurrent refresh, but I would need a bit help there (what to call, example of maybe code which does something similar already). Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Feature: triggers on materialized views

re there any existing benchmarks for such operations I could use to see if there are any performance changes if I change implementation here? Any guidelines how to evaluate this? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Feature: triggers on materialized views

CATE and INSERT triggers after heap swap simulating the above. I made AFTER STATEMENT triggers and it looks like it is working, only NEW table is not populated for some reason. Any suggestions? See attached patch. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m diff --git a/src/back

Re: Feature: temporary materialized views

is deprecated in temporary object creation"? Based on grammar it seems to be used for tables, views, sequences, and soon materialized views. > Of course, you need to add tests and patch the docs. Sure. [1] https://www.postgresql.org/message-id/29165.1545842105%40sss.pgh.pa.us Mitar

Re: Feature: temporary materialized views

know, I do not yet have much experience contributing here. What are next steps? Do I just wait for it to be included into Commitfest? Do I add it there myself? Mitar On Wed, Dec 26, 2018 at 9:00 AM Alvaro Herrera wrote: > > On 2018-Dec-25, Mitar wrote: > > > Sometimes mat

Re: Feature: triggers on materialized views

Hi! I made another version of the patch. This one does UPDATEs for changed row instead of DELETE/INSERT. All existing regression tests are still passing (make check). Mitar On Mon, Dec 24, 2018 at 4:13 PM Mitar wrote: > > Hi! > > Thanks for reply! > > On Mon, Dec 24, 201

Re: Feature: triggers on materialized views

to write calling triggers there. Any reference to an existing code which does something similar would be great. So I think after swapping heaps we should call TRUNCATE trigger and then INSERT for all new rows. Mitar On Mon, Dec 24, 2018 at 6:17 PM Mitar wrote: > > Hi! > > I made ano

Feature: triggers on materialized views

Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index fb0de60a45..aacfa2338a 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -197,7 +197,7 @@ CreateTrigger(CreateTrigStmt

Re: Feature: triggers on materialized views

difference between the two. But yes, all INSERT trigger calls should have NEW provided. So per-statement trigger would have TRUNCATE and INSERT called. And per-row trigger would have TRUNCATE and per-row INSERTs called. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Feature: temporary materialized views

it is easy to add the same thing for materialized views as well. See attached PoC patch. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index d01b258b65..996fe8f53d 100644 --- a/src/backend/commands

Re: Feature: triggers on materialized views

t. So, what to call once material views' heaps are swapped to call triggers? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: commitfest: When are you assigned patches to review?

. [1] https://commitfest.postgresql.org/21/1924/ [2] https://wiki.postgresql.org/wiki/Submitting_a_Patch [3] https://wiki.postgresql.org/wiki/CommitFest Mitar

Re: Implementing Incremental View Maintenance

/CAKLmikP%2BPPB49z8rEEvRjFOD0D2DV72KdqYN7s9fjh9sM_32ZA%40mail.gmail.com [2] https://github.com/nothingisdead/pg-live-query [3] https://www.postgresql.org/docs/devel/sql-createtable.html [4] https://github.com/tozd/node-reactive-postgres/blob/eeda4f28d096b6e552d04c5ea138c258cb5b9389/index.js#L329-L340 Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Feature: triggers on materialized views

Hi! False alarm. It just looks like updating patches takes longer than updating e-mails. Mitar On Fri, Dec 28, 2018 at 12:11 AM Mitar wrote: > > Hi! > > Hm, why in commitfest it does not display the latest patch? > > https://commitfest.postgresql.org/21/1953/ > > I

Re: Feature: temporary materialized views

ample where refresh would not be possible. This is why I felt comfortable removing this. Also, no test failed after removing this. [1] https://github.com/postgres/postgres/blob/master/src/backend/commands/view.c#L554 Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: port of INSTALL file generation to XSLT

Hi! On Fri, Jan 11, 2019 at 1:05 PM Tom Lane wrote: > Failure would leave a .tmp file behind, but I doubt we care enough > about that to work harder than this. Maybe just make sure that "make clean" removes it? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Regression tests using multiple sessions

Hi! How can I define regression tests which should use multiple client sessions to test interaction between them? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Regression tests using multiple sessions

Hi! Thanks. Mitar On Fri, Dec 28, 2018 at 1:01 PM Alvaro Herrera wrote: > > Hi, > > On 2018-Dec-28, Mitar wrote: > > > How can I define regression tests which should use multiple client > > sessions to test interaction between them? > > See src/test/isolatio

Re: Feature: triggers on materialized views

h cases the same trigger would run because materialized view had a row inserted. Pretty nice. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

commitfest: When are you assigned patches to review?

://wiki.postgresql.org/wiki/Reviewing_a_Patch Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Implementing Incremental View Maintenance

can in a reactive manner render that in the UI to the user. So you can easily create a reactive UI which always shows up-to-date data without having to poll or something similar. How are things progressing? Any news on this topic? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Re: Feature: triggers on materialized views

ee a reason why this would not be included. I mean, materialized views are really just a sugar on top of having a table you refresh with a stored query, and if that table can have triggers, why not also a materialized view. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Adding a concept of TEMPORARY TABLESPACE for the use in temp_tablespaces

noise in logs, but installation boots without a problem. So maybe we are closer to this than we realize? [1] https://www.postgresql.org/message-id/flat/20170529185308.GB28209%40momjian.us Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Feature: temporary materialized views

ping me explicitly if there will be anything I am expected to do at any point in time. And just to make sure, these current patches are doing just refactoring but are not also introducing temporary materialized views yet? Or is that also done in patches made by Andreas? Mitar -- http

Re: Feature: triggers on materialized views

ying to explain that I am a bit at loss on concrete next steps I could take here. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Feature: temporary materialized views

LIZED VIEW. Sounds good. I will wait. Thanks. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Feature request: binary NOTIFY

Hi! I might have missed prior discussions about this, but I wonder if it would be possible to support binary payloads for NOTIFY/LISTEN? Again and again I find it very limiting with just text (have to base64 encode data, or convert it to JSON). Mitar -- http://mitar.tnode.com/ https

Re: Feature request: binary NOTIFY

sponse? For binary? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Feature request: binary NOTIFY

t it soon. :-) Thanks. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Persist MVCC forever - retain history

have to run a custom version of PostgreSQL or is this possible through an extension of sort? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Persist MVCC forever - retain history

n’t just nice-to-have features. Oh, I forgot about that. ctid is still just 32 bits? So then for such table with permanent MVCC this would have to be increased, to like 64 bits or something. Then one would not have to do wrap-around protection, no? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Persist MVCC forever - retain history

ortant (not just the last state) because it allows one to merge with a potentially changed local state in the web app while it was offline. So in a way it is logical replication and replay, but just at database - client level. [1] https://eng.uber.com/postgres-to-mysql-migration/ Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Persist MVCC forever - retain history

Hi! On Thu, Jul 2, 2020 at 7:51 PM Mark Dilger wrote: > I expect these issues to be less than half what you would need to resolve, > though much of the rest of it is less clear to me. Thank you for this insightful input. I will think it over. Mitar -- http://mitar.tnode.com/

Re: Persist MVCC forever - retain history

you gave up on that work? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Allowing DESC for a PRIMARY KEY column

st if I would be doing ORDER BY id, revision DESC on the whole table? Because one future query I am working on is where I select all rows but for only the latest (highest) revision. Curious if that will have an effect there. Mitar [1] https://www.postgresql.org/docs/16/indexes-ordering.html -- htt

Allowing DESC for a PRIMARY KEY column

pretty straightforward? Mitar [1] https://stackoverflow.com/questions/45597101/primary-key-with-asc-or-desc-ordering [2] https://www.postgresql.org/docs/16/indexes-ordering.html -- https://mitar.tnode.com/ https://twitter.com/mitar_m https://noc.social/@mitar

Adding application_name to the error and notice message fields

pplication name (when available) to the error and notice message fields [2]? Mitar [1] https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-LINE-PREFIX [2] https://www.postgresql.org/docs/current/protocol-error-fields.html -- https://mitar.tnode.com/ https://twitter.c

Re: Adding application_name to the error and notice message fields

Hi! Oh, I can use PQparameterStatus to obtain application_name of the current connection. It seems then it is not needed to add this information into notice message. Mitar On Wed, Mar 27, 2024 at 4:22 PM Mitar wrote: > > Hi! > > We take care to always set application_name to imp