Updating data: confirmation and question
Before my old server died I ran pg_dumpall on the database there and copied that to my new server. The old database was postgresql-10 and the new one is postgresql-11.5. Am I correct that I can run 'pgsql -f .sql' and overwrite the existing databases with the newer data? Checking the database names in both I see that I mistakenly used hyphens rather than underscores in one database name. I can change that in the .sql file but have not before learned how/whether I can change a database name using psql. What's the best approach to changing the existing hypephenated name? TIA, Rich
Re: Updating data: confirmation and question
On 10/26/19 8:38 AM, Rich Shepard wrote: Before my old server died I ran pg_dumpall on the database there and copied that to my new server. The old database was postgresql-10 and the new one is postgresql-11.5. Am I correct that I can run 'pgsql -f .sql' and overwrite the existing databases with the newer data? That depends on how you ran pg_dumpall. For instance did you use -c?: https://www.postgresql.org/docs/11/app-pg-dumpall.html Checking the database names in both I see that I mistakenly used hyphens rather than underscores in one database name. I can change that in the .sql file but have not before learned how/whether I can change a database name using psql. What's the best approach to changing the existing hypephenated name? This?: https://www.postgresql.org/docs/11/sql-alterdatabase.html ALTER DATABASE name RENAME TO new_name TIA, Rich -- Adrian Klaver adrian.kla...@aklaver.com
Re: Updating data: confirmation and question
On Sat, 26 Oct 2019, Adrian Klaver wrote: That depends on how you ran pg_dumpall. For instance did you use -c?: Adrian, Yes. Always. https://www.postgresql.org/docs/11/sql-alterdatabase.html ALTER DATABASE name RENAME TO new_name Ah, I should have looked. I apologize. Rich
PostgreSQL - unrecognized win32 error code: 38
Hi All I encountered one problem. It repeats again and again. Please help. "ComputeComputer" is one stored procedure. The version of PostgreSQL I am using is 12. 2019-10-26 07:36:58.880 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.880 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.889 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.889 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.889 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.890 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.890 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.890 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.900 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.900 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.900 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.900 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.900 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.900 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.910 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.910 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.910 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.911 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.911 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.911 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.919 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.919 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.919 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.920 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.920 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.920 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.933 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.933 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.933 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.934 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.934 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.934 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.943 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.943 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.943 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.944 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.944 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.944 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.953 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.953 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.953 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.954 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.954 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.954 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.963 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.963 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.963 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.964 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.964 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.964 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.973 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.973 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.973 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.974 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.974 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.974 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.983 HKT [5376] LOG: unrecognized win32 error code: 38 2019-10-26 07:36:58.983 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 2019-10-26 07:36:58.983 HKT [5376] STATEMENT: CALL "dbo"."ComputeComputer"(1) 2019-10-26 07:36:58.984 HKT [5376] LOG: unrecognized win32 error code
Re: PostgreSQL - unrecognized win32 error code: 38
On 10/25/19 5:19 PM, ZhenHua Cai wrote: Hi All I encountered one problem. It repeats again and again. Please help. "ComputeComputer" is one stored procedure. The version of PostgreSQL I am using is 12. 2019-10-26 07:36:58.880 HKT [5376] CONTEXT: SQL function "ComputeComputer" statement 1 Not sure how that can be answered without knowing what ComputeComputer is doing? Thanks Jacky -- Adrian Klaver adrian.kla...@aklaver.com
Re: Updating data: confirmation and question
On 10/26/19 10:39 AM, Rich Shepard wrote: On Sat, 26 Oct 2019, Adrian Klaver wrote: That depends on how you ran pg_dumpall. For instance did you use -c?: Adrian, Yes. Always. Then the question is, do you really want to overwrite the new database? https://www.postgresql.org/docs/11/sql-alterdatabase.html ALTER DATABASE name RENAME TO new_name Ah, I should have looked. I apologize. Rich -- Adrian Klaver adrian.kla...@aklaver.com
Re: Updating data: confirmation and question
On Sat, 26 Oct 2019, Adrian Klaver wrote: Then the question is, do you really want to overwrite the new database? Adrian, I want to overwrite the old databases with the new .sql file. I _think_ there's only one database that's changed, but overwriting all won't hurt. Regards, Rich
Re: I think that my data is saved correctly, but when printing again, other data appears
Or if you know any link to a website where to find an example of this, I will thank you very much, please. Best regards, Yessica Brinkmann El vie., 25 oct. 2019 a las 22:06, Yessica Brinkmann (< yessica.brinkm...@gmail.com>) escribió: > Good evening, sorry for the delay in answering. I have a part-time job and > I was at it. > I understand what you tell me about the "data" value is just a pointer > into the tupdesc associated with the SPI result, and that disappears the > moment I do SPI_finish (). > What I do not understand well is how to use CurrentMemoryContext and > MemoryContextStrdup, since there are not many examples of using them on the > Internet (most are only definitions) and it is the first time I have heard > of this, although I already understand this part now of contexts, because I > was reading on the subject. > Could you please give me an example of use? > Best Regards, > Yessica Brinkmann > > El vie., 25 oct. 2019 a las 12:24, Yessica Brinkmann (< > yessica.brinkm...@gmail.com>) escribió: > >> Thank you so much for your answer. I will be testing the indicated and >> then I give you return. >> Best regards, >> >> Yessica Brinkmann >> >> >> Mensaje original >> Asunto: Re: I think that my data is saved correctly, but when printing >> again, other data appears >> De: Tom Lane >> Para: Yessica Brinkmann >> CC: pgsql-general@lists.postgresql.org >> >> >> Yessica Brinkmann writes: >> > I have a function called get_columnnames, which runs on a Postgresql >> server >> > and returns the names of the columns of a table in a Postgresql >> database. >> > In the first foreach, in the statement: idxcd-> varattnames [cont] = >> data; >> > column names are saved. Those column names are saved correctly. I could >> > verify this by printing some values. >> > But later, in the following foreach, when I reprint the values of the >> names >> > of the columns that had been saved in the idxcd-> varattnames [cont] = >> data >> > statement; They appear to me with errors. >> >> Yeah, because the "data" value is just a pointer into the tupdesc >> associated with the SPI result, and that disappears the moment you >> do SPI_finish(). You'd need to do something to copy the strings >> into a longer-lived context. A plain pstrdup() won't suffice >> because you're in a short-lived SPI context already inside that >> loop; but you could save CurrentMemoryContext before starting up >> SPI and then use MemoryContextStrdup. >> >> regards, tom lane >> >>
Re: Updating data: confirmation and question
On 10/26/19 1:39 PM, Rich Shepard wrote: On Sat, 26 Oct 2019, Adrian Klaver wrote: Then the question is, do you really want to overwrite the new database? Adrian, I want to overwrite the old databases with the new .sql file. I _think_ there's only one database that's changed, but overwriting all won't hurt. Just me, but to keep this from being a 'famous last words' moment I would backup up the new(11.5) instance before proceeding. Regards, Rich -- Adrian Klaver adrian.kla...@aklaver.com
Re: Updating data: confirmation and question
On Sat, 26 Oct 2019, Adrian Klaver wrote: Just me, but to keep this from being a 'famous last words' moment I would backup up the new(11.5) instance before proceeding. Adrian, Okay. That makes good sense. Will do a pg_dumpall on the 11.5 cluster. When I'm actively working on a database I do a nightly dump (datestamped). Thanks, Rich
Re: PostgreSQL - unrecognized win32 error code: 38
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. So you may want to check your own extension code if a module is involved here, or perhaps you are just using a fork of Postgres.. -- Michael signature.asc Description: PGP signature
