Reg. GET STACKED DIAGNOSTICS values to be stored in a single variable
Sir/madam, While exception handling, we have to write multiple repeated lines in each function to handle the GET STACKED DIAGNOSTICS values. it would be better if all items in the diagnostics may be assigned to a single variable/object. at least like GET STACKED DIAGNOSTICS v_state := RETURNED_SQLSTATE || MESSAGE_TEXT || PG_EXCEPTION_DETAIL || PG_EXCEPTION_HINT || PG_EXCEPTION_CONTEXT; Now we have to declare more than 5 variables to store the GET STACKED DIAGNOSTICS values and pass it to another function to record the error log. if it is in a single variable/object then it will reduce the code. -- Ragards Maheswaran R Krishnagiri
Re: Reg. GET STACKED DIAGNOSTICS values to be stored in a single variable
Hi čt 7. 4. 2022 v 11:58 odesílatel Maheswaran R napsal: > Sir/madam, > While exception handling, we have to write multiple repeated lines in each > function to handle the GET STACKED DIAGNOSTICS values. it would be better > if all items in the diagnostics may be assigned to a single > variable/object. > at least like > GET STACKED DIAGNOSTICS v_state := RETURNED_SQLSTATE || MESSAGE_TEXT || > PG_EXCEPTION_DETAIL || PG_EXCEPTION_HINT || PG_EXCEPTION_CONTEXT; > > Now we have to declare more than 5 variables to store the GET STACKED > DIAGNOSTICS values and pass it to another function to record the error log. > if it is in a single variable/object then it will reduce the code. > It can be a good idea, but surely with different syntax. Maybe this statement can be enhanced to use record variable as target, and then the usual syntax for composite value is GET STACKED DIAGNOSTICS r := RETURNED_SQLSTATE, MESSAGE_TEXT, PG_EXCEPTION_DETAIL, ... I am not sure if this mailing list is good for proposing some new features - https://wiki.postgresql.org/wiki/Developer_FAQ Regards Pavel -- > Ragards > Maheswaran R > Krishnagiri >
Re: What have I done!?!?!? :-)
On 4/6/22 18:25, Perry Smith wrote: Rather than explain how I got here, I’ll just explain the state I’m in. ... I’m using Active Record with the psql adapter. It has a disable_referential_integrity which takes a block of code. When the block of code exists, the constraints are put back. At least, that is what I thought. I’m wondering if the disabled constraints are still disabled somehow. If so, how would I check for that and how would I turn them back on? Or am I way off in the weeds? That depends on how exactly Active Record does this disabling of constraints. There is a GUC in PostgreSQL 'session_replication_role'. Setting that to value 'replica' will do precisely that as a side effect. Its primary purpose is for logical replication systems (like Londiste, Slony and logical decoding based ones) to disable user triggers and referential integrity actions (like on delete cascade) as well as integrity checking under the assumption that those actions have been performed on the origin database and will be replicated as well or are unnecessary. Note that changing that setting requires PostgreSQL superuser privilege. Precisely because of the danger of getting your database into an inconsistent state. So **IF** Active Record is using that feature, then it can dump any amount of garbage into your PostgreSQL database and PostgreSQL will happily accept it with zero integrity checking. Best Regards, Jan
Re: Resources on modeling ordered hierachies?
On 2022-04-07 17:45:49 +1200, Tim Uckun wrote: > There a tons of articles about how to model hierarchies in SQL but I > haven't seen any about dealing with hierarchies where the order of > children is important. > > The canonical example is a simple outline > > 1. > 1.1 > 1.1.1 > 1.2 > 2. > 2.1 > > etc > > If I am doing an insert where parent is 1.1 it should name it 1.1.2 > which to me means doing something like select max(id) + 1 where parent > = 1.1 or something like that which might turn out to be expensive. Shouldn't be that bad with an appropriate index. Bigger problem might be that two transactions could attempt this at the same time. > Similarly if I want to insert something between 1.1 and 1.2 I need to > do something like update id set id = id+1 where parent = 1 and id >1 You can mostly get around that by using float8 or even numeric instead of int. Chances are that there is a free number between you numbers. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Resources on modeling ordered hierachies?
On 4/7/22 10:55, Peter J. Holzer wrote: On 2022-04-07 17:45:49 +1200, Tim Uckun wrote: There a tons of articles about how to model hierarchies in SQL but I haven't seen any about dealing with hierarchies where the order of children is important. The canonical example is a simple outline 1. 1.1 1.1.1 1.2 2. 2.1 etc If I am doing an insert where parent is 1.1 it should name it 1.1.2 which to me means doing something like select max(id) + 1 where parent = 1.1 or something like that which might turn out to be expensive. Shouldn't be that bad with an appropriate index. Bigger problem might be that two transactions could attempt this at the same time. Similarly if I want to insert something between 1.1 and 1.2 I need to do something like update id set id = id+1 where parent = 1 and id >1 You can mostly get around that by using float8 or even numeric instead of int. Chances are that there is a free number between you numbers. hp Is there any chance the 1/1.1/1.1.1 stuff can be cosmetic/generated? Corollary: what determines the hierarchical position of the next record? Each item could know its parent (nullable) and it next-sib (nullable). If, as your example might suggest, you are always adding to the end, then you're updating the last sib's null next-sib point and the incoming record has next-sib null. All the single numbers are parentless, 2 is next sib of 1. Trickier to interject a new record between to sibs but not impractical. Can move sibships up/down hierarchy by updating parent and resetting next-sib of last in moved sibship and the next-sib of the injection point to the head of the move sibs.
psql removes dashed comments
Hi, I was wondering why psql loose dashed comments and what can be done about this misbehaviour. I get often some sql, paste it into psql run and edit it via \e. A few iterations until everything works. But psql removes the comments and creates a lot extra work to restore the comments and changes to the query. Here is an example: # start psql, paste something run it, edit \e, rerun and so on. At the end all dashed comments are removed. psql -Xe postgres psql (14.1) Type "help" for help. postgres=# select 1, -- one 2, /* two */ 3 -- three ; select 1, 2, /* two */ 3 ; ?column? | ?column? | ?column? --+--+-- 1 |2 |3 (1 row) postgres=# \e select 1, 2, /* two */ 3 ; ?column? | ?column? | ?column? --+--+-- 1 |2 |3 (1 row) -- Boris
Re: psql removes dashed comments
On 4/7/22 11:25, Boris Zentner wrote: Hi, I was wondering why psql loose dashed comments and what can be done about this misbehaviour. See this recent thread: https://www.postgresql.org/message-id/265623A4-F304-4E68-90D0-343F614DB2B7%40americanefficient.com -- Boris -- Adrian Klaver adrian.kla...@aklaver.com
Re: psql removes dashed comments
On Thursday, April 7, 2022, Boris Zentner wrote: > > I was wondering why psql loose dashed comments and what can be done about > this misbehaviour. > > postgres=# \e > > Because \e treats the query buffer as a single line of code and dashed comments cannot be used, just like meta-commands cannot be used. Including a filename should establish the behavior you desire. David J.
Re: psql removes dashed comments
On Thursday, April 7, 2022, Boris Zentner wrote: > Hi, > > I was wondering why psql loose dashed comments and what can be done about > this misbehaviour. > > # start psql, paste something run it, edit \e, rerun and so on. At the end > all dashed comments are removed. > > > psql -Xe postgres > psql (14.1) > Type "help" for help. > > postgres=# select 1, -- one > 2, /* two */ > 3 -- three > ; > select 1, > 2, /* two */ > 3 > ; > ?column? | ?column? | ?column? > --+--+-- > 1 |2 |3 > (1 row) > > postgres=# \e > select 1, > 2, /* two */ > 3 > ; > ?column? | ?column? | ?column? > --+--+-- > 1 |2 |3 > (1 row) > > > My last comment seems a bit off, but all you’ve done here is demonstrate the documented behavior when the query buffer contains multiple commands when \e is executed. In either case this is working as documented and you really should be using a permanent file for this kind of thing. David J.
Re: psql removes dashed comments
> I was wondering why psql loose dashed comments and what can be done about > this misbehaviour. > > postgres=# \e > > > Because \e treats the query buffer as a single line of code and dashed > comments cannot be used, just like meta-commands cannot be used. > > Including a filename should establish the behavior you desire. > Thanks David! \e filename does indeed help a bit but the file must exists and I have to type and invent the filename before I start. My workaround was \! vim xyz.sql but it is not satisfying. Without thinking to depth about this, maybe a new meta command could help? Something like \eee to fill an initially empty temporary file from the query buffer on the first call and reuse it over the runtime of the psql command.
Re: psql removes dashed comments
Adrian Klaver writes: > On 4/7/22 11:25, Boris Zentner wrote: >> I was wondering why psql loose dashed comments and what can be done about >> this misbehaviour. > See this recent thread: > https://www.postgresql.org/message-id/265623A4-F304-4E68-90D0-343F614DB2B7%40americanefficient.com This might be more on-point: https://www.postgresql.org/message-id/flat/CAJcOf-cAdMVr7azeYR7nWKsNp7qhORzc84rV6d7m7knG5Hrtsw%40mail.gmail.com regards, tom lane
Re: What have I done!?!?!? :-)
On Thu, Apr 7, 2022 at 8:10 AM Jan Wieck wrote: > So **IF** Active Record is using that feature, then it can dump any > amount of garbage into your PostgreSQL database and PostgreSQL will > happily accept it with zero integrity checking. > It's DISABLE TRIGGER ALL https://github.com/rails/rails/blob/831031a8cec5bfe59ef653ae2857d4fe64c5698d/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb#L12
