Re: FW: Error!

[Adrian Klaver]

On 4/15/23 16:56, Arquimedes Aguirre wrote:

Adrian

I am new using this postgres program and pgadmin 4 sorry, I am starting 
to use it.


First to be clear Postgres is the database server and pgAdmin4 is just a 
client for that server, a GUI in this case.




In your first question, you mean that in my initial messages I did not 
mention that I can connect using pgadmin 4. I think you mean that I say 
that the postgres and pgadmin 4 programs are running. I have pgadmin 4 
open at the moment that I open the psql shell and I perform the process 
for the password that does not allow me to proceed. When I say that it 
is running, it is that I have it open and I put the password that I 
established from the beginning and it works in pgadmin 4. I attach a 
screen print when I open the pgadmin 4 program.


That is the screen to open the pgAdmin4 program. You need to enter the 
password to get into the pgAdmin4 program.




In your second question I tell you that I don't have a user for logging, 
only when I start the pgadmin 4 program it asks me for the password. 
attached the screen print.


Do you have a password that gets you to the pgAdmin4 screen where you 
can click on the Servers tab under Browser?


It is a user for login not logging


Three question; Where can I see what connection parameters I am using in 
pgAdmin4? I don't know where I can see that information, please if you 
can tell me, I can locate it and give you that information.


That will be in the Properties for each server when you expand the 
Servers tree.


Expand Servers, the right click on a server name to get to Properties., 
You can also Connect Server from the right click menu. Try that and see 
if you get a connection to a server.




Four question: I am doing a project and I need the programs working 
correctly, that's it.


Five question: Just a Postgresql 15.

Thanks! for your attention! and collaboration!

Sent from Mail  for Windows

*From: *Adrian Klaver 
*Sent: *Saturday, April 15, 2023 5:56 PM
*To: *Arquimedes Aguirre ; pgsql-general 
list 

*Subject: *Re: FW: Error!

On 4/15/23 15:28, Arquimedes Aguirre wrote:
 > Hi Dear
 >
 > If I have the postgres and admin 4 program running with the password
 > that I set from the beginning, but when I want to start the psql shell
 > and set the same password, it throws me the error, I send the screen I
 > think it's self explanatory .

If it where I would not have asked. Nowhere in your opening messages did
you mention you could connect using pgAdmin4.

 > If I can log in to the pgadmin 4 program, I don't have a problem with 
that.


So what user are you logging in as from pgAdmin4?

Also what connection parameters are you using in pgAdmin4?

 >
 > My approach to the problem has always been the same, the password that I
 > established from the beginning when I installed postgres and pgadmin 4
 > is the one that I am using to start in the psql shell and it is not
 > accepted, which is why it does not allow me to move forward, I hope
 > these clarifications help you allow you to understand and attend to the
 > problem that is being presented to me with these tools.
 >
 > *Then you went into the MS dance of reinstalling/rebooting with no *
 >
 > *explanation of what prompted that initial reinstall?* I don’t
 > understand what do you want mean? I have never reinstalling/rebooting,
 > what I said in the email is that I had to install and uninstall it
 > several times due to the errors that were thrown at me, I also sent the
 > screenshot.

Your email said differently:

'... but I got this type of error more than 30 times downloading various
versions of postgresql that are in the link that you sent me.'

Or did you just download for the fun of it and not actually install.

How many versions of Postgres do you have installed now?

 >
 > Thanks!
 >
 > Sent from Mail > for Windows

 >
 > *From: *Adrian Klaver >

 > *Sent: *Saturday, April 15, 2023 11:41 AM
 > *To: *Arquimedes Aguirre >; pgsql-general
 > list >

 > *Subject: *Re: FW: Error!
 >
 > On 4/15/23 07:39, Arquimedes Aguirre wrote:
 >  > Hi Dear!
 >  >
 >  > I was able to install the postgresql program and admin 4, as I 
told you

 >  > before, but I got this type of error more than 30 times downloading
 >  > various versions of postgresql that are in the link that you sent me,
 >
 > This is the part that confuses me. You said you had it working.
 >
 > At that point could you log in using psql with the password?
 >
 > Then you went into the MS dance of reinstalling/rebooting with no
 

