Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Lesser-known Postgres features

hakibenita.com

Post reply on HN
Print view View original

161–167 of 167 posts

Re: Lesser-known Postgres features

#161

Post by Semaphor »

jrochkind1 wrote: post #160
Semaphor wrote: post #147

Earlier quoted context omitted.

> I think putting internal PK's in URLs or anything else publicly exposed is a bad idea Why? Both as a user and as a dev I love that I can just change the PK to get to a specific post, item, whatever instead of changing the whole link.

I mean, the links are going to be a template either way, so you can change the identifier part to change the item; it's just a question of whether the identifier portion is the internal rdbms PK, or something else. It's considered undesirable because it's basically an "implementation detail", it's good to let the internal PK change without having to change the public-facing URLs, or sometimes vice versa, when there a…

Thanks, especially those links are helpful, will read through it :)

Re: Lesser-known Postgres features

#162

Post by hn_throwaway_99 »

netcraft wrote: post #19
gavinray wrote: post #14

I want to stress the importance of not using id int SERIAL If you are on a somewhat recent version of postgres, please do yourself a favor and use: id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY An "identity column", the part here: https://hakibenita.com/postgresql-unknown-features#prevent-s... You might think this is trivial -- but SERIAL creates an "owned" (by a certain user) sequence behind the scenes, and so…

I'll stop short of giving a recommendation or using the word "should", but ill give encouragement to consider using uuid's for keys. I have used them in several systems and have never had any issues with them, and they solve so many issues. The ability to generate a key on the client or on the server or in the database is great for one. And the fact that keys are unique not only in a table but in the system (or many…

What will really be nice is when UUID V7 has wide support, because in many ways it is the best of all possible worlds:

1. The UUIDs are generated in increasing order, so none of the b-tree issues others have mentioned with fully random UUIDs.

2. They're true UUIDs, so migrating between DBs is easy, ensuring uniqueness across DBs is easy, etc.

3. They also have the benefit of having a significant amount of randomness, so if you have a bug that doesn't do an appropriate access check somewhere they are more resistant to someone trying to guess the ID from a previous one.

Re: Lesser-known Postgres features

#163

Post by ForHackernews »

filomeno wrote: post #159
ForHackernews wrote: post #80

Surprised to not see any mention of Postgres native pub/sub messaging: https://vijayg.com/postgresql-pubsub/ Is that feature considered well-known? Or is it so obscure this author didn't know about it?

I read that blog post but I don't quite understand it. I assumed it would work as a regular pub/sub pattern where you get notified when some event happens. However, in the attached example, they still poll the database every half second. I'm not sure I understand the idea.

It's maybe not a great post, it was just one that came up when I searched.

> JDBC driver cannot receive asynchronous notifications.

Maybe their example is limited by the choice of client library.

This might be a better example? https://tapoueh.org/blog/2018/07/postgresql-listen-notify/

Or the offical postgres docs are:

https://www.postgresql.org/docs/14/sql-notify.html

https://www.postgresql.org/docs/14/sql-listen.html

https://www.postgresql.org/docs/14/libpq-notify.html

Re: Lesser-known Postgres features

#164

Post by filomeno »

ForHackernews wrote: post #163
filomeno wrote: post #159

Earlier quoted context omitted.

I read that blog post but I don't quite understand it. I assumed it would work as a regular pub/sub pattern where you get notified when some event happens. However, in the attached example, they still poll the database every half second. I'm not sure I understand the idea.

It's maybe not a great post, it was just one that came up when I searched. > JDBC driver cannot receive asynchronous notifications. Maybe their example is limited by the choice of client library. This might be a better example? https://tapoueh.org/blog/2018/07/postgresql-listen-notify/ Or the offical postgres docs are: https://www.postgresql.org/docs/14/sql-notify.html https://www.postgresql.org/docs/14/sql-listen.ht…

Thanks, it now makes sense.

Quite strange that this specific driver didn't support asynchronous notifications: if you have to poll the database anyway, there's no much difference between doing it without listen/notify support, I guess.

Re: Lesser-known Postgres features

#165

Post by nextaccountic »

afiori wrote: post #154
AlfeG wrote: post #151

Earlier quoted context omitted.

My guess is that Iin 99% collision could occur during insert. Most of the time db will just not allow to insert duplicated record.

my worries were more about access control, it is sort of fine if a costumer experiences data loss because an insert fails and the application doesn't retry, it is less fine if a collision causes a user's documents to be swapped with another user's docoment and they end up showing kinky porn on a live press conference. Sort of the distinction between unspecified behaviour and undefined behaviour in C.

The application shouldn't report the insert as successful if it actually failed. That way, the user don't go around thinking the insert actually succeeded, and there is no data loss (if it didn't succeed it must be retried, by the app or manually)

You need some UX like an error message in a red rectangle or something.

Re: Lesser-known Postgres features

#166

Post by afiori »

nextaccountic wrote: post #165
afiori wrote: post #154

Earlier quoted context omitted.

my worries were more about access control, it is sort of fine if a costumer experiences data loss because an insert fails and the application doesn't retry, it is less fine if a collision causes a user's documents to be swapped with another user's docoment and they end up showing kinky porn on a live press conference. Sort of the distinction between unspecified behaviour and undefined behaviour in C.

The application shouldn't report the insert as successful if it actually failed. That way, the user don't go around thinking the insert actually succeeded, and there is no data loss (if it didn't succeed it must be retried, by the app or manually) You need some UX like an error message in a red rectangle or something.

there would be data loss if the insert/update would meet a collision and either decided to overwrite the record or to report the operation as already completed (the latter is what I imagine git would do for a collision scenario).

the solution might as well just be not to care about this case (no sarcasm).

Re: Lesser-known Postgres features

#167

Post by nextaccountic »

afiori wrote: post #166
nextaccountic wrote: post #165

Earlier quoted context omitted.

The application shouldn't report the insert as successful if it actually failed. That way, the user don't go around thinking the insert actually succeeded, and there is no data loss (if it didn't succeed it must be retried, by the app or manually) You need some UX like an error message in a red rectangle or something.

there would be data loss if the insert/update would meet a collision and either decided to overwrite the record or to report the operation as already completed (the latter is what I imagine git would do for a collision scenario). the solution might as well just be not to care about this case (no sarcasm).

But if we're talking about a database insert and there's a collision (and the uuid column has an unique constraint - as it should, since it's meant to be a primary key), then the insert will not be successful.

Your application might not care about treating this error, but the DB will report it.

Post reply on HN