Re: Determining if a table really changed in a trigger

Hi! On Sat, Nov 6, 2021 at 2:43 PM Tom Lane wrote: > Mitar writes: > > Anyone? Any way to determine the number of affected rows in a statement > > trigger? > > Check the size of the transition relation. Yes, this is what we are currently doing, but it looks very ineffic

Re: Determining if a table really changed in a trigger

" OR "UPDATE 3", > but I don't see how to access that from the trigger. I might have to submit > a patch for that if nobody else knows a way to get it. (Hopefully somebody > will respond with the answer...?) Anyone? Any way to determine the number of affected row

Re: Determining if a table really changed in a trigger

>> operate by row. > > That is not true Sorry to be imprecise. In this thread I am interested in statement triggers, so I didn't mention this explicitly here. So statement triggers do not have NEW and OLD. But you can combine it with a row-level rule and this works then well together.

Re: Determining if a table really changed in a trigger

s (because the condition cannot refer to any values in the table) maybe providing something like AFFECTED variable in there would be the way to go? So one could write: CREATE TRIGGER my_trigger AFTER UPDATE ON my_table FOR EACH STATEMENT WHEN AFFECTED <> 0 EXECUTE FUNCTION my_table_func(); Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Determining if a table really changed in a trigger

Hi! On Wed, Oct 27, 2021 at 1:16 AM Mark Dilger wrote: > If Mitar finds that suppress_redundant_updates_trigger is sufficient, that > may be a simpler solution. Thanks for mentioning it. > > The suppress_redundant_updates_trigger uses memcmp on the old and new rows. > I don&#

Re: Determining if a table really changed in a trigger

n of style or is this a better approach than my: PERFORM * FROM old_values LIMIT 1; IF FOUND THEN ... Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Determining if a table really changed in a trigger

gger is per statement, not per row. So I do not think your approach works there? So this is why I am then making a more complicated check inside the trigger itself. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Determining if a table really changed in a trigger

is generally fast, but probably because it can use indices, not sure how fast *= is, given that it is comparing binary representations. What is experience with this operator of others? Mitar [1] https://www.postgresql.org/docs/current/functions-comparisons.html#COMPOSITE-TYPE-COMPARISON -- http

Determining if a table really changed in a trigger

has an unique index column, if that helps. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Why is writing JSONB faster than just JSON?

so now I am searching for other explanations for the results of my benchmark. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Why is writing JSONB faster than just JSON?

mpression-ility would apply to both JSONB and JSON column types, no? Moreover, it looks like JSONB column type ends up larger on disk. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Why is writing JSONB faster than just JSON?

x27;4597833728' } Type: text Mean: 250060.5 Stddev: 912.9207249263213 Size: { pg_total_relation_size: '4597833728' } [1] https://gitlab.com/mitar/benchmark-pg-json/-/blob/master/example.json Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Why is writing JSONB faster than just JSON?

] https://gitlab.com/mitar/benchmark-pg-json Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Automatically parsing in-line composite types

are making? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Automatically parsing in-line composite types

allations there is (except for the wire format, which has limitations). So having to map to it and back, but without developer having to think about it, might be the best solution. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Automatically parsing in-line composite types

because clients often have optimized JSON readers. Which can beat any other binary serialization format. In node.js, it is simply the fastest there is to transfer data: https://mitar.tnode.com/post/in-nodejs-always-query-in-json-from-postgresql/ Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Automatically parsing in-line composite types

JSONB") and it is clear how to automatically parse that. What I am missing is a way to automatically parse composite types. Those are generally not completely arbitrary, but are defined by the query, not by data. What would be the next step to move this further in some direction? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Automatically parsing in-line composite types

many data types. I think RowDescription should be extended to provide full recursive metadata about all data types. That would be the best way to do it. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Automatically parsing in-line composite types

to provide information for composite types as well, recursively. In that way you would not even have to go and fetch additional information from other types, potentially hitting race conditions. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Automatically parsing in-line composite types

message and then reading descriptions in pg_type table. > > Is there some other way to get full typing information of the result I > am assuming is available to PostreSQL internally? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Automatically parsing in-line composite types

escription message and then reading descriptions in pg_type table. Is there some other way to get full typing information of the result I am assuming is available to PostreSQL internally? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Relaxing NaN/Infinity restriction in JSON fields

datatype. Yes, I see why then so many implement variations on JSON, like BSON and stuff. So that they can have mostly compatible structure, but with all floats and datetime structure. What are thoughts of adding something like that? PgJSON variant. :-) Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Relaxing NaN/Infinity restriction in JSON fields

