[HACKERS] psql \du and \dg commands.
Hello, I don't really know since when those commands are provided by psql, but I found them recently and was quite annoyed by the output given by both of them. Though I find that the \du command's output is quite accurate, I wonder why \dg gives the same informations ? I would have expected to see the group names in a left column, and the list of users that belongs to this group. I know I can get the information by fetching rows of pg_group system table, but I was just wondering about this issue and see what you here would think of it. Thanks for any suggestions about this behavior. signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Multiple SRF right after SELECT
Le mercredi 19 mars 2008, Albert Cervera i Areny a écrit : Hi ! A Dimecres 19 Març 2008, Nikolay Samokhvalov va escriure: 2. Why the query above provides 4 rows, not 2*4=8? Actually, that's interesting -- I can use this query to find l.c.m. But it's defenetely not that I'd expect before my try... 2*4 = 8: select * from generate_series(1, 2) a, generate_series(1, 4) b; If you launch the above query, you just get what you would get if you would do a select from two tables without joining them at all... So, you get the cartesian product of the two ensembles. Can't tell you about the expected behaviour in the query you provided though. I've made few tests with the primary query, and indeed it is strange behavoiour. Consider the following : select generate_series(1, 3), generate_series(1, 4); generate_series | generate_series -+- 1 | 1 2 | 2 3 | 3 1 | 4 2 | 1 3 | 2 1 | 3 2 | 4 3 | 1 1 | 2 2 | 3 3 | 4 which is not fully readeable but if you sort things, you get exactly the same as what you mentionned before : select generate_series(1, 3), generate_series(1, 4) order by 1,2; generate_series | generate_series -+- 1 | 1 1 | 2 1 | 3 1 | 4 2 | 1 2 | 2 2 | 3 2 | 4 3 | 1 3 | 2 3 | 3 3 | 4 So far it is clear, but if you just make things so that the the number of rows returned by one call to generate_series is a multiple of the other, the result is truncated : select generate_series(1, 3), generate_series(1, 6) order by 1,2; generate_series | generate_series -+- 1 | 1 1 | 4 2 | 2 2 | 5 3 | 3 3 | 6 provides the same strange result as initialy discovered, and select generate_series(1, 6), generate_series(1, 3) order by 2,1; generate_series | generate_series -+- 1 | 1 4 | 1 2 | 2 5 | 2 3 | 3 6 | 3 provides the same, mirrored. So, it could be a bug somewhere. Hoping that it will be of any help... Regards. -- Albert Cervera i Areny http://www.NaN-tic.com signature.asc Description: This is a digitally signed message part.
[HACKERS] Idea about sql command create table like
Hello, I've seen that postgreSQL tries to follow sql standards as far as possible, and that sometimes (often) brings it's own extentions. I've thought of one concerning the sql command create table like. As far as I've understood the documentation, one can create a table using another table as a template. Could it be possible then to add the possibility just to rename columns using this command. Here is an example to make my words a better meaning : create table some_lib ( some_code serial primary key, some_text text ); Now, I want a second table that has the same structure, I can do it two ways : create table any_lib ( any_code serial primary key, any_text text ); or create table any_lib like (some_lib including constraints); If I use the second option, the fields of any_lib will be named some_code and some_text, which is not what I want, so I will have to use alter table any_lib rename column some_code to any_code; alter table any_lib rename column some_text to any_text; Could it be possible to have a syntax that would allow to use create table like to rename fields as well, for example : create table any_lib like (some_lib including constraints) with names (any_code, any_text); Of course, this is just an example, and the tables schemas used are not big enough to make it needed in this case, it is just here to illustrate my meaning. If one or more fields have to keep the name of the original table, then one could use one keyword (maybe default) like this : create table any_lib like (some_lib including constraints) with names (default, any_text); This way, the first field would be named some_code... What do you think of this ? signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] pg_dump additional options for performance
Le mardi 26 février 2008, Tom Lane a écrit : In short, what I think we need here is just some more conveniently defined extraction filter switches than --schema-only and --data-only. There's no need for any fundamental change to pg_dump's architecture. Forgive me if what I will say bellow is completly pointless, but I think at this point that the base of this discussion might be wrong. If the decision made here is to keep pg_dump simple, then maybe that it could be left as it is, and create another tool just to extract some parts of a database, either schema or data. As far as I understand what is said here, pg_dump is thought to be a tool used to make a backup of a database to use it somewhere else. So let it be as it is. What I intendeed to mean in my first post, is that it would be great to have a tool that could let one get a partial dump of a database at one time, so as to modify (or not) and to alter the database afterward (or not). I use to work on many databases at a time, and sometime, I have to quickly fix a function, add a trigger to a table... Sometime I create a sql file and save my work before passing the command set to psql, but sometimes I don't have much time and type in the code directly in psql. So far so good, the code works, until a problem is found, and then, I don't have any source file to work on...unless I use pg_dump and search the so big file for the code I want to modify. I hope you see what I mean. Since the idea whas to dump informations about the structure of a table, function type or whatever object one could want from the base, I asked for options for pg_dump, but maybe a new tool (based on pg_dump ?) could satisfy everyone ? signature.asc Description: This is a digitally signed message part.
[HACKERS] One more option for pg_dump...
Hi all, On the 6th of february, there's been a thread about adding new options to pg_dump, but it is now too late for me to add comments to this thread, since all that was said wouldn't be readable at this time, so I add an new thread here. I haven't found any option to dump any user-defined function stored in a database, unless doing a pg_dump -D -s database, but so far one would get the definitions of the tables, the permissions, the triggers, and so on, so when you have a big schema, it is not much user friendly to do a full dump to change one or two lines of code in a function. Could there be an option to pg_dump (let's say --function [func_name]) to be abble to dump the complete source code of a function in a separate file, or on the terminal ? I've found that when in psql, one can type \df+ func_name to have many informations about the so named func_name, but it is not well readeable. Do you think it could be a good thing ? Are there workarounds to have the same comportement that I'm not aware of ? Thanks for your ideas about this. signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] One more option for pg_dump...
Le lundi 25 février 2008, Leonardo Cezar a écrit : Hi Leonardo, Thanks for your quick answer, I didn't know it was a TODO item, and that somepeople were working on it... Keep going, then, cause I'm really waiting for these features ! On Mon, Feb 25, 2008 at 10:48 AM, David BOURIAUD [EMAIL PROTECTED] wrote: Could there be an option to pg_dump (let's say --function [func_name]) to be abble to dump the complete source code of a function in a separate file, or on the terminal ? It's a TODO item. Just not to functions and so others (operators, casts,...) objects as well. I'm coding a fully functional prototype that solves these features. Just now I'm going think in a way to dump overloaded functions that seems me one more complicated issue. Do you think it could be a good thing ? Yep! Hence it's a todo item :-) Are there workarounds to have the same comportement that I'm not aware of ? Alot of lines sed+awk+pg_dump scripts .. Nay, I use vim with two buffers, search for the code I want and copy-paste, but reconn that it's not very user friendly ! Thanks again, I'll try to wait for the improvements in the next version of postgres ! -Leo signature.asc Description: This is a digitally signed message part.
[HACKERS] Feature request concerning postmaster log file.
Hi to all, Could it be possible to have informations about who do what on any tablespace or database logged in separate files, as samba does. I don't think that it should be the default way, since the way events are logged by now seems to please all users, but I'd think that a run-time option could do the stuff. The thing I have in mind is the following : if the run-time option is chosen, any message issued by any command, from connexion to all sql commands launched in any way by a user should go in a separate log file, that could be named log.username for example. Every message provided by the backend could go in the main logfile, as it does by now. One could even imagine that every back-end program could have it's own logfile (I think of autovac, bgwriter and so on). This is the way samba daemon can be tuned, and I find it very usefull, when one have to track an error found by one user. Let me know if you find this idea usefull. Thanks. signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Feature request concerning postmaster log file.
Le vendredi 9 novembre 2007, vous avez écrit : David BOURIAUD wrote: if the run-time option is chosen, any message issued by any command, from connexion to all sql commands launched in any way by a user should go in a separate log file, that could be named log.username for example. You can enable logging user name into postgres log and grep or some other text utility help you to generate required log file. Thanks Zdenek for your answer. I know what you answered me, but in certain cases, using a text utility like grep is not suitable. I think of this when you get a many hundreds of Mo logfile at least, or when you need to track commands made by one peculiar user in real-time. That's why I thought that having a possibility to specify how log file(s) should be handled by postmaster could be of great help. I suggested an option to have one file per user, but one could think of having one file per IP address connected to the server, and so on. The way samba log files can be handled could be a good starting point, but I must admit that I don't have the programming skills required to code any patch... I hope that this answer will clarify my point of view. signature.asc Description: This is a digitally signed message part.
