Re: jsonb_set() strictness considered harmful to data
On 10/20/19 11:07 PM, Tomas Vondra wrote: On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: True. And AFAIK catching exceptions is not really possible in some code, e.g. in stored procedures (because we can't do subtransactions, so no exception blocks). Can you explain the above to me as I thought there are exception blocks in stored functions and now sub-transactions in stored procedures. -- Adrian Klaver adrian.kla...@aklaver.com
Re: jsonb_set() strictness considered harmful to data
On 10/21/19 12:50 PM, Tomas Vondra wrote: On Mon, Oct 21, 2019 at 08:06:46AM -0700, Adrian Klaver wrote: On 10/20/19 11:07 PM, Tomas Vondra wrote: On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: True. And AFAIK catching exceptions is not really possible in some code, e.g. in stored procedures (because we can't do subtransactions, so no exception blocks). Can you explain the above to me as I thought there are exception blocks in stored functions and now sub-transactions in stored procedures. Sorry for the confusion - I've not been particularly careful when writing that response. Let me illustrate the issue with this example: CREATE TABLE t (a int); CREATE OR REPLACE PROCEDURE test() LANGUAGE plpgsql AS $$ DECLARE msg TEXT; BEGIN -- SAVEPOINT s1; INSERT INTO t VALUES (1); -- COMMIT; EXCEPTION WHEN others THEN msg := SUBSTR(SQLERRM, 1, 100); RAISE NOTICE 'error: %', msg; END; $$; CALL test(); If you uncomment the SAVEPOINT, you get NOTICE: error: unsupported transaction command in PL/pgSQL because savepoints are not allowed in stored procedures. Fine. If you uncomment the COMMIT, you get NOTICE: error: cannot commit while a subtransaction is active which happens because the EXCEPTION block creates a subtransaction, and we can't commit when it's active. But we can commit outside the exception block: CREATE OR REPLACE PROCEDURE test() LANGUAGE plpgsql AS $$ DECLARE msg TEXT; BEGIN BEGIN INSERT INTO t VALUES (1); EXCEPTION WHEN others THEN msg := SUBSTR(SQLERRM, 1, 100); RAISE NOTICE 'error: %', msg; END; COMMIT; END; $$; You can do something like the below though: CREATE TABLE t (a int PRIMARY KEY); CREATE OR REPLACE PROCEDURE public.test() LANGUAGE plpgsql AS $procedure$ DECLARE msg TEXT; BEGIN BEGIN INSERT INTO t VALUES (1); EXCEPTION WHEN others THEN msg := SUBSTR(SQLERRM, 1, 100); RAISE NOTICE 'error: %', msg; UPDATE t set a = 2; END; COMMIT; END; $procedure$ test_(postgres)# CALL test(); CALL test_(postgres)# select * from t; a --- 1 (1 row) test_(postgres)# CALL test(); NOTICE: error: duplicate key value violates unique constraint "t_pkey" CALL test_(postgres)# select * from t; a --- 2 (1 row) regards -- Adrian Klaver adrian.kla...@aklaver.com
Re: Alternate methods for multiple rows input/output to a function.
On 5/28/19 7:36 AM, RAJIN RAJ K wrote: --> Function ' filter_id ' filters the ID's based on some conditions. --> Input is set of ID's. (Not directly taking the input since there is no provision to pass multiple rows to a function) To be honest I cannot follow what you are trying to achieve below. I do have one suggestion as to creating temp tables. Why not use a CTE: https://www.postgresql.org/docs/11/queries-with.html in the function to build a 'temp' table on the fly? create function filter_id() return table (id bigint) begin --> Assuming input table is already created #temp_input_id retun query as select id from tbl a inner join #temp_input_id b on (a.id <http://a.id> = b.id <http://b.id>) where a.; end; --> Calling Function: create function caller() return table (id bigint,col1 bigint, col2 bigint) begin --> do some processing --> Find out the input id's for filtering. --> Create temp table for providing input for the filtering function create temp table #TEMP1 as select id from tbla; (Cannot move the input id logic to filter_function) --> calling the filter function create temp table #TEMP2 as select * from filter_id(); --> This is a generic function used in many functions. return query as select a.* from tb3 a inner join tb4 inner join tb 5 inner join #TEMP2; end; Is there any alternate way of achieving this? Passing multiple records to a function im creating a temp table before invoking the function. For receiving an output of multiple rows i'm creating a temp table to reuse further in the code. Can this be done using Refcursor? Is it possible to convert refcursor to a temp table and use it as normal table in query? -- Adrian Klaver adrian.kla...@aklaver.com
Re: Problem about partitioned table
On 10/19/18 2:03 AM, Mehman Jafarov wrote: Hi everyone, I have a problem with partitioned table in PostgreSql. Actually I use the version 10. I created the partitioned table in test environment but face some problems with partitioned table constraint. I google all about it last week and from the official site I get that version 11 will be released and that feature will be supported as I understand it. From version 11 documentation "/Add support for |PRIMARY KEY|, |FOREIGN KEY|, indexes, and triggers on partitioned tables/" I install and configure yesterday as new 11 version released. And test it. Unfortunately I didn't achieve again. Neither I don't understand the new feature nor this case is actually not supported. Please help me about the problem. As you found out: https://www.postgresql.org/docs/11/static/ddl-partitioning.html 5.10.2.3. Limitations "While primary keys are supported on partitioned tables, foreign keys referencing partitioned tables are not supported. (Foreign key references from a partitioned table to some other table are supported.)" Note: I want to create constraint only one-to-one column (/fk_to_user_doc_rel - oid/) BIG QUESTION IS THAT How can I solve this problem? What is your recommendations? Well a FK is a form of a trigger, so maybe create your own trigger on the child table(s). *PLEASE HELP ME !!!* -- */Best Regards,/* */Mehman Jafarov/* */DBA Aministrator at CyberNet LLC/* */ /* -- Adrian Klaver adrian.kla...@aklaver.com
Re: gitmaster server problem?
On 8/19/24 07:27, Tom Lane wrote: Magnus Hagander writes: Right at the moment, https://git.postgresql.org/gitweb/?p=postgresql.git is failing for me with "Backend fetch failed". However, the mail I just tried it and it worked. archives, commitfest.p.o, and wiki.p.o seem to be responding normally, as is gitmaster. regards, tom lane -- Adrian Klaver adrian.kla...@aklaver.com
Re: Code of Conduct plan
On 06/03/2018 11:29 AM, Tom Lane wrote: Two years ago, there was considerable discussion about creating a Code of Conduct for the Postgres community, as a result of which the core team announced a plan to create an exploration committee to draft a CoC [1]. That process has taken far longer than expected, but the committee has not been idle. They worked through many comments and many drafts to produce a version that seems acceptable in the view of the core team. This final(?) draft can be found at https://wiki.postgresql.org/wiki/Code_of_Conduct We are now asking for a final round of community comments. Please send any public comments to the pgsql-general list (only). If you wish to make a private comment, you may send it to c...@postgresql.org. The initial membership of the CoC committee will be announced separately, but shortly. Unless there are substantial objections, or nontrivial changes as a result of this round of comments, we anticipate making the CoC official as of July 1 2018. My comments: 1) Reiterate my contention that this is a solution is search of problem. Still it looks like it is going forward, so see below. 2) "... engaging in behavior that may bring the PostgreSQL project into disrepute, ..." This to me is overly broad and pulls in actions that may happen outside the community. Those if they are actually an issue should be handled where they occur not here. 3) "... members must be sensitive to conduct that may be considered offensive by fellow members and must refrain from engaging in such conduct. " Again overly broad, especially given the hypersensitivity of people these days. I have found that it is enough to disagree with someone to have it called offensive. This section should be removed as proscribed behavior is called out in detail in the paragraphs above it. regards, tom lane [1] https://www.postgresql.org/message-id/56a8516b.8000...@agliodbs.com -- Adrian Klaver adrian.kla...@aklaver.com
Re: Code of Conduct plan
On 9/14/18 1:31 AM, Chris Travers wrote: On Wed, Sep 12, 2018 at 10:53 PM Tom Lane <mailto:t...@sss.pgh.pa.us>> wrote: I wrote: > Stephen Frost mailto:sfr...@snowman.net>> writes: >> We seem to be a bit past that timeline... Do we have any update on when >> this will be moving forward? >> Or did I miss something? > Nope, you didn't. Folks have been on holiday which made it hard to keep > forward progress going, particularly with respect to selecting the initial > committee members. Now that Magnus is back on shore, I hope we can > wrap it up quickly --- say by the end of August. I apologize for the glacial slowness with which this has all been moving. The core team has now agreed to some revisions to the draft CoC based on the comments in this thread; see https://wiki.postgresql.org/wiki/Code_of_Conduct (That's the updated text, but you can use the diff tool on the page history tab to see the changes from the previous draft.) I really have to object to this addition: "This Code is meant to cover all interaction between community members, whether or not it takes place within postgresql.org <http://postgresql.org> infrastructure, so long as there is not another Code of Conduct that takes precedence (such as a conference's Code of Conduct)." I second that objection. It is not in PGDG's remit to cure the world, for whatever form of cure you ascribe to. This is especially true as 'community member' has no strict definition. That covers things like public twitter messages over live political controversies which might not be personally directed. At least if one is going to go that route, one ought to *also* include a safe harbor for non-personally-directed discussions of philosophy, social issues, and politics. Otherwise, I think this is asking for trouble. See, for example, what happened with Opalgate and how this could be seen to encourage use of this to silence political controversies unrelated to PostgreSQL. I think we are about ready to announce the initial membership of the CoC committee, as well, but that should be a separate post. regards, tom lane -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more -- Adrian Klaver adrian.kla...@aklaver.com
Re: Code of Conduct plan
On 9/14/18 6:59 AM, Robert Eckhardt wrote: On Fri, Sep 14, 2018 at 9:41 AM, Adrian Klaver wrote: On 9/14/18 1:31 AM, Chris Travers wrote: I really have to object to this addition: "This Code is meant to cover all interaction between community members, whether or not it takes place within postgresql.org <http://postgresql.org> infrastructure, so long as there is not another Code of Conduct that takes precedence (such as a conference's Code of Conduct)." I second that objection. It is not in PGDG's remit to cure the world, for whatever form of cure you ascribe to. This is especially true as 'community member' has no strict definition. I understand the concern, however, if you look at how attacks happen it is frequently through other sites. Specifically under/poorly moderated sites. For specific examples, people who have issues with people on Quora will frequently go after them on Facebook and Twitter. these aren't a solution looking for a problem. If we just want to look at the clusterfuck that is happening in the reddis community right now we can see conversations spilling onto twitter and into ad hominem vitriol. Ask yourself, if this was a government agency tracking your speech across platforms would you be as approving? Personally I find the whole thing creepy. My $0.02 -- Rob Eckhardt Adrian Klaver adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
Re: Code of Conduct plan
On 9/14/18 7:19 AM, Dave Page wrote: No one is tracking anything as part of the CoC. That's nothing but a straw man argument. Not buying it or the below is null and void: "This Code is meant to cover all interaction between community members, whether or not it takes place within postgresql.org infrastructure, so long as there is not another Code of Conduct that takes precedence (such as a conference's Code of Conduct)." Not sure how the above can be enforced without someone reporting on what is said outside the 'postgresql.org infrastructure'? At any rate, whether I like it or not the CoC is here to stay. I just feel a dissenting opinion is important to the conversation. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Adrian Klaver adrian.kla...@aklaver.com
Re: Code of Conduct plan
On 9/14/18 11:13 AM, Robert Haas wrote: On Fri, Sep 14, 2018 at 11:10 AM, Dave Page wrote: That wording has been in the published draft for 18 months, and noone objected to it that I'm aware of. There will always be people who don't like some of the wording, much as there are often people who disagree with the way a patch to the code is written. Sooner or later though, the general consensus prevails and we have to move on, otherwise nothing will ever get completed. It's not clear to me that there IS a general consensus here. It looks to me like the unelected core team got together and decided to impose a vaguely-worded code of conduct on a vaguely-defined group of people covering not only their work on PostgreSQL but also their entire life. It is not difficult to imagine that someone's private life might include "behavior that may bring the PostgreSQL project into disrepute." However, I also don't think it matters very much. The Code of Conduct Committee is going to consist of small number of people -- at least four, perhaps a few more. But there are hundreds of people involved on the PostgreSQL mailing lists, maybe thousands. If the Code of Conduct Committee, or the core team, believes that it can impose on a very large group of people, all of whom are volunteers, some set of rules with which they don't agree, it's probably going to find out pretty quickly that it is mistaken. If people from that large group get banned for behavior which is perceived by other members of that large group to be legitimate, then there will be a ferocious backlash. Nobody wants to see people who are willing to contribute driven away from the project, and anyone we drive away without a really good reason will find some other project that welcomes their participation. So the only thing that the Code of Conduct Committee is likely to be able to do in practice is admonish people to be nicer (which is probably a good thing) and punish really egregious conduct, especially when committed by people who aren't involved enough that their absence will be keenly felt. In practice, therefore, democracy is going to win out. That's both good and bad. It's good because nobody wants a CoC witch-hunt, and it's bad because there's probably some behavior which legitimately deserves censure and will escape it. +1 -- Adrian Klaver adrian.kla...@aklaver.com