m just saying that the reality is that most people these days use IEEE spec floating numbers so it is sad that those cannot be easily stored in JSON, or a database. Mitar [1] https://tools.ietf.org/html/rfc7159#page-7 -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Relaxing NaN/Infinity restriction in JSON fields

really sad that one cannot stores directly all IEEE 754 double precision floating point values. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Benchmark of using JSON to transport query results in node.js

it on, maybe just pass it on as-is. > Our application is data-intensive, involving a lot of geotracking data across > hundreds of devices at it's core, and then quite a bit of geo/mapping/ > analytics around that.. It seems maybe very similar to what I had in Python/Django

Benchmark of using JSON to transport query results in node.js

-wire transmission? For example, to use something like Capnproto [2] to serialize into structure which can be directly used without any real deserialization? [1] https://mitar.tnode.com/post/181893159351/in-nodejs-always-query-in-json-from-postgresql [2] https://capnproto.org/ Mitar -- http

Re: Optimizing the same PREPAREd static query (without parameters)

maybe decide to change the plan? So it makes a plan, runs it, determines that the plan was not as good as expected, I run it again, it decides to try another plan. It is better, it decides to switch to it and keep it. [1] https://www.postgresql.org/docs/devel/sql-prepare.html Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Optimizing the same PREPAREd static query (without parameters)

Hi! On Mon, Jan 7, 2019 at 12:09 AM David Rowley wrote: > On Mon, 7 Jan 2019 at 18:54, Mitar wrote: > > If I have a PREPAREd query without parameters (static) and I EXECUTE > > it repeatedly in the same session, does PostgreSQL learn/optimize > > anything across those r

Optimizing the same PREPAREd static query (without parameters)

Hi! If I have a PREPAREd query without parameters (static) and I EXECUTE it repeatedly in the same session, does PostgreSQL learn/optimize anything across those runs? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Watching for view changes

everything. [1] https://commitfest.postgresql.org/21/1951/ [2] https://commitfest.postgresql.org/21/1948/ [3] https://github.com/mitar/node-pg-reactivity-benchmark [4] https://github.com/tozd/node-reactive-postgres [5] https://www.postgresql.org/message-id/flat/20181227215726.4d166b4874f8983a641123f5

Re: Dropping and creating a trigger

tance, CONN_STR variable - run: node --experimental-worker --expose-gc index.js pg-query-observer - ignore errors from the app, check PostgreSQL logs [1] https://github.com/mitar/docker-postgres [2] https://github.com/Richie765/pg-table-observer/blob/master/src/PgTableObserver.js#L199 [3] https:/

Dropping and creating a trigger

ER INSERT OR UPDATE OR DELETE ON "assignments" FOR EACH ROW EXECUTE PROCEDURE "tblobs_myapp"(); COMMIT; How is this possible? If I am inside a transaction, this should work, no? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: In which session context is a trigger run?

Hi! On Mon, Dec 31, 2018 at 2:58 AM Peter J. Holzer wrote: > On 2018-12-30 08:56:13 -0800, Adrian Klaver wrote: > > On 12/30/18 3:08 AM, Peter J. Holzer wrote: > > > If I understood Mitar correctly he wants the trigger to execute in the > > > session where it was

Re: In which session context is a trigger run?

client dispatches the call inside the correct client connection/session. It seems to work fine. Probably latency it is adding is also not too big a problem for me. I will see through time. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Using placeholders when creating a materialized view

Hi! Is it possible to use placeholders and provide values to the query when creating a materialized view? It looks like direct passing of values to the query is not possible and I have to encode them? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: In which session context is a trigger run?

sessions linked to OS processes PostgreSQL is using and this is why triggers run based on the session in which a change on the table was made? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: In which session context is a trigger run?

r session, the trigger cannot access "posts_temp". I wonder if there is a way to call "my_function" inside the same temporary context / session where it was defined, because in that same session also "posts_temp" was defined. Mitar

Re: In which session context is a trigger run?

trigger made from my session not run inside my session? Then it could see a temporary table made in my session. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

In which session context is a trigger run?

the user who added the trigger. Is there a way to modify/configure this? Switch the session? Is there some other way that I cold define a trigger which runs for a time of my session and runs and interact with temporary objects inside of my session, when data on regular tables is modified? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Watching for view changes

Hi! I started a patch to address this limitation. [1] [1] https://www.postgresql.org/message-id/CAKLmikMN%2B0W79A7Wg4krK55cTLxb%3DTARdz2b%3Dvqp19ao6AAZjw%40mail.gmail.com Mitar On Mon, Dec 24, 2018 at 1:38 AM Mitar wrote: > > Hi! > > On Mon, Dec 24, 2018 at 1:31 AM George N

