Search Postgresql Archives

Re: postgres wish list

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Sameer Mahajan wrote:
I recently worked on rewriting a fairly complex Berkeley DB based system
using Postgres DB. Following  is a wish list and a brief explanation for
each of those items for postgres. It is not necessarily a comparison of
postgres functionality with that of Berkeley DB but some required useful
functionality currently lacking in postgres. I would like to have some
feedback, comments, suggestions regarding these.

*         Shared memory based connectivity: As such postgres has client
- server model. The TCP-IP nature of its connectivity further adds to
the latency of this communication. It will be nice to have a shared
memory based connectivity between libpq front end and the back end.
Use UNIX domain sockets. You eliminate the need for TCP/IP completely, 
and get rather lower latency and faster communication between client and 
server. It's not shared memory, but it's really rather close in 
performance terms.
Unlike with Berkeley DB, PostgreSQL's server runs at a different 
privilege level to its clients. Using shared memory for client/server 
communication would be more complex when you consider the security 
issues involved. Additionally, my understanding is that the backend is 
used to being able to arrange its memory in the most efficient way 
without worrying about what the client expects to be where and when, 
concerning its self over the accessibility of data the client doesn't 
have permission to see, etc.
o   Nested transaction funtionality: I followed quite a few discussions
stating postgres considering it at some time but later abandoning it due
to some complexity. The "savepoints" are also believed to provide
similar semantics. However it is useful and handy to have the simple
nested transaction syntax and functionality.
I guess I can see the appeal at the application programming level if 
porting an app that's used to nested transactions. Personally I'm very 
happy with savepoints, though - what about them doesn't satisfy your 
particular needs?
I'd expect to be able to translate a BEGIN within an existing 
transaction to a `SAVEPOINT <generated-name>', a COMMIT to `RELEASE 
SAVEPOINT <generated-name>' and a ROLLBACK to `ROLLBACK TO SAVEPOINT 
<generated-name>'. Wouldn't that achieve the desired effect? Or is the 
problem that you don't want your application code to have to know 
whether it's being called from within an existing transaction or not?
If the latter is the issue, you can provide a fairly simple 
context-aware wrapper that does the right thing.
trans_begin(connection_object)
  -> examines transaction state of connection object
     and if no active transaction issues BEGIN, otherwise
     generates and stores a savepoint name.

etc.

You'd just use a simple wrapper structure around the libpq connection object to track your state.
*         Cursors

o   It would be useful to have updateable cursors. Otherwise the
application is required to figure out a required INSERT versus UPDATE
amongst other things.
Pg does have updatable cursors.

http://www.postgresql.org/docs/current/static/sql-declare.html

See "WHERE CURRENT OF" in:

http://www.postgresql.org/docs/current/static/sql-update.html
http://www.postgresql.org/docs/current/static/sql-delete.html

Also, for Pl/PgSQL:

http://www.postgresql.org/docs/current/static/plpgsql-cursors.html

o   Berkeley DB has various positioning (e.g. DB_SET_RANGE ) as well as
operational (e.g. DB_NOOVERWRITE)  options in its cursors. Postgres can
provide such functionality (e.g. using underlying btree for an index to
give DB_SET_RANGE like positioning) very easily.
Maybe you could explain what the options you find useful are, and 
provide examples as to why existing PostgreSQL functionality doesn't do 
what you need? It might help people understand what you want and why.
*         Configurable error handling control: in case of fatal errors
the transaction is aborted by default. It would be useful to make some
configurable options available for the application to control the error
handling in such situations. Berkeley DB has an option where all the
exceptions can be turned off and the system returns only error codes
which are then required to be handled by the application.
I've mostly worked with Pg via psycopg (Python), PL/PgSQL running within 
Pg, and with JDBC (often wrapped by Hibernate). In all these cases error 
handling has been pretty flexible and easy.
I assume what you're really getting at is that you want an implicit 
savepoint before each statement that's released after successful 
statement execution, or rolled back to then released if the statement 
fails and before error information is reported to the application?
If so, while AFAIK there's no built-in facility for this, it's not 
difficult to implement at the application level. Personally I'm dubious 
about the value of including something like that in Pg its self, as it 
encourages apps to throw queries at the DB and see if they work, rather 
than issuing queries that're designed to always succeed. For example, 
rather than (application pseudocode):
sql.execute("SAVEPOINT x; INSERT INTO tablename (key, value) VALUES (1, 
9);");
if (sql.error == unique_violation)
{
sql.execute("ROLLBACK TO SAVEPOINT x; UPDATE tablename SET value = 9 WHERE key = 1;");
}
sql.execute("RELEASE SAVEPOINT x;");


... it's probably better to do:

sql.execute("UPDATE tablename SET value = 9 WHERE key = 1; INSERT INTO tablename (key, value) SELECT 1, 9 WHERE NOT EXISTS(SELECT 1 FROM tablename WHERE key = 1);");
The above will update the value if it's already present, or failing that 
insert a new record with that value. It can be done in a batch with far 
fewer DB round trips and you avoid the overhead of creating and 
releasing savepoints all the time.
In this particular case it'd be nice if Pg had a "MERGE" / "UPSERT" 
feature to handle this case but you can probably see my point.
I've rewritten code that used to use try-and-see (either in client code 
or Pl/PgSQL) to work in an always-succeed style and found that it often 
runs literally hundreds of times faster. It's also much more amenable to 
 tricks like loading a temp table full of dirty data and running a 
single query over it to do the work you want to, which can be VASTLY 
faster than having the app send it all over piecemeal. Once you're 
dealing with network latency with remote clients this starts to become 
very significant.
--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux