PG: Figuring out how rows got deleted

csn

Somehow, thousands of rows in one of my databases got deleted. I can't figure out how. '\s' in psql doesn't show a delete command. Nobody else has accessed the server. My only guess is that perhaps I had a psql open, it was in a transaction, then I closed it or something. But I haven't been using transactions. The rows that got deleted were inserted by a ruby script days ago, and I'd been able to use the rows since then, until today. Doesn't PG have a change/transaction log that might yield some clues? There's nothing in PG's normal log, except 'db1 LOG: unexpected EOF on client connection'.

Sxooter

Did someone run an indexing engine or something like that across all your pages and one of your pages have a "delete" link?

csn

That shouldn't be possible, as I haven't implemented any delete code at this point. SQL injection shouldn't be possible either, not that I suspect that. It's really bizarre - it's exactly like a transaction began, the data was inserted, was visible to all other connections, then was rolled back days later; or old data was loaded from a dump or the original database I migrated from (neither of which happened that I'm aware of).

Hmm, thinking more about it, I coded a Rails action that inserted the data then loaded that in my browser. It was taking forever, so I stopped the browser. Today I had to kill and restart that Rails web server since it was using up over 600K in memory. I wonder if it had a transaction open this whole time. But that shouldn't affect data inserted by other connections...

Sxooter

Could it be a transaction wraparound issue? It takes >2 billion unvacuumed transactions to cause this.

Oh, and if this is a development machine, you should turn on query logging (as well as any other logging that looks useful for your enviro.)

Sxooter

Could ot be a transaction wraparound issue? It takes >2 billion unvacuumed transactions to cause this.

csn

I'm nowhere near 2 billion 😉.

A query log... Rails does that in development mode 🙂. I forgot my 'update' action had a delete statement in it. Looking at the query log, it was called by Alexa. How Alexa found out about the non-public URL, and why it's calling it (3 times), I don't know. I don't have it installed, that I'm aware of. Weird. I need to protect that 'update' action ... 😉