Re: CREATE UNLOGGED MATERIALIZED VIEW

the comment above because they do not exit after the crash anyway. Mitar On Wed, Dec 26, 2018 at 8:35 AM Tom Lane wrote: > > Mitar writes: > > I am looking at parser grammar rules and it looks like the following > > is a valid query: > > CREATE UNLOGGED MATERIALIZED VIEW &

Re: CREATE UNLOGGED MATERIALIZED VIEW

sure logging/not logging really matters. I agree with you. But current default is RELPERSISTENCE_PERMANENT. So there is an option for RELPERSISTENCE_UNLOGGED. So, I think that we should or a) make RELPERSISTENCE_UNLOGGED default, b) document RELPERSISTENCE_UNLOGGED or c) remove it. I propose b).

Re: CREATE UNLOGGED MATERIALIZED VIEW

Hi! That is for transient tables (NEW and OLD tables), no? Not for the main materialized view table. Mitar On Tue, Dec 25, 2018 at 10:14 AM Adrian Klaver wrote: > > On 12/25/18 1:13 AM, Mitar wrote: > > Hi! > > > > I am looking at parser grammar rules and it looks lik

CREATE UNLOGGED MATERIALIZED VIEW

Hi! I am looking at parser grammar rules and it looks like the following is a valid query: CREATE UNLOGGED MATERIALIZED VIEW Any particular reason this is not documented? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Watching for view changes

tables? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Watching for view changes

Hi! I am unable to find cache table's name. :-( Mitar On Mon, Dec 24, 2018 at 1:01 AM Mitar wrote: > > Hi! > > On Sun, Dec 23, 2018 at 11:59 PM George Neuner wrote: > > IIRC the cache table's name is generated. I don't know the proper > > incantati

Re: Watching for view changes

_tables for objects in that space. Oh, what a trick. :-) Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Watching for view changes

makes sense, have the client collect some number of > notifications and read all the indicated rows in one query. You are right. Thanks again. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Determine in a trigger if UPDATE query really changed anything

l? So that I run the refresh only once per base table changes. For pushing notification this can be done at the row level. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Watching for view changes

gers on a materialized view? I am getting: "my_materialized_view" is not a table or view as an error when I am trying to create a trigger on materialized view. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Determine in a trigger if UPDATE query really changed anything

and I think this is reasonable. Still, not sure why I have to store the whole relation just to know if statement really changed anything. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Watching for view changes

views (not sure again why, because aren't materialized views just tables). Would it be possible to use client to subscribe instead of a server? [1] https://onethingsimple.com/2017/10/sync-materialized-views-after-debounce-period/ Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Watching for view changes

could be TEMPORARY, in-memory (and if PostgreSQL runs out of buffer space for it, I would prefer an error), and UNLOGGED. Any particular reasons which would prevent them to be implemented as such? [1] https://github.com/meteor/meteor Mitar On Sat, Dec 22, 2018 at 1:16 PM Rob Sargent wrote: >

Re: Watching for view changes

pen > automagically. But when refreshed, triggers on the cache table would > react to changes. So you are saying I could use triggers on the cache table to know what really changed instead of having to compute diff myself? Interesting. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Watching for view changes

o I get an updated row in the query results (or the view defined with such query). I would have to parse the SQL and figure out how to do transformation myself, no? So, I am not sure how triggers on underlying tables can really inform how to know what in the view has been updated? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Watching for view changes

evel and in this way have some information what is changing. I would prefer doing it in the database though, so that it could be independent from the source of the change. Moreover, not all UPDATE queries really do end up updating the data. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Watching for view changes

ub.com/numtel/pg-live-select Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Watching for view changes

transition relations to get old and new state in the case FOR EACH STATEMENT (but then I would still have to diff it probably myself). And view could represent any query, without me having to try to understand and parse it. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m

Re: Watching for view changes

Hi! Ah, that was a thread about table changes. I am interested in data changes (results from a query). Thanks. Mitar On Thu, Dec 20, 2018 at 8:01 AM Adrian Klaver wrote: > > On 12/20/18 7:54 AM, Mitar wrote: > > Hi! > > > > Thanks. Care for a link or at least t

Re: Watching for view changes

Hi! Thanks. Care for a link or at least thread subject? Also, Googling around this seems a pretty popular request. I am quite surprised that there is not something out-of-box available for this, efficient and scalable. Mitar On Thu, Dec 20, 2018 at 7:33 AM Ron wrote: > > On 12/20/18 3

Watching for view changes

devel/logical-replication-restrictions.html [2] https://www.postgresql.org/docs/devel/trigger-definition.html Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m