Re: FW: Error!

[Adrian Klaver]

On 4/15/23 15:28, Arquimedes Aguirre wrote:

Hi Dear

If I have the postgres and admin 4 program running with the password 
that I set from the beginning, but when I want to start the psql shell 
and set the same password, it throws me the error, I send the screen I 
think it's self explanatory .


If it where I would not have asked. Nowhere in your opening messages did 
you mention you could connect using pgAdmin4.



If I can log in to the pgadmin 4 program, I don't have a problem with that.


So what user are you logging in as from pgAdmin4?

Also what connection parameters are you using in pgAdmin4?



My approach to the problem has always been the same, the password that I 
established from the beginning when I installed postgres and pgadmin 4 
is the one that I am using to start in the psql shell and it is not 
accepted, which is why it does not allow me to move forward, I hope 
these clarifications help you allow you to understand and attend to the 
problem that is being presented to me with these tools.


*Then you went into the MS dance of reinstalling/rebooting with no *

*explanation of what prompted that initial reinstall?* I don’t 
understand what do you want mean? I have never reinstalling/rebooting, 
what I said in the email is that I had to install and uninstall it 
several times due to the errors that were thrown at me, I also sent the 
screenshot.


Your email said differently:

'... but I got this type of error more than 30 times downloading various 
versions of postgresql that are in the link that you sent me.'


Or did you just download for the fun of it and not actually install.

How many versions of Postgres do you have installed now?



Thanks!

Sent from Mail  for Windows

*From: *Adrian Klaver 
*Sent: *Saturday, April 15, 2023 11:41 AM
*To: *Arquimedes Aguirre ; pgsql-general 
list 

*Subject: *Re: FW: Error!

On 4/15/23 07:39, Arquimedes Aguirre wrote:
 > Hi Dear!
 >
 > I was able to install the postgresql program and admin 4, as I told you
 > before, but I got this type of error more than 30 times downloading
 > various versions of postgresql that are in the link that you sent me,

This is the part that confuses me. You said you had it working.

At that point could you log in using psql with the password?

Then you went into the MS dance of reinstalling/rebooting with no
explanation of what prompted that initial reinstall?

Was it:

1) The password did not work

2) Something else?

Can you currently log in via pgAdmin4?

Also what version of Postgres are you currently using?

Is that different from the one you initially started with?


 > that's why I had to ask for help from Microsoft Team team, since it is a
 > rather weird and odd situation. Now I have this problem with the psql
 > shell I can't start the program but it don’t allows me to proceed with

  From your previous posts you show you can start the shell script that
runs psql and that psql actually starts it just do not connect due to
password error.

 > the password, which is the same one that I set from the beginning, now
 > what should I do to fix this once and for all???.
 >
 > Thanks!
 >


--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: cursors with prepared statements

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> I found this email from Peter Eisentraut:
>> https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com
>> 
>> It caused the 42601 error, « syntax error at or near “execute” ». So it 
>> looks like Peter’s patch hasn’t yet been adopted. What is the likelihood 
>> that it will be adopted in a future version?
> 
> Closer to zero than one I'd say, given how that thread ended and not 
> subsequent activity on the feature in the five years since.

Thanks. Shall I assume, too, that there’s no under-the-hood functionality for 
cursors, analogous to what happens with a PL/pgSQL program at run-time, that 
does the moral equivalent of on-demand prepare for a cursor’s defining subquery?

Re: cursors with prepared statements

[David G. Johnston]

On Sat, Apr 15, 2023 at 2:15 PM Bryn Llewellyn  wrote:

> I found this email from Peter Eisentraut:
>
> https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com
>
> It caused the 42601 error, « syntax error at or near “execute” ». So it
> looks like Peter’s patch hasn’t yet been adopted. What is the likelihood
> that it will be adopted in a future version?
>
> Closer to zero than one I'd say, given how that thread ended and not
subsequent activity on the feature in the five years since.


David J.

Re: cursors with prepared statements

[Bryn Llewellyn]

I found this email from Peter Eisentraut:
https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com

> I have developed a patch that allows declaring cursors over prepared 
> statements... This is an SQL standard feature. ECPG already supports it (with 
> different internals). Internally, this just connects existing functionality 
> in different ways, so it doesn't really introduce anything new.

