Network performance optimization
Hi Team, I am performing some benchmark testing with application in aws east 2 and dB in East 1. I am using pgbench for benchmark, the RTT Latency between East 1 to east2 is some where between 12 to 16 ms on an average. Is there any optimization that can be done to reduce the latency and increase the throughout to the PostgreSQL layer?
Re: Network performance optimization
Hi, use of a connection pooler usually helps in such cases. It will not reduce latency, but it will mitigate the problem when the app can benefit from recycling the connections. regards, fabio pardi On 09/09/2020 06:59, J . Mageshwaran wrote: > Hi Team, I am performing some benchmark testing with application in aws east > 2 and dB in East 1. I am using pgbench for benchmark, the RTT Latency between > East 1 to east2 is some where between 12 to 16 ms on an average. Is there any > optimization that can be done to reduce the latency and increase the > throughout to the PostgreSQL layer?
Re: Network performance optimization
On 09/09/2020 04:59, J . Mageshwaran wrote: Hi Team, I am performing some benchmark testing with application in aws east 2 and dB in East 1. I am using pgbench for benchmark, the RTT Latency between East 1 to east2 is some where between 12 to 16 ms on an average. Is there any optimization that can be done to reduce the latency and increase the throughout to the PostgreSQL layer? Even if network datagrams moved at the speed of light and with no serialisation/forwarding delay (which they don't), you're still going to deal with several thousand KMs of distance; I'm positively surprised you're getting such short round-trip times as it stands. If you have any control over the application code, IMO your best shot is to coalesce DB interactions into as few smarter DB queries as you can for each higher order operation (eg: RESTful API calls), as you'll be paying for that fixed RTT overhead for each command. -- Regards Fabio Ugo Venchiarutti OSPCFC Network Engineering Dpt. Ocado Technology -- Notice: This email is confidential and may contain copyright material of members of the Ocado Group. Opinions and views expressed in this message may not necessarily reflect the opinions and views of the members of the Ocado Group. If you are not the intended recipient, please notify us immediately and delete all copies of this message. Please note that it is your responsibility to scan this message for viruses. References to the "Ocado Group" are to Ocado Group plc (registered in England and Wales with number 7098618) and its subsidiary undertakings (as that expression is defined in the Companies Act 2006) from time to time. The registered office of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way, Hatfield, Hertfordshire, AL10 9UL.
Schema/ROLE Rename Issue
Hi, Recently we had renamed schema and role but it will not get reflect in the object definitions ,plz suggest. OLD SCHEMA/ROLE - TEST11 NEW SCHEMA/ROLE - TEST But still we are that TEST11 is present in the definition of sone objects like procudure,functions,triggers etc, so how can we modify the name from TEST11 to TEST in definition. Thanks and Regards, Brajendra
Re: Autovacuum of independent tables
Greetings, * Michael Holzman (michaelholz...@gmail.com) wrote: > I have two applications A and B. A runs SELECT statements only and only on > tableA. B actively updates tableB, A never looks into tableB. B has nothing > to do with tableA. In an ideal world, such distinct applications would probably do better to live in independent PG clusters, which would address this issue with VACUUM'ing and also allow you to do file-level backup/restore of each independently, scale physical replicas independently, as well as manage roles to be specific for each without seeing others, etc. Thanks, Stephen signature.asc Description: PGP signature
Re: Schema/ROLE Rename Issue
On Wed, 2020-09-09 at 17:04 +0530, Brajendra Pratap Singh wrote: > Recently we had renamed schema and role but it will not get reflect in the > object definitions ,plz suggest. > > OLD SCHEMA/ROLE - TEST11 > NEW SCHEMA/ROLE - TEST > > But still we are that TEST11 is present in the definition of sone objects > like procudure,functions,triggers etc, so how can we modify the name from > TEST11 to TEST in definition. > You cannot habe that automatically. While in view and index definitions as well as column default clauses, the internal object identifier is used, so that renaming doesn't break anything, functions are stored as string literals and parsed at execution time. You'll have to edit all your functions (or undo the renaming). Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Schema/ROLE Rename Issue
Thank you Albe.. Could u plz suggest me the best way to get the definition of functions via connecting through psql as while I am trying to change the definition through \ef function_name it won't reflect the changes. Thanks Brajendra On Wed, 9 Sep, 2020, 7:39 PM Laurenz Albe, wrote: > On Wed, 2020-09-09 at 17:04 +0530, Brajendra Pratap Singh wrote: > > Recently we had renamed schema and role but it will not get reflect in > the object definitions ,plz suggest. > > > > OLD SCHEMA/ROLE - TEST11 > > NEW SCHEMA/ROLE - TEST > > > > But still we are that TEST11 is present in the definition of sone > objects like procudure,functions,triggers etc, so how can we modify the > name from TEST11 to TEST in definition. > > > > You cannot habe that automatically. > > While in view and index definitions as well as column default clauses, > the internal object identifier is used, so that renaming doesn't break > anything, functions are stored as string literals and parsed at > execution time. > > You'll have to edit all your functions (or undo the renaming). > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > >
Re: BUG? Slave don't reconnect to the master
On Mon, 7 Sep 2020 23:46:17 +0300 Олег Самойлов wrote: > [...] > >>> why did you add "monitor interval=15"? No harm, but it is redundant with > >>> "monitor interval=16 role=Master" and "monitor interval=17 > >>> role=Slave". > >> > >> I can't remember clearly. :) Look what happens without it. > >> > >> + pcs -f configured_cib.xml resource create krogan2DB ocf:heartbeat:pgsqlms > >> bindir=/usr/pgsql-11/bin pgdata=/var/lib/pgsql/krogan2 > >> recovery_template=/var/lib/pgsql/krogan2.paf meta master notify=true > >> resource-stickiness=10 > >> Warning: changing a monitor operation interval from 15 to 16 to make the > >> operation unique > >> Warning: changing a monitor operation interval from 16 to 17 to make the > >> operation unique > > > > Something fishy here. This command lack op monitor settings. Pacemaker don't > > add any default monitor operation with default interval if you don't give > > one at resource creation. > > > > If you create such a resource with no monitoring, the cluster will > > start/stop it when needed, but will NOT check for its health. See: > > > > https://clusterlabs.org/pacemaker/doc/en-US/Pacemaker/2.0/html/Pacemaker_Explained/s-resource-monitoring.html > > > > May be. But keep in mind, that I uses `pcs`, I do not edit the xml file > directly. And I use too old pacemaker, the default package of CentOS 7 is > pacemaker-1.1.21-4.el7.x86_64, while you link of documentation is for > Pacemaker 2.0. It's the same behavior between both 2.0 and 1.1, but ...(see bellow) > >> So trivial monitor always exists by default with interval 15. > > > > nope. > > This is not true for CentOS 7. I removed my monitor options, for this example. > > pcs cluster cib original_cib.xml > cp original_cib.xml configured_cib.xml > pcs -f configured_cib.xml resource create krogan3DB ocf:heartbeat:pgsqlms > bindir=/usr/pgsql-11/bin pgdata=/var/lib/pgsql/krogan3 > recovery_template=/var/lib/pgsql/krogan3.paf meta master notify=true > resource-stickiness=10 I tried your command, and indeed, pcs creates the missing monitor operation with a default interval of 15. This is surprising, it's the first time I cross these warning messages. Thanks for this information, I wasn't aware of this pcs behavior. But anyway, it's not recommended to create your resources without specifying interval and timeout for each operations. See PAF docs. Just create the two monitor operations related to both roles and you'll not have these warnings. > > [...] > > OK, I understand now. If you want to edit an existing resource, use "pcs > > resource update". Make sure read the pcs manual about how to use it to > > edit/remove/add operations on a resource. > > This is not so easy. To edit existed resource I must to know the "interval" > of this resource, but in this case I am not sure what the interval will be > for the monitor operation of the master role. :) Because > >> > >> Warning: changing a monitor operation interval from 15 to 16 to make the > >> operation unique > >> Warning: changing a monitor operation interval from 16 to 17 to make the > >> operation unique > > I am not sure in what order and what it will be. Thats why I configured as I > configured. This just works. Now we know where these warnings comes from, you have a solution (set both of them explicitly) > >> Looked like the default timeout 10 was not enough for the "master". > > > > It's written in PAF doc. See: > > https://clusterlabs.github.io/PAF/configuration.html#resource-agent-actions > > > > Do not hesitate to report or submit some enhancements to the doc if > > needed. > > May be the documentation was improved. Thanks that you have pointed me on > that. After moving to CentOS 8 I will check with recommended parameters > according to the documentation. You can do it right now with CentOS 7. They are the same. > > [...] > 10:30:55.965 FATAL: terminating walreceiver process dpue to > administrator cmd 10:30:55.966 LOG: redo done at 0/1600C4B0 > 10:30:55.966 LOG: last completed transaction was at log time > 10:25:38.76429 10:30:55.968 LOG: selected new timeline ID: 4 > 10:30:56.001 LOG: archive recovery complete > 10:30:56.005 LOG: database system is ready to accept connections > >>> > The slave with didn't reconnected replication, tuchanka3c. Also I > separated logs copied from the old master by a blank line: > > [...] > > 10:20:25.168 LOG: database system was interrupted; last known up at > 10:20:19 10:20:25.180 LOG: entering standby mode > 10:20:25.181 LOG: redo starts at 0/1198 > 10:20:25.183 LOG: consistent recovery state reached at 0/11000A68 > 10:20:25.183 LOG: database system is ready to accept read only > connections 10:20:25.193 LOG: started streaming WAL from primary at > 0/1200 on tl 3 10:25:05.370 LOG: could not send data to client: > Connection reset by peer 10:26:38.655 FATAL: term
Unexpected results when using GROUP BY GROUPING SETS and bind variables
I wrote a query for a report that needed to summarize table data for different subsets of an ARRAY column. Once I had a working query, I recreated it in my code using an SQL query builder (the awesome jOOQ in this case). Unfortunately the output from the generated SQL was different from the handwritten query. Some of the grouping columns had null in them even though the original query showed the correct values. After some help from the jOOQ users mailing list, I found that the generated query I wrote was inadvertently using bind variables for the array indexes used in the GROUPING SETS. This apparently affects how the query is executed and leads to unexpected output. I wrote an SQL script runnable in psql to demonstrate the issue. The script creates a table with sample data and then executes 2 queries against it. The first one has everything inlined and produces the expected output. The second one uses a prepared statement and bind variables to reproduce the issue. I'm not really sure if this is a bug in query validation or a bug in executing the query or if it's just unreasonable expectations on my part but the output of the 2 queries seems to violate the principle of least surprise. - Aner -- bind-group-by-test.sql -- CREATE TABLE bind_group_by ( path text [] PRIMARY KEY, value int ); INSERT INTO bind_group_by (path, value) VALUES (ARRAY ['A1', 'B1', 'C1', 'D1'], 0), (ARRAY ['A1', 'B1', 'C1', 'D2'], 1), (ARRAY ['A1', 'B1', 'C1', 'D3'], 2), (ARRAY ['A1', 'B1', 'C2', 'D1'], 3), (ARRAY ['A1', 'B1', 'C2', 'D2'], 4), (ARRAY ['A1', 'B1', 'C2', 'D3'], 5), (ARRAY ['A1', 'B1', 'C3', 'D1'], 6), (ARRAY ['A1', 'B1', 'C3', 'D2'], 7), (ARRAY ['A1', 'B1', 'C3', 'D3'], 8), (ARRAY ['A1', 'B2', 'C1', 'D1'], 9), (ARRAY ['A1', 'B2', 'C1', 'D2'], 10), (ARRAY ['A1', 'B2', 'C1', 'D3'], 11), (ARRAY ['A1', 'B2', 'C2', 'D1'], 12), (ARRAY ['A1', 'B2', 'C2', 'D2'], 13), (ARRAY ['A1', 'B2', 'C2', 'D3'], 14), (ARRAY ['A1', 'B2', 'C3', 'D1'], 15), (ARRAY ['A1', 'B2', 'C3', 'D2'], 16), (ARRAY ['A1', 'B2', 'C3', 'D3'], 17), (ARRAY ['A1', 'B3', 'C1', 'D1'], 18), (ARRAY ['A1', 'B3', 'C1', 'D2'], 19), (ARRAY ['A1', 'B3', 'C1', 'D3'], 20), (ARRAY ['A1', 'B3', 'C2', 'D1'], 21), (ARRAY ['A1', 'B3', 'C2', 'D2'], 22), (ARRAY ['A1', 'B3', 'C2', 'D3'], 23), (ARRAY ['A1', 'B3', 'C3', 'D1'], 24), (ARRAY ['A1', 'B3', 'C3', 'D2'], 25), (ARRAY ['A1', 'B3', 'C3', 'D3'], 26), (ARRAY ['A2', 'B1', 'C1', 'D1'], 27), (ARRAY ['A2', 'B1', 'C1', 'D2'], 28), (ARRAY ['A2', 'B1', 'C1', 'D3'], 29), (ARRAY ['A2', 'B1', 'C2', 'D1'], 30), (ARRAY ['A2', 'B1', 'C2', 'D2'], 31), (ARRAY ['A2', 'B1', 'C2', 'D3'], 32), (ARRAY ['A2', 'B1', 'C3', 'D1'], 33), (ARRAY ['A2', 'B1', 'C3', 'D2'], 34), (ARRAY ['A2', 'B1', 'C3', 'D3'], 35), (ARRAY ['A2', 'B2', 'C1', 'D1'], 36), (ARRAY ['A2', 'B2', 'C1', 'D2'], 37), (ARRAY ['A2', 'B2', 'C1', 'D3'], 38), (ARRAY ['A2', 'B2', 'C2', 'D1'], 39), (ARRAY ['A2', 'B2', 'C2', 'D2'], 40), (ARRAY ['A2', 'B2', 'C2', 'D3'], 41), (ARRAY ['A2', 'B2', 'C3', 'D1'], 42), (ARRAY ['A2', 'B2', 'C3', 'D2'], 43), (ARRAY ['A2', 'B2', 'C3', 'D3'], 44), (ARRAY ['A2', 'B3', 'C1', 'D1'], 45), (ARRAY ['A2', 'B3', 'C1', 'D2'], 46), (ARRAY ['A2', 'B3', 'C1', 'D3'], 47), (ARRAY ['A2', 'B3', 'C2', 'D1'], 48), (ARRAY ['A2', 'B3', 'C2', 'D2'], 49), (ARRAY ['A2', 'B3', 'C2', 'D3'], 50), (ARRAY ['A2', 'B3', 'C3', 'D1'], 51), (ARRAY ['A2', 'B3', 'C3', 'D2'], 52), (ARRAY ['A2', 'B3', 'C3', 'D3'], 53), (ARRAY ['A3', 'B1', 'C1', 'D1'], 54), (ARRAY ['A3', 'B1', 'C1', 'D2'], 55), (ARRAY ['A3', 'B1', 'C1', 'D3'], 56), (ARRAY ['A3', 'B1', 'C2', 'D1'], 57), (ARRAY ['A3', 'B1', 'C2', 'D2'], 58), (ARRAY ['A3', 'B1', 'C2', 'D3'], 59), (ARRAY ['A3', 'B1', 'C3', 'D1'], 60), (ARRAY ['A3', 'B1', 'C3', 'D2'], 61), (ARRAY ['A3', 'B1', 'C3', 'D3'], 62), (ARRAY ['A3', 'B2', 'C1', 'D1'], 63), (ARRAY ['A3', 'B2', 'C1', 'D2'], 64), (ARRAY ['A3', 'B2', 'C1', 'D3'], 65), (ARRAY ['A3', 'B2', 'C2', 'D1'], 66), (ARRAY ['A3', 'B2', 'C2', 'D2'], 67), (ARRAY ['A3', 'B2', 'C2', 'D3'], 68), (ARRAY ['A3', 'B2', 'C3', 'D1'], 69), (ARRAY ['A3', 'B2', 'C3', 'D2'], 70), (ARRAY ['A3', 'B2', 'C3', 'D3'], 71), (ARRAY ['A3', 'B3', 'C1', 'D1'], 72), (ARRAY ['A3', 'B3', 'C1', 'D2'], 73), (ARRAY ['A3', 'B3', 'C1', 'D3'], 74), (ARRAY ['A3', 'B3', 'C2', 'D1'], 75), (ARRAY ['A3', 'B3', 'C2', 'D2'], 76), (ARRAY ['A3', 'B3', 'C2', 'D3'], 77), (ARRAY ['A3', 'B3', 'C3', 'D1'], 78), (ARRAY ['A3', 'B3', 'C3', 'D2'], 79), (ARRAY ['A3', 'B3', 'C3', 'D3'], 80); SELECT 'inlined' AS query; SELECT path[1], path[2], path[3], path, sum(value) FROM bind_group_by GROUP BY GROUPING SETS ( (path[1], path[2], path[3], path), (path[1], path[2], path[3]), (path[1], path[2]), (path[1])
Re: Unexpected results when using GROUP BY GROUPING SETS and bind variables
Aner Perez writes: > [ these queries don't give the same results: ] > SELECT path[1], path[2], path[3], path, sum(value) > FROM bind_group_by > GROUP BY GROUPING SETS ( > (path[1], path[2], path[3], path), > (path[1], path[2], path[3]), > (path[1], path[2]), > (path[1]), > () > ) > ORDER BY 1, 2, 3, 4 > ; > PREPARE prepared_group_by (int, int, int, int, int, int, int, int, int, > int, int, int) AS > SELECT path[$1], path[$2], path[$3], path, sum(value) > FROM bind_group_by > GROUP BY GROUPING SETS ( > (path[$4], path[$5], path[$6], path), > (path[$7], path[$8], path[$9]), > (path[$10], path[$11]), > (path[$12]), > () > ) > ORDER BY 1, 2, 3, 4 > ; > EXECUTE prepared_group_by (1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 1); I think you're kind of shooting yourself in the foot here by writing a query with more than one possible interpretation. Since you have GROUP BY items that are both the whole "path" array and specific elements of it, it's not clear whether a targetlist reference such as "path[1]" is meant to refer to the GROUP BY item "path[1]", or to be a subscripting operator applied to the GROUP BY item "path". It appears that the parser makes the first choice in your first query, although that seems like an implementation detail that I wouldn't care to bet on going forward. In the second query, the parser is certainly not going to think that "path[$1]" matches "path[$4]" or any of the other single-element GROUP BY items, so it decides that it means subscripting the "path" item. Now the result will be NULL for any grouping set that doesn't include the "path" item. I haven't gone through the results one-by-one, but just by eyeball I think this is sufficient to explain the discrepancies. (I note that the SQL standard only allows GROUP BY items to be simple column references. Maybe that's not an arbitrary restriction but is meant to forestall this sort of ambiguity? Hard to be sure.) regards, tom lane
Re: Network performance optimization
Fabio: On Wed, Sep 9, 2020 at 1:05 PM Fabio Ugo Venchiarutti wrote: > Even if network datagrams moved at the speed of light and with no > serialisation/forwarding delay (which they don't), you're still going to > deal with several thousand KMs of distance; I'm positively surprised > you're getting such short round-trip times as it stands. Light travels at about a foot per nanosecond in air/vacuum, 20 cm per nanosecond in glass IIRC, so you can RTT 10cm per nanosecond in fiber. This amounts to 100km per millisecond. 1200 to 1600 km in 12-16 ms. East-1/2 are N.Virginia / Ohio, which can be from 240 to 950 km apart ( on a quick google maps measure, not several thousands ), depending on the exact place. And Amazon has really fast pipes going between their regions, so this is not surprising. I have 55ms from ireland (eu west 1) to N.Virg., and they are 5500 km over the great circle. Francisco Olarte.
Re: Unexpected results when using GROUP BY GROUPING SETS and bind variables
Thanks Tom, I figured as much about the second query but I thought it would be safe to use the first version with the inlined indexing. I'm not sure if you're saying that the same query without the unindexed path column in the select would be safe. Like this: -- Do not GROUP BY or SELECT on path by itself -- SELECT path[1], path[2], path[3], path[4], sum(value) FROM bind_group_by GROUP BY GROUPING SETS ( (path[1], path[2], path[3], path[4]), (path[1], path[2], path[3]), (path[1], path[2]), (path[1]), () ) ORDER BY 1, 2, 3, 4; Or if using indexed path elements in the GROUP BY is the issue and I should put the array indexing in a subselect and do the aggregation in the outer select. Like this: -- Safer Subselect Version -- SELECT level1, level2, level3, level4, sum(value) FROM ( SELECT path[1] as level1, path[2] as level2, path[3] as level3, path[4] as level4, value FROM bind_group_by ) AS expanded GROUP BY GROUPING SETS ( (level1, level2, level3, level4), (level1, level2, level3), (level1, level2), (level1), () ) ORDER BY 1, 2, 3, 4; Thanks for the insight, - Aner On Wed, Sep 9, 2020 at 1:13 PM Tom Lane wrote: > Aner Perez writes: > > [ these queries don't give the same results: ] > > > SELECT path[1], path[2], path[3], path, sum(value) > > FROM bind_group_by > > GROUP BY GROUPING SETS ( > > (path[1], path[2], path[3], path), > > (path[1], path[2], path[3]), > > (path[1], path[2]), > > (path[1]), > > () > > ) > > ORDER BY 1, 2, 3, 4 > > ; > > > PREPARE prepared_group_by (int, int, int, int, int, int, int, int, int, > > int, int, int) AS > > SELECT path[$1], path[$2], path[$3], path, sum(value) > > FROM bind_group_by > > GROUP BY GROUPING SETS ( > > (path[$4], path[$5], path[$6], path), > > (path[$7], path[$8], path[$9]), > > (path[$10], path[$11]), > > (path[$12]), > > () > > ) > > ORDER BY 1, 2, 3, 4 > > ; > > EXECUTE prepared_group_by (1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 1); > > I think you're kind of shooting yourself in the foot here by writing > a query with more than one possible interpretation. Since you have > GROUP BY items that are both the whole "path" array and specific > elements of it, it's not clear whether a targetlist reference such > as "path[1]" is meant to refer to the GROUP BY item "path[1]", or > to be a subscripting operator applied to the GROUP BY item "path". > > It appears that the parser makes the first choice in your first > query, although that seems like an implementation detail that > I wouldn't care to bet on going forward. > > In the second query, the parser is certainly not going to think that > "path[$1]" matches "path[$4]" or any of the other single-element > GROUP BY items, so it decides that it means subscripting the "path" > item. Now the result will be NULL for any grouping set that doesn't > include the "path" item. > > I haven't gone through the results one-by-one, but just by eyeball > I think this is sufficient to explain the discrepancies. > > (I note that the SQL standard only allows GROUP BY items to be > simple column references. Maybe that's not an arbitrary restriction > but is meant to forestall this sort of ambiguity? Hard to be sure.) > > regards, tom lane >
Re: Unexpected results when using GROUP BY GROUPING SETS and bind variables
Aner Perez writes: > Or if using indexed path elements in the GROUP BY is the issue and I should > put the array indexing in a subselect and do the aggregation in the outer > select. Like this: > -- Safer Subselect Version -- > SELECT level1, level2, level3, level4, sum(value) > FROM ( > SELECT path[1] as level1, path[2] as level2, path[3] as level3, path[4] > as level4, value > FROM bind_group_by > ) AS expanded > GROUP BY GROUPING SETS ( > (level1, level2, level3, level4), > (level1, level2, level3), > (level1, level2), > (level1), > () > ) > ORDER BY 1, 2, 3, 4; Yeah, that one looks a lot safer from here. There's no question about which expressions are supposed to match what. It should end up with the same plan, too. regards, tom lane
Re: Unexpected results when using GROUP BY GROUPING SETS and bind variables
Of course, right when I hit send I thought of another option that makes the SQL a little less readable but perhaps gets rid of the ambiguity. Using ordinals in the GROUP BY: SELECT path[1], path[2], path[3], path[4], sum(value) FROM bind_group_by GROUP BY GROUPING SETS ( (1, 2, 3, 4), (1, 2, 3), (1, 2), (1), () ) ORDER BY 1, 2, 3, 4; Since I'm generating the SQL I'm not too worried about the readability and it works with bind variables too. I promise I'll stop thinking after this one ;-) Thanks! - Aner On Wed, Sep 9, 2020 at 2:45 PM Tom Lane wrote: > Aner Perez writes: > > Or if using indexed path elements in the GROUP BY is the issue and I > should > > put the array indexing in a subselect and do the aggregation in the outer > > select. Like this: > > > -- Safer Subselect Version -- > > SELECT level1, level2, level3, level4, sum(value) > > FROM ( > > SELECT path[1] as level1, path[2] as level2, path[3] as level3, > path[4] > > as level4, value > > FROM bind_group_by > > ) AS expanded > > GROUP BY GROUPING SETS ( > > (level1, level2, level3, level4), > > (level1, level2, level3), > > (level1, level2), > > (level1), > > () > > ) > > ORDER BY 1, 2, 3, 4; > > Yeah, that one looks a lot safer from here. There's no question about > which expressions are supposed to match what. It should end up with > the same plan, too. > > regards, tom lane >
Oracle Associate Array conversion to PostgreSQL
Hi All,
Since we don't have the support of Associative arrays in Postgres, we need
to convert Oracle associative arrays with a different approach in
PostgreSQL.
Following is a sample piece of code to demonstrate the same.
--Convert Oracle RECORD type to PostgreSQL TYPE.
--=
create type SLOC_TB as ( IN_EFFECT VARCHAR(1),
SUB_LOC_C VARCHAR(6),
START_DT timestamp(0),
END_DT timestamp(0),
SLOC_PHRASE_N VARCHAR(5)
);
---
--Below is simple anonymous block, where TYPE is used as an ARRAY ([]).
TYPE is used as an array object to allow storing multiple rows/records
supported by Index (like index by binary integer in Oracle).
do
$$
declare
lv_list SLOC_TB[];
idx record;
begin
lv_list[1] = ROW('X','XYZ',sysdate(),sysdate()+1, 'abc');
RAISE INFO '%', lv_list[1].IN_EFFECT;
RAISE INFO '%', lv_list[1].SUB_LOC_C;
RAISE INFO '%', lv_list[1].START_DT;
RAISE INFO '%', lv_list[1].END_DT;
RAISE INFO '%', lv_list[1].SLOC_PHRASE_N;
lv_list[2] = ROW('D','Tecore',sysdate(),sysdate()+1, 'MIG');
RAISE INFO '%', lv_list[2].IN_EFFECT;
RAISE INFO '%', lv_list[2].SUB_LOC_C;
RAISE INFO '%', lv_list[2].START_DT;
RAISE INFO '%', lv_list[2].END_DT;
RAISE INFO '%', lv_list[2].SLOC_PHRASE_N;
end$$;
==
--tested the anonymous block and returns values correctly.
zdcqpoc=> do
zdcqpoc-> $$
zdcqpoc$> declare
zdcqpoc$> lv_list SLOC_TB[];
zdcqpoc$> idx record;
zdcqpoc$> begin
zdcqpoc$> lv_list[1] = ROW('X','XYZ',sysdate(),sysdate()+1, 'abc');
zdcqpoc$> RAISE INFO '%', lv_list[1].IN_EFFECT;
zdcqpoc$> RAISE INFO '%', lv_list[1].SUB_LOC_C;
zdcqpoc$> RAISE INFO '%', lv_list[1].START_DT;
zdcqpoc$> RAISE INFO '%', lv_list[1].END_DT;
zdcqpoc$> RAISE INFO '%', lv_list[1].SLOC_PHRASE_N;
zdcqpoc$> lv_list[2] = ROW('D','Tecore',sysdate(),sysdate()+1, 'MIG');
zdcqpoc$> RAISE INFO '%', lv_list[2].IN_EFFECT;
zdcqpoc$> RAISE INFO '%', lv_list[2].SUB_LOC_C;
zdcqpoc$> RAISE INFO '%', lv_list[2].START_DT;
zdcqpoc$> RAISE INFO '%', lv_list[2].END_DT;
zdcqpoc$> RAISE INFO '%', lv_list[2].SLOC_PHRASE_N;
zdcqpoc$> end$$;
INFO: X
INFO: XYZ
INFO: 2020-09-08 03:29:52
INFO: 2020-09-09 03:29:52
INFO: abc
INFO: D
INFO: Tecore
INFO: 2020-09-08 03:29:52
INFO: 2020-09-09 03:29:52
INFO: MIG
DO
But a problem arises when we want to assign any value to a specific column
to array type.
In Oracle we mostly do this way :
lv_list[2].START_DT := sysdate +1;
But above does not work in PostgreSQL. It says syntax error at ".".
What would be the method for this type of single element assignment in an
array created from composite type.
*Best Regards,*
Jagmohan
Re: Oracle Associate Array conversion to PostgreSQL
Hi
čt 10. 9. 2020 v 7:12 odesílatel JAGMOHAN KAINTURA <
jagmohan.kaint...@gmail.com> napsal:
> Hi All,
>
> Since we don't have the support of Associative arrays in Postgres, we need
> to convert Oracle associative arrays with a different approach in
> PostgreSQL.
>
> Following is a sample piece of code to demonstrate the same.
>
> --Convert Oracle RECORD type to PostgreSQL TYPE.
> --=
> create type SLOC_TB as ( IN_EFFECT VARCHAR(1),
> SUB_LOC_C VARCHAR(6),
> START_DT timestamp(0),
> END_DT timestamp(0),
> SLOC_PHRASE_N VARCHAR(5)
> );
> ---
>
> --Below is simple anonymous block, where TYPE is used as an ARRAY ([]).
> TYPE is used as an array object to allow storing multiple rows/records
> supported by Index (like index by binary integer in Oracle).
>
> do
> $$
> declare
> lv_list SLOC_TB[];
> idx record;
> begin
> lv_list[1] = ROW('X','XYZ',sysdate(),sysdate()+1, 'abc');
> RAISE INFO '%', lv_list[1].IN_EFFECT;
> RAISE INFO '%', lv_list[1].SUB_LOC_C;
> RAISE INFO '%', lv_list[1].START_DT;
> RAISE INFO '%', lv_list[1].END_DT;
> RAISE INFO '%', lv_list[1].SLOC_PHRASE_N;
> lv_list[2] = ROW('D','Tecore',sysdate(),sysdate()+1, 'MIG');
> RAISE INFO '%', lv_list[2].IN_EFFECT;
> RAISE INFO '%', lv_list[2].SUB_LOC_C;
> RAISE INFO '%', lv_list[2].START_DT;
> RAISE INFO '%', lv_list[2].END_DT;
> RAISE INFO '%', lv_list[2].SLOC_PHRASE_N;
> end$$;
>
> ==
> --tested the anonymous block and returns values correctly.
>
> zdcqpoc=> do
> zdcqpoc-> $$
> zdcqpoc$> declare
> zdcqpoc$> lv_list SLOC_TB[];
> zdcqpoc$> idx record;
> zdcqpoc$> begin
> zdcqpoc$> lv_list[1] = ROW('X','XYZ',sysdate(),sysdate()+1, 'abc');
> zdcqpoc$> RAISE INFO '%', lv_list[1].IN_EFFECT;
> zdcqpoc$> RAISE INFO '%', lv_list[1].SUB_LOC_C;
> zdcqpoc$> RAISE INFO '%', lv_list[1].START_DT;
> zdcqpoc$> RAISE INFO '%', lv_list[1].END_DT;
> zdcqpoc$> RAISE INFO '%', lv_list[1].SLOC_PHRASE_N;
> zdcqpoc$> lv_list[2] = ROW('D','Tecore',sysdate(),sysdate()+1, 'MIG');
> zdcqpoc$> RAISE INFO '%', lv_list[2].IN_EFFECT;
> zdcqpoc$> RAISE INFO '%', lv_list[2].SUB_LOC_C;
> zdcqpoc$> RAISE INFO '%', lv_list[2].START_DT;
> zdcqpoc$> RAISE INFO '%', lv_list[2].END_DT;
> zdcqpoc$> RAISE INFO '%', lv_list[2].SLOC_PHRASE_N;
> zdcqpoc$> end$$;
> INFO: X
> INFO: XYZ
> INFO: 2020-09-08 03:29:52
> INFO: 2020-09-09 03:29:52
> INFO: abc
> INFO: D
> INFO: Tecore
> INFO: 2020-09-08 03:29:52
> INFO: 2020-09-09 03:29:52
> INFO: MIG
> DO
>
> But a problem arises when we want to assign any value to a specific column
> to array type.
> In Oracle we mostly do this way :
>lv_list[2].START_DT := sysdate +1;
>
> But above does not work in PostgreSQL. It says syntax error at ".".
>
> What would be the method for this type of single element assignment in an
> array created from composite type.
>
Unfortunately, the direct update is not possible. You need use helper
variable
create type tt as (a int, b int);
do $$
declare
x tt[] = ARRAY[(10,20),(30,40)];
_x tt;
begin
_x := x[1];
_x.a := 100;
x[1] := _x;
raise notice '%', x;
end;
$$;
you can read a field of an item of an array of some composite type with
parenthesis - (x[1]).a, but this syntax is not supported on the left part
of the assign statement.
Regards
Pavel
>
>
> *Best Regards,*
> Jagmohan
>
