Re: [GENERAL] quick survey on schema less database usage
rr04 wrote: I am an MIT student doing a project on schema-less database usage and would greatly appreciate if you guys can fill out a quick survey on this (should take 5 mins) http://bit.ly/nosqldb Hi, even when I think that most of the people on this list will answer the same way to your survey, I would be really interested in the results. Can you post a link? Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Video available for PGDay SJC '09
David Fetter schrieb: Just a quick question. Wouldn't it be better to provide the videos in flv format in a player in this page. Since I have a MAC, I have no problems viewing the videos. But with my Linux box and FF 3.5 I can't. You can use xine on your Linux box :) Cheers, David. David, thanks for the tip ;-) CHeers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Video available for PGDay SJC '09
Christophe Pettus schrieb: Greetings, The video recordings of the sessions for PG Day SJC '09 are now available: [snip] Many thanks to Steve Crawford for the audio system and assistance with setup, tear-down, and coordination during the event. -- -- Christophe Pettus x...@thebuild.com Hi Christophe, thanks - that's awesome! Just a quick question. Wouldn't it be better to provide the videos in flv format in a player in this page. Since I have a MAC, I have no problems viewing the videos. But with my Linux box and FF 3.5 I can't. I can imagine that some people have problems with .mov files and (more importantly), mov files are really big - so a lot of traffic is going over the wire. I could assist creating a page with a player (OS stuff) and converting the files to flv if this is a desired way to provide the videos. Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Video available for PGDay SJC '09
Bill Moran schrieb: While I've no objection to someone helping out by converting files, I find it odd that flv is suggested. I've yet to find anything that can play flv files on my FreeBSD desktop machine. I'm pretty sure mplayer can play mov files ... I guess I'll find out this evening when I take time to watch them. You will not need to watch them on your desktop. If your browser supports flash, then you simply watch them with your browser. I think flash (flv, swf) is more supported by the browser than mov - as long as you don't download the videos. For sure mplayer can play them. But why download them at all? ;-) Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Video available for PGDay SJC '09
Greg Stark schrieb: On Tue, Jul 28, 2009 at 1:41 PM, Andreas Wenka.w...@netzmeister-st-pauli.de wrote: Why not use a standardized openly documented container format like mpeg4 with mpeg4 part 2 compression instead of one controlled by a single company like flv or quicktime? That would let people play it using any of various open source codecs which can play mpeg4 part 2. another good approach. I understand the point of view. But this menas to download the files anyway because playing mpeg files in the browser is the same as with mov files - I think. All in all my suggestion to convert the videos to flv and let people see the videos in a flash player was based on the assumption, that - the files are really smaller so less traffic - flash video is the most supported format for watching *in the browser* - it's easy to convert the existing videos to flv - no need to download the videos But now I see that there are different opinions if this approach is a good idea. Im a still confident, that it's not evil to use the format which is best known for the mainstream ... and maybe this point is what I have to see in a different light. Maybe the group of people watching the videos are not mainstream ;-) Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Moving from Windows to Ubuntu - Have a couple of questions
Jennifer Trey schrieb: I am in the process of switching to Ubuntu, and I have a couple of question to get started. I installed pgadmin and postgre through the Synaptic Package Manager. I am unfamiliar with the console so I prefer to use pgAdmin to set things up. congratulations for switching ;-). You should start to get familar with the console because a lot of things are much easier using the console. Especially configuring the PostgreSQL and using psql (the command line interface) Opening pgAdmin I see Servers(0) .. I am trying to add a server and filled out things to match the windows properties but can't get it to work. On the Service part, windows says postgresql-8.3 but on Ubuntu it is empty. I tried to put the same thing there as well, but also with no success. I am guessing I need to do something before that.. the ideas is that I will create a DB later and use my windows backup and restore. You have to edit two files at least: /etc/postgresql/8.3/main/pg_hba.conf This ist the file for setting the authentication for your cluster. Please refer to this part of the documentation: http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html And probably you also have to edit: /etc/postgresql/8.3/main/postgresql.conf Please read the documentation here: http://www.postgresql.org/docs/8.3/interactive/runtime-config-connection.html Most important is listen_adresses ... hm, I think the defaults should be ok to get started. Basically you should be able to connect to the server by setting this in pg_hba.conf: # TYPE DATABASEUSERCIDR-ADDRESS METHOD local all all trust But take care - this should only be set for testing purpose. Is the server running? Type this in the command line: $ /etc/init.d/postgresql-8.3 status If you get something like this 8.3 main 5432 online postgres /var/lib/postgresql/8.3/main \ /var/log/postgresql/postgresql-8.3-main.log PG is running ... Hope that helps for the start Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Video available for PGDay SJC '09
Christophe Pettus schrieb: Historically, MOV has been the least-bad container format; Flash support on anything besides Windows has, traditionally, been very spotty. The files themselves are pretty much the same size; FLV is (as noted) a container format, not a codec, and the video is H.264 either way. I never assumed that flv is a codec ... the size for sure depends on the quality (and resolution). So if you want to have the same high quality as having with mov, then I believe it's the same size. But that's not the intention of using flv. And, of course, you do have to download the video either way; it just starts playing faster if you are using a Flash viewer. Requiring a download first does have the advantage that it keeps the bandwidth off of media.postgresql.org down, since the video only has to be downloaded to your desktop once, rather than each time you watch it. (I'm not sure how much of a real issue this is, however.) agreed. This is the fact, if the video will have the same size in flv also. Then there is concerning the bandwith no difference ... but I would definitely size it down in data size. But hey - this is just the way I would do it. I am not saying that having mov files a bad thing. Really not! ;-) The right answer is to move to using the video tag, now that more browsers are supporting it. For the next event, I'll encourage providing a video-based viewer (since I don't control the HTML on postgresql.org, I can't make any grand promises). uh - HTML 5 is supported by the browser when? Firefox 3.5 does - yeah. But this would be no option for me in the next two years or so ... believe it or not - there are still s many people using IE6 (what brings a lot of headaches to me for layout and javascript coding by the way!) My recapitulation of this discussion is to leave it like it is because it's best suitable in more concerns for most of the people. In the future it is the best idea to use the HTML 5 video/video tags. Sounds good to me ;-) Thanks for taking the time to discuss that with me. Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Video available for PGDay SJC '09
Christophe Pettus schrieb: On Jul 28, 2009, at 8:35 AM, Andreas Wenk wrote: uh - HTML 5 is supported by the browser when? Currently, the video tag is supported by Firefox 3.5, Safari 3 and 4, Chrome 3 (in alpha, I believe), iPhone 3. That being said, we can do a fallback to Quicktime, then to a Flash client, all the way back to IE6. A nice example of fallback code is available at: http://camendesign.com/code/video_for_everybody thanks for the link! I wil check it out asap. Now, Joshua Drake has often uploaded these videos to Vimeo, thus giving everyone an in-browser channel as well. The fallback idea sounds great ;-) If you need help at any time just get in touch with me ... Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable databse listing for non-superuser (\l) ?
Bill Moran schrieb: Scott Marlowe scott.marl...@gmail.com wrote: On Fri, Jul 24, 2009 at 5:02 PM, Brian A. Sekleckilaval...@spiritual-machines.org wrote: All: Any suggestions on how-to, or comments on a potential NFR, to disable non-superuser's from viewing the database list via \l? So, is this a misguided attempt at security through obscurity, or are you looking at limiting the noise that users see when they look at databases? I don't know about misguided, Scott. Security takes many forms. If a client wants shared database hosting, but wants an assurance that other clients using the same shared DB server can't tell who else is using it? It's not security in the strict computer-science definition. Obviously, if the proper ownerships and grants don't exist to protect the data, in addition to said obscurity, then the whole thing is pointless. But such obscurity _in_addition_ to proper, real security, has show usefulness in many areas. Take a properly secured SSH server, for example, and move it to an obscure port #. Now you've reduced the number of mindless bots looking for unprotected root accounts, and your IDS solution that monitors the ssh logs is actually useful. Of course, that's only effective if ssh is properly secured to begin with. Similar concept. Many clients want the cost-effectiveness of shared DB hosting. Many of them also want it kept under wraps that they're doing so. The provider that can do such a thing gets the contract. Those that complain about it's not security, it's obscurity do not get the contract. I mean, didn't Apple just kill someone for letting their new iPhone design leak? this is now going off topic - but what do you mean with your last sentence? Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] split string by special characters
Jan-Erik schrieb:
On 24 Juli, 23:22, a.w...@netzmeister-st-pauli.de (Andreas Wenk)
wrote:
Hi,
I was thinking about that and in my opinion the approach to let the
database do that is the wrong direction. Sure you can do a lot with
regexp_split_to_table or regexp_split_to_array but they are kind of
Yes, I see. You're quite right, the split was intended to do give me
everything in processed chunks it in some easy way as the last part of
the interpretation of the text.
limited compared to a programming language using regular expressions. If
I had to try to get your jobdone, I would try regexp_matches() like:
SELECT regexp_matches('This is just a text, that contain special
characters such as, (comma),(, ) (left and right parenthesis) as
well as ? question, mark.How do I split it up with PostgreSQL?',
E'(\\w*.)\\s+','g');
regexp_matches
{This}
{is}
{just}
{a}
{text,}
{that}
{contain}
{special}
{characters}
{such}
{as,}
{,}
{\}
{left}
{and}
{right}
{parenthesis)}
{as}
{well}
{as}
{\}
{question,}
{How}
{do}
{I}
{split}
{it}
{up}
{with}
(29 rows)
So, you have the ability to catch the seperators like ','. But for now,
teh example just catches the comma. But you want to catch a lot of other
Yes, but then I ran into the problems with separators that regexp
consider as part of the expression and how to dynamically build the
right expression in some unified way for each language.
seperators as well. I suggest you do that within the logic of your
coding language because I don't think this will be an easy way to walk
Guess you're right, because I didn't know how to handle it with the
regexp-approach.
I sat down yesterday and wrote a function that does the job for me in
PL/pgSQL, I'm not quite finished, but can see the light at the end of
the tunnel.
just in case you are running into a black performance hole - you could
try to write it in C as a user defined function. Actually for me it
would be a real big challenge ;-)
The basic approach I'm working with now is to let it find the position
of each delimiter combination within the text, then sort the resulting
array to get it ordered and extract each part.
It won't be fast as lightning, but sufficient for now and as it seem,
allow me to parse text from various files written in different
languages (e.g. programming) just by specifying the delimiters.
;-). This is no database job in my opinion.
I didn't intend to try it either before I spotted some of those
functions... :-)
Then figured it would be nice to do it within the db-engine as all the
data is present there.
I wrote code outside the db-engine some time ago, but then other
aspects made it less desirable to use.
Cheers
Andy
Thank you Andy for the code example and your advice.
I really appreciate that you took your time to show me how and explain
why.
//Jan-Erik
hey you're welcome. When you're done it would be really great to see the
resulting function ;-)
Cheers
Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable databse listing for non-superuser (\l) ?
Greg Stark schrieb: On Sat, Jul 25, 2009 at 2:53 PM, Andreas Wenka.w...@netzmeister-st-pauli.de wrote: I mean, didn't Apple just kill someone for letting their new iPhone design leak? this is now going off topic - but what do you mean with your last sentence? Please don't quote an entire message if you're only responding to part of it. (That's what people are really talking about when they say top-posting is bad.) see: http://www.theregister.co.uk/2009/07/22/security_offical_suspended/ I don't think there's any actual indication that Apple was involved directly. no problem and got it - but my reply was kind of a quick shot because I don't think that posting messages /sentences like that are really useful or helpful for the community ... Bill, don't get me wrong - this is my personal opinion. Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] user/grant - best practices handling permission in production system
Stefano Nichele schrieb: Hi All, I have some questions for you about the best way to handle permission on a database in a production system. The final goal is to have a web application connected to the db using a single user that must run select/delete/insert/update (and maybe truncate) In my opinion that user should NOT own the db and the db itself should NOT be created using that user. Of course that user should NOT be able to create database or other users. The steps could be: 1. using postgres user (or another user with grant for creating database) create the database 2. using the user used in step 1, create the schema and populate tables with initial data 3. using the user used in the previous step, create a new user (the one the webapp will use) 4. give to the new user the grant on all database objects for select/delete/insert/update I totally agree with Greg's answer but just want to give a hint for granting privileges to several objects in one shot as in step 4. pgAdmin III is giving this ability with the grant wizard ... this may help if you don't want to put all the steps in a init script for automatic db setup. Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] split string by special characters
Jan-Erik wrote:
I wonder if you could please help me out to extract a character string
to an array or better yet, a table.
I'd like to split strings of text up into words and delimiters (but
not delete the delimiters). The delimiters are defined as comma,
space, dot, singe/double quotation mark, question mark etc.¹ in a
separate table (delimiters) depending on what rules apply for the
input.
regexp_split_to_array/table seem quite suitable but I have
difficulties to form the right expression with it, apart from that it
remove the delimiters as well.
Example:
This is just a text that contain special characters such as , (comma),
(, ) (left and right parenthesis) as well as ? question mark.
How do I split it up with PostgreSQL?
Expected result:
{This, , is, , just, , a, ..., PostgreSQL, ? }
__
¹) Also later on tags such as html and at other times something
else depending on the circumstances.
//Jan-Erik
Hi,
I was thinking about that and in my opinion the approach to let the
database do that is the wrong direction. Sure you can do a lot with
regexp_split_to_table or regexp_split_to_array but they are kind of
limited compared to a programming language using regular expressions. If
I had to try to get your jobdone, I would try regexp_matches() like:
SELECT regexp_matches('This is just a text, that contain special
characters such as, (comma),(, ) (left and right parenthesis) as
well as ? question, mark.How do I split it up with PostgreSQL?',
E'(\\w*.)\\s+','g');
regexp_matches
{This}
{is}
{just}
{a}
{text,}
{that}
{contain}
{special}
{characters}
{such}
{as,}
{,}
{\}
{left}
{and}
{right}
{parenthesis)}
{as}
{well}
{as}
{\}
{question,}
{How}
{do}
{I}
{split}
{it}
{up}
{with}
(29 rows)
So, you have the ability to catch the seperators like ','. But for now,
teh example just catches the comma. But you want to catch a lot of other
seperators as well. I suggest you do that within the logic of your
coding language because I don't think this will be an easy way to walk
;-). This is no database job in my opinion.
Cheers
Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] comparing NEW and OLD (any good this way?)
Pavel Stehule schrieb: Hello http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body regards Pavel Stehule Pavel, this trick-list is awesome ;-) Thanks for the tip! Cheers Andy P.S.: a link to that would be nice ;-) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql \du [PATCH] extended \du with [+] - was missing
Alvaro Herrera wrote: Andreas Wenk wrote: here is the patch for help.c . I think updating some translations is not neccessary because there is no change. Am I right? Not really. For example the spanish translation file has this: msgid \\du [PATTERN] list roles (users)\n msgstr \\du [PATRÓN] listar roles (usuarios)\n and it needs to read instead: msgid \\du[+] [PATTERN] list roles (users)\n msgstr \\du[+] [PATRÓN] listar roles (usuarios)\n first I want to mention, that I moved this to hackers list. And actually I realized that I have to make more changes. I have to change also the documentation. And the same change has to be made with \dg ... After your reply I understand now what Peter meant with changing the translation files also. I didn't have a look to them and did not know, that \du and \dg is also written there - my fault. I will provide a patch tomorrow. Thanks for everybody's patience with me - I am learning ... ;-) Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql \du [PATCH] extended \du with [+] - was missing
Alvaro Herrera wrote: Tom Lane wrote: We've never before expected patch submitters to patch the .po files, and in fact I would have thought it would be useless to do so. The masters are not in our CVS. Why is Andreas being told to worry about this? I must admit I don't know :-) hm - I don't wanna make it more complicate as it is ... Peter wrote as the first answer to this post to do so - so actually I could do it (I had a short look to the po files two minutes ago - /src/bin/psql/po/). I leave the desicion up to ;-) Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can LIKE under utf8 use INDEXes?
Robert James wrote: Thank you, Tom. I guess I'm a bit confused about things here. How can I find the locale of my database? (I wasn't able to find this in the docs). If I do have the locale set to 'C', do I loose anything by using utf8 for all text fields? use psql: postgres=# \l+ List of databases Name | Owner | Encoding | Collation |Ctype| +---+--+-+-+ postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | output shortend ;-) Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can LIKE under utf8 use INDEXes?
Robert James wrote: Thanks - I don't show any locale: rbt_development= \l List of databases Name | Owner | Encoding +-+-- rbt_development | rbt | UTF8 ... ahm - you are running pg 8.2. There I think the + option is not available (\l+). So if you use a debian based system and installed it via the package manager apt or aptitude you could give this a try: /usr/lib/postgresql/8.2/bin/pg_controldata \ /var/lib/postgresql/8.2/main/ |grep LC This should output something like: LC_COLLATE:de_DE.UTF-8 LC_CTYPE: de_DE.UTF-8 I hope this helps a little ... Cheers Andy P.S.: top posting is ugly ;-) On Wed, Jul 22, 2009 at 6:45 PM, Andreas Wenk a.w...@netzmeister-st-pauli.de mailto:a.w...@netzmeister-st-pauli.de wrote: Robert James wrote: Thank you, Tom. I guess I'm a bit confused about things here. How can I find the locale of my database? (I wasn't able to find this in the docs). If I do have the locale set to 'C', do I loose anything by using utf8 for all text fields? use psql: postgres=# \l+ List of databases Name | Owner | Encoding | Collation |Ctype| +---+--+-+-+ postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | output shortend ;-) Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can LIKE under utf8 use INDEXes?
Andreas Wenk wrote: Robert James wrote: Thanks - I don't show any locale: rbt_development= \l List of databases Name | Owner | Encoding +-+-- rbt_development | rbt | UTF8 ... ahm - you are running pg 8.2. There I think the + option is not available (\l+). So if you use a debian based system and installed it via the package manager apt or aptitude you could give this a try: /usr/lib/postgresql/8.2/bin/pg_controldata \ /var/lib/postgresql/8.2/main/ |grep LC This should output something like: LC_COLLATE:de_DE.UTF-8 LC_CTYPE: de_DE.UTF-8 I hope this helps a little ... Cheers Andy P.S.: top posting is ugly ;-) more correct: \l+ is also available in 8.2 but the output in 8.4 is extended ... Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full text search in PostgreSQL 8.4
Hello,
I recently upgraded to version 8.4 and now full text search with russian
configuration is not working:
template1=# create database test encoding='win1251';
test=# create table test (test varchar(255));
test=# insert into test values ('тест');
test=# select * from test where to_tsvector('russian', test) @@
to_tsquery('russian', 'тест');
In 8.3 version I have result:
test
--
тест
(1 запись)
In 8.4 I have this notice and 0 rows with any table values:
NOTICE: text-search query contains only stop words or doesn't contain
lexemes, ignored
test
--
(0 rows)
Why it may not working in 8.4 version?
Thanks
Hi Konstantin,
I ran your tests with 8.3 and 8.4. I have the expected result:
postgres=# \c test
psql (8.4.0)
You are now connected to database test.
test=# create table test (test varchar(255));
CREATE TABLE
test=# insert into test values ('тест');
INSERT 0 1
test=# select * from test where to_tsvector('russian', test) @@
to_tsquery('russian', 'тест');
test
--
тест
(1 row)
I have a clean installation - means the dictionarys are not edited. After insterting тест
into the russian.stop file, I can reproduce your case:
test=# select * from test where to_tsvector('russian', test) @@
to_tsquery('russian', 'тест');
NOTICE: text-search query contains only stop words or doesn't contain lexemes,
ignored
test
--
(0 rows)
If you would have given the column test the data type tsvector, probably no value would
have been inserted. Just try it with another column and see if you can insert тест into
that column.
This is just an idea ...
Cheers Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql \du [PATCH] extended \du with [+] - was missing
Peter Eisentraut schrieb: On Thursday 16 July 2009 02:12:05 ANdreas Wenk wrote: Hi, I recognized in psql using the internal help (\?) that the *+* sign is missing for the shortcut \du: # \du List of roles Role name | Attributes | Member of --+--+--- # \du+ List of roles Role name | Attributes | Member of | Description --+--+---+- Where shall I place this info? Is this also a bug? Looks like the help is missing this info. If you could provide a patch that also fixes up the translations, that would be most helpful. Hi, here is the patch for help.c . I think updating some translations is not neccessary because there is no change. Am I right? --- src/bin/psql/help.c |2 +- 1 files changed, 1 insertions(+), 1 deletions(-) diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index 5f13b8a..8a541e6 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -219,7 +219,7 @@ slashUsage(unsigned short int pager) fprintf(output, _( \\ds[S+] [PATTERN] list sequences\n)); fprintf(output, _( \\dt[S+] [PATTERN] list tables\n)); fprintf(output, _( \\dT[S+] [PATTERN] list data types\n)); - fprintf(output, _( \\du [PATTERN] list roles (users)\n)); + fprintf(output, _( \\du[+] [PATTERN] list roles (users)\n)); fprintf(output, _( \\dv[S+] [PATTERN] list views\n)); fprintf(output, _( \\l[+] list all databases\n)); fprintf(output, _( \\z [PATTERN] same as \\dp\n)); -- 1.6.0.4 0001-extended-du-with-was-missing.patch (END) Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql \du
Peter Eisentraut schrieb: On Thursday 16 July 2009 02:12:05 ANdreas Wenk wrote: Hi, I recognized in psql using the internal help (\?) that the *+* sign is missing for the shortcut \du: # \du List of roles Role name | Attributes | Member of --+--+--- # \du+ List of roles Role name | Attributes | Member of | Description --+--+---+- Where shall I place this info? Is this also a bug? Looks like the help is missing this info. If you could provide a patch that also fixes up the translations, that would be most helpful. Moin, will be the first time for me but sure, I will do it ;-) Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] best practice transitioning from one datatype to another
Arndt Lehmann schrieb: On Jul 16, 8:05 am, t...@sss.pgh.pa.us (Tom Lane) wrote: CG cgg...@yahoo.com writes: While transitioning from 8.1 to 8.4, I need to transition to the internal UUID type in place of the contrib/uniqueidentifier module. I've built the database around uniqueidentifier, so nearly every table has one column of that data type. It's going to be tedious to ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE uuid; ...repeat 600 times... I'll also have to drop and reload the views and the rules on tables. It'll be tedious even if the tables have no data in them. Can anyone recommend a better/faster way to make the transition? Couldn't you rename the type to uuid in the 8.1 database before you dump? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Just an idea - don't know if it works, first try on a test server: 1. Dump the complete database into text format (pg_dump --format=t) 2. Do a search and replace from uniqueidentifier to uuid 3. Reimport Best Regards, Arndt Lehmann uh - --format=t means tar format. --format=p means plain text ... Or am I missing something? $pg_dump --help Usage: pg_dump [OPTION]... [DBNAME] General options: ... -F, --format=c|t|p output file format (custom, tar, plain text) ... Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function does not exist
Pavel Stehule schrieb: 2009/7/16 dipesh mistry (Imap) dipesh.mis...@mobilefundas.com: Hello, In my function i had defined addnewuser(integer,character,..), and i call this function by Java code. I had created function with integer datatype but database always gives me error, org.postgresql.util.PSQLException: ERROR: function addnewuser(bigint,character varying,) does not exist Why database gives me bigint error instead i had declare integer in function. Next i create one more function named adduser(bigint,character,). but then it gives me same error. are all others parameters really varchar? You can use explicit cast to varchar like SELECT addnewuser(19, ''::varchar, 'a'::varchar, Pavel, just a question. Why should it be necessary to add explicit typcasting here? I can't see the problem in more depth. I still think that the function is called with the wrong parameter. Would be cool to hear your points to understand the problem better ;-) Another idea to be sure that the function is working correctly is to call the function in psql and see if an error is thrown ... if yes its a paramter problem. If no its an external problem (I think this is called by a Java app ... isn't it?). Cheers Andy regards Pavel Stehule -- Thanks, Dipesh If you are not confident, you are doing a trial run. Pavel Stehule wrote: Hello it's look like problem with casting. You defined function name(integer,...) but you call it with bigint param. Bigint cannot be casted to int - so you have to redefine your func - name(bigint, ... regards Pavel Stehule 2009/7/15 dipesh mistry (Imap) dipesh.mis...@mobilefundas.com: Hello, I have create my own function name addnewuser(integer,varchar.), and when I call this function it gives me below error, org.postgresql.util.PSQLException: ERROR: function addnewuser(bigint, character varying, character varying, character varying, character varying, character varying, character varying, unknown, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, integer) does not exist Even though function exist why it gives me this error, we use postgres-8.3.7 latest. We install postgres by .tar,gz file. -- Thanks, Dipesh If you can't make a mistake, you can't make anything. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function does not exist
Pavel Stehule schrieb: 2009/7/16 Andreas Wenk a.w...@netzmeister-st-pauli.de: Pavel Stehule schrieb: 2009/7/16 dipesh mistry (Imap) dipesh.mis...@mobilefundas.com: Hello, In my function i had defined addnewuser(integer,character,..), and i call this function by Java code. I had created function with integer datatype but database always gives me error, org.postgresql.util.PSQLException: ERROR: function addnewuser(bigint,character varying,) does not exist Why database gives me bigint error instead i had declare integer in function. Next i create one more function named adduser(bigint,character,). but then it gives me same error. are all others parameters really varchar? You can use explicit cast to varchar like SELECT addnewuser(19, ''::varchar, 'a'::varchar, Pavel, just a question. Why should it be necessary to add explicit typcasting here? I can't see the problem in more depth. I still think that the function is called with the wrong parameter. Would be cool to hear your points to understand the problem better ;-) ofcourse - explicit casting is hard method, and it's better don't use it. But some times java environments are too smart. Explicit cast should help with searching an problematic param. Pavel Thanks Pavel, then I suggest to first get the function running in psql and then check it in the Java environment ... if this has not be done allready ;-) Cheers Andy Another idea to be sure that the function is working correctly is to call the function in psql and see if an error is thrown ... if yes its a paramter problem. If no its an external problem (I think this is called by a Java app ... isn't it?). Cheers Andy regards Pavel Stehule -- Thanks, Dipesh If you are not confident, you are doing a trial run. Pavel Stehule wrote: Hello it's look like problem with casting. You defined function name(integer,...) but you call it with bigint param. Bigint cannot be casted to int - so you have to redefine your func - name(bigint, ... regards Pavel Stehule 2009/7/15 dipesh mistry (Imap) dipesh.mis...@mobilefundas.com: Hello, I have create my own function name addnewuser(integer,varchar.), and when I call this function it gives me below error, org.postgresql.util.PSQLException: ERROR: function addnewuser(bigint, character varying, character varying, character varying, character varying, character varying, character varying, unknown, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, integer) does not exist Even though function exist why it gives me this error, we use postgres-8.3.7 latest. We install postgres by .tar,gz file. -- Thanks, Dipesh If you can't make a mistake, you can't make anything. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function does not exist
dipesh mistry (Imap) wrote: Hello, I have create my own function name *addnewuser(integer,varchar.)*, and when I call this function it gives me below error, org.postgresql.util.PSQLException: ERROR: function addnewuser(bigint, character varying, character varying, character varying, character varying, character varying, character varying, unknown, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, integer) does not exist Even though function exist why it gives me this error, we use postgres-8.3.7 latest. We install postgres by .tar,gz file. -- Thanks, Dipesh If you can't make a mistake, you can't make anything. Hi, I suppose you are setting the wrong parameter when calling this function. If you use psql use the short cut command \df to get all functions you built and check this again ... such an error message is mostly a hint that something with the params is wrong ... Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] psql \du
Hi, I recognized in psql using the internal help (\?) that the *+* sign is missing for the shortcut \du: # \du List of roles Role name | Attributes | Member of --+--+--- # \du+ List of roles Role name | Attributes | Member of | Description --+--+---+- Where shall I place this info? Is this also a bug? Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Password?
Ms swati chande schrieb: Hi When I write the following commands at the prompt, createuser -S -d -R user1 createdb sample I am asked to enter a password. I have not set any password anywhere. Which password is it asking for? Please help. I have built from source on Windows XP. Thanks is advance, Regards Swati Hi Swati, what are the setting of your pg_hba.conf? I assume that there is a entry like this: # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all password That means that the password you are asked is the password of the standard user for your cluster - commonly postgres. Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Password?
Andreas Wenk schrieb: Ms swati chande schrieb: Hi When I write the following commands at the prompt, createuser -S -d -R user1 createdb sample I am asked to enter a password. I have not set any password anywhere. Which password is it asking for? Please help. I have built from source on Windows XP. Thanks is advance, Regards Swati Hi Swati, what are the setting of your pg_hba.conf? I assume that there is a entry like this: # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all password That means that the password you are asked is the password of the standard user for your cluster - commonly postgres. Cheers Andy *argh* - more detailed to avoid confusion. The auth method 'password' in pg_hba.conf means, that you will be asked for a password for the user you try to create a db with. If no user is given (with createdb -U [username]), this user is postgres ... see also createdb --help for options ... Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[Re: [GENERAL] Password?]
Serge Fonville schrieb: *argh* - more detailed to avoid confusion. The auth method 'password' in pg_hba.conf means, that you will be asked for a password for the user you try to create a db with. If no user is given (with createdb -U [username]), this user is postgres ... Wasn't it that it uses the currently logged on user is used if no user is specified? correct - so this will be postgres because other users are not allowed to use these programs ... /var/lib/postgresql/8.4/bin$ ./createdb test -p 5433 createdb: could not connect to database postgres: FATAL: role duke does not exist $ sudo su postgres postg...@duke-linux:~/8.4/bin$ ./createdb test -p 5433 postg...@duke-linux:~/8.4/bin$ auth method in pg_hba.conf is trust in this case. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Password?
Ms swati chande schrieb: --- On *Wed, 7/8/09, Andreas Wenk /a.w...@netzmeister-st-pauli.de/* wrote: From: Andreas Wenk a.w...@netzmeister-st-pauli.de Subject: Re: [GENERAL] Password? To: Ms swati chande swat...@yahoo.com, PG-General Mailing List pgsql-general@postgresql.org Date: Wednesday, July 8, 2009, 3:47 PM Ms swati chande schrieb: Thanks Andy, I am working on Windows XP. Have built from source using Visual Studio 2005. I have made a change in pg_hba.conf to include the ipconfig of my system. # TYPE DATABASEUSERCIDR-ADDRESS METHOD *hostall all my ipconfig trust* # IPv4 local connections: hostall all 127.0.0.1/32 trust # IPv6 local connections: #hostall all ::1/128 trust This was to take care of the following problem: LOG: could not bind IPv4 socket: Address already in use HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for * FATAL: could not create any TCP/IP sockets For this I changed the listen_addresses to my current ip. and made the same change in pg_hba.conf. Thanks Regards Swati So does it work now ? Why is there a * sign before host? This seems to be incorrect ... P.S.: dont' forget to reply also to the mailinglist (reply to all) No its still not working. The * doesn't exist in pg_hba. It was probably in the mail as I had formatted that line to be 'bold'. ah ok .. Actually it should work if you set listen_addresses to '*' in postgresql.conf. Did you change anything else in postgresql.conf or pg_hba.conf? I am not too experienced with Windows so maybe someone with more knowledge is able to find the trick (I installed 8.4 once with the one click installer ...no problems at all). But as far as I understand something is wrong with: WARNING: could not create listen socket for * FATAL: could not create any TCP/IP sockets I understand correct, that you fixed this? Then it should work as I mentioned earlier ... Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Password?
Ms swati chande schrieb: Thanks Andy, I am working on Windows XP. Have built from source using Visual Studio 2005. I have made a change in pg_hba.conf to include the ipconfig of my system. # TYPE DATABASEUSERCIDR-ADDRESS METHOD *hostall all my ipconfig trust* # IPv4 local connections: hostall all 127.0.0.1/32 trust # IPv6 local connections: #hostall all ::1/128 trust This was to take care of the following problem: LOG: could not bind IPv4 socket: Address already in use HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for * FATAL: could not create any TCP/IP sockets For this I changed the listen_addresses to my current ip. and made the same change in pg_hba.conf. Thanks Regards Swati So does it work now ? Why is there a * sign before host? This seems to be incorrect ... P.S.: dont' forget to reply also to the mailinglist (reply to all) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Re: Password?]
Jasen Betts schrieb: On 2009-07-08, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: Serge Fonville schrieb: *argh* - more detailed to avoid confusion. The auth method 'password' in pg_hba.conf means, that you will be asked for a password for the user you try to create a db with. If no user is given (with createdb -U [username]), this user is postgres ... Wasn't it that it uses the currently logged on user is used if no user is specified? correct - so this will be postgres because other users are not allowed to use these programs ... /var/lib/postgresql/8.4/bin$ ./createdb test -p 5433 createdb: could not connect to database postgres: FATAL: role duke does not exist $ sudo su postgres postg...@duke-linux:~/8.4/bin$ ./createdb test -p 5433 postg...@duke-linux:~/8.4/bin$ auth method in pg_hba.conf is trust in this case. if it's trust any user can do ~postgres/8.4/bin/createdb -U postgres -p 5433 test nope! what you did is calling createdb as system user postgres (I believe because of the ~ sign at the beginning) *and* giving the option -U postgres. That works for sure and you even don't need -U postgres since you are allready postgres. But leave -U postgres away as a system user not equal to postgres ... see my example above. Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Re: Password?]
Steve Atkins schrieb: On Jul 8, 2009, at 6:19 AM, Andreas Wenk wrote: Jasen Betts schrieb: On 2009-07-08, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: Serge Fonville schrieb: *argh* - more detailed to avoid confusion. The auth method 'password' in pg_hba.conf means, that you will be asked for a password for the user you try to create a db with. If no user is given (with createdb -U [username]), this user is postgres ... Wasn't it that it uses the currently logged on user is used if no user is specified? correct - so this will be postgres because other users are not allowed to use these programs ... That's not true. you are right!Sorry for that mistake. /var/lib/postgresql/8.4/bin$ ./createdb test -p 5433 createdb: could not connect to database postgres: FATAL: role duke does not exist $ sudo su postgres postg...@duke-linux:~/8.4/bin$ ./createdb test -p 5433 postg...@duke-linux:~/8.4/bin$ auth method in pg_hba.conf is trust in this case. if it's trust any user can do ~postgres/8.4/bin/createdb -U postgres -p 5433 test nope! what you did is calling createdb as system user postgres (I believe because of the ~ sign at the beginning) *and* giving the option -U postgres. That works for sure and you even don't need -U postgres since you are allready postgres. But leave -U postgres away as a system user not equal to postgres ... see my example above. Nor is that. Why not? I think it is but maybe I did not write it understandable enough. See my example. Most of the postgresql client tools, including createdb, can be used by any operating system user to connect to the database as any database user. If they are called with -U foo then they will attempt to connect to the database as database user foo. If they are not called with -U then they will usually attempt to connect to the database as the current operating system user (though that can be overridden with the PGUSER or PGSERVICE environment variables). So if I'm logged in as steve and I do createdb test then I will try and connect to the database as database user steve and create the test database. If I do createdb -U postgres test I will try to connect to the database as database user postgres and create the test database. this is exactly my example. I am the system user duke but there is no role duke in the db. The result is the error message. Using -U postgres is successful. But in the case shown by Swati she is allready the system user postgres. So there's no need to put the option -U postgres. Correct? See my example. Whether I'm prompted for a password or not depends on the settings in pg_hba.conf. Typically the postgres operating system user is allowed to connect to the database as the postgres database user without a password. Other OS user / database user combinations may or may not need a password depending on whether pg_hba.conf is set up to ask for one or not - how that is set up as default varies, but it's fairly common to require a password. exactly. Thanks a lot for explaining that way better. Cheers, Steve Actually I think we both know how it works ;-) Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Password?
Ms swati chande schrieb: Hi, I started everything again from scratch. 1. Created a new user(Swati), with limited/ restricted rights. Ensured that no password is set anywhere. 2. Ran initdb from the new user. c:\postgresql\bininitdb -D c:\postgresql\data2 It displayed the DEBUG: start transaction and commit transaction states etc. and ended with DEBUG: exit(0) A warning with the following statement was also displayed: WARNING: enabling trust authentication foe local connections you can change this by editing pg_hba.conf or by initdb -A. 3. After this I executed pg_ctl: c:\postgresql\binpg_ctl -D c:\postgresql\data2 -l logfile start got the the message:server starting and the logfile contained the following: LOG: could not bind IPv4 socket: No error HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for localhost FATAL: could not create any TCP/IP sockets LOG: could not bind IPv4 socket: No error HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for localhost FATAL: could not create any TCP/IP sockets 4. To take care of the above issues, Made the following change in the postgresql.conf file: listen_addresses = 'xxx.xxx.x.x' (my current ip) and in pg_hba: host all all 'xxx.xxx.x.x' trust 5.Then issued c:\postgresql\binpg_ctl -D c:\postgresql\data2 -l logfile start again. Now got the following in logfile: LOG: database system was shut down at 2009-07-08 22:34:50 LOG: database system is ready to accept connections LOG: autovacuum launcher started 6.Opened another command window. Now when I write in the new window (or even in the same), c:\postgresql\bincreatedb demo OR c:\postgresql\bincreateuser -S -d -R svc I am prompted for password, I don't know what to enter here. I think I am making some mistake in pg_hba.conf. Can't make out. Must be some brainless blunder some where. Thanks a ton for sparing your time and bearing with me. Please guide. Regards Swati Swati, sorry to say - but I got no solution as I cannot try to simulate this. I do not have a Windows machine ... hopefully someone else can help. One thing anyway ... Step 4. seems to be correct. Actually, is there a user postgres on your system? Why not give postgres then a password (in the windows user administration) and use c:\postgresql\bincreateuser -U postgres -S -d -R svc But this is really vague ... Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Sugestion a db modele like mysql workbrench
originally posted at BUGS list - now answering at GENERAL list Pavel Golub schrieb: Hello, Oscar. First of all you shouldn't post such messages here since this is not a bug for sure. Take a look on MicroOLAP Database Designer for PostgreSQL at http://microolap.com/products/database/postgresql-designer/ You will be surprised :) Hi Pavel, since I did some research for such a tool, I am wondering if there is a similar one for none Windows OS. I installed this tool and thought it's a good one - but sadly it's working only on Windows ;-). This is interesing for people needing more options as given in pgAdmin. Do you know something else? Thankls a lot Cheers Andy You wrote: OMAE The following bug has been logged online: OMAE Bug reference: 4903 OMAE Logged by: Oscar Miguel Amezcua Estrella OMAE Email address: obel...@gmail.com OMAE PostgreSQL version: 8.4 OMAE Operating system: Opensuse 11.1 OMAE Description:Sugestion a db modele like mysql workbrench OMAE Details: OMAE hiyas im newuser for postgresql and we try to find a software like mysql OMAE workbrench to modelate data bases and we not found a good program for this OMAE work and we like to sugestion this one to make a software like mysql OMAE workbrench that can help to migrate more people from mysql to postgreSQL -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Sugestion a db modele like mysql workbrench
USTID: DE 238093396 Pavel Golub schrieb: Hello, Andreas. You wrote: AW originally posted at BUGS list - now answering at GENERAL list AW Pavel Golub schrieb: Hello, Oscar. First of all you shouldn't post such messages here since this is not a bug for sure. Take a look on MicroOLAP Database Designer for PostgreSQL at http://microolap.com/products/database/postgresql-designer/ You will be surprised :) AW Hi Pavel, AW since I did some research for such a tool, I am wondering if there is a similar one for AW none Windows OS. I installed this tool and thought it's a good one - but sadly it's AW working only on Windows ;-). This is interesing for people needing more options as given AW in pgAdmin. AW Do you know something else? The latest release of MicroOLAP Database Designer for PostgreSQL is specialy tested for Wine support. Thus all you need is to install Wine of the latest version. Some details ma be found here: http://pgolub.wordpress.com/2009/05/27/pgmdd-1-2-8-wine-out-of-the-box/ AW Thankls a lot Cheers AW Andy You wrote: OMAE The following bug has been logged online: OMAE Bug reference: 4903 OMAE Logged by: Oscar Miguel Amezcua Estrella OMAE Email address: obel...@gmail.com OMAE PostgreSQL version: 8.4 OMAE Operating system: Opensuse 11.1 OMAE Description:Sugestion a db modele like mysql workbrench OMAE Details: OMAE hiyas im newuser for postgresql and we try to find a software like mysql OMAE workbrench to modelate data bases and we not found a good program for this OMAE work and we like to sugestion this one to make a software like mysql OMAE workbrench that can help to migrate more people from mysql to postgreSQL Hi Pavel, thanks a lot for the info ... Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Sugestion a db modele like mysql workbrench
Thomas Kellerer schrieb: Andreas Wenk wrote on 07.07.2009 09:33: originally posted at BUGS list - now answering at GENERAL list Pavel Golub schrieb: Hello, Oscar. First of all you shouldn't post such messages here since this is not a bug for sure. Take a look on MicroOLAP Database Designer for PostgreSQL at http://microolap.com/products/database/postgresql-designer/ You will be surprised :) Hi Pavel, since I did some research for such a tool, I am wondering if there is a similar one for none Windows OS. I installed this tool and thought it's a good one - but sadly it's working only on Windows ;-). This is interesing for people needing more options as given in pgAdmin. Do you know something else? Try out Power*Architect: http://www.sqlpower.ca/page/architect Thomas Hey this seems to be a nice one ... just installed it on my MAC at home. Really good. I will check it tomorrow on Linux in the office ... Thanks for the tip! Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Windows installer for pg-8.4 confusion
Stuart McGraw schrieb: First, thanks to everyone who contributed to 8.4 -- the new features list looks great! In the past I have always installed the Windows binary installer from postgresql.org. For pg-8.4 I see that the download directory now has a pointer to the EnterpriseDB one click installer. Has the EnterpriseDB installer now become the official Windows distribution? Or will the standard pginstaller appear some time in the future? Hi, having a look at http://www.postgresql.org/ftp/binary/v8.4rc1/win32/ will answer your question ;-) Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem search on text arrays, using the overlaps () operator
John Cheng schrieb: --- For some reason, I am seeing a big difference in our real database. I don't want to just rewrite all of our queries yet. I'm guessing the data makes a big difference. What would be a good way to examine the data to figure out what's the best way to write our queries? Is there any features in PostgreSQL that can help me improve the performance? Any advice would be greatly appreciated! Hi, did you think about using the fulltext search integrated up from version 8.3. I never used your approach and don't know if the fulltextsearch is suitable for your case ... just a hint. http://www.postgresql.org/docs/8.4/interactive/textsearch.html Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Howto get the contents of mystopwords/dictionary/synonym/thesaurus ?
Joost Kraaijeveld wrote: Hi, Hi, Is it possible to get an overview/the contents of the stopwords list, dictionary, synonyms or thesaurus using an SQL query, e.g. SELECT * from stopwords? if I understand correctly, you want to see the content of the dictionarys. No - you can't see the entries with SQL because the data is not stored in the database but in files. Usually you can find the files in /usr/share/postgresql/8.x/tsearch_data/ or /usr/local/share/postgresql/8.x/tsearch_data/ Is it possible to add or remove entries from the dictionaries using SQL? see above ... TIA Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG_DUMP/RESTORE Would like an explanation of these (non-critical) errors
James B. Byrne wrote: Hi, pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 4; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop schema public because other objects depend on it HINT: Use DROP ... CASCADE to drop the dependent objects too. Command was: DROP SCHEMA public; pg_restore: [archiver (db)] could not execute query: ERROR: schema public already exists Command was: CREATE SCHEMA public; WARNING: errors ignored on restore: 2 The pg_dump command is: pg_dump --create --format=c --user=postgres --verbose hll_redmine | gzip /var/data/pas-redmine/db/dump/hll_redmine.pgsql.gz rsync -avz --bwlimit=35 --delete-after --exclude=database.yml --exclude=*.log --exclude=*cache --exclude=*ruby_sess* /var/data/pas-redmine inet03.mississauga.harte-lyne.ca:/var/data 1 /dev/null --create is not working here because you select a custom format for your dump. --create is only working with plain SQL dumps. The pg_restore command, which generates the error, is: gunzip /var/data/pas-redmine/db/dump/hll_redmine.pgsql.gz | pg_restore --clean --user=postgres --dbname=hll_redmine ; vacuumdb --user=postgres --full --analyze hll_redmine 1 /dev/null with the --clean parameter you delete existing objects in hll_redmine but there are dependant objects. A common way to avoid this is to drop the whole database first, create a new one and then restore the dump into it. Means use --create instead of --clean. Dropping the database can cause problems because you have to cut all client connections before being able to cut it. So maybe this approach is not working for you. Another idea is not to use any of these parameters and dump only the data. pg_restore -a Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GIN and GiST index - more in depth info
Hi everybody, actually I am wondering if anybody can give me some links to a more in depth info concerning the GIN and GiST index. After having read the docu and searching the web for more info, I am still not satisfied with the knowledge I have. For sure I had a look to Oleg Bartunov' s and Teodor Sigaev's website at http://www.sai.msu.su/~megera/wiki/ but for me it's still not clear how to describe the differences between the indexes and the usage scenarios when to use GIN or GiST. Every info is higly appreciated. Thanks Andy -- Netzmeister St.Pauli St.Pauli - Hamburg - Germany Andreas Wenk http://www.netzmeister-st-pauli.de http://blog.netzmeister-st-pauli.de mailto:a.w...@netzmeister-st-pauli.de -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] fulltext search udf
Hi everybody,
altough I know that the way over a Trigger is preferred, I can't see what is wrong in my
function. It must be an escaping error:
CREATE OR REPLACE FUNCTION
user_insert_vector_data(text,text,text,numeric,numeric,numeric,text)
RETURNS void AS
$$
DECLARE
art_nr ALIAS for $1;
bezeichnung ALIAS FOR $2;
beschreibung ALIAS FOR $3;
preis ALIAS FOR $4;
steuersatz ALIAS FOR $5;
aktionspreis ALIAS FOR $6;
stichworte ALIAS for $7;
vect tsvector;
BEGIN
vect := setweight(to_tsvector('german',coalesce(stichworte,'')),'A') ||
setweight(to_tsvector('german',coalesce(beschreibung,'')),'B');
EXECUTE 'INSERT INTO produkte
(art_nr,bezeichnung,beschreibung,preis,steuersatz,aktionspreis,stichworte,vector)
VALUES
('''||art_nr||''','''||bezeichnung||''','''||beschreibung||''','||preis||',
'||steuersatz||','||aktionspreis||','''||stichworte||''','||vect||')';
END;
$$ LANGUAGE plpgsql;
Firering this statement:
select
user_insert_vector_data('adfvb','adfvb','adfvb',15.5,25.50,2,'alpha,beta,cesar');
results in this error:
FEHLER: Syntaxfehler bei »:«
LINE 3: ... 25.50,2,'alpha,beta,cesar','beta':2A 'adfvb...
^
QUERY: INSERT INTO produkte
(art_nr,bezeichnung,beschreibung,preis,steuersatz,aktionspreis,stichworte,vector)
VALUES ('adfvb','adfvb','adfvb',15.5,
25.50,2,'alpha,beta,cesar','beta':2A 'adfvb':4B 'alpha':1A
'cesar':3A)
CONTEXT: PL/pgSQL function user_insert_vector_data line 14 at EXECUTE
statement
vector is of datatype tsvector. So I don't understand why pg is complaining ...
Thank's for any hints and sorry for annoying if I am too blind ...
Cheers
Andy
--
Netzmeister St.Pauli
St.Pauli - Hamburg - Germany
Andreas Wenk
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] fulltext search udf
Tom Lane wrote:
Andreas Wenk a.w...@netzmeister-st-pauli.de writes:
EXECUTE 'INSERT INTO produkte
(art_nr,bezeichnung,beschreibung,preis,steuersatz,aktionspreis,stichworte,vector)
VALUES
('''||art_nr||''','''||bezeichnung||''','''||beschreibung||''','||preis||',
'||steuersatz||','||aktionspreis||','''||stichworte||''','||vect||')';
This is not going to work with such a half-baked approach to quoting the
data values --- any value with ' or \ in it will break it. You could
use quote_literal(), but I wonder why you are bothering with EXECUTE at
all. If you just did the INSERT directly it would run faster and be a
lot less ugly.
regards, tom lane
Tom, thanks for the hint! I was really doing the wrong thing. There have
been even more errors what made me confused. Now I got it ...
Cheers
Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] Question about Privileges
Jack W schrieb: On Thu, Mar 12, 2009 at 3:45 PM, John R Pierce pie...@hogranch.com mailto:pie...@hogranch.com wrote: Jack W wrote: I also find that if I only grant privileges on database to dbuser as below, without granting privileges on Schema and table to dbuser, dbuser still can not do SELECT on the tables. mydb=# grant all privileges on Database mydb to dbuser; Is there any simple way to grant All privileges to dbuser on all the 10 tables? the simplest way is to make dbuser the OWNER of the database, and have him create all the tables too, then he automatically has full rights to it. Thanks. In my application, one user will create the database, then other users can remotely access it through ODBC/JDBC to access the database. In this case, I have to grant the privileges to each user one by one, right? SCHEMA privileges grant the rights to connect to a schema, and create/modify schemas table/view/etc privileges grant the rights to select/insert/update/etc the mentioned tables. In my test as below: mydb=#select * from mydb_schema.mydb_table1; If I only grant the privileges to the table: mydb=# grant all privileges on mydb_schema.mydb_table1 to dbuser; The SELECT will fail, the error is: STATEMENT: select * from mydb_schema.mydb_table1; ERROR: permission denied for schema mydb_schema So I have to grant the privileges to the schema mydb_schema in order to do SELECT on mydb_table1. This is a common mistake. You have to grant the privileges always also to the schema explicit and not just to the table in a schema. PostgreSQL is not going so far to warn you, that the user has now the privileges on the table but *not* to the schema. As Adrian allready mentioned, you should use group roles and then grant the privileges for the schema to the group role if you have more different users ... Cheers Andy -- St.Pauli - Hamburg - Germany Andreas Wenk Jack -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bug?: permission denied for schema on on delete set null
Ivan Sergio Borgonovo schrieb: I get a Query failed: ERROR: permission denied for schema user_test CONTEXT: SQL statement UPDATE ONLY user_test.shop_commerce_baskets SET sid = NULL WHERE $1::pg_catalog.text OPERATOR(pg_catalog.=) sid::pg_catalog.text This query is run when I do a DELETE FROM user_test.sessions WHERE timestamp 1236672815; as a result of an on delete set null but when I directly do a update user_test.shop_commerce_baskets set sid=null; I get no error. create table user_test.sessions( sid int primary key, ); create table user_test.shop_commerce_baskets ( sid int references sessions (sid) on delete set null, ... ); I'm on PostgreSQL 8.3.6 (Debian Lenny). thanks which rights does the actual user have for the schema user_test? Query failed: ERROR: permission denied for schema user_test CONTEXT: I think the user does not have the rights for the schema ... Cheers Andy -- St.Pauli - Hamburg - Germany Andreas Wenk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bug?: permission denied for schema on on delete set null
Ivan Sergio Borgonovo schrieb: On Tue, 10 Mar 2009 14:31:56 +0100 Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: which rights does the actual user have for the schema user_test? Query failed: ERROR: permission denied for schema user_test CONTEXT: I think the user does not have the rights for the schema ... I just run http://pgedit.com/tip/postgresql/access_control_functions on public and user_test schema + grant all on schema user_test to user_test; It was related to something similar to this: http://archives.postgresql.org//pgsql-general/2007-06/msg01365.php I still have to sort it out since I did some random changes to ownership of schema and tables. It seems that the schemas have to be owned by the group and not by the single users. To sum it up: a role as a group owning everything several roles as users member of the previous role everything owned by the group permissions assigned to the single users In general you will put a user into a group role. And if the group role does not have the permissions for the schema you will run into this issue. So give the permissions to the group role and it will work ... Check this out: http://archives.postgresql.org//pgsql-admin/2009-02/msg00268.php up to http://archives.postgresql.org//pgsql-admin/2009-02/msg00274.php maybe you follow that discussion for some minutes ... Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bug?: permission denied for schema on on delete set null
Tom Lane schrieb: Ivan Sergio Borgonovo m...@webthatworks.it writes: I get a Query failed: ERROR: permission denied for schema user_test CONTEXT: SQL statement UPDATE ONLY user_test.shop_commerce_baskets SET sid = NULL WHERE $1::pg_catalog.text OPERATOR(pg_catalog.=) sid::pg_catalog.text This query is run when I do a DELETE FROM user_test.sessions WHERE timestamp 1236672815; as a result of an on delete set null Queries for RI constraints are run with the permissions of the owner of the other table. It looks to me like the owner of user_test.sessions doesn't have usage permission on schema user_test ... regards, tom lane that's what I tried to say ;-) therfore the examples in my other posts ... Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enable user access from remote host
Piotre Ugrumov schrieb: On 8 Mar, 02:08, pie...@hogranch.com (John R Pierce) wrote: Martin Gainty wrote: postgresql.conf : change listen_address to a real ip change it to '*' or you won't be able to use localhost... alternately, youc could specify ip.of.net.iface,localhost if you wanted to be specific. -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Hi, I inserted this line at the end of the pg_hba.conf hosttestangeloident sameuser I inserted the to allow to everyone to access to the db. But if I try to access, from another host, I receive an error. What is the error in that line? just leave it blank ... no signs ... Cheers Andy -- Andreas Wenk Hamburg - Germany Thanks, bye bye. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] cursor question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, short question. Why is this not possible: CREATE OR REPLACE FUNCTION user_cursor_open(text) RETURNS refcursor AS $$ DECLARE curs1 CURSOR FOR SELECT * FROM $1; BEGIN OPEN curs1; RETURN curs1; END; $$ LANGUAGE plpgsql; Or, how do I set a parameter when using cursor? Thanks in advance - -- St.Pauli - Hamburg - Germany Andreas Wenk -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJpWlyVa7znmSP9AwRAv2MAJ9jHICtLeIEv+QVzeqBWSkheXDmpgCgwMUH t0/HWRPsx56jGhSVUQXyb9I= =XMXi -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
Re: [GENERAL] cursor question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Pavel Stehule schrieb: Hello when you would to change name of table - you have to use dynamic query 38.7.2.2. OPEN FOR EXECUTE OPEN unbound_cursor [ [ NO ] SCROLL ] FOR EXECUTE query_string; http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html regards Pavel Stehule Hi Pavel, thanks a lot. It works for me like this: CREATE OR REPLACE FUNCTION user_cursor_test(refcursor,text) RETURNS refcursor AS $$ BEGIN OPEN $1 FOR EXECUTE 'SELECT name FROM ' || $2; RETURN $1; END; $$ LANGUAGE plpgsql; Cheers Andy 2009/2/25 Andreas Wenk a.w...@netzmeister-st-pauli.de: Hi, short question. Why is this not possible: CREATE OR REPLACE FUNCTION user_cursor_open(text) RETURNS refcursor AS $$ DECLARE curs1 CURSOR FOR SELECT * FROM $1; BEGIN OPEN curs1; RETURN curs1; END; $$ LANGUAGE plpgsql; Or, how do I set a parameter when using cursor? Thanks in advance - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJpYU2Va7znmSP9AwRAgGAAJ9En2TEupu7t994DIxi9ql5LH5sXACdHIi1 jNiaLX2mEmYWRxHDcGavbsM= =XUcF -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
Re: [GENERAL] Vacuums taking forever :(
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Phoenix Kiula schrieb: In my conf_pg, the autovacuum is on, so the DB should be (or I hope is being) regularly vacuumed. These are my settings: work_mem = 20MB temp_buffers = 4096 authentication_timeout = 10s ssl = off checkpoint_warning = 3600 random_page_cost = 1 autovacuum = on autovacuum_vacuum_cost_delay = 20 vacuum_cost_delay= 20 autovacuum_naptime = 10 stats_start_collector= on stats_row_level = on autovacuum_vacuum_threshold = 75 autovacuum_analyze_threshold = 25 autovacuum_analyze_scale_factor = 0.02 autovacuum_vacuum_scale_factor = 0.01 Hi , just a quick thought. What is your maintenance_work_mem parameter set to? I think with that lot Updates and Inserts this should not be too low ... Cheers Andy The autovacuum was clearly not enough, so we also have a crontab that vacuums the tables every hour. This is PG 8.2.9. These cron jobs are taking over 35 minutes for a vacuum! What's the use of a vacuum if it takes that long, and the DB performance is tragic in the meantime? I'd truly appreciate some thoughts from people with experience of vacuum management of highly available online databases. About 10-20 million accesses for this one. Most are SELECTs. We have about 500,000 INSERTs and about 800,000 UPDATEs. Just 11 tables, of which only one is like 10 million rows. Two are close to 500,000 rows, rest are really small. It is this 10 million row thing that's the worry. Thanks! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJiEIgVa7znmSP9AwRAvUoAKCCuRycQVPCiEBkCxLvxrnXIa2ZqwCfZSI1 uooHCg8rIW6Zdt7pJU7YZMM= =vO+P -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
Re: [GENERAL] Vacuums taking forever :(
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Phoenix Kiula schrieb: On Tue, Feb 3, 2009 at 9:09 PM, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Phoenix Kiula schrieb: In my conf_pg, the autovacuum is on, so the DB should be (or I hope is being) regularly vacuumed. These are my settings: work_mem = 20MB temp_buffers = 4096 authentication_timeout = 10s ssl = off checkpoint_warning = 3600 random_page_cost = 1 autovacuum = on autovacuum_vacuum_cost_delay = 20 vacuum_cost_delay= 20 autovacuum_naptime = 10 stats_start_collector= on stats_row_level = on autovacuum_vacuum_threshold = 75 autovacuum_analyze_threshold = 25 autovacuum_analyze_scale_factor = 0.02 autovacuum_vacuum_scale_factor = 0.01 Hi , just a quick thought. What is your maintenance_work_mem parameter set to? I think with that lot Updates and Inserts this should not be too low ... maintenance_work_mem = 512M wal_buffers = 64 checkpoint_segments = 128 checkpoint_timeout = 900 fsync= on Is this enough? My server has 4GB RAM. Try to increase it but check also what Andrew wrote in the other reply. Maybe ther is the problem ... Cheers Andy -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJiEz4Va7znmSP9AwRAsD8AKC4+98SzjKfi4YLgAQ1697rr2DgpgCg3oXH PMYe+Y3OJf3YnisJ/rU2REI= =d7/G -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
Re: [GENERAL] ALTER TABLE with TYPE serial does not work
Jasen Betts schrieb: On 2009-01-31, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: Hi List, I have a short question to psql. Why does this not work: postgres=# ALTER TABLE tab1 ALTER COLUMN nr TYPE serial; ERROR: type serial does not exist but this: postgres=# ALTER TABLE tab1 DROP COLUMN nr; ALTER TABLE postgres=# ALTER TABLE tab1 ADD COLUMN nr serial; NOTICE: ALTER TABLE will create implicit sequence tab1_nr_seq for serial column tab1.nr ALTER TABLE because serial isn't a type. ah - I think this is what the error message says ;-/ ... ?? !! -- St.Pauli - Hamburg - Germany Andreas Wenk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ALTER TABLE with TYPE serial does not work
Richard Broersma schrieb: On Sat, Jan 31, 2009 at 3:16 PM, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: Why does this not work: postgres=# ALTER TABLE tab1 ALTER COLUMN nr TYPE serial; ERROR: type serial does not exist serial is really just short-hand for making an integer column use default incrementing function. The following will fully explain what it is so that you can alter the column: http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL Thanks Richard, that helped. I thought maybe there is another reason because when I create a table and use serial as type (like I would when I use integer) it works well. I know that searial is just for my convenience as written in the manual. I failed to understand, that it is not really a type. Cheers Andy -- St.Pauli - Hamburg - Germany Andreas Wenk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ALTER TABLE with TYPE serial does not work
Scott Marlowe schrieb: On Sat, Jan 31, 2009 at 9:04 PM, Richard Broersma richard.broer...@gmail.com wrote: On Sat, Jan 31, 2009 at 3:16 PM, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: Why does this not work: postgres=# ALTER TABLE tab1 ALTER COLUMN nr TYPE serial; ERROR: type serial does not exist serial is really just short-hand for making an integer column use default incrementing function. The following will fully explain what it is so that you can alter the column: http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL Seeing as it works with adding a column, and I've seen instructions for creating a sequence, and then adding a dependency into the system tables, it's quite reasonable to expect that one day it will work with alter table alter column. But it's probably more complicated than just making it a serial type, there's probably some question of setting the sequence according to the max value in the table. I'd be surprised if it's not on the TODO list somewhere. Thanks for this Scott. For me as a user it would be cool to have it ... hopefully it's on a TODO list ;-). On the other hand I don't think that this case will show up too often because the decision to have a column in a table with a incrementing sequence should be made while designing the database structure ... Cheers Andy -- St.Pauli - Hamburg - Germany Andreas Wenk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ALTER TABLE with TYPE serial does not work
Hi List, I have a short question to psql. Why does this not work: postgres=# ALTER TABLE tab1 ALTER COLUMN nr TYPE serial; ERROR: type serial does not exist but this: postgres=# ALTER TABLE tab1 DROP COLUMN nr; ALTER TABLE postgres=# ALTER TABLE tab1 ADD COLUMN nr serial; NOTICE: ALTER TABLE will create implicit sequence tab1_nr_seq for serial column tab1.nr ALTER TABLE Sitting in front of a MAC OS X Leo with pg 8.2 Thanks a lot! Cheers Andy -- St.Pauli - Hamburg - Germany Andreas Wenk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Text search with ispell
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tommy Gildseth schrieb: I'm trying to figure out how to use PostgreSQL's fulltext search with an ispell dictionary. I'm having a bit of trouble figuring out where this norwegian.dict comes from though. When I install the norwegian ispell dictionary, i get 4 files, nb.aff, nb.hash, nn.aff and nn.hash. What I'm unable to figure out, is the steps needed to use this for PostgreSQL? Which version are you running? It's important to know, because tsearch2 is integrated since version 8.3. The behaviour for implementing in earlier versions is therefore different ... Cheers Andy - -- St.Pauli - Hamburg - Germany Andreas Wenk -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJfum6Va7znmSP9AwRAlN4AJ9odanCrD3R+gMzb7yzJjXWEKfCUACeN1Tv SmVDeFa6xemj53T2cMUFoyM= =khkB -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
Re: [GENERAL] pg_restore: [archiver] entry ID -825110830 out of range -- perhaps a corrupt TOC
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dennis C schrieb: OK that was it! Wow, thank you so very much! Nice to know it was just plpython tracking such an obsolete version of postgresql much to my dismay now (especially even going backwards, which didn't even occur to me), as opposed to postgresql itself being less reliable than I've come to expect over the years! Thanks for all your great work with that too in the first place! cool that it's working now ;-) On Tue, Jan 20, 2009 at 10:40 AM, Scott Marlowe scott.marl...@gmail.com mailto:scott.marl...@gmail.com wrote: On Tue, Jan 20, 2009 at 11:15 AM, Dennis C dcsw...@gmail.com mailto:dcsw...@gmail.com wrote: Greetings; And thanks for your reply! I tried the following: less xaa | grep ^; xaa may be a binary file. See it anyway? y Binary file (standard input) matches And so am not sure which version I did the following from: pg_dump -c -F c -Z 9 [databasename] It's kind of important, but... PostgreSQL's dump and restore commands are designed to work from the same versions or going a new version from an older version. Going backwards is not supported. That was what I head in mind asking you about the version ;-) Thank's to Scott for bringing it to the point ;-) But I installed it about a year ago, so whichever was the release then. Am trying to restore to the following: 8.2 or 8.3. Unless you were using a version supplied by a distro, which could go further back. postgresql-client-7.4.21 PostgreSQL database (client) postgresql-plpython-7.4.21_1 A module for using Python to write SQL functions postgresql-server-7.4.21 The most advanced open-source database available anywhere Now's the time to upgrade. 7.4 is the oldest supported version, which means it's next for the chopping block. It's also A LOT slower than 8.3. Can you get and install a newer version of pgsql, preferably 8.3 and try restoring there? cat * | pg_restore -d [databasename] The normal way to run it is to use the -f switch for the file pg_restore -d dbname -f filename Not sure there's anything wrong with your way, but I've never used pg_restore like that. Cheers Andy -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdvxAVa7znmSP9AwRAqqhAKCswD9ioSbJuIwiBLZLfTdTaW+jVwCgy3d7 IQiwmaLkNoxs7zbSZcH1+5E= =i/ZQ -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
Re: [GENERAL] autovacuum daemon
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Abdul Rahman schrieb: Deal All, I have set postgresql.conf for autovacuum and need to know weather the process is running/working or not. Hi Abdul, 1. you could check the log file 2. select setting from pg_settings where name = 'autovacuum'; Cheers Andy - -- Andreas Wenk St.Pauli - Hamburg - Germany Regards, Avdul Rehman. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdzk6Va7znmSP9AwRAk2nAJ9eeGtXGZbyMoPFZwukmg0T/4sWtgCg28iN B6m0nEgLALvIv2ZGZt9syvs= =4zR5 -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
Re: [GENERAL] pg_restore: [archiver] entry ID -825110830 out of range -- perhaps a corrupt TOC
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, maybe I did not understand - but what exactly happens when you do something like pg_restore -d databasename -L backup.toc backup.bak (bak - or what ever custom format you use) Is the version you did the backup with the same like the postgres version you want to restore to? I am not sure, but maybe there are occuring some problems ... $ less test.toc | grep ^; [...] ; Dumped from database version: 8.3.5 ; Dumped by pg_dump version: 8.3.5 [...] Cheers Andy - -- St.Pauli - Hamburg - Germany Andreas Wenk Dennis C schrieb: Greetings: I already did some searches on the pg_restore: [archiver] entry ID -825110830 out of range -- perhaps a corrupt TOC error and am still not sure why my database's not restoring after upgrading the FreeBSD and select ports such as PostGreSQL. I did see something from a long time ago about altering the table, especially column names, quite possibly being a problem and I have done a lot of that, but as many backups as I've also done this past year, this' the first time I've had to use the restore again and am now not even sure how to get my database back. If this seems familiar and simple enough for anyone, please advise including which details may be useful here. Thanks, -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdYu7Va7znmSP9AwRAunPAKCliM90HkrNzQ51ectMmziqc7gTGwCeNrco VvYrDL/hbinlF9gaPUMau1o= =M8mK -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
[GENERAL] import sql dump with psql - language creation throws error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi everybody, I have an automated mechanism to restore a demo database each night with an SQL dump. What I do inbetween a shell script is the following: 1. all database access is canceled 2. dropdb 3. createdb 4. import SQL dump: psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP The last step is the issue. The shell script is run by an cronjob and if one of the steps is failing, the crondaemon sends an E-Mail. The cluster allready exists (for sure) and the language plpgsl also. The last point (4.) always creates an error (what is clear), allthough the dump is imported anyway: ERROR: Language »plpgsql« allready exists psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP || echo Der dump konnte nicht eingespielt werden. 2 And because the ERROR message is the output, the crondaemon sends an email. Question: Where can I prevent bulding the language again? My idea was to do that while creating the dump or while importing the dump. But as far as I understand, that's not possible. The dump is created on another server using /usr/bin/pg_dump -Ft -t sys_language garfield sys_language.tar Thanks for any advice Andy - -- Andreas Wenk St.Pauli - Hamburg - Germany -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdaQsVa7znmSP9AwRAlE0AKCVTqsD9X8nMtGHcTsfzHVElK5ePQCeMC3c vqOqVcx0ns26Nf8esi4xp/A= =zDAD -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
Re: [GENERAL] import sql dump with psql - language creation throws error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi depesz, thanks a lot for the reply. I think that will not work, because this is (still) a 8.1 and tsearch2 is integreated. That means I have to use template1 otherwise tsearch2 would not be there ... Am I right? P.S.: upgrade to 8.3 is planned asap ;-) - -- St.Pauli - Hamburg - Germany Andreas Wenk hubert depesz lubaczewski schrieb: On Tue, Jan 20, 2009 at 11:15:08AM +0100, Andreas Wenk wrote: Where can I prevent bulding the language again? My idea was to do that while creating the dump or while importing the dump. But as far as I understand, that's not possible. instead of createdb use: createdb -D template0 Best regards, depesz -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdacrVa7znmSP9AwRAlZ/AJoDlznuIlPI/ODu9HSQfSIXlKc9FgCgjdmo 6/WJlfiUo0pNjwpaoS9XIok= =3AQA -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
Re: [GENERAL] import sql dump with psql - language creation throws error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Adrian Klaver schrieb: On Tuesday 20 January 2009 2:15:08 am Andreas Wenk wrote: Hi everybody, I have an automated mechanism to restore a demo database each night with an SQL dump. What I do inbetween a shell script is the following: 1. all database access is canceled 2. dropdb 3. createdb 4. import SQL dump: psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP The last step is the issue. The shell script is run by an cronjob and if one of the steps is failing, the crondaemon sends an E-Mail. The cluster allready exists (for sure) and the language plpgsl also. The last point (4.) always creates an error (what is clear), allthough the dump is imported anyway: ERROR: Language »plpgsql« allready exists psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP || echo Der dump konnte nicht eingespielt werden. 2 And because the ERROR message is the output, the crondaemon sends an email. Question: Where can I prevent bulding the language again? My idea was to do that while creating the dump or while importing the dump. But as far as I understand, that's not possible. The dump is created on another server using /usr/bin/pg_dump -Ft -t sys_language garfield sys_language.tar Could be lack of coffee, but I am somewhat confused. Is this indeed the dump file you are restoring? I didn't think you could restore a tar dump with psql. Also you are only dumping one table, so I am not sure why the whole db is being recreated. Adrian, no lack of coffee but my fault. You are totally right - that was a copy and paste error. For sure the dump is *.sql. Until now there is no onboard solution for this issue. Means, the import of the dump is working correct but a message is thrown: FEHLER: Sprache »plpgsql« existiert bereits (means ERROR: Language »plpgsql« allready exists). What I do now - and this is not really beautiful - is to erase that message before oputput is created from the shell script: psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP 21 | grep -v FEHLER: Sprache »plpgsql« existiert bereits exit 0 Now, no output from the script is catched by the crondaemon and no email is sent. On the other hand, no error from the above line is catched at all ... unfortunately ... By the way - language plpgsql was created with createlang plpgsql template1. And because of tsearch2 it's not possible to create the db from template0. Got another idea? Cheers Andy - -- St.Pauli - Hamburg - Germany Andreas Wenk Thanks for any advice Andy -- Andreas Wenk St.Pauli - Hamburg - Germany -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdegzVa7znmSP9AwRAvtbAKDNv9O3HyEe7kn1fjQpPIMRtk9PPwCfRf5a KkatRS9OojoHBXGxQA05gKY= =qhRy -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
Re: [GENERAL] import sql dump with psql - language creation throws error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Adrian Klaver schrieb: On Tuesday 20 January 2009 7:05:23 am Andreas Wenk wrote: Adrian, no lack of coffee but my fault. You are totally right - that was a copy and paste error. For sure the dump is *.sql. Until now there is no onboard solution for this issue. Means, the import of the dump is working correct but a message is thrown: FEHLER: Sprache »plpgsql« existiert bereits (means ERROR: Language »plpgsql« allready exists). What I do now - and this is not really beautiful - is to erase that message before oputput is created from the shell script: psql -o /dev/null $DB /var/lib/postgresql/scripts/$SQL_DUMP 21 | grep -v FEHLER: Sprache »plpgsql« existiert bereits exit 0 Now, no output from the script is catched by the crondaemon and no email is sent. On the other hand, no error from the above line is catched at all ... unfortunately ... By the way - language plpgsql was created with createlang plpgsql template1. And because of tsearch2 it's not possible to create the db from template0. Got another idea? Cheers Andy Just to point you to Grzegorz's suggestion of using the -c switch in the pg_dump command. To quote the manual: -c Output commands to clean (drop) database objects prior to (the commands for) creating them. This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore. I allready tried -c ... it's not what I need or helps - thanks anyway ;-) Also I am not sure cron sending the email is a bad thing. Serves as indicator that the process ran. Yes that's correct ... in a way - but imagine you have 20 cronjobs running - do you really want to spam your mailbox with these messages? I think the better way is to leave it running and only in case of an error inform me. I think this is the common way sysadmins are doing it ... Cheers Andy -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJdfLiVa7znmSP9AwRAqYlAKCONfrcirRuDzFYYs9+1Sbg46JejgCgif0V 2RMlNbRaqK7aAomCk6tzPow= =+whp -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
[GENERAL] MD5 password issue
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi everybody, I posted this allready to the ADMIN list but recieved no reply (what is for sure ok in a way ;-) ). So I thought I'll give it a try here. Sorry for any inconvenience. We are trying to understand an issue concerning the md5 password encryption. The situation is as follows. In pg_hba.conf we have: # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: hostall all 127.0.0.1/32 md5 hostall all 192.168.97.0/24 md5 in pg_authid we get: postgres=# SELECT rolname,rolpassword from pg_authid; rolname | rolpassword - ---+- postgres | pgadmin | plaintext odie | md5passsorrrd The user odie was created with: CREATE ROLE odie LOGIN ENCRYPTED PASSWORD 'feedme'; The user pgadmin was created with: $ createuser -a -d -P -N -U postgres pgadmin The -N parameter forces not to encrypt the password - what we can see as a result in pg_authid (if this makes sense or not is another question ;-) ). Now the question: why is the user pgadmin able to connect to the database using pgAdmin III from 192.168.97.30? That sould not be possible ... or am I wrong? Thanks for any advice Cheers Andy - -- St.Pauli - Hamburg - Germany Andreas Wenk -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJb2y+Va7znmSP9AwRAmGlAKCaingauIMGRvIqAqMBVdiBfhkoXwCeM1kR M/fZSYeJKq9tMe791MhN2J8= =V7hS -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
Re: [GENERAL] MD5 password issue
Hi Joshua Joshua D. Drake schrieb: On Thu, 2009-01-15 at 18:05 +0100, Andreas Wenk wrote: postgres=# SELECT rolname,rolpassword from pg_authid; rolname | rolpassword - ---+- postgres | pgadmin | plaintext odie | md5passsorrrd The user odie was created with: CREATE ROLE odie LOGIN ENCRYPTED PASSWORD 'feedme'; The user pgadmin was created with: $ createuser -a -d -P -N -U postgres pgadmin Per the help. You need to pass -E to have it be an encrypted (md5 hash) style password. Sure - I know we added -N so that the password is not encrypted What version of PostgreSQL is this as I recall all newer versions do this by default. this was made with a 8.1 version ... Usage: createuser [OPTION]... [ROLENAME] Options: -s, --superuser role will be superuser -S, --no-superuserrole will not be superuser -d, --createdbrole can create new databases -D, --no-createdb role cannot create databases -r, --createrole role can create new roles -R, --no-createrole role cannot create roles -l, --login role can login (default) -L, --no-loginrole cannot login -i, --inherit role inherits privileges of roles it is a member of (default) -I, --no-inherit role does not inherit privileges -c, --connection-limit=N connection limit for role (default: no limit) -P, --pwpromptassign a password to new role -E, --encrypted encrypt stored password -N, --unencrypted do not encrypt stored password -e, --echoshow the commands being sent to the server --helpshow this help, then exit --version output version information, then exit Connection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port -U, --username=USERNAME user name to connect as (not the one to create) -W, --passwordforce password prompt If one of -s, -S, -d, -D, -r, -R and ROLENAME is not specified, you will be prompted interactively. Joshua D. Drake -- St.Pauli - Hamburg - Germany Andreas Wenk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MD5 password issue
Alvaro Herrera schrieb: Andreas Wenk wrote: Yes thats correct with the IP address range. Maybe I did not understand the auth concept yet. I thought, that with METHOD set to md5, a md5 hashed password is required. The password is submitted with the PHP 5 pg_connect function - as plain text. It is specified to pg_connect as plain text, but it is sent over the wire md5-hashed. So maybe the better question is: what is the difference between METHOD password and md5? As I assume now because of your answers, it has nothing to do with either the password is md5 hashed or not? The difference is what travels on the wire. ok thanks - I think I got it now ... ;-) Cheers Andy -- St.Pauli - Hamburg - Germany Andreas Wenk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MD5 password issue
Hi Tom, Tom Lane schrieb: Andreas Wenk a.w...@netzmeister-st-pauli.de writes: In pg_hba.conf we have: # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: hostall all 127.0.0.1/32 md5 hostall all 192.168.97.0/24 md5 Now the question: why is the user pgadmin able to connect to the database using pgAdmin III from 192.168.97.30? That sould not be possible ... or am I wrong? Why shouldn't it be possible? You've specifically allowed connections from that IP range. Yes thats correct with the IP address range. Maybe I did not understand the auth concept yet. I thought, that with METHOD set to md5, a md5 hashed password is required. The password is submitted with the PHP 5 pg_connect function - as plain text. (If you're wondering why he didn't have to type his password, it's likely because pgAdmin is getting it out of ~/.pgpass or some private settings file.) regards, tom lane Also to Peter. It is like that - the pasword is stored in ~/.pgpass as expected. So maybe the better question is: what is the difference between METHOD password and md5? As I assume now because of your answers, it has nothing to do with either the password is md5 hashed or not? Thanks to everybody! cheers Andy -- St.Pauli - Hamburg - Germany Andreas Wenk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