I tried this in Version 15.2 (avoiding the question of how to bind actual 
arguments to placeholders):

prepare series as select s.v from generate_series(1, 10) as s(v);
start transaction;
declare cur scroll cursor for execute series;

It caused the 42601 error, « syntax error at or near “execute” ». So it looks 
like Peter’s patch hasn’t yet been adopted. What is the likelihood that it will 
be adopted in a future version?

p.s. The section doc "PL/pgSQL under the Hood” explains that expressions and 
static SQL statements in a PL/pgSQL source text are effectively prepared when 
first encountered at run time in a session. But simple tests show that there’s 
no evidence if this in the “pg_prepared_statements” catalog view. Is it 
possible that the subquery that is used to declare a cursor (using ordinary top 
level SQL or the PL/pgSQL API) is also invisibly prepared under the hood?

Logical replication failed with SSL SYSCALL error

[shaurya jain]

Hi Team,

Postgres Version:- 13.8
Issue:- Logical replication failing with SSL SYSCALL error
Priority:-High

We are migrating our database through logical replications, and all of
sudden below error pops up in the source and target logs which leads us to
nowhere.

*Logs from Source:-*
LOG:  could not send data to client: Connection reset by peer
STATEMENT:  COPY public.test TO STDOUT
FATAL:  connection to client lost
STATEMENT:  COPY public.test TO STDOUT

*Logs from Target:-*
2023-04-15 19:07:02 UTC::@:[1250]:ERROR: could not receive data from WAL
stream: SSL SYSCALL error: Connection timed out
2023-04-15 19:07:02 UTC::@:[1250]:CONTEXT: COPY test, line 365326932
2023-04-15 19:07:03 UTC::@:[505]:LOG: background worker "logical
replication worker" (PID 1250) exited with exit code 1
2023-04-15 19:07:03 UTC::@:[7155]:LOG: logical replication table
synchronization worker for subscription " sub_tables_2_180", table "test"
has started
2023-04-15 19:12:05
UTC:10.144.19.34(33276):postgres@webadmit_staging:[7112]:WARNING:
there is no transaction in progress
2023-04-15 19:14:08
UTC:10.144.19.34(33324):postgres@webadmit_staging:[6052]:LOG:
could not receive data from client: Connection reset by peer
2023-04-15 19:17:23 UTC::@:[2112]:ERROR: could not receive data from WAL
stream: SSL SYSCALL error: Connection timed out
2023-04-15 19:17:23 UTC::@:[1089]:ERROR: could not receive data from WAL
stream: SSL SYSCALL error: Connection timed out
2023-04-15 19:17:23 UTC::@:[2556]:ERROR: could not receive data from WAL
stream: SSL SYSCALL error: Connection timed out
2023-04-15 19:17:23 UTC::@:[505]:LOG: background worker "logical
replication worker" (PID 2556) exited with exit code 1
2023-04-15 19:17:23 UTC::@:[505]:LOG: background worker "logical
replication worker" (PID 2112) exited with exit code 1
2023-04-15 19:17:23 UTC::@:[505]:LOG: background worker "logical
replication worker" (PID 1089) exited with exit code 1
2023-04-15 19:17:23 UTC::@:[7287]:LOG: logical replication apply worker for
subscription "sub_tables_2_180" has started
2023-04-15 19:17:23 UTC::@:[7288]:LOG: logical replication apply worker for
subscription "sub_tables_3_192" has started
2023-04-15 19:17:23 UTC::@:[7289]:LOG: logical replication apply worker for
subscription "sub_tables_1_180" has started

Just after this error, all other replication slots get disabled for some
time and come back online along with COPY command with the new PID in
pg_stat_activity.

I have a few queries regarding this:-

   1. The exact reason for disconnection (Few articles claim memory and few
   network)
   2. Will it lead to data inconsistency?
   3. Does this new PID COPY command again migrate the whole data of the
   test table once again?

Please help we got stuck here.
-- 
Thanks and Regards,
Shaurya Jain
email:- 12345shau...@gmail.com
*Mobile:- +91-8802809405*
LinkedIn:- https://www.linkedin.com/in/shaurya-jain-74353023

