Re: [GENERAL] documentation for lo_* functions
Hello Pawel, 2014-11-17 11:55 GMT+03:00 Pawel Veselov pawel.vese...@gmail.com: Hi. Where is the proper documentation for lo_* functions (e.g. lo_open) that are available as SQL functions? I see libpq functions documented in /static/lo-interfaces.html, but not the SQL ones (from pg_catalog). Here http://www.postgresql.org/docs/9.4/static/lo-funcs.html There are additional server-side functions corresponding to each of the client-side functions described earlier; indeed, for the most part the client-side functions are simply interfaces to the equivalent server-side functions. -- // Dmitriy.
Re: [GENERAL] Integrating C++ singletons into postgresql extensions???
Hello, 2014-10-18 3:59 GMT+04:00 Stephen Woodbridge wood...@swoodbridge.com: Hi, I've been writing C++ code that needs to get wrapped into a postgresql extension and it has a few singleton classes like: 1. Config object that holds some general configuration information. 2. Stats object for global stats collection. 3. Logger object for enabling/disabling debug logging to a file. 4. curlpp class for making outbound calls to an OSRM web service from the c++ code I can probably do without 1-3 if I had to when the code is integrated, but they are handy to keep around in the code for when we are developing and working outside the postgresql database. I could #ifdef then all, but I would like to avoid cluttering the code with tons of #ifdef. Item 4 is a little more problematic and needs to be kept around or something else implemented to take its place. I have looked at the curl extension and actual built another module in C that uses those ideas., but the curlpp is a nice OO class that encapsules and hides all the curl stuff from the C++ code. So my question(s) are: 1. Any insights into singletons working/not working within the postgresql server extensions? My understanding is that these are implemented as global static classes with a lifetime of the process and they have no destructor, so I'm a little worried about memory leaks or multiple queries sharing the singleton. 2. Is there another way implement the equivalent of a singleton for C++ code that has a lifetime of the query in the server? 3. What do for logging classes when integrating C++ code into postgresql? Any help or ideas would be appreciated. I would have written extension in pure C. And I would get rid of singletones. (Singletone - artificial constraints that the programmer creates for himself. Btw, C++ - too :-) ) I would use Extension Configuration Tables to store the configuration http://www.postgresql.org/docs/9.4/static/extend-extensions.html#AEN57238 -- // Dmitriy.
Re: [GENERAL] Why isn't Java support part of Postgresql core?
2014-09-15 9:22 GMT+04:00 cowwoc cow...@bbs.darktech.org: Hi, Out of curiosity, why is Postgresql's Java support so poor? I am specifically looking for the ability to write triggers in Java. I took a look at the PL/Java project and it looked both incomplete and dead, yet other languages like Javascript are taking off. I would have expected to see very strong support for Java because it's the most frequently used language on the server-side. What's going on? Why isn't this a core language supported alongside SQL, Perl and Python as part of the core project? Out of curiosity and what's going on? Why there is no Common Lisp in PostgresSQL's core? :-) -- // Dmitriy.
Re: [GENERAL] Async IO HTTP server frontend for PostgreSQL
Hello, David 2014-09-10 4:31 GMT+04:00 David Boreham david_l...@boreham.org: Hi Dmitriy, are you able to say a little about what's driving your quest for async http-to-pg ? I'm curious as to the motivations, and whether they match up with some of my own reasons for wanting to use low-thread-count solutions. For many web projects I consider Postgres as a development platform. Thus, I prefer to keep the business logic (data integrity trigger functions and API functions) in the database. Because of nature of the Web, many concurrent clients can request a site and I want to serve maximum possible of them with minimal overhead. Also I want to avoid a complex solutions. So, I believe that with asynchronous solution it's possible to *stream* the data from the database to the maximum number of clients (which possible can request my site over a slow connection). -- // Dmitriy.
Re: [GENERAL] Async IO HTTP server frontend for PostgreSQL
Hello, John 2014-09-10 17:25 GMT+04:00 John DeSoi de...@pgedit.com: On Sep 9, 2014, at 7:31 PM, David Boreham david_l...@boreham.org wrote: Hi Dmitriy, are you able to say a little about what's driving your quest for async http-to-pg ? I'm curious as to the motivations, and whether they match up with some of my own reasons for wanting to use low-thread-count solutions. For some discussion and preliminary design, see also https://wiki.postgresql.org/wiki/HTTP_API John DeSoi, Ph.D. While this is not related to the %subj%, I've glanced and the link above. And I found this complex. (As many many many nice featured things on the market today.) Could you tell me please, for example, why the URLs like /databases/:database_name/schemas/:schema_name/tables/:table_name OR /databases/:database_name/schemas/:schema_name/table/:table_name/indexes/:index_name Whats wrong with SQL way to get such info from sys. catalogs or the information schema? I guess, I know the answer -- its just pretty nice and clear. But I think it's just a waste of time (of users which should learn all of these fancies, and developers which should implement them). -- // Dmitriy.
Re: [GENERAL] Async IO HTTP server frontend for PostgreSQL
Hello, Steve 2014-09-10 21:08 GMT+04:00 Steve Atkins st...@blighty.com: On Sep 10, 2014, at 12:16 AM, Dmitriy Igrishin dmit...@gmail.com wrote: Hello, David 2014-09-10 4:31 GMT+04:00 David Boreham david_l...@boreham.org: Hi Dmitriy, are you able to say a little about what's driving your quest for async http-to-pg ? I'm curious as to the motivations, and whether they match up with some of my own reasons for wanting to use low-thread-count solutions. For many web projects I consider Postgres as a development platform. Thus, I prefer to keep the business logic (data integrity trigger functions and API functions) in the database. Because of nature of the Web, many concurrent clients can request a site and I want to serve maximum possible of them with minimal overhead. Also I want to avoid a complex solutions. So, I believe that with asynchronous solution it's possible to *stream* the data from the database to the maximum number of clients (which possible can request my site over a slow connection). That's going to require you to have one database connection open for each client. If the client is over a slow connection it'll keep the database connection open far longer than is needed, (compared to the usual pull data from the database as fast as the disks will go, then spoonfeed it out to the slow client approach). Requiring a live database backend for every open client connection doesn't seem like a good idea if you're supporting many slow concurrent clients. Good point. Thus, some of caching on the HTTP server side should be implemented then. -- // Dmitriy.
Re: [GENERAL] Async IO HTTP server frontend for PostgreSQL
Hello, Allan 2014-09-11 0:29 GMT+04:00 Allan Kamau kamaual...@gmail.com: Dear Dmitriy, To add on to David's suggestions, Data caching is a difficult task to undertake. Consider an example where your data may not all fit into memory, when you cache these data outside PostgreSQL you would need to look into memory management as well as issues around concurrent population of this cache as well as means to keep the data in the cache fresh in tune with any changes to the data. These are no trivial tasks and the database community has spent years constructing and improving algorithms to do this on behalf of the front end database application developer. Also each time a TCP connection is created, additional compute resources are consumed by the OS as well as the database management server software. Memory - limited resource. Hence, it's possible to catch out of memory everywhere. By caching in this case I mean flushing the data retrieved by HTTP server from the database server on the disk during socket read-ready event dispatching if the retrieved data cannot be send to the HTTP-client (socket not write-ready yet). When the socket to the HTTP-client became write-ready the data will be streamed from cache. I believe, it's not hard to implement it. And note, there is no need to make such cache shared. It's just a temporary files with live time of HTTP request dispatching. A simpler way would be to use connection pooling where a thread of your running application borrows a connection from a pool of open connections, executes the SQL command then returns the connection immediately on completion of the SQL command. This will require few concurrent connections (depending of configuration) and let the database do the caching of the data for you. For effective database data caching may need to make adjustments of the PostgreSQL configuration file (postgresql.conf file) as well as the operating system resources configuration. This way the response time of your client application will degrade gracefully with the increase of concurrent client requests. In practice, this approach works. How well? It works. But with thread/connection pools your site can be blocked by attacker (cool-hacker) by initiating many slow concurrent connections. Using threads a good only if you're doing a lot of CPU work, rather than communication work. For small number of concurrent connections, the speed advantage direct “streaming” solution may have over the traditional connection pooling solution may hardly be noticeable to end user. The easier way to increase response time is to look into PostgreSQL performance tuning as well as investing in faster hardware (mainly the the disk subsystem and more RAM). Yes, I agree. Good and fast hardware is always good :-). But every hardware will has limited resources anyway. And I believe, that for many tasks async solution will help to leverage it to the max. Regards, Allan. On Wed, Sep 10, 2014 at 8:25 PM, Dmitriy Igrishin dmit...@gmail.com wrote: Hello, Steve 2014-09-10 21:08 GMT+04:00 Steve Atkins st...@blighty.com: On Sep 10, 2014, at 12:16 AM, Dmitriy Igrishin dmit...@gmail.com wrote: Hello, David 2014-09-10 4:31 GMT+04:00 David Boreham david_l...@boreham.org: Hi Dmitriy, are you able to say a little about what's driving your quest for async http-to-pg ? I'm curious as to the motivations, and whether they match up with some of my own reasons for wanting to use low-thread-count solutions. For many web projects I consider Postgres as a development platform. Thus, I prefer to keep the business logic (data integrity trigger functions and API functions) in the database. Because of nature of the Web, many concurrent clients can request a site and I want to serve maximum possible of them with minimal overhead. Also I want to avoid a complex solutions. So, I believe that with asynchronous solution it's possible to *stream* the data from the database to the maximum number of clients (which possible can request my site over a slow connection). That's going to require you to have one database connection open for each client. If the client is over a slow connection it'll keep the database connection open far longer than is needed, (compared to the usual pull data from the database as fast as the disks will go, then spoonfeed it out to the slow client approach). Requiring a live database backend for every open client connection doesn't seem like a good idea if you're supporting many slow concurrent clients. Good point. Thus, some of caching on the HTTP server side should be implemented then. -- // Dmitriy. -- // Dmitriy.
Re: [GENERAL] Async IO HTTP server frontend for PostgreSQL
2014-09-09 1:28 GMT+04:00 Merlin Moncure mmonc...@gmail.com:
On Mon, Sep 8, 2014 at 12:59 PM, Dmitriy Igrishin dmit...@gmail.com
wrote:
Dear community,
I need a %subj% -- high performance HTTP server solution
based on asynchronous IO with ability to run PostgreSQL's
functions from HTML templates asynchronously and passing
the results to the HTTP client.
For example, consider a simple template:
html
div id=rows
${get_rows(id := :id)}
/div
/html
The function get_rows() will be called asynchronously
during the dispatching HTTP request and the result of
it will streams immediately to the HTTP client via async IO.
Currently, I've found only a module for NGINX
https://github.com/FRiCKLE/ngx_postgres
but it does not what I need.
Ideally, it should be a simple C (libevent based) or C++
(boost::asio based) library.
Please, if anyone has a ready solution of the idea described
above, let me know, because I don't want waste my time to write
it from scratch.
It's not in C, but you should take a very good look at node.js.
merlin
Yeah, it looks interesting and AFAIK there are already bindings
for node.js to asynchronous libpq's API --
https://github.com/brianc/node-postgres/blob/master/src/binding.cc#L43
Thanks for the point, Merlin.
--
// Dmitriy.
[GENERAL] Async IO HTTP server frontend for PostgreSQL
Dear community,
I need a %subj% -- high performance HTTP server solution
based on asynchronous IO with ability to run PostgreSQL's
functions from HTML templates asynchronously and passing
the results to the HTTP client.
For example, consider a simple template:
html
div id=rows
${get_rows(id := :id)}
/div
/html
The function get_rows() will be called asynchronously
during the dispatching HTTP request and the result of
it will streams immediately to the HTTP client via async IO.
Currently, I've found only a module for NGINX
https://github.com/FRiCKLE/ngx_postgres
but it does not what I need.
Ideally, it should be a simple C (libevent based) or C++
(boost::asio based) library.
Please, if anyone has a ready solution of the idea described
above, let me know, because I don't want waste my time to write
it from scratch.
--
// Dmitriy.
Re: [GENERAL] How to discard partially retrieved result set with the C API?
2014-02-18 13:44 GMT+04:00 邓尧 tors...@gmail.com: When single row mode is enabled, after retrieving part of the result set, I'm no longer interested in the rest of it (due to error handling or other reasons). How can I discard the result set without repeatedly calling PQgetResult() in such situation ? The result set may be quite large and it's inefficient to call PQgetResult() repeatedly, so it's necessary to do so sometimes. Thanks Yao I think you should use PQcancel(). -- // Dmitriy.
Re: [GENERAL] Drop all overloads of a function without knowing parameter types
2014-02-03 Evan Martin postgre...@realityexists.net: Thanks for that oid::regprocedure trick! A query like this is fairly simple once you know it, but completely non-obvious when you don't. I'm not sure what conditions others want to search on (couldn't find it in the list archives), but by qualified function name seems like the obvious one. If you don't wish to add that to the core, how about a system function that does this and is mentioned in the documentation for DROP FUNCTION? That way, if people have other criteria they can find the function, look at its source and adapt it to their needs. If you don't want to add a function, either, it would be good to at least document this (on the DROP FUNCTION page). Something like Note: DROP FUNCTION does not allow you to drop a function without knowing its argument types, but you can use the following script to drop all overloads of a given function name... The function name (as well as the name of any other database object, such as view or rule) can have a prefix or suffix. (We are prefix our functions with the class name to consider them as methods in terms of object-oriented design.) Thus, such system function for dropping functions should accept something like a regular expression as it argument. Moreover, how about other database objects which can be dropped: views, triggers, rules, domains etc etc. For completeness it is necessary the existence of system functions for dropping these objects too. So, I am with Tom here. -- // Dmitry.
Re: [GENERAL] Temporary table already exists
2014-01-31 Albe Laurenz laurenz.a...@wien.gv.at: mephysto wrote: Hi Albe, this is code of my stored function: CREATE OR REPLACE FUNCTION :FUNCTION_SCHEMA.get_deck_types [...] BEGIN [...] CREATE LOCAL TEMPORARY TABLE deck_types ON COMMIT DROP AS SELECT stored_functions_v0.get_card_deck_types(t1.id_master_card) AS deck_type_ids FROM ccg_schema.deck_composition T0 ,ccg_schema.cards_per_user T1 WHERE id_deck = p_id_deck AND t1.id_owner = l_id_user AND t0.id_card = t1.id_card; [...] END; ConnectionPool reuse connections, of course, but how you can see from my code, the temporary table deck_types are already defined with ON COMMIT DROP clause, so I think that my work is not in transaction. Am I true? If so, how can I put my code in transaction? Hmm, unless you explicitly use the SQL statements BEGIN (or START TRANSACTION) and COMMIT, PostgreSQL would execute each statement in its own connection. In this case, the statement that contains the function call would be in its own connection, and you should be fine. There are two things I can think of: - The function is called more than once in one SQL statement. - You use longer transactions without being aware of it (something in your stack does it unbeknownst to you). You could try to set log_statement to all and see what SQL actually gets sent to the database. You could also include EXECUTE 'DROP TABLE deck_types'; in your function. I would recommend to use DISCARD ALL before returning the connection to the pool anyway. But it's not about current problem. The OP's problem is about why ON COMMIT DROP does not work. -- // Dmitry.
Re: [GENERAL] Positional parameters and question mark notation
Hey Andreas, 2014/1/20 Andreas Lubensky luben...@cognitec.com Hi, Is there any way to make PostgreSQL support the question mark (?) notation instead of the dollar sign ($1, $2...) for prepared statements? Most databases use the question mark notation and we have a set of simple queries that are supposed to be portable across different database systems. So far we use PostgreSQL via ODBC, but now we want to switch to a native libpq implementation. I believe that C-function for replacing '?' with '$n' can easily implemented in the application code. Or you may want to look at libpqtypes: http://libpqtypes.esilo.com/ Or (if you are C++) you may want to look at SOCI: http://soci.sourceforge.net/ -- // Dmitriy.
[GENERAL] Return value of current_user before calling SECURITY DEFINER function.
Hello, Is there are way to determine a subject? Thanks. -- // Dmitriy.
Re: [GENERAL] Return value of current_user before calling SECURITY DEFINER function.
For clarity, is it possible to write a SECURITY DEFINER function which returns a value of current_user at the moment of call? 2013/12/4 Dmitriy Igrishin dmit...@gmail.com Hello, Is there are way to determine a subject? Thanks. -- // Dmitriy. -- // Dmitriy.
Re: [GENERAL] Return value of current_user before calling SECURITY DEFINER function.
2013/12/4 Pavel Stehule pavel.steh...@gmail.com Hello pls, try session_user Not really :-(. The user can perform SET ROLE before calling the SECURITY DEFINER function and I need to know who is the caller at the time of call. I need something like a calling_user() function... -- // Dmitriy.
Re: [GENERAL] Return value of current_user before calling SECURITY DEFINER function.
In other words, I need to get an information about *current* role of the caller inside the SECURITY DEFINER function. 2013/12/4 Dmitriy Igrishin dmit...@gmail.com 2013/12/4 Pavel Stehule pavel.steh...@gmail.com Hello pls, try session_user Not really :-(. The user can perform SET ROLE before calling the SECURITY DEFINER function and I need to know who is the caller at the time of call. I need something like a calling_user() function... -- // Dmitriy. -- // Dmitriy.
Re: [GENERAL] Blowfish Encrypted String
2013/9/26 Craig Boyd craigbo...@gmail.com Hello All, I have a string in a program that I have encrypted using Blowfish and I am now trying to figure out the best way to store that in PostgreSQL so that I can store it and retrieve it later for decryption. I have searched around and have not found some good examples of how to go about this. So my first questions is: Can someone point me to a tutorial or posting that shows how one might do that? Failing that: What data type should I use to store this? I believe that you should use bytea datatype. What does the SQL look like to INSERT/SELECT the field? Just like any other INSERT/SELECT query. -- // Dmitriy.
Re: [GENERAL] [HACKERS] Frontend/backend protocol improvements proposal (request).
2013/6/28 Albe Laurenz laurenz.a...@wien.gv.at Dmitriy Igrishin wrote: Since there can be only one unnamed prepared statement per session, there should be only one such object per connection. It should not get deallocated; maybe it could be private to the connection, which only offers a parseUnnamed and executeUnnamed mathod. More precisely, there can be only one uniquely named prepared statement (named or unnamed) per session. Could you provide a signature of parseUnnamed and executeUnnamed please? I don't clearly understand this approach. I'm just brainstorming. I'm thinking of something like void Connection::prepareUnnamed(const char *query, int nParams, const Oid *paramTypes); and Result Connection::executeUnnamed(int nParams, const char * const *paramValues, const int *paramLengths, const int *paramFormats, int resultFormat); But I'm not saying that this is the perfect solution. I see. It's more like C-styled design, but not C++. If you really want your users to be able to set prepared statement names, you'd have to warn them to be careful to avoid the problem of name collision -- you'd handle the burden to them. That's of course also a possible way, but I thought you wanted to avoid that. The mentioned burden is already handled by backend which throws duplicate_prepared_statement (42P05) error. I mean the problem that you create a prepared statement, then issue DEALLOCATE stmt_name create a new prepared statement with the same name and then use the first prepared statement. I see. But I see no problem to invalidate localy allocated descriptors of the remote prepared statements Prepared_statement* pst1 = connection-describe(name); Prepared_statement* pst2 = connection-describe(name); // pst2 points to the same remote object That seems like bad design to me. I wouldn't allow different objects pointing to the same prepared statement. What is the benefit? Shouldn't the model represent reality? Well, then the C and C++ languages are bad designed too, because they allow to have as many pointers to the same as the user like (needs) :-) That's a different thing, because all these pointers contain the same value. So if pst1 and pst2 represent the same object, I'd like pst1 == pst2 to be true. IOW, you want to map localy allocated object to the remote one. Or course, it is possible. But I dislike approach, since: a) objects pointed by pst* should be owned by the Connection instance; b) these objects may have its own state depending on the application context (e.g., different valuess binded, result format settings etc etc.) but it will be impossible due to a). BTW, according to 18.6.3.1 of The C++ Programming Language by Stroustrup, The default meaning of and == for pointers are rarely useful as comparison criteria for the objects pointed to. So, I believe that you means that you'd like *pst1 == *pst2. And yes, I'll agree with it. Really, I don't see bad design here. Describing prepared statement multiple times will results in allocating several independent descriptors. ... but for the same prepared statement. (As with, for example, performing two SELECTs will result in allocating several independent results by libpq.) But those would be two different statement to PostgreSQL, even if the query strings are identical. Mind you, I'm not saying that I am the person that decides what is good taste and what not, I'm just sharing my sentiments. Don't worry, I'm mature enough to understand this :-) And thank you for you suggestions! Of course an error during DEALLOCATE should be ignored in that case. It's hard to conceive of a case where deallocation fails, but the connection is fine. And if the connection is closed, the statement will be deallocated anyway. Why this error should be ignored? I believe that this should be decided by the user. As a library author I don't know (and cannot know) how to react on such errors in the end applications. Again, I would say that that is a matter of taste. I just cannot think of a case where this would be important. Maybe, but I'm firmly belive, that errors, provoked by the user, should not be ignored at all. This is a one of design principles of my library. Btw, by the reason 2) there are no any transaction RAII classes as in some other libraries, because the ROLLBACK command should be executed in the destructor and may throw. I tend to believe that such errors could also be ignored. If ROLLBACK (or anything else) throws an error, the transaction will get rolled back anyway. Perhaps, but, again, I don't know how the user will prefer to react. So, I prefer just to throw and allow the user to decide. Agreed, it's a matter of taste. Yours
Re: [GENERAL] [HACKERS] Frontend/backend protocol improvements proposal (request).
2013/6/26 Albe Laurenz laurenz.a...@wien.gv.at Dmitriy Igrishin wrote: I understand the problem now. I pondered a bit over your design, and I came up with a different idea how to represent prepared statements in a C++ library. First, a prepared statement is identified by its name. To make the relationship between a PreparedStatement object and the PostgreSQL prepared statement unique, I suggest that the prepared statement name should not be exposed to the library user. It should be a private property that is set in the initializer in a unique fashion (for example, it could be a string representation of the memory address of the object). That way, there can never be a name collision. That should take care of the problem. In fact something like was implemented in very early versions of my library. There are some reasons why I've redesigned the library: 1) If the user does not specify the name of the prepared statement (or specify it as ) it is considered as unnamed prepared statement -- a one of the important concepts of the frontend/backend protocol, which is a base of my current design. The unnamed prepared statements are very useful since they are deallocated authomatically when the backend receives the next Parse message with empty name. If you want unnamed prepared statements in your library, I would use a different class for them since they behave quite differently. That would also make this concern go away. I've considered this approach also on the designing stage, but I dislike it, because Named_prepared_statement and Unnamed_prepared_statement, derived from Prepared_statement, will have exactly the same invariant. The name is a common property of all prepared statements. Since there can be only one unnamed prepared statement per session, there should be only one such object per connection. It should not get deallocated; maybe it could be private to the connection, which only offers a parseUnnamed and executeUnnamed mathod. More precisely, there can be only one uniquely named prepared statement (named or unnamed) per session. Could you provide a signature of parseUnnamed and executeUnnamed please? I don't clearly understand this approach. 2) Meaningful names of the named prepared statements (as any other database objects) may be useful while debugging the application. Imagine the memory addresses (or any other surrogate names) in the Postgres logs... That wouldn't worry me, but that's a matter of taste. Hence, the name() method should be public and name().empty() means unnamed prepared statement. You could offer a getter for the name if anybody needs it for debugging. If you really want your users to be able to set prepared statement names, you'd have to warn them to be careful to avoid the problem of name collision -- you'd handle the burden to them. That's of course also a possible way, but I thought you wanted to avoid that. The mentioned burden is already handled by backend which throws duplicate_prepared_statement (42P05) error. I also wouldn't provide a deallocate() method. A deallocated prepared statement is useless. I think that it would be more logical to put that into the destructor method. If somebody wants to get rid of the prepared statement ahead of time, they can destroy the object. I've also considered this approach and there are some reasons why I don't implemented the prepared statement class this way: 1) There are Describe message in the protocol. Thus, any prepared statement can be also described this way: Prepared_statement* pst1 = connection-describe(name); Prepared_statement* pst2 = connection-describe(name); // pst2 points to the same remote object Think about the pst as a pointer to the remote object (prepared statement). Since each statement can be described multiple times, the deleting one of them should not result in deallocating the prepared statement by the backend. That seems like bad design to me. I wouldn't allow different objects pointing to the same prepared statement. What is the benefit? Shouldn't the model represent reality? Well, then the C and C++ languages are bad designed too, because they allow to have as many pointers to the same as the user like (needs) :-) Really, I don't see bad design here. Describing prepared statement multiple times will results in allocating several independent descriptors. (As with, for example, performing two SELECTs will result in allocating several independent results by libpq.) As a bonus, the user can bind different data to independent prepared statements objects stepwise (which may be important in some cases) before executing. 2) The best way to inform the user about errors in the modern C++ are exceptions. The dellocate operation (as any other query to the database) can be result in throwing some exception. But descructors should not throw. (If you are familiar
Re: [GENERAL] [HACKERS] Frontend/backend protocol improvements proposal (request).
2013/6/25 Albe Laurenz laurenz.a...@wien.gv.at Dmitriy Igrishin wrote: While developing a C++ client library for Postgres I felt lack of extra information in command tags in the CommandComplete (B) message [...] It seems like bad design to me to keep a list of prepared statements on the client side when it is already kept on the server side (accessible with the pg_prepared_statements view). What's wrong with the following: If the user wants to deallocate an individual prepared statement, just send DEALLOCATE statement name to the server. If the statement does not exist, the server will return an error. If the user wants to deallocate all statements, just send DEALLOCATE ALL. Why do you need to track prepared statements on the client side? Thats great, but there is a some problem -- the *another* statement with the same name (and moreover with same parameters!) can be prepared after deallocating. And this can result in bugs. So, the client-side allocated pointer to the remote statement must be invalidated immediatly after deallocating. I understand the problem now. I pondered a bit over your design, and I came up with a different idea how to represent prepared statements in a C++ library. Thanks for thinking about it, Albe! First, a prepared statement is identified by its name. To make the relationship between a PreparedStatement object and the PostgreSQL prepared statement unique, I suggest that the prepared statement name should not be exposed to the library user. It should be a private property that is set in the initializer in a unique fashion (for example, it could be a string representation of the memory address of the object). That way, there can never be a name collision. That should take care of the problem. In fact something like was implemented in very early versions of my library. There are some reasons why I've redesigned the library: 1) If the user does not specify the name of the prepared statement (or specify it as ) it is considered as unnamed prepared statement -- a one of the important concepts of the frontend/backend protocol, which is a base of my current design. The unnamed prepared statements are very useful since they are deallocated authomatically when the backend receives the next Parse message with empty name. 2) Meaningful names of the named prepared statements (as any other database objects) may be useful while debugging the application. Imagine the memory addresses (or any other surrogate names) in the Postgres logs... Hence, the name() method should be public and name().empty() means unnamed prepared statement. Of course somebody could find out what the statement name is and manually issue a DEALLOCATE, but that would only cause future use of the prepared statement to fail with an error, which I think is ok. Also, if somebody uses SQL PREPARE to create a prepared statement whose name collides with one of the automatically chosen names, they get what they deserve in my opinion. It might be useful to warn users. I also wouldn't provide a deallocate() method. A deallocated prepared statement is useless. I think that it would be more logical to put that into the destructor method. If somebody wants to get rid of the prepared statement ahead of time, they can destroy the object. I've also considered this approach and there are some reasons why I don't implemented the prepared statement class this way: 1) There are Describe message in the protocol. Thus, any prepared statement can be also described this way: Prepared_statement* pst1 = connection-describe(name); Prepared_statement* pst2 = connection-describe(name); // pst2 points to the same remote object Think about the pst as a pointer to the remote object (prepared statement). Since each statement can be described multiple times, the deleting one of them should not result in deallocating the prepared statement by the backend. 2) The best way to inform the user about errors in the modern C++ are exceptions. The dellocate operation (as any other query to the database) can be result in throwing some exception. But descructors should not throw. (If you are familiar with C++ well you should know about the gotchas when destructors throw.) So, there are deallocate() method which seems to me ok. Btw, by the reason 2) there are no any transaction RAII classes as in some other libraries, because the ROLLBACK command should be executed in the destructor and may throw. Does that make sense? Thanks again for suggestions, Albe! -- // Dmitriy.
Re: [GENERAL] [HACKERS] Frontend/backend protocol improvements proposal (request).
2013/6/24 Albe Laurenz laurenz.a...@wien.gv.at
I'm moving this discussion to -general.
Okay, lets continue here.
Dmitriy Igrishin wrote:
While developing a C++ client library for Postgres I felt lack of extra
information in command tags in the CommandComplete (B) message [...]
for the following commands:
It seems like bad design to me to keep a list of prepared statements
on the client side when it is already kept on the server side
(accessible with the pg_prepared_statements view).
What's wrong with the following:
If the user wants to deallocate an individual prepared statement,
just send DEALLOCATE statement name to the server. If the
statement does not exist, the server will return an error.
If the user wants to deallocate all statements, just send
DEALLOCATE ALL.
Why do you need to track prepared statements on the client side?
Nothing wrong if the user wants to deal with scary and cumbersome code.
As library author, I want to help people make things simpler.
I don't think that anything would change on the user end.
But I think so.
To understand me, please look at the pseudo C++ code below.
// A class designed to work with prepared statements
class Prepared_statement {
public:
// Methods to generate a Bind message, like
Prepared_statement* bind(Position, Value);
// ... and more
// Methods to send Execute message, like
void execute();
void execute_async();
};
class Connection {
public:
// many stuff ...
void close();
Prepared_statement* prepare(Name, Query);
void prepare_async(Statement);
// Make yet another instance of prepared statement.
Prepared_statement* prepared_statement(Name);
// etc.
};
The Connection class is a factory for Prepared_statement instances.
As you can see, the Connection::prepare() returns new instance of
*synchronously* prepared statement. Next, the user can bind values
and execute the statement, like this:
void f(Connection* cn)
{
// Prepare unnamed statement and execute it.
cn-prepare(SELECT $1::text)-bind(0, Albe)-execute();
// Ps: don't worry about absence of delete; We are using smart
pointers :-)
}
But there is a another possible case:
void f(Connection* cn)
{
Prepared_statement* ps = cn-prepare(SELECT $1::text);
cn-close(); // THIS SHOULD invalidate all Prepared_statement
instances ...
ps-bind(0, Albe); // ... to throw the exception here
}
Attempting to send a bind message over a closed connection
will result in a PostgreSQL error. All you have to do is wrap
that into an exception of your liking.
Okay, thanks for the info.
Moreover, consider:
void f(Connection* cn)
{
Prepared_statement* ps1 = cn-prepare(ps1, SELECT $1::text);
cn-deallocate(ps1); // THIS invalidates ps1 object...
Shouldn't that be
cn-deallocate(ps1);
without quotes?
No, because Connection::deallocate(const string) considered by me as a
wrapper over
DEALLOCATE SQL command. (As any other SQL command wrapper declared as the
Connection class member.) But it can be overloaded though, but there are
Prepared_statement::deallocate(void) (without arguments) instead.
ps1-bind(0, Albe); // ... to throw the exception here
Prepared_statement* ps2 = cn-prepare(ps2, SELECT $1::text);
cn-perform(DEALLOCATE ps2); // THIS SHOULD ALSO invalidate ps2
object...
ps2-bind(0, Albe); // ... to throw the exception here
}
Again, sending a bind message for a deallocated prepared statement
will cause a PostgreSQL error automatically.
Thats great, but there is a some problem -- the *another* statement with
the same
name (and moreover with same parameters!) can be prepared after
deallocating.
And this can result in bugs. So, the client-side allocated pointer to the
remote
statement must be invalidated immediatly after deallocating.
In the latter case when the user deallocates named prepared statement
directly,
the implementation of Connection can invalidates the prepared statement
(ps2) by
analyzing and parsing CommandComplete command tag to get it's name.
And please note, that the user can send DEALLOCATE asynchronously. And
there is
only two ways to get the prepared statement (or another session
object's) name:
1) Parse the SQL command which the user is attempts to send;
2) Just get it from CommandComplete command tag.
I beleive that the 1) is a 100% bad idea.
PS: this C++11 library is not publicaly available yet, but I hope it
will this year.
I still think that it is a bad idea to track this on the client side.
What's wrong with throwing an exception when you get a PostgreSQL error?
If you want to distinguish between certain error conditions,
you can use the SQLSTATE. For example, trying to execute a deallocated
statement would cause SQLSTATE 26000.
See above why it make sense.
// Dmitriy.
Re: [GENERAL] Re: [HACKERS] Frontend/backend protocol improvements proposal (request).
2013/6/24 Tom Lane t...@sss.pgh.pa.us Albe Laurenz laurenz.a...@wien.gv.at writes: Why do you need to track prepared statements on the client side? The proposed change would fail to allow that anyway; consider the possibility of a server-side function doing one or more PREPAREs or DEALLOCATEs. The command tag would be completely inadequate for reporting that. Ah, indeed! I does not considered that. Thanks for the info. Space is also a problem, since existing clients expect the tags to be pretty short --- for instance, libpq has always had a hard-wired limit of 64 bytes (CMDSTATUS_LEN) on what it can store for the tag. That's not enough for a command name plus a full-length identifier. :-( If we were to try to do this, we'd need to invent some other reporting mechanism, perhaps similar to ParameterStatus for GUC_REPORT variables. But that would be a protocol break, which means it's unlikely to happen anytime soon. Is there are chance to add this idea in the TODO? Btw, maybe we'd need also to identify each prepared statement (and portal) not only by the name, but by the automatically generated id included by the backend in ParseComplete and then pass this id with Bind messages to let the backend throws an error if the prepared statement (portal) is deallocated? (This would be truly automatically prepared statement backend tracking as menioned by Albe.) -- // Dmitriy.
Re: [GENERAL] Function tracking
2013/6/7 Pavel Stehule pavel.steh...@gmail.com Hello 2013/6/7 Rebecca Clarke r.clark...@gmail.com: Hi all I'm looking for suggestions on the best way to track the updates to a function. We have two databases, Dev Live, so I want to update Live with just the functions that have been modified in the DEV databas3e. Is there another, easier way to track the updates than manually recording it in a document? (I'm trying to eliminate human interference). There is a few tools http://stackoverflow.com/questions/4804779/how-to-check-difference-between-two-databases-in-postgressql http://pgdiff.sourceforge.net/ But I prefer editing files for storing schema and function definitions. And I use a git. I dislike direct object modifying via tools like pgAdmin and similar. Same here. -- // Dmitriy.
[GENERAL] Confusing error message.
Hey, It seems to me, that this is confusing: dmitigr= create schema test; CREATE SCHEMA dmitigr= create table test.test(); CREATE TABLE dmitigr= table test.test; ERROR: relation test.test does not exist LINE 1: table test.test; ^ dmitigr= table test.test1; ERROR: relation test.test1 does not exist LINE 1: table test.test1; Shouldn't be first error looks like: ERROR: relation test.test does not exist LINE 1: table test.test; ^ (with quoted relation name *as specified in the query*) I've spend some time to find a bug in the application, which performed query with entire quoted schema-qualified relation name (i.e. schema.relation instead of schema.relation or just schema.relation), and the error handler printed to the log an error message a confusing message. Thoughts? -- // Dmitriy.
Re: [GENERAL] OID of type by name.
2013/3/29 tahoe-gary gba...@salesforce.com In what version of PG is the 'my_type'::regtype::oid syntax available? I want to introduce this to the JDBC driver which currently does the most ridiculous query that totally ignores search path. JDBC driver does this currently: SELECT oid FROM pg_catalog.pg_type WHERE typname = ? So if you have more than one type of the same name (or perhaps a UDT and a table with the same name as in my case) it just grabs whichever one first appears in pg_type regardless of search path. So I intend to change that query to: SELECT ?::regtype::oid But I need to know if I should be checking the server version or not. What is the min version that that query will work on? According to the documentation, I think that Object Identifier Types was introduced in 7.3. http://www.postgresql.org/docs/7.3/static/datatype-oid.html -- // Dmitriy.
[GENERAL] Bug or feature? (The constraint of the domain of extension can be dropped...)
Hey hackers, According to http://www.postgresql.org/docs/9.2/static/extend-extensions.html PostgreSQL will not let you drop an individual object contained in an extension, except by dropping the whole extension. But this rule does not apply to domain constraints, i.e. it is not possible to drop domain of some extenstion but it is possible to drop any or all of its constraints. (And in fact drop it.) I am consider this is a bug. Don't you? -- // Dmitriy.
Re: [GENERAL] Bug or feature? (The constraint of the domain of extension can be dropped...)
2013/4/5 Tom Lane t...@sss.pgh.pa.us Dmitriy Igrishin dmit...@gmail.com writes: According to http://www.postgresql.org/docs/9.2/static/extend-extensions.html PostgreSQL will not let you drop an individual object contained in an extension, except by dropping the whole extension. But this rule does not apply to domain constraints, i.e. it is not possible to drop domain of some extenstion but it is possible to drop any or all of its constraints. (And in fact drop it.) I am consider this is a bug. Don't you? No. The domain is a member of the extension, its properties are not. We do not generally forbid ALTER on extension members. During development for instance a quick ALTER can be a whole lot more convenient than dropping and reloading the whole extension. Debatable, because in practice during development it's often better to recreate the whole database which takes seconds. (The database with hundreds of functions, views, domains, triggers etc.) In fact, we store all except CREATE TABLE ... in files. In particular, storing functions in file(s) are much much much more convenient for big refactoings (which are often during development) rather than using annoying \ef in psql(1) which just a time killer. Whether it's a good idea to ALTER extension member objects in production is a different question. Typically you'd install them as owned by somebody with sense enough not to do that. Indeed. To *me*, you solution looks like workaround. But of course, this is debatable. Well, thank you for explanation! -- // Dmitriy.
Re: [GENERAL] out of memory issue
04.03.2013 18:25 пользователь Merlin Moncure mmonc...@gmail.com написал: On Sun, Mar 3, 2013 at 11:05 AM, G N myte...@gmail.com wrote: Hello Friends, Hope you are all well... I have a specific issue, where my query fails with below error while trying to export data from pgadmin SQL tool. There are no such issues when the result set is small. But it returns error when the result set is bit large. Any inputs please ? Where and how should memory be increased in case ? out of memory for query result I'm guessing your query is returning a lot of data and the export itself is not being produced with COPY. As such, you are subject to the limits of the 32 bit libpq you are probably using (or if you are using 64 bit, you are well and truly running out of memory). Solution to move forward. learn COPY and psql \copy. Refer documentation. I am curious how about single row mode implemented in 9.2 in this case? merllin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] RAISE NOTICE ... and CONTEXT field of the error report.
Hey all, Is there way to turn off printing of CONTEXT field of the error report in the following case: create or replace function foo() returns void language plpgsql as $$ begin raise notice 'notice from foo()'; end; $$; create or replace function bar() returns void language plpgsql as $$ begin perform foo(); end; $$; wog=# select foo(); NOTICE: notice from foo() foo - (1 row) wog=# select bar(); NOTICE: notice from foo() CONTEXT: SQL statement SELECT foo() PL/pgSQL function bar() line 3 at PERFORM bar - (1 row) If it does not possible, I would like to have this feature. From the POV of the PL/pgSQL user I think it should be customizable mode of PL/pgSQL. -- // Dmitriy.
Re: [GENERAL] RAISE NOTICE ... and CONTEXT field of the error report.
Hey Marc, 2013/1/22 Marc Schablewski m...@clickware.de Am 22.01.2013 14:59, schrieb Dmitriy Igrishin: Hey all, Is there way to turn off printing of CONTEXT field of the error report I think, this might help: http://www.depesz.com/2008/07/12/suppressing-context-lines-in-psql/ Marc I am sorry for thoughtlessness! It's really should be filtered by client application. Thanks for point! -- // Dmitriy.
Re: [GENERAL] libpq error message deallocation
2012/12/4 icholy ilia.ch...@gmail.com PQerrorMessage function return char const* char const* msg = PQerrorMessage(conn); Now since it's const, I don't think I should be deallocating it and I've never seen that done in any examples. But then, when and how does it get freed? At first I thought it gets deallocated once another error message is requested but that's not the case. // cause some error char const* msg1 = PQerrorMessage(pgconn); // cause another error char const* msg2 = PQerrorMessage(pgconn); // still works std::cout msg1 msg2 std::endl; Can someone shed some light on this for me? PQerrorMessage() returns pointer to the last allocated string from the PGConn. The memory on this string will be deallocated with PQfinish(). In the above case, msg1 is invalid pointer and you just got lucky. Please, see description of PQerrorMessage() here http://www.postgresql.org/docs/9.2/static/libpq-status.html // Dmitriy.
Re: [GENERAL] pg_listening_channels()
2012/11/30 Igor Neyman iney...@perceptron.com -Original Message- From: Greg Sabino Mullane [mailto:g...@turnstep.com] Sent: Thursday, November 29, 2012 11:34 PM To: pgsql-general@postgresql.org Subject: Re: pg_listening_channels() On the contrary, it was very well discussed and designed. Why do you even care if the anyone is listening or not? Simply remove the check if anyone listens step and send the NOTIFY. Well, I guess we disagree on this. Why trashing the system with NOTIFYs no one listens to? Of course, like Tom Lane suggested, I could create a table similar to now obsolete pg_listener and manage it from the client that LISTENs and gets notifications. Also, what sense pg_listening_channels() function makes, if it returns channels that I created (in my current session/connection)? I don't need this function to know whether I issued LISTEN my_channel or not. We need pg_listening_channels() because the information it returns should be stored in the DB and applications (libraries) does not need to store it in special places. Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy.
Re: [GENERAL] Creating and managing triggers
Hey, 2012/10/9 Tom Lane t...@sss.pgh.pa.us Dean Myerson d...@deanmyerson.org writes: I need to create some triggers and the docs seem pretty straightforward. When I tried to create one using CREATE TRIGGER, it took over 20 minutes, and the second one hadn't finished over more than an hour. And I later found that all other database users in the company were locked out during this process. The table getting the triggers has about 187000 rows in it and is pretty central, so lots of functions join with it. CREATE TRIGGER, per se, should be nearly instantaneous. It sounds like the CREATE TRIGGER command is blocked behind some other operation that has a (not necessarily exclusive) lock on the table; and then everything else is queueing up behind the CREATE TRIGGER's exclusive lock request. Look into pg_locks and pg_stat_activity to see what's holding things up. I'd bet on an old idle-in-transaction session, that may have done nothing more exciting than reading the table at issue, but is still blocking things for failure to close its transaction. Sitting idle with an open transaction is something to be discouraged for a lot of reasons besides this one. ... They restarted the database server when the second create trigger hung, so I don't know what happened with it. Whoever they is needs to learn a bit more about being a Postgres DBA, methinks. There are smaller hammers than a database restart. I didn't even save the name, obviously a problem on my part. But there should be some equivalent of Show Trigger, shouldn't there? psql's \dt command is the usual thing, or if you like GUIs you could try PgAdmin. Obviously, typo. \d[S+] your_table_name instead of \dt. -- // Dmitriy.
Re: [GENERAL] Moving several databases into one database with several schemas
2012/9/7 Merlin Moncure mmonc...@gmail.com
On Thu, Sep 6, 2012 at 10:12 PM, Edson Richter edsonrich...@hotmail.com
wrote:
Em 06/09/2012 15:40, John R Pierce escreveu:
On 09/06/12 5:30 AM, Edson Richter wrote:
You could change the default setting for the user with
ALTER ROLE someuser SET search_path=...
That is perfect! I can have separate users for each application, and
then
they will have the correct search path.
You saved my day,
the default search_path is $USER,public, so by naming your schema's to
the
usernames, you don't even need to alter role...
Wonderful, this would have the effect I expect that the connection
defines
the path. Then I'll use user to select the specific schema, and the
public
schema as the main schema.
Thanks to you all, I think I have everything needed to put my migration
project in practice.
I do this exact thing frequently. I route everything through dumps.
Here's some roughed out bash script for ya.. The basic MO is to
restore hack the restore script with sed, restoring to a scratch
schema so that the drop/reload of the client private schema can be
deferred until the data is already loaded.
function load_client {
client=$1
database=master_db
echo [`date`] Loading $client
psql -c update client set load_started = now(), LoadedPO = NULL
where name = '$client'; $database
get backup database and place into $client.current.gz
psql -c drop schema if exists ${client}_work cascade $database
21 | grep ERROR
psql -c create schema ${client}_work $database 21 | grep ERROR
gzip -cd $client.backup.gz | sed s/^SET search_path = public/SET
search_path=${client}_work/ | psql -XAq $database 21 | grep ERROR |
grep -v plpgsql
psql -c begin; drop schema if exists ${client} cascade; alter
schema ${client}_work rename to $client; commit; $database
psql -c update client set load_finished = now() where name =
'$client'; $database
rm -f $client.current.gz
}
To cut restore time down I run them in parallel:
NUM_FORKS=4
function do_parallel {
while [ `jobs | wc -l` -ge $NUM_FORKS ]
do
sleep 1
done
$@
}
Then it's just a matter of:
get $clients somehow
for client in $clients
do
do_parallel load_client $client
done
Great stuff, Merlin! ;-)
--
// Dmitriy.
Re: [GENERAL] PQfformat question and retrieving bytea data in C
2012/8/30 Albe Laurenz laurenz.a...@wien.gv.at Jason Armstrong wrote: I have updated my C library to return the binary data correctly. I note the restriction on not being able to retrieve different columns in different formats. Actually, PostgreSQL supports that if you use the line protocol to talk to the server (see the description of Bind (F) in http://www.postgresql.org/docs/9.1/static/protocol-message-formats.html) . Alas, this is not supported by the C API. Maybe that would be a useful extension to libpq. +1 -- // Dmitriy.
Re: [GENERAL] PQfformat question and retrieving bytea data in C
Hey Jason,
2012/8/29 Jason Armstrong j...@riverdrums.com
I have a question regarding the return value of PQfformat()
I have a 'data' column in my table, type bytea (postgresql 9.1.5).
In postgresql.conf:
bytea_output = 'escape'
When I execute the query:
PGresult *res = PQexec(db, SELECT data::bytea FROM data_table WHERE
id='xxx')
PQexec() always returns data in the text format. You should use
PQexecParams() to obtain the data as binary.
And I run through the results:
int i, j;
for (i = 0; i PQntuples(res); i++) {
for (j = 0; j PQnfields(res); j++) {
printf(Format %d: %d\n, j, PQfformat(res, j));
printf(Type %d: %d\n, j, PQftype(res, j));
}
}
This prints that the format is type 0, and the type is 17.
Shouldn't the format be 1 (binary data)?
I am getting a discrepancy between data that I put into the table and
data I retrieve.
When I dump the data, using:
int di;
char *val = PQgetvalue(res, i, j);
for (di = 0; di 16; di++) fprintf(stderr, %2x , val[di]);
I see the following:
30 5c 33 33 32 5c 30 30 30 5c 30 31 31 5c 30 30
But when I look at the same data in the database:
psql select encode(substr(data, 0, 16), 'hex') from data_table where
id='xxx';
encode
30da00090132420520203137323030
This is the data I'm expecting to get back. Is the '00' (third byte)
causing the problem?
The data looks the same at a certain place (ie it starts with the same
byte 30, then the C code has 22 bytes whereas the db hex dump has 7
bytes, then the data is the same again. The 7/22 number of bytes isn't
always the same, across the different data values).
--
Jason Armstrong
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
// Dmitriy.
Re: [GENERAL] Views versus user-defined functions: formatting, comments, performance, etc.
2012/8/29 Merlin Moncure mmonc...@gmail.com
On Tue, Aug 28, 2012 at 3:25 PM, Dmitriy Igrishin dmit...@gmail.com
wrote:
2012/8/20 Merlin Moncure mmonc...@gmail.com
On Sun, Aug 19, 2012 at 8:14 AM, Dmitriy Igrishin dmit...@gmail.com
wrote:
For various reasons, this often goes the wrong way. Views are often
the right way to go. +1 on your comment above -- the right way to do
views (and SQL in general) is to organize scripts and to try and
avoid
managing everything through GUI tools. It works.
The drawback of this approach is that in some cases we need a
factory function(s) (in terms of the OOP) which returns one or a
set of objects (i.e. the function returns the view type). But since
the views are not in the dump we are forced to abandon this solution
and go with workarounds (such as creating extra composite types
to use as returning values or use the tables).
Could you elaborate on this?
Suppose we've designed a simple class hierarchy (I'll use C++ notation):
class User { ... };
class Real_user : public User { ... };
class Pseudo_user : public User { ... };
Suppose we've decided that objects of these classes will be stored
in one database table:
CREATE TYPE user_type AS ENUM ('real', 'pseudo');
CREATE TABLE user (id serial NOT NULL,
tp user_type NOT NULL,
user_property1 text NOT NULL,
user_property2 text NOT NULL,
real_user_property1 text NULL,
real_user_property2 text NULL,
pseudo_user_property1 text NULL);
For simple mapping we've creating the (updatable, with rules) views:
CREATE VIEW real_user_view
AS SELECT * FROM user WHERE tp = 'real';
CREATE VIEW pseudo_user_view
AS SELECT * FROM user WHERE tp = 'pseudo';
CREATE VIEW user_view
AS SELECT * FROM real_user_view
UNION ALL SELECT * FROM pseudo_user_view;
The C++ classes above will operate on these views.
Finally, suppose we need a function which gets a Real_user's
instance by known identifier (or a key):
The C++ function may be defined as:
Real_user* real_user(int id);
At the same time this function can call PL/pgSQL's function:
CREATE FUNCTION real_user(id integer)
RETURNS real_user_view ...
So, the factory function real_user() is depends on the view. And
when the views are not in the dump (stored in the separate place)
this is an annoying limitation and we must use some of the
workarounds. (Use the table user as a return value or create
an extra composite type with the same structure as for the
real_user_view).
Hm, couple points (and yes, this is a common problem):
*) how come you don't have your function depend on the table instead
of the view? this has the neat property of having the function
I always do emphasis on the code style and on the easiness of
maintenance. And I looks at the views as on the classes (aka abstractions).
In many cases I don't want to care how (and where) the data is actually
stored -- in the one table, or in the many tables, or whatever.
AFAIK, the main goal of the views to provide such abstraction.
automatically track added columns to the table.
Agreed, this is a nice feature.
*) if that's still a headache from dependency point of view, maybe you
can use composite-type implemented table:
postgres=# create type foo as (a int, b int);
CREATE TYPE
postgres=# create table bar of foo;
CREATE TABLE
postgres=# create view baz as select * from bar;
CREATE VIEW
postgres=# alter type foo add attribute c int cascade;
ALTER TYPE
postgres=# \d bar
Table public.bar
Column | Type | Modifiers
+-+---
a | integer |
b | integer |
c | integer |
Typed table of type: foo
Thanks for the solution! But it seems like a workaround here.
*) do you really need a factory function to create 'user' -- why not
allow regular inserts?
By factory function I mean the function which creates an instance
for the client -- i.e. selecting object from the data source :-)
*) I usually do some variant of this:
create table fruit
(
fruit_id int primary key,
type text,
freshness numeric
);
create table apple
(
fruit_id int primary key references fruit on delete cascade
deferrable initially deferred,
cyanide_content numeric
);
create table orange
(
fruit_id int primary key references fruit on delete cascade
deferrable initially deferred,
vitamin_c_content numeric
);
create or replace function hs(r anyelement) returns hstore as
$$
select hstore($1);
$$ language sql immutable strict;
create or replace view fruit_ext as
select f.*,
coalesce(hs(a), hs(o)) as properties
from fruit f
left join apple a using(fruit_id)
left join orange o using(fruit_id);
insert into fruit
Re: [GENERAL] C locale versus en_US.UTF8. (Was: String comparision in PostgreSQL)
2012/8/29 Merlin Moncure mmonc...@gmail.com On Wed, Aug 29, 2012 at 12:43 PM, Bruce Momjian br...@momjian.us wrote: On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote: On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure mmonc...@gmail.com wrote: citext unfortunately doesn't allow for index optimization of LIKE queries, which IMNSHO defeats the whole purpose. to the best way remains to use lower() ... this will be index optimized and fast as long as you specified C locale for your database. What is the difference between C and en_US.UTF8, please? We see that the same query (that invokes a sort) runs 15% faster under the C locale. The output between C and en_US.UTF8 is identical. We're considering moving our database from en_US.UTF8 to C, but we do deal with internationalized text. Well, C has reduced overhead for string comparisons, but obviously doesn't work well for international characters. The single-byte encodings have somewhat less overhead than UTF8. You can try using C locales for databases that don't require non-ASCII characters. To add: The middle ground I usually choose is to have a database encoding of UTF8 but with the C (aka POSIX) locale. This gives you the ability to store any unicode but indexing operations will use the faster C string comparison operations for a significant performance boost -- especially for partial string searches on an indexed column. This is an even more attractive option in 9.1 with the ability to specify specific collations at runtime. Good point! Thanks! -- // Dmitriy.
Re: [GENERAL] Views versus user-defined functions: formatting, comments, performance, etc.
2012/8/20 Merlin Moncure mmonc...@gmail.com
On Sun, Aug 19, 2012 at 8:14 AM, Dmitriy Igrishin dmit...@gmail.com
wrote:
For various reasons, this often goes the wrong way. Views are often
the right way to go. +1 on your comment above -- the right way to do
views (and SQL in general) is to organize scripts and to try and avoid
managing everything through GUI tools. It works.
The drawback of this approach is that in some cases we need a
factory function(s) (in terms of the OOP) which returns one or a
set of objects (i.e. the function returns the view type). But since
the views are not in the dump we are forced to abandon this solution
and go with workarounds (such as creating extra composite types
to use as returning values or use the tables).
Could you elaborate on this?
Suppose we've designed a simple class hierarchy (I'll use C++ notation):
class User { ... };
class Real_user : public User { ... };
class Pseudo_user : public User { ... };
Suppose we've decided that objects of these classes will be stored
in one database table:
CREATE TYPE user_type AS ENUM ('real', 'pseudo');
CREATE TABLE user (id serial NOT NULL,
tp user_type NOT NULL,
user_property1 text NOT NULL,
user_property2 text NOT NULL,
real_user_property1 text NULL,
real_user_property2 text NULL,
pseudo_user_property1 text NULL);
For simple mapping we've creating the (updatable, with rules) views:
CREATE VIEW real_user_view
AS SELECT * FROM user WHERE tp = 'real';
CREATE VIEW pseudo_user_view
AS SELECT * FROM user WHERE tp = 'pseudo';
CREATE VIEW user_view
AS SELECT * FROM real_user_view
UNION ALL SELECT * FROM pseudo_user_view;
The C++ classes above will operate on these views.
Finally, suppose we need a function which gets a Real_user's
instance by known identifier (or a key):
The C++ function may be defined as:
Real_user* real_user(int id);
At the same time this function can call PL/pgSQL's function:
CREATE FUNCTION real_user(id integer)
RETURNS real_user_view ...
So, the factory function real_user() is depends on the view. And
when the views are not in the dump (stored in the separate place)
this is an annoying limitation and we must use some of the
workarounds. (Use the table user as a return value or create
an extra composite type with the same structure as for the real_user_view).
PS. I'm tried to found a SQL formatter for format views definitions
stored in the database, but unsuccessful.
Even if you could find one, I wouldn't use it: the database changes
the structure of you query. Suppose you had:
CREATE VIEW v AS SELECT *, a*b AS product FROM foo;
The database converts that to:
CREATE VIEW v AS SELECT a,b, a*b AS product FROM foo;
That means that if you add columns after the fact, the view definition
in the database will diverge from what the source would create.
Besides that, there are a number of other things that the database
does like add unnecessary casts, column aliases and parentheses that
make a purely textual solution impossible.
merlin
--
// Dmitriy.
Re: [GENERAL] Question about granting permissions
Hey Matvey, 2012/8/26 Matvey Teplov matvey.tep...@gmail.com Hi, Sorry to bother with the stupid question guys - I'm new to the Postgres. I'm having issue allowing user to access the database - the user is not allowed to access the data. I do the following: 1) grant all on database testdb table mytable to trinity; By the command above you're granting privileges on the database object itself, i.e. connect, create schemas, creating the temprorary tables. postgres=# \l List of databases Name| Owner | Encoding | Collation |Ctype| Access privileges ---+--+--+-+-+--- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres testdb| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres : postgres=CTc/postgres : trinity=CTc/postgres (4 rows) But when I login (psql -d testdb -U trinity) as trinity and check it, it doesn't work. testdb= select * from mytable; ERROR: permission denied for relation mytable testdb= \dp Access privileges Schema | Name | Type | Access privileges | Column access privileges +-+---+---+-- public | mytable | table | | (1 row) There is also entry in the log: 2012-08-26 13:06:01 CEST testdb trinity ERROR: permission denied for relation mytable 2012-08-26 13:06:01 CEST testdb trinity STATEMENT: select * from mytable; Can someone explain what do I do wrong? Thank you in advance! You need to grant on the another database object (table): GRANT SELECT ON mytable TO trinity. Please, see http://www.postgresql.org/docs/9.2/static/sql-grant.html -- // Dmitriy.
Re: [GENERAL] Views versus user-defined functions: formatting, comments, performance, etc.
2012/8/18 Merlin Moncure mmonc...@gmail.com On Fri, Aug 17, 2012 at 5:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: Adam Mackler adammack...@gmail.com writes: I notice when I save a view, I lose all the formatting and comments. As I was writing a complicated view, wanting to retain the format and comments, I thought I could just save it as a function that returns a table value. A function would evaluate to the same value as a view, but changing it later might be less confusing. A lot of people choose to save the source text in some sort of SCM (eg git), and then just import via CREATE OR REPLACE VIEW when they change it. This tends to soon scale up to large scripts that define collections of related objects. However, I'm guessing (since I don't know anything about the internals) that the loss of formatting and comments is a result of the view being processed and stored in a more computer-friendly format, while functions are simply stored as the text that I type. Correct. The reparse time per se is generally not a big deal, but the execution penalty associated with a function can be. If you go this way you'll want to make sure that your function can be inlined --- use EXPLAIN to make sure you get a plan matching the bare view, and not just something that says Function Scan. For various reasons, this often goes the wrong way. Views are often the right way to go. +1 on your comment above -- the right way to do views (and SQL in general) is to organize scripts and to try and avoid managing everything through GUI tools. It works. The drawback of this approach is that in some cases we need a factory function(s) (in terms of the OOP) which returns one or a set of objects (i.e. the function returns the view type). But since the views are not in the dump we are forced to abandon this solution and go with workarounds (such as creating extra composite types to use as returning values or use the tables). PS. I'm tried to found a SQL formatter for format views definitions stored in the database, but unsuccessful. -- // Dmitriy.
Re: [GENERAL] Field size
Hey aliosa 2012/8/16 aliosa constantinica_a...@yahoo.com Hello I am using libpq to find information about fileds of table ( type and size). I have a problem with getting the sizeof varchar fields. If a table has a fiels varchar[35] and want to obtain 35. I used PQgetlength or PQfsize but is not good for my work. Can someone tell me what function shall I use to get the size of filed ? You should use PQfmod(). -- // Dmitriy.
[GENERAL] Determining a table column by the view column.
Hey all, Is there way to determine a table column referenced by a view column via SQL? I want to create an universal function to determine mandatoriness of some column of the view (i.e. check the not null constraint of underlying table column). Thanks. -- // Dmitriy.
[GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
Hey all, According to http://www.postgresql.org/docs/9.2/static/sql-alterrole.html A query: ALTER ROLE davide WITH PASSWORD NULL; removes a role's password. But it's impossible to pass empty (NULL) password to the backend by using libpq, because connectOptions2() defined the fe-connect.c reads a password from the ~/.pgpass even when a password specified as an empty string literal (). Also, when connecting to the server via psql(1) by using a role with removed password psql exists with status 2 and prints the error message: psql: fe_sendauth: no password supplied Thanks. -- // Dmitriy.
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
Hey Guillaume, 2012/7/24 Guillaume Lelarge guilla...@lelarge.info On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote: Hey all, According to http://www.postgresql.org/docs/9.2/static/sql-alterrole.html A query: ALTER ROLE davide WITH PASSWORD NULL; removes a role's password. But it's impossible to pass empty (NULL) password to the backend by using libpq, because connectOptions2() defined the fe-connect.c reads a password from the ~/.pgpass even when a password specified as an empty string literal (). Also, when connecting to the server via psql(1) by using a role with removed password psql exists with status 2 and prints the error message: psql: fe_sendauth: no password supplied Yes, and? I don't see how this could be a bug. If your authentication method asks for a password, you need to have one. Yes, I need. I just want to have empty password (). If you have resetted it, well, you shouldn't have. Or you really want that your users could connect without a password, and then you need to change your authentication method with trust. But no-one will encourage you to do that. Why I need to change an auth. method? If I've used a \password command in psql(1) and specified an empty password for my role I need to ask a database admin to change an auth. method? :-) Cool! Please note, psql(1) allow to do it as well as SQL - too. -- // Dmitriy.
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
2012/7/24 Guillaume Lelarge guilla...@lelarge.info On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote: Hey Guillaume, 2012/7/24 Guillaume Lelarge guilla...@lelarge.info On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote: Hey all, According to http://www.postgresql.org/docs/9.2/static/sql-alterrole.html A query: ALTER ROLE davide WITH PASSWORD NULL; removes a role's password. But it's impossible to pass empty (NULL) password to the backend by using libpq, because connectOptions2() defined the fe-connect.c reads a password from the ~/.pgpass even when a password specified as an empty string literal (). Also, when connecting to the server via psql(1) by using a role with removed password psql exists with status 2 and prints the error message: psql: fe_sendauth: no password supplied Yes, and? I don't see how this could be a bug. If your authentication method asks for a password, you need to have one. Yes, I need. I just want to have empty password (). If you have resetted it, well, you shouldn't have. Or you really want that your users could connect without a password, and then you need to change your authentication method with trust. But no-one will encourage you to do that. Why I need to change an auth. method? If I've used a \password command in psql(1) and specified an empty password for my role I need to ask a database admin to change an auth. method? :-) Cool! Please note, psql(1) allow to do it as well as SQL - too. If your admin sets PostgreSQL so that a password needs to be given while trying to connect, a simple user shouldn't be able to bypass that by setting no password for his role. So, yes, if you want to be able to not use a password, you need to change your authentification method. dmitigr= CREATE USER test ENCRYPTED PASSWORD 'test'; CREATE ROLE dmitigr= \c dmitigr test Password for user test: You are now connected to database dmitigr as user test. dmitigr= \password Enter new password: Enter it again: Now the user test will not be able to connect to the server. This behaviour is incorrect. -- // Dmitriy.
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
2012/7/24 Dmitriy Igrishin dmit...@gmail.com 2012/7/24 Guillaume Lelarge guilla...@lelarge.info On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote: Hey Guillaume, 2012/7/24 Guillaume Lelarge guilla...@lelarge.info On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote: Hey all, According to http://www.postgresql.org/docs/9.2/static/sql-alterrole.html A query: ALTER ROLE davide WITH PASSWORD NULL; removes a role's password. But it's impossible to pass empty (NULL) password to the backend by using libpq, because connectOptions2() defined the fe-connect.c reads a password from the ~/.pgpass even when a password specified as an empty string literal (). Also, when connecting to the server via psql(1) by using a role with removed password psql exists with status 2 and prints the error message: psql: fe_sendauth: no password supplied Yes, and? I don't see how this could be a bug. If your authentication method asks for a password, you need to have one. Yes, I need. I just want to have empty password (). If you have resetted it, well, you shouldn't have. Or you really want that your users could connect without a password, and then you need to change your authentication method with trust. But no-one will encourage you to do that. Why I need to change an auth. method? If I've used a \password command in psql(1) and specified an empty password for my role I need to ask a database admin to change an auth. method? :-) Cool! Please note, psql(1) allow to do it as well as SQL - too. If your admin sets PostgreSQL so that a password needs to be given while trying to connect, a simple user shouldn't be able to bypass that by setting no password for his role. So, yes, if you want to be able to not use a password, you need to change your authentification method. dmitigr= CREATE USER test ENCRYPTED PASSWORD 'test'; CREATE ROLE dmitigr= \c dmitigr test Password for user test: You are now connected to database dmitigr as user test. dmitigr= \password Enter new password: Enter it again: Now the user test will not be able to connect to the server. This behaviour is incorrect. Full version :-) dmitigr= CREATE USER test ENCRYPTED PASSWORD 'test'; CREATE ROLE dmitigr= \c dmitigr test Password for user test: You are now connected to database dmitigr as user test. dmitigr= ALTER ROLE test PASSWORD ''; ALTER ROLE dmitigr= \c dmitigr test FATAL: password authentication failed for user test Previous connection kept It's an incorrect behaviour because it's a user's decision what a password to have - empty or not. I'm dubious that the user of some WEB site should contact to the site admin to ask him to change the auth. method because the user sets his password to NULL :-). On the other hand, it's a developer's decision to allow empty passwords or not to allow them in the software. -- // Dmitriy.
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
2012/7/24 Tom Lane t...@sss.pgh.pa.us Dmitriy Igrishin dmit...@gmail.com writes: But it's impossible to pass empty (NULL) password to the backend Please note that empty and null are not the same thing... Yes, I know. But why the ALTER ROLE treats '' as NULL and as the result all of values of pg_catalog.pg_authid.rolpassword are always NULL even when the password in ALTER ROLE was specified as ''? :-) That is the reason why I've considered empty and NULL as the same thing :-) by using libpq, because connectOptions2() defined the fe-connect.c reads a password from the ~/.pgpass even when a password specified as an empty string literal (). I rather doubt that we'll change this, because it seems more likely to break applications that rely on that behavior than to do anything useful. Surely nobody in their right mind uses an empty password. (If anything, I'd be more inclined to make the backend treat an empty password as an error than to try to make libpq safe for the case. Even if we did change libpq, there are probably issues with empty passwords in jdbc and who knows how many other places.) Then it should be at least documented in the libpq's documentation that explicitly specified empty password will force libpq to get it from the file? regards, tom lane -- // Dmitriy.
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
2012/7/24 Tom Lane t...@sss.pgh.pa.us Dmitriy Igrishin dmit...@gmail.com writes: 2012/7/24 Tom Lane t...@sss.pgh.pa.us Please note that empty and null are not the same thing... Yes, I know. But why the ALTER ROLE treats '' as NULL and as the result all of values of pg_catalog.pg_authid.rolpassword are always NULL even when the password in ALTER ROLE was specified as ''? :-) It does not do that for me. What PG version are you testing? Oops, I am sorry, Tom. It doesn't on my 9.2beta2. Perhaps, it's too hot in Russia now... But maybe it's worth it to add a parameter key word to libpq, e.g. passwd which's empty value will be treated as an empty password? -- // Dmitriy.
Re: [GENERAL] How to declare return type for a function returning several rows and columns?
Hey Alexander, 2012/6/12 Alexander Farber alexander.far...@gmail.com Hello, I'm trying to create the following function which gives me a runtime error, because it obviously doesn't return a mere integer but several rows and columns (result of a join): # create or replace function pref_daily_misere() returns setof integer as $BODY$ begin create temporary table temp_ids (id varchar not null) on commit drop; insert into temp_ids (id) select id from pref_money where yw = to_char(current_timestamp - interval '1 week', 'IYYY-IW') order by money desc limit 10; create temporary table temp_rids (rid integer not null) on commit drop; insert into temp_rids (rid) select rid from pref_cards where id in (select id from temp_ids) and bid = 'Мизер' and trix 0; -- return query select rid from temp_rids; return query SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM. HH24:MI') as day, c2.bid, c2.trix, c2.pos, c2.money, c2.last_ip, c2.quit, u.id, u.first_name, u.avatar, u.female, u.city, u.vip CURRENT_DATE as vip FROM pref_rounds r JOIN pref_cards c1 USING (rid) JOIN pref_cards c2 USING (rid) JOIN pref_users u ON u.id = c2.id WHERE r.rid in (select rid from temp_rids) order by rid, pos; return; end; $BODY$ language plpgsql; The runtime error in PostgreSQL 8.4.11 is: # select pref_daily_misere(); ERROR: structure of query does not match function result type DETAIL: Number of returned columns (15) does not match expected column count (1). CONTEXT: PL/pgSQL function pref_daily_misere line 18 at RETURN QUERY Does anybody please have an advice here? You can create the view with your query: SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM. HH24:MI') as day, c2.bid, c2.trix, c2.pos, c2.money, c2.last_ip, c2.quit, u.id, u.first_name, u.avatar, u.female, u.city, u.vip CURRENT_DATE as vip FROM pref_rounds r JOIN pref_cards c1 USING (rid) JOIN pref_cards c2 USING (rid) JOIN pref_users u ON u.id = c2.id; and use this view both as the function return type and for selecting inside the function. -- // Dmitriy.
Re: [GENERAL] how to for loop with distinct values?
2012/5/22 Merlin Moncure mmonc...@gmail.com On Mon, May 21, 2012 at 3:39 PM, J.V. jvsr...@gmail.com wrote: I am banging my head over this. I want to select distinct values from a varchar column and iterate through the values. I want to select the distinct values from this column and loop through them (using as a variable) in a raise notice statement and also in an update statement. I have not been able to do this trying dozens of different approaches. I could not find an example even after searching google. So for example, suppose table: mytable has a column value that is defined as a varchar: for tmp_var in select distinct(value) from mytable where value2='literal' loop raise notice 'I want to print a message here - the tmp_var is [' || tmp_var || ']'; == error on this line update table set somecolumn = ''' || tmp_var || ''' end loop; I want to use each distinct value in a raise notice line and an update statement. tmp_var has to be in ' ' ticks or will not work. it is failing on the first FOR statement stating: invalid input syntax for integer: some_distinct_value. How do I select varchar distinct values and iterate using variables in a raise notice statement and inside another update statement? this seems simple to do , but have not found a way. Well it looks like you have a couple of problems here. First, when you 'raise notice', generally you do it like this: raise notice 'value of var is %', var; And not do string concatenation. As for the update statement, you should be quoting tmp_var. At most you should be casting (tmp_var::int) and then be diagnosing why you have non integer data in a value you are trying to put into a integer column (which is the point of the insert). So, you are very close -- it all comes down to how you are handling the NOTICE i think. A quick review of the examples here: http://www.postgresql.org/docs/9.2/static/plpgsql-errors-and-messages.html might be helpful. merlin Nice color and font ;-) -- // Dmitriy.
Re: [GENERAL] Confusion about composite indexes
2012/5/22 Merlin Moncure mmonc...@gmail.com On Mon, May 21, 2012 at 2:34 PM, Bill Mitchell b...@publicrelay.com wrote: I am searching for some logic behind the selection of an index in postgres -- it seems that if I have a composite index based on both columns in a join table, it's only referenced if I query on the first term in the composite index. I've read http://www.postgresql.org/docs/9.1/static/indexes-multicolumn.html over and over and think that this is the same scenario as what I face. As an example: OUTLET: has OUTLET_ID as a primary key, consisting of about a million rows MEDIA: has MEDIA_ID as a primary key, and table consists of only 10 rows OUTLET_MEDIA: a join table used to correlate, and this has about a million rows Each outlet may have 1+ Media (technically, 0 or more, in this schema) Table public.outlet_media Column | Type | Modifiers ---++--- outlet_id | bigint | not null media_id | bigint | not null Indexes: outlet_media_pkey PRIMARY KEY, btree (outlet_id, media_id) Foreign-key constraints: fkfde1d912281e6fbf FOREIGN KEY (media_id) REFERENCES media(media_id) fkfde1d9125014e32a FOREIGN KEY (outlet_id) REFERENCES outlet(outlet_id) When I test performance, using an OUTLET_ID, the query uses the outlet_media_pkey index # explain analyze select * from outlet_media where outlet_id in (select outlet_id from outlet order by random() limit 50); QUERY PLAN -- Nested Loop (cost=67625.64..68048.50 rows=50 width=16) (actual time=841.115..884.669 rows=50 loops=1) - HashAggregate (cost=67625.64..67626.14 rows=50 width=8) (actual time=841.048..841.090 rows=50 loops=1) - Limit (cost=67624.89..67625.01 rows=50 width=8) (actual time=840.980..841.011 rows=50 loops=1) - Sort (cost=67624.89..70342.66 rows=1087110 width=8) (actual time=840.978..840.991 rows=50 loops=1) Sort Key: (random()) Sort Method: top-N heapsort Memory: 27kB - Seq Scan on outlet (cost=0.00..31511.88 rows=1087110 width=8) (actual time=6.693..497.383 rows=1084628 loops=1) - Index Scan using outlet_media_pkey on outlet_media (cost=0.00..8.43 rows=1 width=16) (actual time=0.869..0.870 rows=1 loops=50) Index Cond: (outlet_id = outlet.outlet_id) Total runtime: 884.759 ms (10 rows) However if I try the reverse, to search using the MEDIA_ID # explain analyze select * from outlet_media where media_id in (select media_id from media where media_name='Online News'); QUERY PLAN --- Hash Join (cost=1.19..21647.53 rows=362125 width=16) (actual time=0.034..0.034 rows=0 loops=1) Hash Cond: (outlet_media.media_id = media.media_id) - Seq Scan on outlet_media (cost=0.00..16736.76 rows=1086376 width=16) (actual time=0.012..0.012 rows=1 loops=1) - Hash (cost=1.18..1.18 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 0kB - HashAggregate (cost=1.17..1.18 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1) - Seq Scan on media (cost=0.00..1.16 rows=1 width=8) (actual time=0.012..0.012 rows=0 loops=1) Filter: ((media_name)::text = 'Online News'::text) Total runtime: 0.084 ms (9 rows) Thanks in advance for whatever light can be shed. If it's safer for me to just create individual indexes on each of the two columns ( Multicolumn indexes should be used sparingly. In most situations, an index on a single column is sufficient and saves space and time) There are a couple of things going on here. First of all, 'order by random() limit..' guarantees a full seq scan and a short of whatever you're ordering, so it's never going to be very efficient. When you wen the other way, you supplied a hard search term which can be fed to the index and optimized through. Secondly, if you have a mapping table that has to support searches in both directions, it's pretty typical to do something like this: create table map(a int references ..., b int references..., primary key(a,b)); create index on map(b); So you can get fully index lookups on all of a, b, ab, and ba. the primary key can't optimize ba because indexes only fully match if candidate fields are supplied from left to right order. They can still help somewhat, but to a lesser degree. BTW, I would like to know is it worth it to create 3rd index on map(a) to reduce the size of
[GENERAL] Frontend/Backend protocol question.
Hey all, According to http://www.postgresql.org/docs/9.1/static/protocol-flow.html#AEN91458 is not actually necessary for the frontend to wait for ReadyForQuery before issuing another command. But is it necessary for frontend to wait for ReadyForQuery before sending Describe message? Or is it necessary to wait for RowDescription/NoData after sending Describe before sending Query or, say, Prepare? In short, is it necessary for frontend to wait for responses on sent messages before sending a new ones? Thanks! -- // Dmitriy.
[GENERAL] Unambiguous identification of the cluster.
Hey all, Is there are way to uniquely identify the cluster? May be some identificator such as UUID? PS. I need to unambiguous identify the database. There are current_catalog (and current_database()) function. There are also inet_server_addr() and inet_server_port() functions, but these functions returns NULL if the connection established via socket. On the other hand, the server may be configured to listen some port (not 5432) and interface and if the connection established via TCP/IP, these functions will returns non-NULL values. So, it is not convenient (or even not possible?) to unambiguous identify the cluster on the client side (without knowledge about default port and inteface which are listened by particular Postgres server). Any ideas? -- // Dmitriy.
Re: [GENERAL] Index on parent/child hierarchy
Hey, 2012/1/25 Merlin Moncure mmonc...@gmail.com On Wed, Jan 25, 2012 at 5:54 AM, Jason Armstrong j...@riverdrums.com wrote: Hi I'm looking for advice on the best way to index a table that is defined as: create table uuid.master(id uuid, parent uuid references uuid.master(id), type_id smallint, primary key(id)); Besides the primary key, I have these two indices on the table too: CREATE INDEX master_parent_idx ON uuid.master(parent); CREATE INDEX master_type_idx ON uuid.master(type_id); I have data arranged in four levels (ie type_id is from 1 to 4): 1. id=A type_id=1 2. id=B parent=A type_id=2 3. id=C parent=B type_id=3 4.id=D parent=C type_id=4 2. id=E parent=A type_id=2 3. id=F parent=E type_id=3 4.id=G parent=F type_id=4 4.id=H parent=F type_id=4 4.id=I parent=F type_id=4 3. id=J parent=E type_id=3 4.id=K parent=J type_id=4 I want to count all type_id=4 for a particular type_id=1 uuid. I use this query: SELECT count(t4.id) FROM uuid.master AS t4 INNER JOIN uuid.master AS t3 ON t4.parent=t3.id INNER JOIN uuid.master AS t2 ON t3.parent=t2.id INNER JOIN uuid.master AS t1 ON t2.parent=t1.id WHERE t1.id=UUID Apart from creating a separate table to keep track of the counts, is there a good way to index the table to help? If your data is organized as a tree, tends to run deep (causing many recursion joins), and you often make sweeping subset operations starting from a parent node, and your data isn't too heavily updated, you might want to consider materialized path organization instead of parent/child. Both arrays and strings work for that. id=I parents=A,E,F type_id=4 SELECT count(*) FROM uuid.master WHERE parents LIKE 'A,E%' and type_id = 4; The point here is that you can exploit the tree structure with a btree index. Before we got recursive queries, this was often the best way to do it, but now it's kind of a niche solution to be used when certain things fall into place. merlin Another approarch is to use ltree. It's easy and robust. http://www.postgresql.org/docs/9.1/static/ltree.html -- // Dmitriy.
Re: [GENERAL] On duplicate ignore
Hey Gnanakumar, 2012/1/18 Gnanakumar gna...@zoniac.com Just create a unique index on EMAIL column and handle error if it comes Thanks for your suggestion. Of course, I do understand that this could be enforced/imposed at the database-level at any time. But I'm trying to find out whether this could be solved at the application layer itself. Any thoughts/ideas? Exactly at the application level you just need to ignore an unique constraint violation error reported by the backend. You may also wrap INSERT statement in the PL/pgSQL function or in the DO statement and catch the exception generated by the backend. // Dmitriy.
Re: [GENERAL] Pgsql problem
Hey pasman, 2012/1/12 pasman pasmański pasma...@gmail.com Hi. I write function in pgsql. This function needs to execute other functions by name. I do it using loop: declare r record; begin for r in execute 'select ' || $1 || '()' loop end loop; But I can't convert a record to array of text. How to do it ? Presently, hstore is a most flexible solution in such cases. See http://www.postgresql.org/docs/9.1/static/hstore.html -- // Dmitriy.
Re: [GENERAL] Keywords
Hey vyang, 2012/1/11 vyang vy...@apt-cafm.com Hello List, I’m wondering if there is a way to retrieve/query PostgreSQL for a list of key words matching that of Appendix C. SQL Key Words. I’m using PostgreSQL 9.1 and java. I’ve already tried java’s DatabaseMetaData.getSQLKeywords, but the API states “Retrieves a comma-separated list of all of this database's SQL keywords that are NOT also SQL:2003 keywords.” As a result many key words are left out. Any help on this matter is much appreciated. vyang Use pg_get_keywords(). http://www.postgresql.org/docs/9.1/static/functions-info.html -- // Dmitriy.
Re: [GENERAL] How to code lo_creat lo_write lo_read in non-blocking mode
Hey ChoonSoo,
2012/1/6 ChoonSoo Park luisp...@gmail.com
I just wonder if there is a way to program lo client interfaces (lo_creat,
lo_write, lo_read) in non-blocking mode.
PQsendQueryParams works perfect for executing a sql command in
non-blocking mode. But I couldn't find a way for handling large objects.
These functions uses obsolete fast-path interface to call
these functions on the backend:
dmitigr= select oid, proname from pg_proc where proname ~ E'^lo_' or
proname in ('loread', 'lowrite');
oid | proname
--+-
764 | lo_import
767 | lo_import
765 | lo_export
952 | lo_open
953 | lo_close
954 | loread
955 | lowrite
956 | lo_lseek
957 | lo_creat
715 | lo_create
958 | lo_tell
1004 | lo_truncate
964 | lo_unlink
(13 rows)
So, you can call these functions using regular SQL
(prepared statements may be used for improving
performance in this case).
Do you have any example?
I can't imagine how (and why) to work with LOs
in asynchronous mode because LOs stored as a
sequence of chunks (the size is usually 4kb)
of the type bytea in the special table. As consequence all
operations on the LOs must be inside an explicitly opened
transaction block.
Thank you,
Choon Park
--
// Dmitriy.
Re: [GENERAL] PQexecParams with binary resultFormat vs BINARY CURSOR
Hey Mateusz, 2011/11/11 Mateusz Łoskot mate...@loskot.net Hi, Considering query for binary data stored directly in tables using libpq API, I'm trying to understand what is the difference between specifying binary format in functions like PQexecParams and use of BINARY CURSOR. For example, with query like this: SELECT large_image FROM tbl; where large_image is a custom type, is there a big difference between binary format specified to libpq and use of BINARY CURSOR? Is it client-side binary vs server-side binary processing? Simply, I'd like to avoid textual-binary conversions at any stage. (Endianness is not an issue here.) Best regards, ...The concept of a binary cursor as such is thus obsolete when using extended query protocol — any cursor can be treated as either text or binary. ... from http://www.postgresql.org/docs/9.1/static/sql-declare.html -- // Dmitriy.
Re: [GENERAL] PQexecParams with binary resultFormat vs BINARY CURSOR
2011/11/11 Mateusz Łoskot mate...@loskot.net Hi Dmitriy, 2011/11/11 Dmitriy Igrishin dmit...@gmail.com: 2011/11/11 Mateusz Łoskot mate...@loskot.net Considering query for binary data stored directly in tables using libpq API, I'm trying to understand what is the difference between specifying binary format in functions like PQexecParams and use of BINARY CURSOR. For example, with query like this: SELECT large_image FROM tbl; where large_image is a custom type, is there a big difference between binary format specified to libpq and use of BINARY CURSOR? Is it client-side binary vs server-side binary processing? Simply, I'd like to avoid textual-binary conversions at any stage. (Endianness is not an issue here.) Best regards, ...The concept of a binary cursor as such is thus obsolete when using extended query protocol — any cursor can be treated as either text or binary. ... from http://www.postgresql.org/docs/9.1/static/sql-declare.html Thanks, this is interesting. I've been reading more about this and the picture seems to be clear: Note: The choice between text and binary output is determined by the format codes given in Bind, regardless of the SQL command involved. http://www.postgresql.org/docs/9.1/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY However, I'm not sure how I can utilise this feature of the extended query protocol with libpq API. Concretely, how to translate the binding with format specification here choice between text and binary output is determined by the format codes given in Bind Does it mean the protocol automagically switches between text/binary depending on format code (0|1) specified to PQexecParams and friends? The Bind(F) message contains array with the parameter format codes. So, yes, all you need is to pass array with format codes to ::PQexecParams and libpq will let it go. Side question, is this new feature of the extended query protocol in 9.x line? The extended query sub-protocol introduced in protocol version 3.0. -- // Dmitriy.
Re: [GENERAL] Client-site lo_export
Hey, 2011/10/28 whiplash whipl...@bss.org.ua Hello! I use client-side lo_export for save large object to file. If i login as database owner then i do not have error (output file exists), but if i login as not database owner then function lo_export returning -1. My code is simple: PGresult *res = PQexec(pg_conn, begin); PQclear(res); int ret = lo_export(pg_conn, repo_oid, filename); res = PQexec(pg_conn, end); PQclear(res); OS: Win7, 32-bit PostgreSQL: 9.1.0 What could be the problem? Thank you for helping! I guess it's because of insufficient privileges on the LOB. Please refer this URL: http://www.postgresql.org/docs/9.1/static/lo-implementation.html -- // Dmitriy.
Re: [GENERAL] 9.1 got really fast ;)
2011/10/16 John R Pierce pie...@hogranch.com On 10/15/11 1:59 PM, Chris Travers wrote: Are you saying that Windows XP is the ultimate server OS for high performance PostgreSQL installations? Are there optimizations that this platform can take advantage of, perhaps extending Pg timelines into actual time travel that are not available on other platforms? powered by Neutrinos? :-) [*] wait, never mind, they think they found the error that lead to those FTL results http://arxiv.org/PS_cache/**arxiv/pdf/1110/1110.2685v1.pdfhttp://arxiv.org/PS_cache/arxiv/pdf/1110/1110.2685v1.pdf -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy.
Re: [GENERAL] bytea columns and large values
Hey Merlin, The lo interface sucks but it's slightly better on resources for really huge bytea and tends to be more consistently implemented in database drivers. If I was doing this, I would of course be crafting a carefully generated client in C, using libpqtypes, which is the gold standard for sending bytea against which all others should be judged. I am sorry, but why the Large Objects interface sucks? -- // Dmitriy.
Re: [GENERAL] bytea columns and large values
2011/10/2 Merlin Moncure mmonc...@gmail.com On Sat, Oct 1, 2011 at 4:27 AM, Dmitriy Igrishin dmit...@gmail.com wrote: Hey Merlin, The lo interface sucks but it's slightly better on resources for really huge bytea and tends to be more consistently implemented in database drivers. If I was doing this, I would of course be crafting a carefully generated client in C, using libpqtypes, which is the gold standard for sending bytea against which all others should be judged. I am sorry, but why the Large Objects interface sucks? hm, good question. well, it's not sql and is outside the type system. imo, bytea is really the way to go for sending binary. also, if it turns out that we need to add features to send large pieces of data, it should not be specific to bytea. But at the moment, the only way to put/get the data piecewise is to use LOBs. merlin merlin -- // Dmitriy.
[GENERAL] Millions of largeobjects the production databases.
Hey Community, Just curious, is there are heavily loaded servers with databases in production with tons (millions) of largeobjects (pics, movies)? Theoretically, everything should be fine with it, but it is always interesting to know how things works in practice. Thanks! -- // Dmitriy.
Re: [GENERAL] Parameterized prepared statements
Hey Craig, Things like pre-parsed prepared statements that're re-planned on every execution are often proposed as solutions to this. This has me wondering: rather than expensively re-planning from scratch, would it be possiblet to adjust the planning process so that *multiple* alternative plans would be cached for a query, using placeholders for unknown rowcounts and costs? At execution, the unknown costs would be filled in and the plans compared then the best plan picked for this execution. Is this crazy talk, or could it significantly reduce the cost of re-planning parameterized prepared statements to the point where it'd be worth doing by default? Its a good suggestion of some kind of optimization at the server side. This idea can be extended to an auto-prepare mode (like an auto-commit mode that we have today in Postgres). But its not so hard to let the application (or library) to decide what to use in different cases: prepared statement or regular statement. Thus, I think it is not worth it... -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy.
Re: [GENERAL] Using Postgresql as application server
2011/8/18 s...@bestmx.ru s...@bestmx.ru Merlin Moncure пишет: On Thu, Aug 18, 2011 at 5:48 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: c kshreeseva.learn...@gmail.com** writes: Many users are using it and found it stable and scalable. Important is that web server is external to the database and a mod_pgsql like mod_plsql is used to connect web server to database. Each page is considered as a stored procedure in the oracle database. I am not thinking of implementing as it is It's been around for a long time already: http://asmith.id.au/mod_libpq.**htmlhttp://asmith.id.au/mod_libpq.html mod_libpq looks like it hasn't been updated in quite a while (apache 1.3 only) -- I think a node.js http server is superior in just about every way for this case. I 100% agree with the comments on the page though. merlin i still recommend nginx I recommend Wt: http://www.webtoolkit.eu/ :-) -- // Dmitriy.
Re: [GENERAL] Using Postgresql as application server
2011/8/18 s...@bestmx.ru s...@bestmx.ru Dmitriy Igrishin пишет: 2011/8/18 s...@bestmx.ru mailto:s...@bestmx.ru s...@bestmx.ru mailto: s...@bestmx.ru Merlin Moncure пишет: On Thu, Aug 18, 2011 at 5:48 AM, Dimitri Fontaine dimi...@2ndquadrant.fr mailto:dimi...@2ndquadrant.fr** wrote: c kshreeseva.learn...@gmail.com mailto:shreeseva.learning@**gmail.comshreeseva.learn...@gmail.com writes: Many users are using it and found it stable and scalable. Important is that web server is external to the database and a mod_pgsql like mod_plsql is used to connect web server to database. Each page is considered as a stored procedure in the oracle database. I am not thinking of implementing as it is It's been around for a long time already: http://asmith.id.au/mod_libpq.**htmlhttp://asmith.id.au/mod_libpq.html mod_libpq looks like it hasn't been updated in quite a while (apache 1.3 only) -- I think a node.js http server is superior in just about every way for this case. I 100% agree with the comments on the page though. merlin i still recommend nginx I recommend Wt: http://www.webtoolkit.eu/ :-) it looks like feces and uses well-tested patterns of desktop GUI development Oh oh. So unprofessional comment! Well, have a nice coding a Web 2.0 application with nginx + PostgreSQL :-) -- // Dmitriy.
Re: [GENERAL] Using Postgresql as application server
2011/8/18 s...@bestmx.ru s...@bestmx.ru Dmitriy Igrishin пишет: 2011/8/18 s...@bestmx.ru mailto:s...@bestmx.ru s...@bestmx.ru mailto: s...@bestmx.ru Dmitriy Igrishin пишет: 2011/8/18 s...@bestmx.ru mailto:s...@bestmx.ru mailto:s...@bestmx.ru mailto:s...@bestmx.ru s...@bestmx.ru mailto:s...@bestmx.ru mailto:s...@bestmx.ru mailto:s...@bestmx.ru Merlin Moncure пишет: On Thu, Aug 18, 2011 at 5:48 AM, Dimitri Fontaine dimi...@2ndquadrant.fr mailto:dimi...@2ndquadrant.fr** mailto:dimi...@2ndquadrant.fr mailto:dimi...@2ndquadrant.fr** wrote: c kshreeseva.learn...@gmail.com mailto:shreeseva.learning@**gmail.comshreeseva.learn...@gmail.com mailto:shreeseva.learning@**gmail.comshreeseva.learn...@gmail.com mailto:shreeseva.learning@**gmail.comshreeseva.learn...@gmail.com writes: Many users are using it and found it stable and scalable. Important is that web server is external to the database and a mod_pgsql like mod_plsql is used to connect web server to database. Each page is considered as a stored procedure in the oracle database. I am not thinking of implementing as it is It's been around for a long time already: http://asmith.id.au/mod_libpq.**htmlhttp://asmith.id.au/mod_libpq.html mod_libpq looks like it hasn't been updated in quite a while (apache 1.3 only) -- I think a node.js http server is superior in just about every way for this case. I 100% agree with the comments on the page though. merlin i still recommend nginx I recommend Wt: http://www.webtoolkit.eu/ :-) it looks like feces and uses well-tested patterns of desktop GUI development Oh oh. So unprofessional comment! Well, have a nice coding a Web 2.0 application with nginx + PostgreSQL :-) -- // Dmitriy. who said web 2.0 ? i've never used religious idioms in a technical talk. I see. You're using only nginx :-) -- // Dmitriy.
Re: [GENERAL] TRUNCATE pg_largeobject
Hey Tamas, 2011/7/25 Tamas Vincze vin...@neb.com Is it safe? This table is around 500GB and because of performance reasons I slowly moved all large objects to regular files on a NetApp share. Now it shows 0 records: # select count(*) from pg_largeobject; count --- 0 (1 row) but disk space and RAM by the free space map is still occupied. I'd like to clean it up. A VACUUM FULL would probably do it, but it would take several hours with heavy I/O and I'd like to avoid that on a production server. Can I safely issue a TRUNCATE pg_largeobject? It is v8.1.9. Thanks! I think that SELECT lo_unlink(loid) FROM (SELECT DISTINCT loid FROM pg_largeobject) AS foo would be better than direct truncation. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy.
Re: [GENERAL] How to create auto-increment field WITHOUT a sequence object?
Hey Vincent, 2011/7/3 Vincent Veyron vv.li...@wanadoo.fr Le vendredi 01 juillet 2011 à 12:28 +0400, Dmitriy Igrishin a écrit : Then I don't clearly understand the existence of locks (the LOCK command, SELECT FOR UPDATE clause and so on) if the usage of them gives only problems... Chris already explained why twice : you MUST lock on insert to get gapless sequences Not me :-). The OP must do it. So, what problem here? Deadlocks? Again, if deadlocks are so dangerous, why the LOCK command exists? Can't you just : -create the records with a regular sequence, that will have gaps -when you want to export, number an additional column from 1 to 10 000 and use that as key ? I don't use any locks explicitly :-) -- Vincent Veyron http://marica.fr/ Logiciel de gestion des sinistres et des contentieux pour le service juridique -- // Dmitriy.
Re: [GENERAL] How to create auto-increment field WITHOUT a sequence object?
Hey Alban, 2011/7/3 Alban Hertroys dal...@solfertje.student.utwente.nl On 3 Jul 2011, at 16:10, Dmitriy Igrishin wrote: you MUST lock on insert to get gapless sequences Not me :-). The OP must do it. So, what problem here? Deadlocks? Again, if deadlocks are so dangerous, why the LOCK command exists? It's not deadlocks, it's concurrent updates that are the trouble. If you don't lock, you run the risk for two records being assigned the same number concurrently. Thanks for clarify, but I know why resources must be locked when they are used concurrently :-). See my previous post about SELECT FOR UPDATE ... and I don't see the problem with it. As well as with the LOCK command. With a unique constraint added into the mix (and there should be one) that means that one of the transactions will fail the unique constraint check on commit. It's possible to catch that in the client and redo the transaction with a new ID, but if that's not acceptable (for example because it matters which transaction got the ID first) then you need to lock records. Sure. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:1257,4e109f6512095013212184! -- // Dmitriy.
Re: [GENERAL] How to create auto-increment field WITHOUT a sequence object?
Hey Chris,
The suggestion of using for
update is a good one, but it doesn't entirely get rid of the problem,
which is inherent in ensuring gapless numbering in a system with
concurrent transactions.
Why not?
I mean the following solution:
CREATE TABLE myseq(tabnm text not null, lastid integer not null);
INSERT INTO myseq SELECT 'mytab', 0; -- initialization
CREATE OR REPLACE FUNCTION public.myseq_nextval(a_tabnm text)
RETURNS integer
LANGUAGE sql
STRICT
AS $function$
UPDATE myseq SET lastid = li + 1 FROM
(SELECT lastid li FROM myseq WHERE tabnm = $1 FOR UPDATE) foo
RETURNING lastid;
$function$
-- Test
dmitigr= BEGIN;
BEGIN
dmitigr= SELECT myseq_nextval('mytab');
myseq_nextval
---
1
(1 row)
dmitigr= ROLLBACK;
ROLLBACK
dmitigr= SELECT * FROM myseq;
tabnm | lastid
---+
mytab | 0
(1 row)
So, with this approach you'll get a lock only on INSERT.
dmitigr= CREATE TABLE mytab(id integer not null DEFAULT
myseq_nextval('mytab'));
CREATE TABLE
dmitigr= INSERT INTO mytab DEFAULT VALUES;
INSERT 0 1
dmitigr= INSERT INTO mytab DEFAULT VALUES;
INSERT 0 1
dmitigr= SELECT * FROM mytab;
id
1
2
(2 rows)
--
// Dmitriy.
Re: [GENERAL] How to create auto-increment field WITHOUT a sequence object?
2011/7/1 Chris Travers chris.trav...@gmail.com
On Fri, Jul 1, 2011 at 1:16 AM, Dmitriy Igrishin dmit...@gmail.com
wrote:
Hey Chris,
The suggestion of using for
update is a good one, but it doesn't entirely get rid of the problem,
which is inherent in ensuring gapless numbering in a system with
concurrent transactions.
Why not?
Just because it locks less doesn't mean that it doesn't lock.
The point is: if gaps are acceptable then the sequences which exist
outside of transactions are idea. If gaps are not acceptable, you
have to lock and force transactions through the system serially which
means a possibility of deadlocks and performance issues. These issues
are inherent in gapless numbering because you can't get a gapless
sequence when things roll back without such locks.
Then I don't clearly understand the existence of locks (the LOCK
command, SELECT FOR UPDATE clause and so on) if the usage
of them gives only problems...
I mean the following solution:
CREATE TABLE myseq(tabnm text not null, lastid integer not null);
INSERT INTO myseq SELECT 'mytab', 0; -- initialization
CREATE OR REPLACE FUNCTION public.myseq_nextval(a_tabnm text)
RETURNS integer
LANGUAGE sql
STRICT
AS $function$
UPDATE myseq SET lastid = li + 1 FROM
(SELECT lastid li FROM myseq WHERE tabnm = $1 FOR UPDATE) foo
RETURNING lastid;
$function$
-- Test
dmitigr= BEGIN;
BEGIN
dmitigr= SELECT myseq_nextval('mytab');
myseq_nextval
---
1
(1 row)
dmitigr= ROLLBACK;
ROLLBACK
dmitigr= SELECT * FROM myseq;
tabnm | lastid
---+
mytab | 0
(1 row)
So, with this approach you'll get a lock only on INSERT.
True. But the point us that you MUST lock on insert to get gapless
sequences, and this creates inherent problems in terms of performance
and concurrency, so that you should not use it unless you really have
no other choice (i.e. because the tax authorities demand it).
Sure, but, again, why LOCK and SELECT FOR UPDATE exists ?
Best Wishes,
Chris Travers
--
// Dmitriy.
Re: [GENERAL] How to create auto-increment field WITHOUT a sequence object?
Hey Dmitry, 2011/6/30 Dmitry Koterov dmitry.kote...@gmail.com Hello. I need to create an auto-increment field on a table WITHOUT using sequences: CREATE TABLE tbl( name TEXT, uniq_id INTEGER ); Each INSERT to this table must generate a new uniq_id which is distinct from all others. The problem is that these INSERTs are rolled back oftenly (i.e. they are executed within a transaction block which is rolled back time to time), this is an existing design of the current architecture and unfortunately we have to live with it. And I need as compact uniq_id generation (with minimum holes) as it possible - this is a VERY important requirement (to export these values into external systems which accepts only IDs limited from 1 to 10). So I cannot use sequences: sequence value is obviously not rolled back, so if I insert nextval(...) as uniq_id, I will have large holes (because of often transaction rollbacks) and exhaust 10 uniq_ids very fast. How to deal with all this without sequences? I tried BEGIN; LOCK TABLE tbl; INSERT INTO tbl(uniq_id) VALUES((SELECT max(uniq_id) FROM tbl) + 1); COMMIT; Consider to create table with column of type integer and write a function which will perform SELECT FOR UPDATE ... and returns the next value, i.e. BEGIN; INSERT INTO tbl(uniq_id) SELECT uniq_id_generator(); -- SELECT FOR UPDATE inside COMMIT; -- or ROLLBACK -- // Dmitriy.
Re: [GENERAL] PostgreSQL 9.0 users
Hey Zhidong, 2011/6/11 Zhidong She zhidong@gmail.com Hi all, Could you please give us some typical users that already upgraded to version 9.0? We have a debate internally on choosing 8.4 or 9.0 as our product backend database. We are switched our current development from 9.0 to 9.1 beta already without any doubts. And if you have any performance benchmark result, I will highly appreciate. Many thanks, sheldon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy.
Re: [GENERAL] Converting between varchar and float when updating
2011/4/28 Thomas Larsen Wessel mrve...@gmail.com I have a table with the following schema: CREATE TABLE foo (bar VARCHAR(32)); Every bar value has a format like a float, e.g. 2.5. Now I want that value multiplied by two and saved again as varchar. I was hoping to do smth like: UPDATE foo SET bar = TO_VARCHAR( TO_FLOAT(bar) * 2); -- INCORRECT Try UPDATE foo SET bar = (bar::numeric * 2); How is that done? I know that the bar attribute ought to have type FLOAT, but I have to work with this legacy database. And anyway this table will rarely be updated. Sincerely, Thomas -- // Dmitriy.
Re: [GENERAL] Converting between varchar and float when updating
2011/4/28 Vibhor Kumar vibhor.ku...@enterprisedb.com On Apr 28, 2011, at 2:56 PM, Thomas Larsen Wessel wrote: UPDATE foo SET bar = TO_VARCHAR( TO_FLOAT(bar) * 2); -- INCORRECT If you are sure bar contains float value, then try following: UPDATE foo SET bar = bar::float * 2; NB: I am sure that OP is not sure :-) And since foo.bar is varchar, it is better to use numeric instead of float :-) Thanks Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy.
Re: [GENERAL] Converting between varchar and float when updating
2011/4/28 Thomas Larsen Wessel mrve...@gmail.com I have a table with the following schema: CREATE TABLE foo (bar VARCHAR(32)); Every bar value has a format like a float, e.g. 2.5. Now I want that value multiplied by two and saved again as varchar. I was hoping to do smth like: UPDATE foo SET bar = TO_VARCHAR( TO_FLOAT(bar) * 2); -- INCORRECT How is that done? I know that the bar attribute ought to have type FLOAT, but I have to work with this legacy database. And anyway this table will rarely be updated. Sincerely, Thomas Btw, why are you store numeric values in varchar instead of numeric? -- // Dmitriy.
Re: [GENERAL] Converting between varchar and float when updating
2011/4/28 Vibhor Kumar vibhor.ku...@enterprisedb.com On Apr 28, 2011, at 3:22 PM, Dmitriy Igrishin wrote: NB: I am sure that OP is not sure :-) And since foo.bar is varchar, it is better to use numeric instead of float :-) Now, this make to ask question, why numeric? How its better than float? Only one point, Vibhor. I believe that varchar data type was chosen for exact storage of numeric values. According to chapter 8.1.3 of the doc. for this case the usage of numeric is preferred over floating data types. Thanks Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.com -- // Dmitriy.
Re: [GENERAL] Converting between varchar and float when updating
2011/4/28 Thomas Larsen Wessel mrve...@gmail.com Thanks a lot :) Both of the following work UPDATE foo SET bar = (bar::float * 2); removes trailing zeros on the decimal side, if no decimals dont show any . UPDATE foo SET bar = (bar::numeric * 2); keeps decimals, i.e. 2.000 * 2 - 4.000 That leads me to two additional questions: 1) Can I specify how many decimals I want to be stored back from the result? E.g. 2 / 3 = 0. but I want to just save 0.66. Try UPDATE foo SET bar = (bar::numeric(1000,2) * 2); 2) Can I make a criteria that it should only update on the strings that can be converted. Maybe smth. like: UPDATE foo SET bar = (bar::numeric * 2) WHERE bar::is_numeric; Try for example WHERE bar ~ E'^\\s*[-+e\\.0-9]+\\s*$' Thomas P.S.: Dmitriy asked why I save these values in VarChar. Well, I agree, that they should be numeric, but I did not design the schema which is btw 10 years old. You can try change data type of the column, e.g.: ALTER TABLE foo SET DATA TYPE numeric(10, 2) USING bar::numeric(10,2); On Thu, Apr 28, 2011 at 12:18 PM, Vibhor Kumar vibhor.ku...@enterprisedb.com wrote: On Apr 28, 2011, at 3:41 PM, Dmitriy Igrishin wrote: Only one point, Vibhor. I believe that varchar data type was chosen for exact storage of numeric values. According to chapter 8.1.3 of the doc. for this case the usage of numeric is preferred over floating data types. Ah! Got it. This I have missed. Thanks Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.com -- // Dmitriy.
Re: [GENERAL] Converting between varchar and float when updating
2011/4/28 Dmitriy Igrishin dmit...@gmail.com 2011/4/28 Thomas Larsen Wessel mrve...@gmail.com Thanks a lot :) Both of the following work UPDATE foo SET bar = (bar::float * 2); removes trailing zeros on the decimal side, if no decimals dont show any . UPDATE foo SET bar = (bar::numeric * 2); keeps decimals, i.e. 2.000 * 2 - 4.000 That leads me to two additional questions: 1) Can I specify how many decimals I want to be stored back from the result? E.g. 2 / 3 = 0. but I want to just save 0.66. Try UPDATE foo SET bar = (bar::numeric(1000,2) * 2); 2) Can I make a criteria that it should only update on the strings that can be converted. Maybe smth. like: UPDATE foo SET bar = (bar::numeric * 2) WHERE bar::is_numeric; Try for example WHERE bar ~ E'^\\s*[-+e\\.0-9]+\\s*$' Thomas P.S.: Dmitriy asked why I save these values in VarChar. Well, I agree, that they should be numeric, but I did not design the schema which is btw 10 years old. You can try change data type of the column, e.g.: ALTER TABLE foo SET DATA TYPE numeric(10, 2) USING bar::numeric(10,2); Oh, sorry ALTER TABLE foo ALTER bar SET DATA TYPE numeric(10, 2) USING bar::numeric(10,2); On Thu, Apr 28, 2011 at 12:18 PM, Vibhor Kumar vibhor.ku...@enterprisedb.com wrote: On Apr 28, 2011, at 3:41 PM, Dmitriy Igrishin wrote: Only one point, Vibhor. I believe that varchar data type was chosen for exact storage of numeric values. According to chapter 8.1.3 of the doc. for this case the usage of numeric is preferred over floating data types. Ah! Got it. This I have missed. Thanks Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.com -- // Dmitriy. -- // Dmitriy.
Re: [GENERAL] Global Variables in plpgsql
Hey Nick, 2011/4/11 Nick Raj nickrajj...@gmail.com Hi, Can anyone know how to define global variable in plpgsql? Thanks Why if you are already inside a database system ? :-) Just use tables. Regards, Raj -- // Dmitriy.
[GENERAL] Stange IO error while working with large objects.
Hey all, I've never experienced such problems before pefrorming some tests on large objects. I am on Ubuntu and my HDD is whole encrypted (LVM2). I've imported large object ~ 1.5 Gb of size. After this, entire system lost performance dramaticaly and the disk activity becomes anomalous. After reboot everithing is fine with OS, but attempt to remove all large object results in error: dmitigr=# select lo_unlink(loid) from (select distinct loid from pg_largeobject) as foo; ERROR: could not read block 704833 in file base/16386/11838.5: read only 4096 of 8192 bytes What does this means -- hardware / OS / Postgres failure ? Any suggestions ? -- // Dmitriy.
Re: [GENERAL] Stange IO error while working with large objects.
I've checked the disk with badblocs(8). The results are: File /pgsql/9.0/data0/base/16386/11838.5 (inode #3015588, mod time Wed Mar 30 13:13:13 2011) has 50 multiply-claimed block(s), shared with 1 file(s): The bad blocks inode (inode #1, mod time Wed Mar 30 15:23:19 2011) After this, I've dropped the database and create a new one. Problem is solved. All the same it is interesting, why there was such problem? I am disturbed because I intend to use large objects in production... Suggestions ? 2011/3/30 Dmitriy Igrishin dmit...@gmail.com Hey all, I've never experienced such problems before pefrorming some tests on large objects. I am on Ubuntu and my HDD is whole encrypted (LVM2). I've imported large object ~ 1.5 Gb of size. After this, entire system lost performance dramaticaly and the disk activity becomes anomalous. After reboot everithing is fine with OS, but attempt to remove all large object results in error: dmitigr=# select lo_unlink(loid) from (select distinct loid from pg_largeobject) as foo; ERROR: could not read block 704833 in file base/16386/11838.5: read only 4096 of 8192 bytes What does this means -- hardware / OS / Postgres failure ? Any suggestions ? -- // Dmitriy. -- // Dmitriy.
Re: [GENERAL] Stange IO error while working with large objects.
Hey Merlin, 2011/3/30 Merlin Moncure mmonc...@gmail.com On Wed, Mar 30, 2011 at 7:37 AM, Dmitriy Igrishin dmit...@gmail.com wrote: I've checked the disk with badblocs(8). The results are: File /pgsql/9.0/data0/base/16386/11838.5 (inode #3015588, mod time Wed Mar 30 13:13:13 2011) has 50 multiply-claimed block(s), shared with 1 file(s): The bad blocks inode (inode #1, mod time Wed Mar 30 15:23:19 2011) After this, I've dropped the database and create a new one. Problem is solved. All the same it is interesting, why there was such problem? I am disturbed because I intend to use large objects in production... It's very unlikely the problem is postgres. I give it equal odds on hard disk issue (did you check smart?) or lvm bug. I'd consider stress testing this storage stack for a while. Thanks for the reply. It seems to me a hardware problem too. I'll check SMART. merlin -- // Dmitriy.
Re: [GENERAL] Maximum number of tables
Hey Manos, 2011/3/16 Manos Karpathiotakis m...@di.uoa.gr Let me explain a few things about our dataset. We are using a system named Sesame [1] that stores and queries RDF data. In our case, it uses Postgres as a relational backend. In RDF, data are triples. Here is an example of an RDF triple: ex:Postgres rdf:type ex:RDBMS Triples are stored in Postgres. We can use two storing schemes. A monolithic scheme where every triple is stored in 1 huge table: Triple(int subject, int predicate, int object) and some additional information is stored in other tables of the form dictionary(int id, string original_value). This results in a schema with approximately 10 tables. Have you considered to use hstore in this case? http://www.postgresql.org/docs/9.0/static/hstore.html A per-predicate scheme can also be used. This storing scheme creates a table for every distinct predicate. For example, to store the aforementioned triple, we would create a table type(int subject, int object) and we would insert a tuple with the encoded values for ex:Postgres and ex:RDBMS. Queries for RDF data can be expressed in the SPARQL query language. Sesame translates SPARQL queries to SQL queries depending on the storing scheme being used. So, you can imagine that when we use the monolithic storing scheme, queries would be translated to an SQL query with many self-joins on a huge triple table. On the other hand, if we use the predicate schema, SPARQL queries are translated to many joins between smaller tables. In our case, we want to store 111M triples (and we would like to experiment with even datasets) that consists of approximately 10.000 distinct predicates. This means that when we would strongly prefer to use the per-predicate storing scheme to have faster query execution. However we are not experienced in tuning Postgres for this kind of data. Until now, we disabled WAL, disabled autocommit, increased shared buffers to 512mb, temp buffers to 64mb but we haven't tried disabling indices and foreign key constraints because it would require some code restructuring (I understand however the performance increase that we would get if we disabled them). We are currently loading the dataset in chunks that perform 24.000.000 insertions to the database. Could you suggest some values for shared buffers, temp buffers, maintenance_work_mem, checkpoint_segments or other relevant parameters that we could use as a starting point? Best Regards, Manos Karpathiotakis [1] http://www.openrdf.org/ On Tue, Mar 15, 2011 at 7:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Manos Karpathiotakis m...@di.uoa.gr writes: Hi all, I am using postgres 9.0 under CentOS 5.5 (Dual Xeon Quad Core @ 2,44 GHz, 64GB RAM, 2TB RAID 5). In my case, postgres is used as a backend for the RDF store Sesame. I am trying to store a dataset that consists of approximately 900.000.000 insertions (organized in either 10 tables, or in an arbitrary number of tables exceeding 2000). Loading the data in 10 tables takes about two days, while in the 2nd case, an org.postgresql.util.PSQLException: ERROR: out of shared memory error is thrown. I'm guessing that you're seeing this through some client-side code that helpfully suppresses the HINT about raising max_locks_per_transaction :-( If your application tries to touch all 2000 tables in one transaction, you will need to raise that parameter to avoid running out of locktable space for the AccessShareLocks it needs on all those tables. However ... generally speaking, I'd guess that whatever you did to refactor 10 tables into 2000 was a bad idea --- one table with an extra key column is almost always a better design than N basically-identical tables. The latter will be a *lot* harder to use, and probably won't fix your performance problem anyway. You should attack the performance problem in a different way. Have you read http://www.postgresql.org/docs/9.0/static/populate.html ? regards, tom lane -- === Manos Karpathiotakis National Kapodistrian University of Athens Department of Informatics Telecommunications, Office B25 Management of Data Information Knowledge Group Panepistimioupolis, Ilissia GR-15784 Athens, Greece Tel: +30.210.727.5159 Fax: +30.210.727.5214 e-mail: m...@di.uoa.gr === -- // Dmitriy.
Re: [GENERAL] Values larger than 1/3 of a buffer page cannot be indexed.
Hey Viktor, 2011/3/13 Viktor Nagy viktor.n...@toolpart.hu hi, when trying to insert a long-long value, I get the following error: index row size 3120 exceeds maximum 2712 for index ir_translation_ltns HINT: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full text indexing. is there a way to generate this recommended function index of an md5 hash on an already existing database and tables (filled with data)? Just create index this way, e.g. CREATE INDEX ir_translation_ltns ON tab ((md5(col))); where tab and col are table and column of which you want to create btree index. thanks, Viktor -- // Dmitriy.
Re: [GENERAL] equivalent of mysql's SET type?
2011/3/11 Merlin Moncure mmonc...@gmail.com
On Thu, Mar 10, 2011 at 4:13 PM, Dmitriy Igrishin dmit...@gmail.com
wrote:
2011/3/9 John R Pierce pie...@hogranch.com
On 03/08/11 5:06 PM, Reece Hart wrote:
I'm considering porting a MySQL database to PostgreSQL. That database
uses MySQL's SET type. Does anyone have advice about representing this
type
in PostgreSQL?
MySQL DDL excerpt:
CREATE TABLE `transcript_variation` (
`transcript_variation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`transcript_stable_id` varchar(128) NOT NULL,
...
`consequence_type`
set('ESSENTIAL_SPLICE_SITE','STOP_GAINED','STOP_LOST','COMPLEX_INDEL','SPLICE_SITE')
) ENGINE=MyISAM AUTO_INCREMENT=174923212 DEFAULT CHARSET=latin1;
why not just have a set of booleans in the table for these individual
on/off attributes? wouldn't that be simplest?
Yes, it might be simplest at first sight.
But classical solution is relation N - N scales simpler than
any tricks with bytes.
Unfortunately, enums and composite types are not extensible. And
if you need to add yet another option (or remove some option) it
will be problematic.
In case of N - N relation you need just use INSERT/DELETE.
actually composite types are fairly workable if you use table instead
of a type (you can drop/add column, etc). in 9.1 you will be able to
do this with vanilla composite type
(http://developer.postgresql.org/pgdocs/postgres/sql-altertype.html).
Good news! Thanks for pointing that.
in typical case I would agree that classic approach of separate
relation is typically the way to go, there are exceptions -- for
example enum gives you inline ordering -- or as in this case where OP
is looking to simplify porting large body of application code.
Agree.
merlin
--
// Dmitriy.
Re: [GENERAL] Why length(to_char(1::integer, '9')) = 2 ?
2011/3/10 Bruce Momjian br...@momjian.us Dmitriy Igrishin wrote: dmitigr= SELECT ''||to_char(0.1, '0.9')||'' AS v; v 0.1 dmitigr= SELECT ''||to_char(0.1, 'FM0.9')||'' AS v; v --- 0.1 So, padding zeroes suppressed by FM is a rest of the value. Any documentation changes suggested? I propose to replace fill mode (suppress padding blanks and zeroes) located here http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIONS-FORMATTING-NUMERICMOD-TABLE with fill mode (suppress padding blanks and trailing zeroes). -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- // Dmitriy.
Re: [GENERAL] equivalent of mysql's SET type?
2011/3/9 John R Pierce pie...@hogranch.com
On 03/08/11 5:06 PM, Reece Hart wrote:
I'm considering porting a MySQL database to PostgreSQL. That database uses
MySQL's SET type. Does anyone have advice about representing this type in
PostgreSQL?
MySQL DDL excerpt:
CREATE TABLE `transcript_variation` (
`transcript_variation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`transcript_stable_id` varchar(128) NOT NULL,
...
`consequence_type`
set('ESSENTIAL_SPLICE_SITE','STOP_GAINED','STOP_LOST','COMPLEX_INDEL','SPLICE_SITE')
) ENGINE=MyISAM AUTO_INCREMENT=174923212 DEFAULT CHARSET=latin1;
why not just have a set of booleans in the table for these individual
on/off attributes? wouldn't that be simplest?
Yes, it might be simplest at first sight.
But classical solution is relation N - N scales simpler than
any tricks with bytes.
Unfortunately, enums and composite types are not extensible. And
if you need to add yet another option (or remove some option) it
will be problematic.
In case of N - N relation you need just use INSERT/DELETE.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
// Dmitriy.
[GENERAL] Why length(to_char(1::integer, '9')) = 2 ?
Hey all, dmitigr= select to_char(1, '9'); to_char - 1 dmitigr= select length(to_char(1, '9')); length 2 Why to_char() includes preceding blank space in the result ? -- // Dmitriy.
Re: [GENERAL] Why length(to_char(1::integer, '9')) = 2 ?
2011/3/9 Pavel Stehule pavel.steh...@gmail.com 2011/3/9 Dmitriy Igrishin dmit...@gmail.com: Hey all, dmitigr= select to_char(1, '9'); to_char - 1 dmitigr= select length(to_char(1, '9')); length 2 Why to_char() includes preceding blank space in the result ? it is compatibility with Oracle? Do you mean the case of MI ? So, is this leading space reserved for a sign of number by default ? Regards Pavel -- // Dmitriy. -- // Dmitriy.
Re: [GENERAL] Why length(to_char(1::integer, '9')) = 2 ?
2011/3/9 Pavel Stehule pavel.steh...@gmail.com 2011/3/9 Dmitriy Igrishin dmit...@gmail.com: 2011/3/9 Pavel Stehule pavel.steh...@gmail.com 2011/3/9 Dmitriy Igrishin dmit...@gmail.com: Hey all, dmitigr= select to_char(1, '9'); to_char - 1 dmitigr= select length(to_char(1, '9')); length 2 Why to_char() includes preceding blank space in the result ? it is compatibility with Oracle? Do you mean the case of MI ? So, is this leading space reserved for a sign of number by default ? yes pavel=# select '' || to_char(-1,'9') || ''; ?column? ── -1 (1 row) Aha! Thanks. regards Pavel Regards Pavel -- // Dmitriy. -- // Dmitriy. -- // Dmitriy.
Re: [GENERAL] Why length(to_char(1::integer, '9')) = 2 ?
2011/3/9 Sim Zacks s...@compulab.co.il -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/09/2011 03:12 PM, Dmitriy Igrishin wrote: Hey all, dmitigr= select to_char(1, '9'); to_char - 1 dmitigr= select length(to_char(1, '9')); length 2 Why to_char() includes preceding blank space in the result ? -- // Dmitriy. I don't know why, but to work around it use: select to_char(1, 'FM9'); select length(to_char(1, 'FM9')); Thanks! But I am missing something or there is a documentation inaccuracy: http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIONS-FORMATTING-NUMERICMOD-TABLEsays: fill mode (suppress padding blanks and zeroes) Test: dmitigr= select to_char(12,'FM0009'); to_char - 0012 dmitigr= select length(to_char(12,'FM0009')); length 4 So, FM suppresses only padding blanks not zeroes... Any comments? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iQEcBAEBAgAGBQJNd3/JAAoJEHr2Gm0ENObOsUMH/ApWyfc5c5A56m1pAP7raIEd dmY0/aocCCnQbariREZIGSJPrmcWDKnNe3yNLjV2Y3+EY+eaicxy2GPTVamOrfqN tYQ/ImH3IkrzQk1bfRX+lnUJQGEmMi8ClzAatKUIifGJwMuj7y1xUl/VBTP0lBvI GuQQaElNkpGaPRTJZlorrtqEBgWmiyBT07gK02IST9xFsUPnrF0niNlqcaphF2Ga kKgFfVJ8u/C3KbwowVPh5GYZHgIM1T8x6SPzpcsnFVrIGN+avnuvdEInxomCZDNN FLuRBEPK9NFTG6rdIyrtfy5C6HVm/q7rO1alW0hjuszou1t2gBCOkmXtva9V5gY= =pGTI -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy.
Re: [GENERAL] Why length(to_char(1::integer, '9')) = 2 ?
2011/3/9 Adrian Klaver adrian.kla...@gmail.com On Wednesday, March 09, 2011 5:34:41 am Dmitriy Igrishin wrote: But I am missing something or there is a documentation inaccuracy: http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIO NS-FORMATTING-NUMERICMOD-TABLEsays: fill mode (suppress padding blanks and zeroes) Test: dmitigr= select to_char(12,'FM0009'); to_char - 0012 dmitigr= select length(to_char(12,'FM0009')); length 4 So, FM suppresses only padding blanks not zeroes... Any comments? test(5432)aklaver=select to_char(12,''); to_char - 12 test(5432)aklaver=select to_char(12,'FM'); to_char - 12 It is a little confusing, but you asked for the 0 in your specification so they are not considered padding. Look at the examples in the table listed below to get an idea of what I am talking about. http://www.postgresql.org/docs/9.0/static/functions-formatting.html Table 9-25 Yes, I see, thanks! I just talking about phrase fill mode (suppress padding blanks and zeroes) in the documentation should be rephrased to fill mode (suppress padding blanks). Or I misunderstood what is padding zeroes without explicitly specification 0 pattern in the format format template... -- Adrian Klaver adrian.kla...@gmail.com -- // Dmitriy.
Re: [GENERAL] Why length(to_char(1::integer, '9')) = 2 ?
2011/3/9 Adrian Klaver adrian.kla...@gmail.com On 03/09/2011 09:59 AM, Dmitriy Igrishin wrote: 2011/3/9 Adrian Klaver adrian.kla...@gmail.com mailto:adrian.kla...@gmail.com On Wednesday, March 09, 2011 5:34:41 am Dmitriy Igrishin wrote: But I am missing something or there is a documentation inaccuracy: http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIO NS-FORMATTING-NUMERICMOD-TABLEsays: fill mode (suppress padding blanks and zeroes) Test: dmitigr= select to_char(12,'FM0009'); to_char - 0012 dmitigr= select length(to_char(12,'FM0009')); length 4 So, FM suppresses only padding blanks not zeroes... Any comments? test(5432)aklaver=select to_char(12,''); to_char - 12 test(5432)aklaver=select to_char(12,'FM'); to_char - 12 It is a little confusing, but you asked for the 0 in your specification so they are not considered padding. Look at the examples in the table listed below to get an idea of what I am talking about. http://www.postgresql.org/docs/9.0/static/functions-formatting.html Table 9-25 Yes, I see, thanks! I just talking about phrase fill mode (suppress padding blanks and zeroes) in the documentation should be rephrased to fill mode (suppress padding blanks). To get technical it means suppress unspecified padding O's. See below for example. Or I misunderstood what is padding zeroes without explicitly specification 0 pattern in the format format template... This combination from the example table shows that: to_char(-0.1, 'FM9.99') '-.1' to_char(0.1, '0.9') ' 0.1' The 0 in 0.1 is not strictly needed, so if you use FM it will be suppressed. Ahh, I guess I understand (thanks to you examples). Lets look at the test: dmitigr= SELECT ''||to_char(-0.1, 'FM9.99')||'' AS v; v --- -.1 dmitigr= SELECT ''||to_char(0.1, '0.9')||'' AS v; v 0.1 dmitigr= SELECT ''||to_char(0.1, 'FM0.9')||'' AS v; v --- 0.1 dmitigr= SELECT ''||to_char(0.1, '0.9')||'' AS v; v 0.1 dmitigr= SELECT ''||to_char(0.1, 'FM0.9')||'' AS v; v --- 0.1 So, padding zeroes suppressed by FM is a rest of the value. Thank you very much! -- Adrian Klaver adrian.kla...@gmail.com mailto:adrian.kla...@gmail.com -- // Dmitriy. -- Adrian Klaver adrian.kla...@gmail.com -- // Dmitriy.
Re: [GENERAL] Why count(*) doest use index?
2011/3/8 Merlin Moncure mmonc...@gmail.com On Mon, Mar 7, 2011 at 3:16 PM, Glenn Maynard gl...@zewt.org wrote: On Mon, Mar 7, 2011 at 1:13 PM, Merlin Moncure mmonc...@gmail.com wrote: On Sun, Mar 6, 2011 at 2:57 PM, Glenn Maynard gl...@zewt.org wrote: That's often perfectly fine, with read-heavy, single-writer workloads. I definitely wish there was a way to create indexes to track counters on various types of queries, even if it eliminates write concurrency on affected writes. Doing it by hand is a pain. beyond what the stats system does you mean? The stats system only helps for the most basic case--counting the number of rows in a table. In my experience that's not very common; most of the time it's counting total results from some more interesting query, eg. for pagination. In my particular case, I'm caching results for SELECT COUNT(*), expr2 FROM table WHERE expr GROUP BY expr2 (for a very limited set of expressions). SELECT COUNT(*) FROM table WHERE expr; will use index (assuming expr is optimizable and is worth while to optimize). Your case might be interesting for cache purposes if expr2 is expensive, but has nothing to do with postgres index usage via count(*). mysql/myisam needs to scan as well in this case -- it can't magically 'look up' the value as it can for the in filtered (very special) case... Exactly! it only differs from pg in that it can skip heap visibility check because all records are known good (and pg is moving towards optimizing this case in mostly read only workloads!) merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy.
