Re: getting tables list of other schema too
Hello Atul, You can use set a filter to limit the tables returned, i.e: \dt college.* HTH, Thomas Le mer. 24 févr. 2021 à 08:54, Atul Kumar a écrit : > Hi, > > I have postgres 9.6 cluster running on centos 7 machine. > > when I set search_path to any user made schema with below command > > [enterprisedb@stg-edb02 ~ 01:51:39]$ psql edb > edb=# \c test > > set search_path to college; > > and after listing the tables with command \dt, we should get list of > tables of schema college only. > > but here I am getting list of tables of schema college and list of > tables of schema sys along with it. > > > Why is it happening, please suggest. > > > test=# \dt > List of relations > Schema | Name | Type |Owner > +-+---+-- > college | ta_rule_error | table | college > college | team_import | table | college > college | test_24022021 | table | enterprisedb > sys| callback_queue_table| table | enterprisedb > sys| dual| table | enterprisedb > sys| edb$session_wait_history| table | enterprisedb > sys| edb$session_waits | table | enterprisedb > sys| edb$snap| table | enterprisedb > sys| edb$stat_all_indexes| table | enterprisedb > sys| edb$stat_all_tables | table | enterprisedb > sys| edb$stat_database | table | enterprisedb > sys| edb$statio_all_indexes | table | enterprisedb > sys| edb$statio_all_tables | table | enterprisedb > sys| edb$system_waits| table | enterprisedb > sys| plsql_profiler_rawdata | table | enterprisedb > sys| plsql_profiler_runs | table | enterprisedb > sys| plsql_profiler_units| table | enterprisedb > sys| product_component_version | table | enterprisedb > sys| scheduler_0100_component_name | table | college > sys| scheduler_0200_program | table | college > sys| scheduler_0250_program_argument | table | college > sys| scheduler_0300_schedule | table | college > sys| scheduler_0400_job | table | college > sys| scheduler_0450_job_argument | table | college > > >
Re: getting tables list of other schema too
yes I know that, but my doubt is why \dt is showing tables of other schemas even I am setting the search_path. Regards, Atul On 2/24/21, Thomas Boussekey wrote: > Hello Atul, > > You can use set a filter to limit the tables returned, i.e: > > \dt college.* > > HTH, > Thomas > > Le mer. 24 févr. 2021 à 08:54, Atul Kumar a écrit : > >> Hi, >> >> I have postgres 9.6 cluster running on centos 7 machine. >> >> when I set search_path to any user made schema with below command >> >> [enterprisedb@stg-edb02 ~ 01:51:39]$ psql edb >> edb=# \c test >> >> set search_path to college; >> >> and after listing the tables with command \dt, we should get list of >> tables of schema college only. >> >> but here I am getting list of tables of schema college and list of >> tables of schema sys along with it. >> >> >> Why is it happening, please suggest. >> >> >> test=# \dt >> List of relations >> Schema | Name | Type |Owner >> +-+---+-- >> college | ta_rule_error | table | college >> college | team_import | table | college >> college | test_24022021 | table | enterprisedb >> sys| callback_queue_table| table | enterprisedb >> sys| dual| table | enterprisedb >> sys| edb$session_wait_history| table | enterprisedb >> sys| edb$session_waits | table | enterprisedb >> sys| edb$snap| table | enterprisedb >> sys| edb$stat_all_indexes| table | enterprisedb >> sys| edb$stat_all_tables | table | enterprisedb >> sys| edb$stat_database | table | enterprisedb >> sys| edb$statio_all_indexes | table | enterprisedb >> sys| edb$statio_all_tables | table | enterprisedb >> sys| edb$system_waits| table | enterprisedb >> sys| plsql_profiler_rawdata | table | enterprisedb >> sys| plsql_profiler_runs | table | enterprisedb >> sys| plsql_profiler_units| table | enterprisedb >> sys| product_component_version | table | enterprisedb >> sys| scheduler_0100_component_name | table | college >> sys| scheduler_0200_program | table | college >> sys| scheduler_0250_program_argument | table | college >> sys| scheduler_0300_schedule | table | college >> sys| scheduler_0400_job | table | college >> sys| scheduler_0450_job_argument | table | college >> >> >> >
Re: getting tables list of other schema too
On Wed, 2021-02-24 at 15:54 +0530, Atul Kumar wrote: > > > > I have postgres 9.6 cluster running on centos 7 machine. > > > when I set search_path to any user made schema with below command > > > [enterprisedb@stg-edb02 ~ 01:51:39]$ psql edb > > > edb=# \c test > > > set search_path to college; > > > and after listing the tables with command \dt, we should get list of > > > tables of schema college only. > > > but here I am getting list of tables of schema college and list of > > > tables of schema sys along with it. > > > Why is it happening, please suggest. > > > test=# \dt > > > List of relations > > > Schema | Name | Type |Owner > > > +-+---+-- > > > college | ta_rule_error | table | college > > > college | team_import | table | college > > > college | test_24022021 | table | enterprisedb > > > sys| callback_queue_table| table | enterprisedb > > > sys| dual| table | enterprisedb > > > sys| edb$session_wait_history| table | enterprisedb > > > sys| edb$session_waits | table | enterprisedb > > > sys| edb$snap| table | enterprisedb > > > sys| edb$stat_all_indexes| table | enterprisedb > > > sys| edb$stat_all_tables | table | enterprisedb > > > sys| edb$stat_database | table | enterprisedb > > > sys| edb$statio_all_indexes | table | enterprisedb > > > sys| edb$statio_all_tables | table | enterprisedb > > > sys| edb$system_waits| table | enterprisedb > > > sys| plsql_profiler_rawdata | table | enterprisedb > > > sys| plsql_profiler_runs | table | enterprisedb > > > sys| plsql_profiler_units| table | enterprisedb > > > sys| product_component_version | table | enterprisedb > > > sys| scheduler_0100_component_name | table | college > > > sys| scheduler_0200_program | table | college > > > sys| scheduler_0250_program_argument | table | college > > > sys| scheduler_0300_schedule | table | college > > > sys| scheduler_0400_job | table | college > > > sys| scheduler_0450_job_argument | table | college > > yes I know that, but my doubt is why \dt is showing tables of other > schemas even I am setting the search_path. The problem is that you are not running PostgreSQL, but EnterpriseDB's closed source fork, and they obviously hacked the "search_path" so that it automatically includes a "sys" schema, but they were not consistent enough to exclude that schema from "\dt". You could complain to EnterpriseDB --- in my opinion, that schema should only show up in "\dtS" output. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: getting tables list of other schema too
Atul: On Wed, Feb 24, 2021 at 11:24 AM Atul Kumar wrote: > yes I know that, but my doubt is why \dt is showing tables of other > schemas even I am setting the search_path. AFAIK dt list "tables", not "tables in the schemas in search path". It states " By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.", but these sys schema does not seem to be a system one. Francisco Olarte.
Re: getting tables list of other schema too
On Wed, Feb 24, 2021 at 12:12 PM Francisco Olarte wrote: > AFAIK dt list "tables", not "tables in the schemas in search path". > It states " By default, only user-created objects are shown; supply a > pattern or the S modifier to include system objects.", but these sys > schema does not seem to be a system one. Zap it, after (incorrectly after posting) searching for "schema" in the docs I found, in a paragraph far, far, away, \d* uses search path. Francisco Olarte.
Different query result, maybe bad index
Hello. We have three servers running postgres 9.6, master and two slaves feeded by streaming replication. On of those slaves we are getting different query results. The replica was recreated from scratch, but problem persists. If set enable_indexscan='off' and set enable_bitmapscan='off' we get correct result. It looks like problem with some index...? Can anybody saw such behaviour? I'll then send of course the table, index, etc. definition if necessary. Thank you. Best regards Václav
Re: Different query result, maybe bad index
On Wed, 2021-02-24 at 12:50 +0100, Václav Steiner wrote: > We have three servers running postgres 9.6, master and two slaves feeded by > streaming replication. > On of those slaves we are getting different query results. The replica was > recreated from scratch, but problem persists. > > If set enable_indexscan='off' and set enable_bitmapscan='off' we get correct > result. > It looks like problem with some index...? > > Can anybody saw such behaviour? If the problem persist when you rebuild the standby, I suspect that the servers are running different C libraries or C library versions, so that the collations with the same name behave slightly differently. That could lead to the observed behavior with indexes on string data types. Make sure to use the same C library version on both systems. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Different query result, maybe bad index
Hi, On Wed, Feb 24, 2021 at 7:50 PM Václav Steiner wrote: > > We have three servers running postgres 9.6, master and two slaves feeded by > streaming replication. > On of those slaves we are getting different query results. The replica was > recreated from scratch, but problem persists. > > If set enable_indexscan='off' and set enable_bitmapscan='off' we get correct > result. > It looks like problem with some index...? > > Can anybody saw such behaviour? Are those indexes on collatable data type? If yes, the most likely explanation would be that both server have different underlying library. If you're on GNU/Linux and one of the server has glibc 2.28 and the other doesn't, don't look any further. See for reference https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html > I'll then send of course the table, index, etc. definition if necessary. That would be helpful, including detail on the server itself.
Batch update million records in prd DB
Hello, Now need to update several million records in a table in prd DB, if can use batch update 1000 records and commit each time, if it will affect prd application like below sample script please? Sample script: DO $MAIN$ DECLARE affect_count integer; chunk_size CONSTANT integer :=1000; sleep_sec CONSTANT numeric :=0.1; BEGIN loop exit when affect_count=0; UPDATE tbl a SET name = '' WHERE a.id IN (SELECT id FROM tbl b WHERE name IS NULL LIMIT chunk_size); GET DIAGNOSTICS affect_count = ROW_COUNT; commit; PERFORM pg_sleep(sleep_sec); end loop; END; $MAIN$; Thanks and best regards
Re: getting tables list of other schema too
I am sorry but I am not clear from your response, as I have created another instance with same version 9.6 but there no system schema or its tables are visible. Please help. On 2/24/21, Francisco Olarte wrote: > On Wed, Feb 24, 2021 at 12:12 PM Francisco Olarte > wrote: >> AFAIK dt list "tables", not "tables in the schemas in search path". >> It states " By default, only user-created objects are shown; supply a >> pattern or the S modifier to include system objects.", but these sys >> schema does not seem to be a system one. > > Zap it, after (incorrectly after posting) searching for "schema" in > the docs I found, in a paragraph far, far, away, \d* uses search path. > > Francisco Olarte. >
Re: Batch update million records in prd DB
Of course it will impact a system using that table, but not significant I expect and the production system should handle it. If you are committing like this, then you can kill the script at any time and not lose any work. The query to find the next IDs to update is probably the slowest part of this depending on what indexes you have.
Re: Slow index creation
Thanks for all the suggestions, When the server is not in use for mission-critical work, I'll definitely going to do some testing based on your ideas. Will let you know what comes out of that Cheers, Paul On Thu, Feb 18, 2021 at 6:49 PM hubert depesz lubaczewski wrote: > On Thu, Feb 18, 2021 at 10:24:25AM -0700, Michael Lewis wrote: > > [1] > https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/ > > > > Thanks for this reference. I enjoy your blog, but haven't made the time > to read all the archives somehow. Stuff doesn't stick very > > well when it isn't yet "needed" info besides. > > I have seen overhead from 'raise notice' in small functions that are > sometimes called many thousands of times in a single query, but > > hadn't done the test to verify if the same overhead still exists for > raise debug or another level below both client_min_messages > > and log_min_messages. Using your examples, I saw about .006 ms for each > call to RAISE DEBUG with a client/log_min as notice/warning. > > Sure, this overhead is definitely possible, but kinda besides the point > - there will be some slowdowns in other places, and it will be good to > track them. > That's why I suggested to do it on small sample of data. > > Best regards, > > depesz > >
Re: Slow index creation
Why not create a table with cols a, b, c and d. Where you insert a row for each combination and key and index abc then return d? ons. 24. feb. 2021, 21:15 skrev Paul van der Linden < paul.doskabou...@gmail.com>: > Thanks for all the suggestions, > > When the server is not in use for mission-critical work, I'll definitely > going to do some testing based on your ideas. > Will let you know what comes out of that > > Cheers, > Paul > > On Thu, Feb 18, 2021 at 6:49 PM hubert depesz lubaczewski < > dep...@depesz.com> wrote: > >> On Thu, Feb 18, 2021 at 10:24:25AM -0700, Michael Lewis wrote: >> > [1] >> https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/ >> > >> > Thanks for this reference. I enjoy your blog, but haven't made the time >> to read all the archives somehow. Stuff doesn't stick very >> > well when it isn't yet "needed" info besides. >> > I have seen overhead from 'raise notice' in small functions that are >> sometimes called many thousands of times in a single query, but >> > hadn't done the test to verify if the same overhead still exists for >> raise debug or another level below both client_min_messages >> > and log_min_messages. Using your examples, I saw about .006 ms for each >> call to RAISE DEBUG with a client/log_min as notice/warning. >> >> Sure, this overhead is definitely possible, but kinda besides the point >> - there will be some slowdowns in other places, and it will be good to >> track them. >> That's why I suggested to do it on small sample of data. >> >> Best regards, >> >> depesz >> >>