RE: FW: Error!

[Arquimedes Aguirre]

Hi Dear!

I was able to install the postgresql program and admin 4, as I told you before, 
but I got this type of error more than 30 times downloading various versions of 
postgresql that are in the link that you sent me, that's why I had to ask for 
help from Microsoft Team team, since it is a rather weird and odd situation. 
Now I have this problem with the psql shell I can't start the program but it 
don’t allows me to proceed with the password, which is the same one that I set 
from the beginning, now what should I do to fix this once and for all???.

Thanks!

Sent from Mail for Windows

From: Adrian Klaver
Sent: Wednesday, April 12, 2023 7:30 PM
To: Arquimedes Aguirre; pgsql-general 
list
Subject: Re: FW: Error!

On 4/12/23 14:47, Arquimedes Aguirre wrote:
> Hello Dear!
>
> I attach the errors that the system threw me when installing the
> Postgresql program.

The  first screenshot is about pgAdmin which is a Postgres client not
Postgres. Also it is from 2023-02-19, so has anything changed since then?

The second screenshot shows you actually connecting to Postgres so I
don't see an error.

>
> I was installing it on the same website as the link you sent me.
>
> I am using the same password that I used to create the password and
> despite that it throws me the same error.
>
> Thanks! I hope you can help me solve this error that does not allow me
> to move forward, sorry for the inconveniences.
>
> Thanks again, best regards!
>
> Sent from Mail  for Windows
>
> *From: *Adrian Klaver 
> *Sent: *Wednesday, April 12, 2023 10:27 AM
> *To: *Arquimedes Aguirre ; pgsql-general
> list 
> *Subject: *Re: FW: Error!
>
> On 4/10/23 17:55, Arquimedes Aguirre wrote:
>  > Sent from Mail  > for Windows
>  >
>  > *From: *Arquimedes Aguirre  >
>  > *Sent: *Sunday, April 9, 2023 5:59 PM
>  > *To: *pgsql-advoc...@postgresql.org
>  >
>  > *Subject: *Error
>  >
>  > Hello there Dear!
>  >
>  > I have an error initializing PSQL shell, who can help me to solve it.
>  >
>  > Please I require your attention, I need to start developing a project,
>  > apparently there is a hacker that is intervening in this process, I
>  > appreciate your investigation some research and answer.
>  >
>  > Just to install the program, I got several errors without any common
>  > sense, I asked the Microsoft support team for help and in 5 minutes they
>  > solved the problem, it's very weird and odd. Apparently they have
>  > control of my personal computer.
>
> If MS support solved it why are you asking how to solve it?
>
> FYI, do not use images for textual information, copy and paste the text
> from the terminal .
>
> I don't see any evidence of a hacker. I do see evidence of incorrect
> passwords being used.
>
> Did you install Postgres from here?:
>
> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
> 
>
>  > If so at some point in the process you where asked to create a password.
> Use that password when invoking the psql shell command with the user
> being the default postgres.
>
>
>  > I hope that you can pay due attention to these problems that do not
>  > allow me to advance in what I want to develop for my project. I hope you
>  > can find the person responsible for these abnormalities.
>  >
>  > Thanks!
>  >
>  > Sent from Mail  > for Windows
>  >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: FW: Error!

[Adrian Klaver]

On 4/15/23 07:39, Arquimedes Aguirre wrote:

Hi Dear!

I was able to install the postgresql program and admin 4, as I told you 
before, but I got this type of error more than 30 times downloading 
various versions of postgresql that are in the link that you sent me, 


This is the part that confuses me. You said you had it working.

At that point could you log in using psql with the password?

Then you went into the MS dance of reinstalling/rebooting with no 
explanation of what prompted that initial reinstall?


Was it:

1) The password did not work

2) Something else?

Can you currently log in via pgAdmin4?

Also what version of Postgres are you currently using?

Is that different from the one you initially started with?


that's why I had to ask for help from Microsoft Team team, since it is a 
rather weird and odd situation. Now I have this problem with the psql 
shell I can't start the program but it don’t allows me to proceed with 


