Re: PostgreSQL - unrecognized win32 error code: 38
On Mon, Oct 28, 2019 at 09:51:07AM -0700, ZhenHua Cai wrote: > The following is the SQL statement of that function. > > DROP TABLE IF EXISTS "GenericReadList" ; > CREATE TEMP TABLE "GenericReadList"( > "ComputerProfileId" int NOT NULL, > "Trustee" uuid NOT NULL, > "AccessControlType" smallint NULL, > "AceOrder" int NULL > ); The original complain comes from a function called ComputeComputer. What does it do and where does it come from? Does it call any in-core code which would cause the failure. If the function is a SQL function, could you provide a self-contained test case? -- Michael signature.asc Description: PGP signature
Re: SQL pretty pritner?
https://www.sqlinform.com/ Quoting stan : I have a presentation later in the week, and i plan on printing out some files containing SQL commands. I have used some "pretty printers" in the past for things like Perl scripts. What I am thinking of s something that bolds keywords, handles page breaks, and does some formatting. Development being done in an Ubuntu Linux environment. Anyone have a recommendation? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin -- S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH Signal (Safer than WhatsApp): +49 1578 7723737 Handys: +41 78 947 36 21 | +49 1578 772 37 37 Tox-Id: B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B
Re: SQL pretty pritner?
Hello, For some formatting I use PSPad.exe (https://www.pspad.com/) It has, at least, some of the features you are looking for. It is free and has the possibility to format SQL and other languages. Dias Costa On 28-10-2019 17:06, Reid Thompson wrote: On Sun, 2019-10-27 at 16:54 -0700, Adrian Klaver wrote: https://urldefense.proofpoint.com/v2/url?u=https-3A__sourceforge.net_projects_pgformatter_=DwICaQ=w8fEmIi-B5lsC0MnOiGTloUx5YmN70-3jVCmqqbXNxM=4r1gVE34nFB7YgioINuzq1cdGrlIBszDh26sKYP7ux8=9zYVbOR6UMXoTr5abTczqiDnMEYJ1mNU7uePMEtYLJ8=7jw7I_b0hNpBHZAfA2NLtvgbWepTlS5zqSGEt6xq9IA= this ^^^ -- J. M. Dias Costa Telef. 214026948 Se divulgar esta mensagem por terceiros, por favor: 1. Apague o meu endereço de correio electrónico e o meu nome. 2. Apague também os endereços dos seus amigos antes de distribuir. 3. Enderece como cópia oculta (Cc ou Bcc) para os SEUS destinatários. Agindo deste modo, dificultará a disseminação de "vírus", "spams" e "banners" e contribuirá para manter a privacidade de todos e cada um. Obrigado. Nota: Não se deverá ao acaso a ocorrência de palavras na minha escrita que não respeitem o malfadado acordo ortográfico.
Re: PostgreSQL - unrecognized win32 error code: 38
The following is the SQL statement of that function. DROP TABLE IF EXISTS "GenericReadList" ; CREATE TEMP TABLE "GenericReadList"( "ComputerProfileId" int NOT NULL, "Trustee" uuid NOT NULL, "AccessControlType" smallint NULL, "AceOrder" int NULL ); INSERT INTO "GenericReadList" SELECT t1."ComputerProfileId", t1."Trustee", t1."AccessControlType", t1."AceOrder" FROM ( SELECT a."ComputerProfileId", s."ADUser" "Trustee", a."AccessControlType", a."AceOrder" FROM "TmpResult" r INNER JOIN "TmpTrusteeSid" s ON s."ADUser" = r."Trustee" INNER JOIN "AccessRights" a ON a."TrusteeSid" = s."Sid" AND r."ComputerProfileId" = a."ComputerProfileId" AND a."AccessRight" = 4 ) t1 Thanks Jacky On Sun, Oct 27, 2019 at 7:45 AM Tom Lane wrote: > Michael Paquier writes: > > On Sat, Oct 26, 2019 at 11:02:26AM -0700, Adrian Klaver wrote: > >> Not sure how that can be answered without knowing what ComputeComputer > is > >> doing? > > > Yes, there is nothing of this kind in the PostgreSQL code. > > Sure there is: win32error.c produces exactly that message if its > mapping table has no entry for the Windows error code. > > According to > > > https://docs.microsoft.com/en-us/windows/win32/debug/system-error-codes--0-499-?redirectedfrom=MSDN > > code 38 decimal is ERROR_HANDLE_EOF, "Reached the end of the file.", > which indeed is not in the doserrors[] table. > > But that just deepens the mystery --- if we hit an EOF, why wasn't > that handled through more-normal channels? We still need to know > what that function was trying to do, and whether any non-core > C code was involved. > > regards, tom lane >
Re: SQL pretty pritner?
On Sun, 2019-10-27 at 16:54 -0700, Adrian Klaver wrote: > https://urldefense.proofpoint.com/v2/url?u=https-3A__sourceforge.net_projects_pgformatter_=DwICaQ=w8fEmIi-B5lsC0MnOiGTloUx5YmN70-3jVCmqqbXNxM=4r1gVE34nFB7YgioINuzq1cdGrlIBszDh26sKYP7ux8=9zYVbOR6UMXoTr5abTczqiDnMEYJ1mNU7uePMEtYLJ8=7jw7I_b0hNpBHZAfA2NLtvgbWepTlS5zqSGEt6xq9IA= this ^^^
RE: SQL pretty pritner?
>From: Adrian Klaver >On 10/27/19 3:21 PM, stan wrote: >> I have a presentation later in the week, and i plan on printing out >> some files containing SQL commands. I have used some "pretty printers" >> in the past for things like Perl scripts. What I am thinking of s >> something that bolds keywords, handles page breaks, and does some formatting. > >http://sqlformat.darold.net/ > >https://sourceforge.net/projects/pgformatter/ If I had that task, I'd use a combo of tools. "pgFormatter" that Adrian shows above is what you use to reformat to make it aligned nicely, plus it does some colorization. The first link is the online tool; the 2nd link is if you want to download the code to your own server. Example: select count(*) from tablea where x is null; Result: SELECT count(*) FROM tablea WHERE x IS NULL; You can't see it here, but all the keywords are bold and colored, and "count" is colored but not bolded. If that colorization isn't good enough for you, then as others have said, paste the result into "vim" to get the colorization, pasting that into PowerPoint/Word or something that honors the colors...or just take a screen shot of that...or whatever works for you. HTH, Kevin This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.
Re: I think that my data is saved correctly, but when printing again, other data appears
Thank you very much for the answer. Best regards, Yessica Brinkmann El lun., 28 oct. 2019 a las 8:03, Joe Conway () escribió: > On 10/27/19 8:01 PM, Yessica Brinkmann wrote: > > Thank you so much for the answers. > > By telling me this: "MemoryContextStrdup enables you to create a copy of > > a string in an explicitly specified memory context." I better understood > > the function of MemoryContextStrdup. > > And thank you very much to Mr. Jony Cohen for giving me the reference of > > his work. I really was already researching about his work, and I > > included it in the state of the art of my thesis, but I didn't look at > > the source code. > > I will be looking at the source code for a help, and especially in this > > case to see for the moment, the maintenance of the context for its > > subsequent restoration. > > For better understanding of how Postgres manages memory, you might want > to also read this: > > > https://github.com/postgres/postgres/blob/master/src/backend/utils/mmgr/README > > and possibly browse through this: > > > https://github.com/postgres/postgres/blob/master/src/backend/utils/mmgr/mcxt.c > > HTH, > > Joe > -- > Crunchy Data - http://crunchydata.com > PostgreSQL Support for Secure Enterprises > Consulting, Training, & Open Source Development > >
Re: jsonb_set() strictness considered harmful to data
On Mon, Oct 28, 2019, at 08:52, Andrew Dunstan wrote: > > For release 13+, I have given some more thought to what should be done. > I think the bar for altering the behaviour of a function should be > rather higher than we have in the present case, and the longer the > function has been sanctioned by time the higher the bar should be. > However, I think there is a case to be made for providing a non-strict > jsonb_set type function. To advance th4e discussion, attached is a POC > patch that does that. This can also be done as an extension, meaning > that users of back branches could deploy it immediately. I've tested > this against release 12, but I think it could go probably all the way > back to 9.5. The new function is named jsonb_ set_lax, but I'm open to > bikeshedding. > > Thank you Andrew, and I understand the difficulty in making changes to functions that already exist in production deployments. An additional function like this would be helpful to many. -- Mark Felder ports-secteam & portmgr alumni f...@freebsd.org
Re: SQL pretty pritner?
All, VI has a lot of option ( that I haven’t tried) and I would bet that there are options for formatting and page breaks, etc. It’s a bit of a learning curve though. bobb > On Oct 28, 2019, at 8:44 AM, Rob Sargent wrote: > > Think Before You Click: This email originated outside our organization. > > >> On Oct 28, 2019, at 7:37 AM, Ron wrote: >> >> But does it reformat the text? >> > It’s an “indented text mode” so beeline respect current indentation. I don’t > think it will take an stream out sql and add newlines at predictable places. > Not at My desk to try. > >>> On 10/28/19 8:00 AM, Basques, Bob (CI-StPaul) wrote: >>> All, >>> >>> Take a look at the VI(m) editor. There is a Syntax mode for highlighting >>> different file types, as well as tools for exporting the highlighted text >>> to HTML. I have yet to find a file type that it doesn’t already handle. >>> >>> Works really slick. >>> >>> bobb >>> >>> >>> > On Oct 27, 2019, at 5:21 PM, stan wrote: Think Before You Click: This email originated outside our organization. I have a presentation later in the week, and i plan on printing out some files containing SQL commands. I have used some "pretty printers" in the past for things like Perl scripts. What I am thinking of s something that bolds keywords, handles page breaks, and does some formatting. Development being done in an Ubuntu Linux environment. Anyone have a recommendation? >>> >> >> -- >> Angular momentum makes the world go 'round. >> >> > >
Re: SQL pretty pritner?
As far using VIM (vs EMACS): https://en.wikipedia.org/wiki/Editor_war :c) bobb On Oct 28, 2019, at 8:33 AM, Rob Sargent mailto:robjsarg...@gmail.com>> wrote: Think Before You Click: This email originated outside our organization. On Oct 28, 2019, at 7:00 AM, Basques, Bob (CI-StPaul) mailto:bob.basq...@ci.stpaul.mn.us>> wrote: All, Take a look at the VI(m) editor. There is a Syntax mode for highlighting different file types, as well as tools for exporting the highlighted text to HTML. I have yet to find a file type that it doesn’t already handle. Works really slick. bobb Well if you’re going to go there, emacs will hilite and execute your sql. On Oct 27, 2019, at 5:21 PM, stan
Re: jsonb_set() strictness considered harmful to data
On 10/21/19 9:28 AM, Andrew Dunstan wrote:
> On 10/21/19 2:07 AM, Tomas Vondra wrote:
>> On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote:
I think the general premise of this thread is that the application
developer does not realize that may be necessary, because it's a bit
surprising behavior, particularly when having more experience with
other
databases that behave differently. It's also pretty easy to not notice
this issue for a long time, resulting in significant data loss.
Let's say you're used to the MSSQL or MySQL behavior, you migrate your
application to PostgreSQL or whatever - how do you find out about this
behavior? Users are likely to visit
https://www.postgresql.org/docs/12/functions-json.html
but that says nothing about how jsonb_set works with NULL values :-(
>>>
>>>
>>> We should certainly fix that. I accept some responsibility for the
>>> omission.
>>>
>> +1
>>
>>
>
> So let's add something to the JSON funcs page like this:
>
>
> Note: All the above functions except for json_build_object,
> json_build_array, json_to_recordset, json_populate_record, and
> json_populate_recordset and their jsonb equivalents are strict
> functions. That is, if any argument is NULL the function result will be
> NULL and the function won't even be called. Particular care should
> therefore be taken to avoid passing NULL arguments to those functions
> unless a NULL result is expected. This is particularly true of the
> jsonb_set and jsonb_insert functions.
>
>
>
> (We do have a heck of a lot of Note: sections on that page)
>
>
For release 13+, I have given some more thought to what should be done.
I think the bar for altering the behaviour of a function should be
rather higher than we have in the present case, and the longer the
function has been sanctioned by time the higher the bar should be.
However, I think there is a case to be made for providing a non-strict
jsonb_set type function. To advance th4e discussion, attached is a POC
patch that does that. This can also be done as an extension, meaning
that users of back branches could deploy it immediately. I've tested
this against release 12, but I think it could go probably all the way
back to 9.5. The new function is named jsonb_ set_lax, but I'm open to
bikeshedding.
cheers
andrew
--
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 9fe4a4794a..bbe5bbf3ae 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1232,6 +1232,15 @@ LANGUAGE INTERNAL
STRICT IMMUTABLE PARALLEL SAFE
AS 'jsonb_set';
+CREATE OR REPLACE FUNCTION
+ jsonb_set_lax(jsonb_in jsonb, path text[] , replacement jsonb,
+create_if_missing boolean DEFAULT true,
+null_value_treatment text DEFAULT 'use_json_null')
+RETURNS jsonb
+LANGUAGE INTERNAL
+CALLED ON NULL INPUT IMMUTABLE PARALLEL SAFE
+AS 'jsonb_set_lax';
+
CREATE OR REPLACE FUNCTION
parse_ident(str text, strict boolean DEFAULT true)
RETURNS text[]
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 3553a304b8..c9ce8e53e9 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -4395,6 +4395,68 @@ jsonb_set(PG_FUNCTION_ARGS)
}
+/*
+ * SQL function jsonb_set_lax(jsonb, text[], jsonb, boolean, text)
+ */
+Datum
+jsonb_set_lax(PG_FUNCTION_ARGS)
+{
+ /* Jsonb *in = PG_GETARG_JSONB_P(0); */
+ /* ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); */
+ /* Jsonb *newval = PG_GETARG_JSONB_P(2); */
+ /* bool create = PG_GETARG_BOOL(3); */
+ text *handle_null;
+ char *handle_val;
+
+ if (PG_ARGISNULL(0) || PG_ARGISNULL(1) || PG_ARGISNULL(3))
+ PG_RETURN_NULL();
+
+ /* could happen if they pass in an explicit NULL */
+ if (PG_ARGISNULL(4))
+ ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("need delete_key, return_target, use_json_null, or raise_exception")));
+
+ if (! PG_ARGISNULL(2))
+ return jsonb_set(fcinfo);
+
+ handle_null = PG_GETARG_TEXT_P(4);
+ handle_val = text_to_cstring(handle_null);
+
+ if (strcmp(handle_val,"raise_exception") == 0)
+ {
+ ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("null jsonb value")));
+ }
+ else if (strcmp(handle_val, "use_json_null") == 0)
+ {
+ Datum newval;
+
+ newval = DirectFunctionCall1(jsonb_in, CStringGetDatum("null"));
+
+ /* XXX can we stomp on fcinfo->args like this? */
+ fcinfo->args[2].value = newval;
+ fcinfo->args[2].isnull = false;
+ return jsonb_set(fcinfo);
+ }
+ else if (strcmp(handle_val, "delete_key") == 0)
+ {
+ return jsonb_delete_path(fcinfo);
+ }
+ else if (strcmp(handle_val, "return_target") == 0)
+ {
+ Jsonb *in = PG_GETARG_JSONB_P(0);
+ PG_RETURN_JSONB_P(in);
+ }
+ else
+ {
+
Re: SQL pretty pritner?
Hello all, I use this github project in order to highlight SQL code on my slides: https://romannurik.github.io/SlidesCodeHighlighter/ It doesn't highlight 100% of Postgres SQL code, but it's a correct basis! Hope this helps, Thomas Le lun. 28 oct. 2019 à 14:37, Ron a écrit : > But does it reformat the text? > > On 10/28/19 8:00 AM, Basques, Bob (CI-StPaul) wrote: > > All, > > > > Take a look at the VI(m) editor. There is a Syntax mode for > highlighting different file types, as well as tools for exporting the > highlighted text to HTML. I have yet to find a file type that it doesn’t > already handle. > > > > Works really slick. > > > > bobb > > > > > > > >> On Oct 27, 2019, at 5:21 PM, stan wrote: > >> > >> Think Before You Click: This email originated outside our organization. > >> > >> > >> I have a presentation later in the week, and i plan on printing out some > >> files containing SQL commands. I have used some "pretty printers" in the > >> past for things like Perl scripts. What I am thinking of s something > that > >> bolds keywords, handles page breaks, and does some formatting. > >> > >> Development being done in an Ubuntu Linux environment. > >> > >> Anyone have a recommendation? > > > > -- > Angular momentum makes the world go 'round. > > >
Re: SQL pretty pritner?
> On Oct 28, 2019, at 7:37 AM, Ron wrote: > > But does it reformat the text? > It’s an “indented text mode” so beeline respect current indentation. I don’t think it will take an stream out sql and add newlines at predictable places. Not at My desk to try. >> On 10/28/19 8:00 AM, Basques, Bob (CI-StPaul) wrote: >> All, >> >> Take a look at the VI(m) editor. There is a Syntax mode for highlighting >> different file types, as well as tools for exporting the highlighted text to >> HTML. I have yet to find a file type that it doesn’t already handle. >> >> Works really slick. >> >> bobb >> >> >> On Oct 27, 2019, at 5:21 PM, stan wrote: >>> >>> Think Before You Click: This email originated outside our organization. >>> >>> >>> I have a presentation later in the week, and i plan on printing out some >>> files containing SQL commands. I have used some "pretty printers" in the >>> past for things like Perl scripts. What I am thinking of s something that >>> bolds keywords, handles page breaks, and does some formatting. >>> >>> Development being done in an Ubuntu Linux environment. >>> >>> Anyone have a recommendation? >> > > -- > Angular momentum makes the world go 'round. > >
Re: SQL pretty pritner?
But does it reformat the text? On 10/28/19 8:00 AM, Basques, Bob (CI-StPaul) wrote: All, Take a look at the VI(m) editor. There is a Syntax mode for highlighting different file types, as well as tools for exporting the highlighted text to HTML. I have yet to find a file type that it doesn’t already handle. Works really slick. bobb On Oct 27, 2019, at 5:21 PM, stan wrote: Think Before You Click: This email originated outside our organization. I have a presentation later in the week, and i plan on printing out some files containing SQL commands. I have used some "pretty printers" in the past for things like Perl scripts. What I am thinking of s something that bolds keywords, handles page breaks, and does some formatting. Development being done in an Ubuntu Linux environment. Anyone have a recommendation? -- Angular momentum makes the world go 'round.
Re: SQL pretty pritner?
> On Oct 28, 2019, at 7:00 AM, Basques, Bob (CI-StPaul) > wrote: > > All, > > Take a look at the VI(m) editor. There is a Syntax mode for highlighting > different file types, as well as tools for exporting the highlighted text to > HTML. I have yet to find a file type that it doesn’t already handle. > > Works really slick. > > bobb > > Well if you’re going to go there, emacs will hilite and execute your sql. > >> On Oct 27, 2019, at 5:21 PM, stan >
Re: SQL pretty pritner?
All, Take a look at the VI(m) editor. There is a Syntax mode for highlighting different file types, as well as tools for exporting the highlighted text to HTML. I have yet to find a file type that it doesn’t already handle. Works really slick. bobb > On Oct 27, 2019, at 5:21 PM, stan wrote: > > Think Before You Click: This email originated outside our organization. > > > I have a presentation later in the week, and i plan on printing out some > files containing SQL commands. I have used some "pretty printers" in the > past for things like Perl scripts. What I am thinking of s something that > bolds keywords, handles page breaks, and does some formatting. > > Development being done in an Ubuntu Linux environment. > > Anyone have a recommendation? > > -- > "They that would give up essential liberty for temporary safety deserve > neither liberty nor safety." >-- Benjamin Franklin > >
Re: I think that my data is saved correctly, but when printing again, other data appears
On 10/27/19 8:01 PM, Yessica Brinkmann wrote: > Thank you so much for the answers. > By telling me this: "MemoryContextStrdup enables you to create a copy of > a string in an explicitly specified memory context." I better understood > the function of MemoryContextStrdup. > And thank you very much to Mr. Jony Cohen for giving me the reference of > his work. I really was already researching about his work, and I > included it in the state of the art of my thesis, but I didn't look at > the source code. > I will be looking at the source code for a help, and especially in this > case to see for the moment, the maintenance of the context for its > subsequent restoration. For better understanding of how Postgres manages memory, you might want to also read this: https://github.com/postgres/postgres/blob/master/src/backend/utils/mmgr/README and possibly browse through this: https://github.com/postgres/postgres/blob/master/src/backend/utils/mmgr/mcxt.c HTH, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
