Re: "PANIC: could not open critical system index 2662" - twice
Hi, On 2023-06-19 10:04:35 +, Evgeny Morozov wrote: > There haven't been any updates posted to > https://www.postgresql.org/message-id/20230509040203.z6mvijumv7wxcuib%40awork3.anarazel.de > so I just wanted to check if there is any update on the status of the > patch? Can we expect it in PostgreSQL 15.4? Thanks. I pushed the fixes to all branches just now. Thanks for the report! Greetings, Andres Freund
Re: Problem perhaps after upgrading to pgadmin4 7.4
> On 13/07/2023 13:20 CEST Carl Erik Eriksson wrote: > > Query tool PGadmin on my mac > > If I enter a query like select count(*) from table_1I get a correct > response from the server > If I enter select * from table_1 I get an error message that I do not > understand: > Error Message:missing FROM-clause entry for table "rel" > LINE 8: ...ER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid > > PSQL tool (PGadmin on my Mac) > > However if I enter the same from the PSQL tool I get the correct output. > > But that output goes to my screen and I have found no way of directing it to > a file on my Mac. Redirect the output in psql with meta command \out: \out /path/to/file.txt select * from table_1; > Carl E Eriksson That's a cool name ;) -- Erik
Re: Problem perhaps after upgrading to pgadmin4 7.4
Problem solved - thanks Depez! (The solution for now is to downgrade to PGadmin4 version 7.2) > On 13 Jul 2023, at 13:52, hubert depesz lubaczewski wrote: > > On Thu, Jul 13, 2023 at 01:20:03PM +0200, Carl Erik Eriksson wrote: >> If I enter a query like select count(*) from table_1I get a correct >> response from the server >> If I enter select * from table_1 I get an error message that I do not >> understand: >> Error Message:missing FROM-clause entry for table "rel" >> LINE 8: ...ER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid >> PSQL tool (PGadmin on my Mac) >> However if I enter the same from the PSQL tool I get the correct output. >> >> But that output goes to my screen and I have found no way of directing it to >> a file on my Mac. >> >> Any suggestions? Either getting rid of the error message and getting the >> output to my screen within the Query tool where I can then dump the output >> to a file >> OR a way of running my query using the PSQL tool and redirecting the output >> to a file on my computer. >> >> An amateur who is up a creek without any paddle.. > > Which pg version you're on? > > Generally, try updating to newest pgadmin, from git repo > (https://github.com/pgadmin-org/pgadmin4/), there is/was a bug that > caused problems when using pg < 11 : > https://github.com/pgadmin-org/pgadmin4/commit/732bcc2b4d91f0126cf69d69c14df199bc4106bc > > Best regards, > > depesz >
Re: Problem perhaps after upgrading to pgadmin4 7.4
Thanks - it seems we might be on pg 10.17-1 /Carl > On 13 Jul 2023, at 13:52, hubert depesz lubaczewski wrote: > > On Thu, Jul 13, 2023 at 01:20:03PM +0200, Carl Erik Eriksson wrote: >> If I enter a query like select count(*) from table_1I get a correct >> response from the server >> If I enter select * from table_1 I get an error message that I do not >> understand: >> Error Message:missing FROM-clause entry for table "rel" >> LINE 8: ...ER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid >> PSQL tool (PGadmin on my Mac) >> However if I enter the same from the PSQL tool I get the correct output. >> >> But that output goes to my screen and I have found no way of directing it to >> a file on my Mac. >> >> Any suggestions? Either getting rid of the error message and getting the >> output to my screen within the Query tool where I can then dump the output >> to a file >> OR a way of running my query using the PSQL tool and redirecting the output >> to a file on my computer. >> >> An amateur who is up a creek without any paddle.. > > Which pg version you're on? > > Generally, try updating to newest pgadmin, from git repo > (https://github.com/pgadmin-org/pgadmin4/), there is/was a bug that > caused problems when using pg < 11 : > https://github.com/pgadmin-org/pgadmin4/commit/732bcc2b4d91f0126cf69d69c14df199bc4106bc > > Best regards, > > depesz >
Re: Problem perhaps after upgrading to pgadmin4 7.4
On Thu, Jul 13, 2023 at 01:20:03PM +0200, Carl Erik Eriksson wrote: > If I enter a query like select count(*) from table_1I get a correct > response from the server > If I enter select * from table_1 I get an error message that I do not > understand: > Error Message:missing FROM-clause entry for table "rel" > LINE 8: ...ER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid > PSQL tool (PGadmin on my Mac) > However if I enter the same from the PSQL tool I get the correct output. > > But that output goes to my screen and I have found no way of directing it to > a file on my Mac. > > Any suggestions? Either getting rid of the error message and getting the > output to my screen within the Query tool where I can then dump the output to > a file > OR a way of running my query using the PSQL tool and redirecting the output > to a file on my computer. > > An amateur who is up a creek without any paddle.. Which pg version you're on? Generally, try updating to newest pgadmin, from git repo (https://github.com/pgadmin-org/pgadmin4/), there is/was a bug that caused problems when using pg < 11 : https://github.com/pgadmin-org/pgadmin4/commit/732bcc2b4d91f0126cf69d69c14df199bc4106bc Best regards, depesz
Problem perhaps after upgrading to pgadmin4 7.4
Query tool PGadmin on my mac If I enter a query like select count(*) from table_1I get a correct response from the server If I enter select * from table_1 I get an error message that I do not understand: Error Message:missing FROM-clause entry for table "rel" LINE 8: ...ER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid PSQL tool (PGadmin on my Mac) However if I enter the same from the PSQL tool I get the correct output. But that output goes to my screen and I have found no way of directing it to a file on my Mac. Any suggestions? Either getting rid of the error message and getting the output to my screen within the Query tool where I can then dump the output to a file OR a way of running my query using the PSQL tool and redirecting the output to a file on my computer. An amateur who is up a creek without any paddle.. Carl E Eriksson
JOB | DBA (Tokyo)
Hello, I'm working with an employer that is looking to hire a DBA for their office in Tokyo. They use MySQL but are looking to migrate in the near future to Postgres. Consequently, I had hoped some members of this group may like to discuss. I can be reached using "JamesBTobin (at) Gmail (dot) Com". Kind regards, James
Re: create a temp table in SPI
On Thu, 2023-07-13 at 13:12 +0800, 黄宁 wrote:
> I want to create some temporary tables in SPI, but after I created the table
> and inserted the data, I can’t query any data, why?
>
> the postgres version:
> PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
> code:
>
>
> int ret = 0;
> SPI_connect();
> ret = SPI_execute("CREATE GLOBAL TEMP TABLE temp_table (id int, value
> text)", false, 0);
>
> ret = SPI_execute("INSERT INTO temp_table VALUES (1, 'a'), (2, 'b')",
> false, 0);
>
> ret = SPI_execute("SELECT * FROM temp_table", true, 0);
>
> if(SPI_processed > 0)
...
That's because you set "read_only" to "true" when you executed the query,
so that the command counter is not incremented, and the query cannot see
the results from the previous statement.
The documentation is quite clear here:
It is generally unwise to mix read-only and read-write commands within
a single function using SPI; that could result in very confusing behavior,
since the read-only queries would not see the results of any database
updates done by the read-write queries.
Yours,
Laurenz Albe
Re: EDB to Postgres Migration
On Thu, 2023-07-13 at 11:58 +0530, KK CHN wrote: > Recently I happened to have managed a few EDB instances running on the > EDB-10 version . > > I am looking for an option for migrating all these EDB instances to Postgres > Community edition. If you are not using any of EDB's special featurs, a simple pg_upgrade could work. Otherwise, things can become tricky. Test well. Yours, Laurenz Albe
create a temp table in SPI
I want to create some temporary tables in SPI, but after I created the
table and inserted the data, I can’t query any data, why?
the postgres version:
PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
code:
int ret = 0;
SPI_connect();
ret = SPI_execute("CREATE GLOBAL TEMP TABLE temp_table (id int, value
text)", false, 0);
if(ret != SPI_OK_UTILITY)
{
elog(ERROR, "%s: create failed", __FUNCTION__);
}
ret = SPI_execute("INSERT INTO temp_table VALUES (1, 'a'), (2, 'b')",
false, 0);
if(ret != SPI_OK_INSERT)
{
elog(ERROR, "%s: insert failed", __FUNCTION__);
}
// SPI_commit();
ret = SPI_execute("SELECT * FROM temp_table", true, 0);
if(ret != SPI_OK_SELECT)
{
elog(ERROR, "%s: select failed", __FUNCTION__);
}
if(SPI_processed > 0)
{
}
else {
// elog(ERROR, "%s: find nothing", __FUNCTION__);
}
// SPITupleTable *tuptable = SPI_tuptable;
// TupleDesc tupdesc = tuptable->tupdesc;
// // the numvals is 0,but I insert two records
// if (tuptable->numvals > 0)
// {
// HeapTuple tuple = tuptable->vals[0];
// char *tupleval1 = SPI_getvalue(tuple, tupdesc, 1);
// char *tupleval2 = SPI_getvalue(tuple, tupdesc, 2);
// pfree(tupleval1);
// pfree(tupleval2);
// }
// else
// {
// elog(ERROR, "%s: can not query something", __FUNCTION__);
// }
// do something with tuptable
SPI_finish();
pg_restore mostly idle on restoring a large number of tables
Hi, restoring a 1.5TB database with about 800k tables on i3.4xlarge AWS instace, PgSQL V12.15 on Ubuntu. Running pg_restore with -j 16, I noticed the pg_restore is busy for an hour or so with IO at 80%+ and then most of processes start idling and only a few doing some work, disk IO at 1-2%, pg_stat_activity is mostly idle, same goes for CPU, and this state proceeds for further 6 hours, disk space increases very slowly. I thought because of a lot of small tables, number of workers should be increased to increase parallel efficiency, so I tried with -j 128. The situation was somewhat better, but most of the workers start idling, again disk IO lowers to about 4% util, CPU util goes to about 4%, too. Stracing workers produces the perpetual read call on most pg_restore workers: # strace -p 59567 strace: Process 59567 attached read(3, With only about 10 or so (out of 128) workers doing some actual work: strace -p 59367 -e sendto strace: Process 59367 attached sendto(4, "Q\0\0\0\263ALTER TABLE ONLY raw.space0"..., 180, MSG_NOSIGNAL, NULL, 0) = 180 sendto(4, "Q\0\0\0.TRUNCATE TABLE ONLY raw.spa"..., 47, MSG_NOSIGNAL, NULL, 0) = 47 sendto(4, "Q\0\0\0\205COPY raw.space000448117 (da"..., 134, MSG_NOSIGNAL, NULL, 0) = sendto(4, "Q\0\0\0\vCOMMIT\0", 12, MSG_NOSIGNAL, NULL, 0) = 12 sendto(4, "Q\0\0\0pCREATE INDEX idx_space00044"..., 113, MSG_NOSIGNAL, NULL, 0) = 113 sendto(4, "Q\0\0\0\263ALTER TABLE ONLY raw.space0"..., 180, MSG_NOSIGNAL, NULL, 0) = 180 ... .. some lines omitted I would think that all workers would proceed with creating indexes and doing some useful work until the restore is finished completely? Most of the tables are very small, 2 indexes per table and without any foreign references etc., we have a multi tenant environment. Thanks, Boris
EDB to Postgres Migration
List, Recently I happened to have managed a few EDB instances running on the EDB-10 version . I am looking for an option for migrating all these EDB instances to Postgres Community edition. 1. What major steps / actions involved ( in bird's eye view ) for a successful migration to postgres community edition . ( From EDB 10 to Postgres 14 ) 2. What major challenges are involved? (or any hurdles ?) Please enlighten me with your experience.. Any reference links most welcome .. PS: - The EDB instances are live and in production.. I can get a down time ( 5 to 15 Minutes Maximum) Or can live porting and upgrading to postgres 14 is possible with minimal downtime ? Request your guidance, Krishane.