From your previous posts you show you can start the shell script that 
runs psql and that psql actually starts it just do not connect due to 
password error.


the password, which is the same one that I set from the beginning, now 
what should I do to fix this once and for all???.


Thanks!




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Guidance on INSERT RETURNING order

[Mike Bayer]

On Fri, Apr 14, 2023, at 11:17 PM, Tom Lane wrote:
> Federico  writes:
>> Would something like what was proposed by Mike Bayer be considered?
>
>>> A new token called "tuple_order" or something
>>> 
>>> INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING 
>>> table.id, inserted.tuple_order
>>> 
>>> tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which correlate 
>>> the each row delivered by RETURNING to each entry in the VALUES clause, in 
>>> the order they were stated in that VALUES clause, that is entry (1, 2, 3) 
>>> would be tuple_order 1, entry (4, 5, 6) would be tuple order 2, etc.
>
> As proposed, I don't think so.  Something over in the RETURNING clause has
> exactly no connection to VALUES.  What do you do if it's INSERT ... SELECT
> and there are several VALUES clauses down inside the SELECT?

in my "plan", the token would not be supported and an error would be raised.   


>
> There is some prior art in this area, though.  See the more-or-less
> SQL-standard WITH ORDINALITY option for functions-in-FROM.  It seems to me
> that it could be plausible to attach WITH ORDINALITY to a VALUES clause,
> which would give you a rock-solid connection between the VALUES rows and
> the ordinality-column values, and then you could include that column in
> RETURNING.

I appreciate this idea!Any kind of keyword / syntax that frees us from 
having to round-trip additional data to the database and/or generate more 
complex syntaxes for certain kinds of default generation schemes would simplify 
the approach. 

Re: JSON / ASP.NET AJAX Dates support in PostgreSQL

[Peter J. Holzer]

On 2023-04-15 09:12:41 -0700, Adrian Klaver wrote:
> On 4/15/23 03:46, Peter J. Holzer wrote:
> > On 2023-04-14 10:44:08 -0700, Adrian Klaver wrote:
> > > On 4/14/23 9:31 AM, Peter J. Holzer wrote:
> > > > On 2023-04-13 10:07:09 -0500, Ron wrote:
> > > > > On 4/13/23 09:44, Sebastien Flaesch wrote:
> > > > >       "LastChangeDateTime" : "\/Date(1672692813062+0100)\/"
> > > > > 
> > > > >   Warning: Note the backslash before the slashes!
> > > > 
> > > > That's a Noop. According to RFC 8259, "\/" is the same as "/" (no idea
> > > > why they even specified that - it seems quite pointless).
> > > 
> > > It is a cheat explained here:
> > > 
> > > https://weblogs.asp.net/bleroy/dates-and-json
> > 
> > Yes, but it needs a specialized JSON parser to note that. As they write:
> > 
> > > Of course, a parser that doesn't know about this convention will just
> > > see a string,
> > 
> > And not only will it just see a string, it will output a string that's
> > indistinguishable from a string with the input
> > "/Date(1672692813062+0100)/". So any code after the parser can't detect
> > those extra backslashes. (This would include for example the object_hook
> > in the Python json Decoder which gets the decoded strings, not the raw
> > strings).
> 
> I would encourage you to read the whole post, it is short. Bottom line, this
> is a cheat MS created for the Microsoft Ajax Library. Their hope was(from
> the post):

Yes, I got that.

[...]
> I'm going to go out on a limb and say whatever JSON parsing Postgres is
> doing does not recognize this format.

Exactly. This was my point. Nor is any other JSON parser (except the one
which invented that cheat of course) likely to recognize it. And it's
sort of hard to add to existing parsers without breaking app
compatibility. In short: I don't think adding that to Postgres is a good
idea.

hp

PS: I do appreciate it as a hack, though.

-- 
   _  | 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: JSON / ASP.NET AJAX Dates support in PostgreSQL

[Adrian Klaver]

On 4/15/23 03:46, Peter J. Holzer wrote:

On 2023-04-14 10:44:08 -0700, Adrian Klaver wrote:

On 4/14/23 9:31 AM, Peter J. Holzer wrote:

