pg_ctl.exe deleted on abrupt shutdown of Windows
Team, After abrupt shutdown of Windows, we are seeing the pg_ctl.exe file getting deleted automatically. Due to this Postgres service cannot be started and need to be re-installed. We have checked the antivirus and confirmed that it didn't delete the file. Can you let us know if anyone has observed the file getting deleted upon abrupt shutdown of Windows? Thanks, Joel
Optimize query
Hi How would you optimize a query with greater than in where clause eg select * from table1 where id > 1000 and there is an index on id column regards
Re: Optimize query
Hi Yambu, the btree index type is optimized for this kind of operators (>, >=, <, <=, =). For deep understanding on how to plan an query optimization I would recommend https://classroom.google.com/c/MTQ4MzczNDExMjM4 and https://use-the-index-luke.com. Another tip is use in the select clause only the columns that are significant to answer the question you user is making executing this query. Sorry for my foreign english. Best regards. Raúl. El mar, 15 dic 2020 a las 11:18, Yambu () escribió: > Hi > > How would you optimize a query with greater than in where clause eg > > select * from table1 where id > 1000 > > and there is an index on id column > > regards >
Re: Optimize query
On 12/15/20 8:17 AM, Yambu wrote: Hi How would you optimize a query with greater than in where clause eg select * from table1 where id > 1000 and there is an index on id column The question as written is unanswerable. - Why do you think it needs to be optimized? - What is the table cardinality, and the query cardinality? - What does EXPLAIN say? - Have you run ANALYZE on table1? Bottom line: Postgres *might* think that it is more efficient to scan the whole table. -- Angular momentum makes the world go 'round.
Re: pg_ctl.exe deleted on abrupt shutdown of Windows
On 12/15/20 2:44 AM, Joel Mariadasan (jomariad) wrote: Team, After abrupt shutdown of Windows, we are seeing the pg_ctl.exe file getting deleted automatically. Due to this Postgres service cannot be started and need to be re-installed. We have checked the antivirus and confirmed that it didn’t delete the file. Can you let us know if anyone has observed the file getting deleted upon abrupt shutdown of Windows? What is causing the shutdown? Thanks, Joel -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_ctl.exe deleted on abrupt shutdown of Windows
On 12/15/20 4:44 AM, Joel Mariadasan (jomariad) wrote: Team, After abrupt shutdown of Windows, we are seeing the pg_ctl.exe file getting deleted automatically. Maybe English is your second language, but "we are seeing" means that Windows regularly shuts down, and deletes pg_ctl.exe every time. Therefore, you must be reinstalling pg_ctl.exe every time. Due to this Postgres service cannot be started and need to be re-installed. We have checked the antivirus and confirmed that it didn’t delete the file. Can you let us know if anyone has observed the file getting deleted upon abrupt shutdown of Windows? *Only* pg_ctl.exe gets deleted? Anyway, there's nothing in Postgres that says "delete pg_ctl.exe on startup". This smells strongly of filesystem corruption which requires a Windows guru. -- Angular momentum makes the world go 'round.
postgres auditing truncates and deletes on tables
for an RDS Aurora PostgreSQL v11.7 database I want to setup DDL and truncate and delete auditing. I am setting log_statement to DDL, to log all DDLs (this excludes truncate statements). I also want to setup auditing only for deletes and truncates on tables. I don't want to set log_statement to all, as this would fill up my logs. Checking in pg_audit, it supports write class which includes delete and truncate, but it also includes write/update and copy which I don't need. Is there any way to setup auditing to audit DDL plus deletes/truncates only using pg_audit/native auditing?
Re: pg_ctl.exe deleted on abrupt shutdown of Windows
On Tue, 2020-12-15 at 10:00 -0600, Ron wrote: > > After abrupt shutdown of Windows, we are seeing the pg_ctl.exe file getting > > deleted automatically. > > Only pg_ctl.exe gets deleted? Anyway, there's nothing in Postgres that says > "delete pg_ctl.exe on startup". > This smells strongly of filesystem corruption which requires a Windows guru. Not that I am one, but this smacks of anti-virus software that mistakenly thinks "pg_ctl.exe" is malware and removes or "isolates" it. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: pg_ctl.exe deleted on abrupt shutdown of Windows
On 12/15/20 11:26 AM, Laurenz Albe wrote: On Tue, 2020-12-15 at 10:00 -0600, Ron wrote: After abrupt shutdown of Windows, we are seeing the pg_ctl.exe file getting deleted automatically. Only pg_ctl.exe gets deleted? Anyway, there's nothing in Postgres that says "delete pg_ctl.exe on startup". This smells strongly of filesystem corruption which requires a Windows guru. Not that I am one, but this smacks of anti-virus software that mistakenly thinks "pg_ctl.exe" is malware and removes or "isolates" it. OP mentioned that they checked that AV didn't "delete" the file. Nothing about quarantine, though. -- Angular momentum makes the world go 'round.
Re: pg_ctl.exe deleted on abrupt shutdown of Windows
Hi, > On 15. Dec, 2020, at 18:37, Ron wrote: > > On 12/15/20 11:26 AM, Laurenz Albe wrote: >> On Tue, 2020-12-15 at 10:00 -0600, Ron wrote: After abrupt shutdown of Windows, we are seeing the pg_ctl.exe file getting deleted automatically. >>> Only pg_ctl.exe gets deleted? Anyway, there's nothing in Postgres that >>> says "delete pg_ctl.exe on startup". >>> This smells strongly of filesystem corruption which requires a Windows guru. >> Not that I am one, but this smacks of anti-virus software that mistakenly >> thinks "pg_ctl.exe" is malware and removes or "isolates" it. > > OP mentioned that they checked that AV didn't "delete" the file. Nothing > about quarantine, though. I'm no Windows guru either, but AFAIK, some AV software moves "infected" files some place else, i.e. out of the bin directory. That means, could it be possible that it's "deleted" from the bin directory, but be put somewhere else in a quarantine directory? A "dir /s" or something like that may help. And what does "shutdown" mean? Stop of the service, or proper shutdown of Windows, or sudden power-off? Or losing a network drive which the PostgreSQL software resides on and hence killing the running database, or what does "shutdown" mean in this context? What is an "*abrupt* shutdown"? Just a thought. There's probably not much to speculate on unless there are more details. Cheers, Paul
