postgressomethi

I always thought there should be a two-arg overload of new, so you could write new(bool, true) or new(int, 20). Would solve the problem without any trickery.

A Nintendo Switch is a non-hardware decryption system?

Could "effectively controls access" be attacked here? The purpose of the hardware is not to control access.

You might think adding a UNIQUE index would cause the "losing" xact to get constraint errors, but instead both xacts succeed and no longer have a race condition.

This is not true. What happens is that the (sub)transaction that loses the race to the index is aborted:

  =# INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);
  ERROR:  duplicate key value violates unique constraint "foo_bar_idx"
  DETAIL:  Key (bar)=(2) already exists.

Imagine booting your computer and having to give an email to login offline.

As much as I hate what modern Windows has become, this is not actually true. If you know the correct sequence of clicks you can avoid this. Not defending this bullshit, though.

Ah, now I see I responded to the wrong message.

I'm sorry, I wasn't being clear. Any column meaning "this row is to be ignored for most applications" is an annoying pattern.

While that's kind of convenient in a lot of ways, it also makes querying the database really annoying, since you have to remember to add the filtering to every single query or you're screwed. Personally, I wish there was a database-implementation-acknowledged "deleted" flag, which could even expose a "history table"-like interface.

Sequences kind of have the same issue, because you don't know if a gap is because of a rollback or an uncommitted transaction. Though with some logic you can do a pretty good job at this with sequences. And then you're not in the realm of "simple" anymore, at all.

Polling an updated_at column is not robust in its most simple form, as transactions are not guaranteed to commit in that order.

No.

That's still, in my opinion, overkill and unnecessary.

Adding a column, changing column's nullability and adding/changing constraints is already zero-downtime in PG.

Making a previously nullable column NOT NULL is not zero downtime. Neither are adding constraints -- except in some cases with NOT VALID, which isn't quite the same thing.

Dropping a column and changing the data type are not zero-downtime.

Dropping a column is zero downtime.

I don't like really solutions which force everything into a single schema just to do migrations. They shouldn't be that difficult.

In this particular case, rather than making everything a view all the time, you could just use views during a migration window to get the same effect. Replace the table with a view which has all the columns plus the new name as an alias for the old one, migrate all the clients, replace the view with the table with the column renamed. No special permanent crap necessary.

This approach doesn’t work for LISTEN because typically a client will listen for its entire lifecycle so you can’t share connections in a pool.

But you only need one connection for LISTEN per database, total. So I'm confused why this is made out to be a big issue.

Can this quote die already? Rob has repeatedly demonstrated he doesn't know what's best for everyone.

LISTEN ties up one connection completely

I've seen this twice in this thread, but I don't know what that means. Can you explain a bit?

Huh? The VPN isn't for privacy.

Unless you edit pg_ident.conf, your postgres install will not listen for connections outside of on localhost.

I don't know what the defaults are, but pg_ident.conf has absolutely nothing to do with this. The main configuration file (I think postgresql.conf usually) has listen_addresses, which controls the addresses on which postgres listens, as you might guess.

pg_hba.conf (not pg_ident.conf) controls the authentication methods the server asks from the client, depending on how they're connecting.