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

PostgreSQL 9.3 Beta 1 Released

postgresql.org

Post reply on HN
Print view View original

111–118 of 118 posts

Re: PostgreSQL 9.3 Beta 1 Released

#111

Post by moonlight80 »

btown wrote: post #27
masklinn wrote: post #9

The short log is missing one of the most important and awesome new feature to application writers: a bunch of new PQresultErrorFields[0] on constraint failure, providing access to (context-dependent) the raw schema name, table name, column name, constraint name and datatype involved. Previously these remained locked into the database and to get programmatically-useable info (for logging, better error messages or to t…

Where would one find out if anyone is working on a patch for ActiveRecord?

I think ActiveRecord pattern is more limiting you than helpful. I found the following post quite interesting: http://mindref.blogspot.com/2013/02/sql-vs-orm.html

Re: PostgreSQL 9.3 Beta 1 Released

#112

Post by moonlight80 »

lneves wrote: post #89
jeffdavis wrote: post #48

So, what's still missing in postgres? To make the responses as constructive as possible, please also specify: * Whether you currently use postgres, and what other systems you use (presumably ones that do have the feature that is missing in postgres) * Whether you currently need the feature, or whether you anticipate the need in the future, or whether you expect that other people will need it * Whether the missing fea…

I've been using postgres for a few years now, I also use MSSQL, MonetDB and MySQL. Some of the things I miss most frequently are: - Easy table partitioning. - Support for "phrase search" in fts. - MERGE/UPSERT support. - Columnar storage (and related optimizations) for "OLAP like" workloads. - EXPLAIN ANALYSE that traces functions.

We migrated recently from M$ $QL to postgres and are VERY happy with this move.

Re: PostgreSQL 9.3 Beta 1 Released

#113

Post by ocharles »

baudehlo wrote: post #85
joevandyk wrote: post #70

Earlier quoted context omitted.

I could really use UPSERT. I'm using postgresql right now. My use case: I could have a table like: create table items (id uuid primary key, attributes json); I'd have a webapp using javascript for adding/editing new items. If the user wants to add a new item, the client code would generate the uuid. Editing and adding new rows would both use the same UPSERT/MERGE code -- no need for separate insert or update statemen…

For this I currently use: UPDATE Table SET ... WHERE id=$1 if (rows_updated == 0) { INSERT INTO Table ... WHERE NOT EXISTS (SELECT 1 FROM Table WHERE id=$1) if (rows_inserted == 0) { throw "Conflict!" } } It's a bit of a hack, but works well enough. I agree would be nice to have a more transparent way of doing it.

This is very susceptible to race conditions, which is why many of us want true UPSERT semantics. http://www.postgresql.org/docs/current/static/plpgsql-contro... is the current best way to do UPSERT in PG.

Re: PostgreSQL 9.3 Beta 1 Released

#114

Post by skinnymuch »

moonlight80 wrote: post #112
lneves wrote: post #89

Earlier quoted context omitted.

I've been using postgres for a few years now, I also use MSSQL, MonetDB and MySQL. Some of the things I miss most frequently are: - Easy table partitioning. - Support for "phrase search" in fts. - MERGE/UPSERT support. - Columnar storage (and related optimizations) for "OLAP like" workloads. - EXPLAIN ANALYSE that traces functions.

We migrated recently from M$ $QL to postgres and are VERY happy with this move.

> M$ $QL

Really?

Heh, hopefully you're consistent and have similar antics for the likes of Apple, Google, et al

Re: PostgreSQL 9.3 Beta 1 Released

#115

Post by epo »

ocharles wrote: post #113
baudehlo wrote: post #85

Earlier quoted context omitted.

For this I currently use: UPDATE Table SET ... WHERE id=$1 if (rows_updated == 0) { INSERT INTO Table ... WHERE NOT EXISTS (SELECT 1 FROM Table WHERE id=$1) if (rows_inserted == 0) { throw "Conflict!" } } It's a bit of a hack, but works well enough. I agree would be nice to have a more transparent way of doing it.

This is very susceptible to race conditions, which is why many of us want true UPSERT semantics. http://www.postgresql.org/docs/current/static/plpgsql-contro... is the current best way to do UPSERT in PG.

So if there is a way to do UPSERT what is the problem? Are people just complaining about the lack of syntactic sugar?

Re: PostgreSQL 9.3 Beta 1 Released

#116

Post by masklinn »

epo wrote: post #115
ocharles wrote: post #113

Earlier quoted context omitted.

This is very susceptible to race conditions, which is why many of us want true UPSERT semantics. http://www.postgresql.org/docs/current/static/plpgsql-contro... is the current best way to do UPSERT in PG.

So if there is a way to do UPSERT what is the problem? Are people just complaining about the lack of syntactic sugar?

> what is the problem?

Quoting ocharles:

> susceptible to race conditions, which is why many of us want true UPSERT semantics

The snippet provided in the postgres doc is the current best, but it's still has a number of flaws:

> This coding assumes the unique_violation error is caused by the INSERT, and not by, say, an INSERT in a trigger function on the table. It might also misbehave if there is more than one unique index on the table, since it will retry the operation regardless of which index caused the error. More safety could be had by using the features discussed next to check that the trapped error was the one expected.

Re: PostgreSQL 9.3 Beta 1 Released

#117

Post by andyzweb »

jeffdavis wrote: post #48

So, what's still missing in postgres? To make the responses as constructive as possible, please also specify: * Whether you currently use postgres, and what other systems you use (presumably ones that do have the feature that is missing in postgres) * Whether you currently need the feature, or whether you anticipate the need in the future, or whether you expect that other people will need it * Whether the missing fea…

Temporal tables conforming closely to SQL:2011. The ability to use temporary tables created with ON COMMIT DROP in non-serializable prepared transactions (so probably only READ-COMMITTED transactions).

* we currently use postgresql, oracle, mysql, teradata, mssql

Re: PostgreSQL 9.3 Beta 1 Released

#118

Post by crypto5 »

olavgg wrote: post #95
crypto5 wrote: post #82

Earlier quoted context omitted.

page level(not field level) snappy compression

You can use PostgreSQL together with ZFS and with lz4 compression enabled and maybe gzip will improve performance even more. http://www.citusdata.com/blog/64-zfs-compression Btrfs also supports compression.

This solution doesn't look production solid for linux server. I am not sure about lz4, but gzip in my cassandra experience is significantly reducing performance, Db needs spend too much CPU on compression. Snappy works just fine.
Post reply on HN