script libraries?
as another Oracle DBA trying to pick up Postgresql one thing i haven't come across are script libraries such as there are for Oracle ( https://github.com/tanelpoder/tpt-oracle and https://oracle-base.com/dba/scripts as examples). Does anything like that exist for PG? Would be nice to see how people navigate through PG on the command line. Thanks!
Re: script libraries?
čt 30. 4. 2020 v 15:54 odesílatel Chris Stephens napsal: > as another Oracle DBA trying to pick up Postgresql one thing i haven't > come across are script libraries such as there are for Oracle ( > https://github.com/tanelpoder/tpt-oracle and > https://oracle-base.com/dba/scripts as examples). > > Does anything like that exist for PG? Would be nice to see how people > navigate through PG on the command line. > There is not too much - Postgres is significantly compact and needs less admin work There are some query set's https://github.com/gsusrafael/postgres-psql-for-dba https://github.com/NikolayS/postgres_dba https://wiki.postgresql.org/wiki/Category:Performance_Snippets https://wiki.postgresql.org/wiki/Category:Snippets Regards Pavel > Thanks! >
Re: script libraries?
Chris Stephens schrieb am 30.04.2020 um 15:54: as another Oracle DBA trying to pick up Postgresql one thing i haven't come across are script libraries such as there are for Oracle (https://github.com/tanelpoder/tpt-oracle and https://oracle-base.com/dba/scripts as examples). Does anything like that exist for PG? Would be nice to see how people navigate through PG on the command line. The Postgres Wiki has some scripts: https://wiki.postgresql.org/wiki/Category:Snippets Then there is a collection from pgExperets: https://github.com/pgexperts/pgx_scripts
Re: Detecting renamed columns via pgouput in logical replication ?
On 2020-Apr-27, Andreas Andreakis wrote: > Are there plans to add comprehensive schema change detection abilities via > logical replication ? either by embedding more information into pgoutput or > perhaps by embedding the schema DDLs ? There aren't any plans currently that I am aware of. There was a project to implement this a few years ago (part of which was mine) but it's not complete and I haven't heard of anyone working on completing it. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: pg_stat_statements : how to catch non successfully finished statements ?
Tom Lane-2 wrote > legrand legrand < > legrand_legrand@ > > writes: >> So the only solution is to had queryId to ErrorData in this hook >> or create a new hook fired on ERROR and containing queryId ? > > I see no particular need for a new hook. What's needed here is for > pgss_ExecutorRun (and maybe some other existing functions in > pg_stat_statements) to go ahead and record the statement when they catch > an error thrown out of standard_ExecutorRun, rather than just updating > the module's nested_level variable and re-throwing. > > The hard part here is that you have to be really careful what you do in > a PG_CATCH block, because the only thing you know for sure about the > backend's state is that it's not good. Catalog fetches are right out, > and anything that might itself throw an error had best be avoided as > well. (Which, among other things, means that examining executor state > would be a bad idea, and I'm not even sure you'd want to traverse the plan > tree.) > > I'm not convinced that it's practical for pg_stat_statements to make a new > shared hashtable entry under those constraints. But figuring out how to > minimize the risks around that is the stumbling block, not lack of a hook. > > regards, tom lane As far as I have been testing this with *cancelled* queries (Cancel, pg_cancel_backend(), statement_timeout, ...), I haven't found any problem. Would limiting the PG_CATCH block to thoses *cancelled* queries and *no other error*, be an alternate solution ? If yes, is there a way to identify what was the reason of the error when entering the PG_CATCH block (and point me to any exemple) ? Thanks in advance. Regards PAscal -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: pg_stat_statements : how to catch non successfully finished statements ?
legrand legrand writes: > Tom Lane-2 wrote >> The hard part here is that you have to be really careful what you do in >> a PG_CATCH block, because the only thing you know for sure about the >> backend's state is that it's not good. Catalog fetches are right out, >> and anything that might itself throw an error had best be avoided as >> well. (Which, among other things, means that examining executor state >> would be a bad idea, and I'm not even sure you'd want to traverse the plan >> tree.) >> I'm not convinced that it's practical for pg_stat_statements to make a new >> shared hashtable entry under those constraints. But figuring out how to >> minimize the risks around that is the stumbling block, not lack of a hook. > As far as I have been testing this with *cancelled* queries (Cancel, > pg_cancel_backend(), statement_timeout, ...), I haven't found any problem. > Would limiting the PG_CATCH block to thoses *cancelled* queries > and *no other error*, be an alternate solution ? I do not see that that would make one iota of difference to the risk that the executor state tree is inconsistent at the instant the error is thrown. You can't test your way to the conclusion that it's safe, either (much less that it'd remain safe); your test cases surely haven't hit every CHECK_FOR_INTERRUPTS call in the backend. regards, tom lane
