[HACKERS] Extract Jsonb key and values
Hi all, I am having jsonb as C character string received by WAL decoding and want to extract all its key and value pairs. Which is the best approach for extracting keys and its values? i) Converting the C string to a PostgreSQL jsonb object ii) Using open-source json-c library cheers - Harry
[HACKERS] Logical Decoding - Execute join query
Hi all, I tried to execute a join query using SPI_execute() in logical decoding part and got inconsistent values (i am referring it as inconsistent since it is returning the old values which is present at the postgresql server start). My data directory has to tables table1(a integer PRIMARY KEY, b integer, c integer) table2(x integer PRIMARY KEY, y integer, z integer) I have table1 as a | b | c ---+---+--- 1 | 1 | 1 2 | 2 | 2 and table 2 as x | y | z ---+---+--- 1 | 1 | 1 2 | 2 | 2 Then through psql client inserted a new row to table1 as: insert into table1(3,3,3); While decoding this insert query i am trying to execute the below query using SPI_execute SELECT * FROM (table1 LEFT JOIN table2 ON ((table1.a = table2.x))); And got only 2 rows. I cant able to get any new rows that are inserted. Are these new values get locked somewhere? Is there a way to get inserted changes? PS:When i restart the pgsql server i can able to get those new values that are inserted/updated in previous instance. cheers - Harry
[HACKERS] Incremental refresh of materialized view - Patch
Hi all I am building a patch to refresh materialized view incrementally from the change set decoded by using logical decoding from WAL. As of now i can able to generate the changes that has to be updated in the materialized view but the thing was it not possible to do any DML operations on MATVIEWS. Only from the concurrent refresh of matviews the DML operations are allowed. However if the same methods in matview.c OpenMatViewIncrementalMaintenance CloseMatViewIncrementalMaintenance are mad extern its possible to do DML from the patches like i am building now. Is there any other way of doing DML operations on materialized views from patch.? correct me if i am wrong cheers - Harry
Re: [HACKERS] Incremental refresh of materialized view - Patch
Hi all I am building a patch to refresh materialized view incrementally from the change set decoded by using logical decoding from WAL. As of now i can able to generate the changes that has to be updated in the materialized view but the thing was it not possible to do any DML operations on MATVIEWS. Only from the concurrent refresh of matviews the DML operations are allowed. However if the same methods in matview.c OpenMatViewIncrementalMaintenance CloseMatViewIncrementalMaintenance are mad extern its possible to do DML from the patches like i am building now. Is there any other way of doing DML operations on materialized views from patch.? correct me if i am wrong cheers - Harry
[HACKERS] full table delete query
Hi all, How postgresql handles full table delete in terms of loading the full table in these scenarios consider one big table(tablename: bigtable) and the query will be delete from bigtable; 1)which doesn't have any foreign table reference with any other tables 2)And when this table is referenced by other table cheers - Harry
[HACKERS] Delete query on materialized view
Hi all I am trying to delete/insert a row on materialized view which has join from a UDF by using SPI_execute. Materialized views are not allowed to do any DML changes once created,so by bypassed that check by enabling MatViewIncrementalMaintenanceIsEnabled. so DML queries can be executed in MV. But i dont want to bypass that check i tried to change the relkind for the materialized view to 'r' (normal table relation) in pg_class table and tried the same query but its throwing an error. ## ERROR: could not find attribute -1 in subquery targetlist ## Is there a way to enable DML queries on materialized views which has join without overwriting matview_maintenance_depth to 1 as the method was local in matview.c? cheers - Harry