On 2023-04-13 10:07:09 -0500, Ron wrote:

On 4/13/23 09:44, Sebastien Flaesch wrote:
  Is there an easy way to convert JSON data containing ASP.NET AJAX Dates
  into PostgreSQL timestamp?

  I have this kind of JSON data:

  {
      "PurchaseOrder" : "45",
      "CreationDate" : "\/Date(167235840)\/",
      "LastChangeDateTime" : "\/Date(1672692813062+0100)\/"
  }

  Warning: Note the backslash before the slashes!


That's a Noop. According to RFC 8259, "\/" is the same as "/" (no idea
why they even specified that - it seems quite pointless).


It is a cheat explained here:

https://weblogs.asp.net/bleroy/dates-and-json


Yes, but it needs a specialized JSON parser to note that. As they write:


Of course, a parser that doesn't know about this convention will just
see a string,


And not only will it just see a string, it will output a string that's
indistinguishable from a string with the input
"/Date(1672692813062+0100)/". So any code after the parser can't detect
those extra backslashes. (This would include for example the object_hook
in the Python json Decoder which gets the decoded strings, not the raw
strings).


I would encourage you to read the whole post, it is short. Bottom line, 
this is a cheat MS created for the Microsoft Ajax Library. Their hope 
was(from the post):


"
We're pretty much satisfied with this solution to the date problem, but 
of course for the moment very few serializers and parsers support that 
convention. It would be great if this could become the consensus across 
the industry.

"

NOTE: This is from Friday, January 18, 2008

I'm going to go out on a limb and say whatever JSON parsing Postgres is 
doing does not recognize this format.






 hp



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Guidance on INSERT RETURNING order

[Federico]

On Sat, 15 Apr 2023 at 15:40, Tom Lane  wrote:
>
> Federico  writes:
> > I think the tuple order would not be connected to the values, but be
> > determined by the input order of the rows in the insert. So when using
> > INSERT ... SELECT the tuple_order value would be determined by the
> > output of the select, using the same logic as ROW_NUMBER.
>
> We already rejected the idea that INSERT must preserve the order of
> the incoming tuples.  Please don't re-propose it with different
> wording.
>
> regards, tom lane

That was not my intention, what I meant is that the hypothetical
inserted.tuple_order in case of insert .. select would be determined
by the output order of the select. Insert would be free to insert the
row as it pleases.
Sorry if it wasn't clear.

Best
  Federico

Re: Guidance on INSERT RETURNING order

[Tom Lane]

Federico  writes:
> I think the tuple order would not be connected to the values, but be
> determined by the input order of the rows in the insert. So when using
> INSERT ... SELECT the tuple_order value would be determined by the
> output of the select, using the same logic as ROW_NUMBER.

We already rejected the idea that INSERT must preserve the order of
the incoming tuples.  Please don't re-propose it with different
wording.

regards, tom lane

Re: JSON / ASP.NET AJAX Dates support in PostgreSQL

[Peter J. Holzer]

On 2023-04-14 10:44:08 -0700, Adrian Klaver wrote:
> On 4/14/23 9:31 AM, Peter J. Holzer wrote:
> > On 2023-04-13 10:07:09 -0500, Ron wrote:
> > > On 4/13/23 09:44, Sebastien Flaesch wrote:
> > >  Is there an easy way to convert JSON data containing ASP.NET AJAX 
> > > Dates
> > >  into PostgreSQL timestamp?
> > > 
> > >  I have this kind of JSON data:
> > > 
> > >  {
> > >      "PurchaseOrder" : "45",
> > >      "CreationDate" : "\/Date(167235840)\/",
> > >      "LastChangeDateTime" : "\/Date(1672692813062+0100)\/"
> > >  }
> > > 
> > >  Warning: Note the backslash before the slashes!
> > 
> > That's a Noop. According to RFC 8259, "\/" is the same as "/" (no idea
> > why they even specified that - it seems quite pointless).
> 
> It is a cheat explained here:
> 
> https://weblogs.asp.net/bleroy/dates-and-json

Yes, but it needs a specialized JSON parser to note that. As they write:

> Of course, a parser that doesn't know about this convention will just
> see a string,

