unrelated to this particular release, but since we're talking about postgres... how do people find the ability to write stored procedures and functions in languages other than just SQL? I'm coming from a MSSQL shop and curious how writing a query in python for example has benefited anyone if at all -- did you / can you use modules like numpy or pandas in a postgres python procedure?
PostgreSQL 10 Beta 1 Released
91–100 of 172 posts
Re: PostgreSQL 10 Beta 1 Released
#92Earlier quoted context omitted.
Except that it doesn't scale like MongoDB does. How sharding / cluster works? By default isn't Postgres a single master?
I would say that it isn't configured to scale like Mongo out of the box...but that doesn't mean it can't. You can go outside of Postgres core to get multi-master solutions with easy sharding and clustering...with the open sourcing of CitusDB and 2nd Quadrant's pglogical and BDR extensions there are options out there. You can also roll your own (if you really want)...and it is relatively approachable to do so using bu…
None of what you posted is built in and thus supported by the vendor.
That may not matter to you but it's a deal breaker for those of us in enterprises. We can't just be rolling our own versions of PostgreSQL and we can't use CitusDB when it is not supported by other vendors for use with their products.
The point still remains that after all these year PostgreSQL's scalability story is still a mess.
Re: PostgreSQL 10 Beta 1 Released
#93Earlier quoted context omitted.
Thanks for the explanation! update comment set (modified, body) = (select now(), 'edited comment') where id = 123; You're right, it works the same without the `row` keyword in 9.6.
Actually now that I re-read the grammar, sub-selects are yet another accepted syntax. Looks like the ROW syntax doesn't support row expressions like I thought. Not really sure the benefit beside making it easier to programmatically construct UPDATEs.
It's for when you want to update more than one column with values from the same record in another table, eg:
UPDATE books
SET (title, isbn) = (
SELECT title, isbn FROM other_books
WHERE other_books.foo = books.bar);
UPDATE books
SET title = other_books.title, isbn=other_books.isbn
FROM other_books
WHERE other_books.foo = books.bar;Re: PostgreSQL 10 Beta 1 Released
#94Earlier quoted context omitted.
My claim wasn't that it didn't fulfill their needs, it was that it doesn't fulfill all needs (gamedev is one example that I'm familiar with). Postgres storing JSON types != All mongo functionality I'm sure I could achieve everything I'm doing in mongo by some roundabout way in Postgres, but if you're doing a large amount of reading/modifying partial fields within JSON structure, it's the exact use case for mongo.
https://www.github.com/JerrySievert/MongoLike that said, I actually use partial JSONB updates on a regular basis, but I tend to use PLV8 to do the heavy lifting.
What is the point of posting that ?
(1) It's a proof of concept, (2) it hasn't been updated in 3 years and (3) it still isn't the same syntax as MongoDB.
The point still remains that PostgreSQL isn't just a 1-1 replacement for MongoDB which is pretty common sense to me anyway.
Re: PostgreSQL 10 Beta 1 Released
#95Earlier quoted context omitted.
Actually now that I re-read the grammar, sub-selects are yet another accepted syntax. Looks like the ROW syntax doesn't support row expressions like I thought. Not really sure the benefit beside making it easier to programmatically construct UPDATEs.
It's for when you want to update more than one column with values from the same record in another table, eg: UPDATE books SET (title, isbn) = ( SELECT title, isbn FROM other_books WHERE other_books.foo = books.bar); You can also do it with UPDATE ... FROM (which may be more efficient), but that's a PostgreSQL extension, while the added-in-9.5 syntax is SQL standard. UPDATE books SET title = other_books.title, isbn=ot…
That's not the row syntax though, that's the sub-select syntax (my initial confusion). i.e., there are three options according to the grammar:
SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT ) }
Re: PostgreSQL 10 Beta 1 Released
#96I wished they would implement more from SQL:2011. I have a lot of applications that would benefit from system versioned tables. This is a good sum up of useful modern SQL features: https://www.slideshare.net/MarkusWinand/modern-sql
this slide deck is pure gold, just what i needed. thanks!
Glad I could help!
Here's the recording for that very presentation, by the way. https://www.youtube.com/watch?v=8wMybGTlf8I
Re: PostgreSQL 10 Beta 1 Released
#97I know this sounds icky to some, but what I really want from Postgres is a proper equivalent to MSSQL's FILESTREAM. I know, I know, "databases are bad for files" - but let's take something like an ECM suite where images and documents are literally part of a transaction, having to synchronize those between filesystem and database breaks the Atomic constraint in so many ways. PostgreSQL has LOB support, but oid's being…
Postgres dev here. > but what I really want from Postgres is a proper equivalent to MSSQL's FILESTREAM. What sizes of files and such are you interested in? What kind of read/write patterns? I do think we need some improvements in the area. Not enough that I'll drop the other stuff I'm working on, which I think is higher priority, but enough to discuss approaches and review patches. It'd be cool if you could comment o…
Small files, 25-50K on average (images of patient charts), and they're all they're all write-once read-many.
Re: PostgreSQL 10 Beta 1 Released
#98The native table partitioning makes me so happy. I'd been doing this for years with really hacky external modules and tons of triggers. Sadly, even then there were always weird edge cases. Postgres really has become the most versatile database out there. I cringe whenever I have to work with MySQL again...
I work with MySQL in my current job. After years of Postgres it feels like dealing with some parody of database.
Re: PostgreSQL 10 Beta 1 Released
#99Earlier quoted context omitted.
Then you might as well use pg.
I think he meant that you can turn off fsyncs in pg in order to add random data loss :-)
You might be interested in this paper where the author touches on fsync.
https://people.freebsd.org/~seanc/postgresql/scale15x-2017-p...
Re: PostgreSQL 10 Beta 1 Released
#100Earlier quoted context omitted.
You would get compression with Postgres running on ZFS.
Database plus Copy-On-Write file systems sound like a bad idea. I am imagining a modest 100gb database being re-written for every change. I am sure there is some way to work around this, but wouldn't this be the default behavior with a typical database and typical COW file system?
You might be interested in reading this paper: https://people.freebsd.org/~seanc/postgresql/scale15x-2017-p...