And not only will it just see a string, it will output a string that's
indistinguishable from a string with the input
"/Date(1672692813062+0100)/". So any code after the parser can't detect
those extra backslashes. (This would include for example the object_hook
in the Python json Decoder which gets the decoded strings, not the raw
strings).

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

Should I add a new schema for my application?

[Alexander Farber]

Good morning,

I am seeking for a general advice if it would be a good idea for a
PostgreSQL/PostGIS using application to add a third schema.

I am using postgis/postgis Docker image which comes with "public" schema.

Then I have loaded OpenStreetMap data into the database and have created
"osm_schema" for that:

# psql -p 5432 -U postgres

CREATE DATABASE osm_database TEMPLATE=template_postgis;
CREATE USER osm_username WITH ENCRYPTED PASSWORD 'osm_password';
GRANT ALL PRIVILEGES ON DATABASE osm_database TO osm_username; -- TODO
read only

# psql -p 5432 -U osm_username osm_database

CREATE SCHEMA osm_schema AUTHORIZATION osm_username;
ALTER ROLE osm_username SET search_path TO osm_schema, public;

After loading OSM data with the osm2pgsql tool I have 3 tables in there:

# psql -p 5432 -U osm_username osm_database
psql (15.2)

osm_database=> \dt
   List of relations
   Schema   |Name| Type  |Owner
++---+--
 osm_schema | planet_osm_line| table | osm_username
 osm_schema | planet_osm_point   | table | osm_username
 osm_schema | planet_osm_polygon | table | osm_username
 osm_schema | planet_osm_roads   | table | osm_username
 public | spatial_ref_sys| table | postgres
(5 rows)

And now I need one more table, for the new mapping feature in my
application, which would reference the osm_id column in the
planet_osm_roads.

I have not used database schemas at all until yet, I am looking for an
advice if I should add a third schema here, just for my application?

Would it be a good long term idea and what advantages/disadvantages would
that bring with it?

Thank you for any feedback and also I have to note, that I am really amazed
by all these software products -

PostgreSQL, PostGIS and also the OpenStreetMap - how smooth the
installation has worked for me - just an hour spent and I have the whole
planet at my fingertips - this is not coming for free, there are probably
decades of effort by many people in the products.

Best regards
Alex

Re: Guidance on INSERT RETURNING order

[Federico]

On Sat, 15 Apr 2023 at 05:17, Tom Lane  wrote:
>
> Federico  writes:
> > Would something like what was proposed by Mike Bayer be considered?
>
> >> A new token called "tuple_order" or something
> >>
> >> INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING 
> >> table.id, inserted.tuple_order
> >>
> >> tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which 
> >> correlate the each row delivered by RETURNING to each entry in the VALUES 
> >> clause, in the order they were stated in that VALUES clause, that is entry 
> >> (1, 2, 3) would be tuple_order 1, entry (4, 5, 6) would be tuple order 2, 
> >> etc.
>
> As proposed, I don't think so.  Something over in the RETURNING clause has
> exactly no connection to VALUES.  What do you do if it's INSERT ... SELECT
> and there are several VALUES clauses down inside the SELECT?

I think the tuple order would not be connected to the values, but be
determined by the input order of the rows in the insert. So when using
INSERT ... SELECT the tuple_order value would be determined by the
output of the select, using the same logic as ROW_NUMBER.

> There is some prior art in this area, though.  See the more-or-less
> SQL-standard WITH ORDINALITY option for functions-in-FROM.  It seems to me
> that it could be plausible to attach WITH ORDINALITY to a VALUES clause,
> which would give you a rock-solid connection between the VALUES rows and
> the ordinality-column values, and then you could include that column in
> RETURNING.

Well if returning could output rows that are not in the inserted
table, like mentioned also by Devid Johnston, it would open to
alternatives to tuple_order, like using

  INSERT INTO tbl(data)
  SELECT v.d FROM (VALUES ('x', 1), ('y', 2)) AS v(d, num)
  RETURNING tbl.id, v.num

> regards, tom lane

I'm not familiar with the internals of Postgresql, so I don't know
what would be more complex to implement in this case. I think both
solutions would work equally well from the client point of view.

Thanks for the reply,
 Federico