Re: [GENERAL] transfering tables into other schema

2009-02-17 Thread Scott Marlowe
On Tue, Feb 17, 2009 at 12:34 AM, Ivan Sergio Borgonovo m...@webthatworks.it wrote: I've around 150-200 tables in the same schema. Some of them have pk/fk relationships and are referenced into functions (~20). One of them is surely referenced by most of those 20 and it is the largest. I'd

[GENERAL] Strange Grant behavior in postgres 8.3

2009-02-17 Thread Schwaighofer Clemens
Version: PostgreSQL 8.3.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1) 4.3.2 I have a DB foo created and owned by postgres. No I created another role called bar and with the user postgres in the db foo I did: # grant all on foo to bar; when I select from pg_database I

Re: [GENERAL] transfering tables into other schema

2009-02-17 Thread Raymond O'Donnell
On 17/02/2009 08:09, Scott Marlowe wrote: ALTER TABLE name SET SCHEMA new_schema; And you can do this for all your tables inside a transaction, making it an all-or-nothing operation. :-) Ray. -- Raymond O'Donnell, Director of

Re: [GENERAL] transfering tables into other schema

2009-02-17 Thread Sam Mason
On Tue, Feb 17, 2009 at 01:09:10AM -0700, Scott Marlowe wrote: On Tue, Feb 17, 2009 at 12:34 AM, Ivan Sergio Borgonovo m...@webthatworks.it wrote: I've around 150-200 tables in the same schema. Some of them have pk/fk relationships and are referenced into functions (~20). I'd like to

Re: [GENERAL] Strange Grant behavior in postgres 8.3

2009-02-17 Thread Albe Laurenz
Schwaighofer Clemens wrote: Version: PostgreSQL 8.3.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real(Debian 4.3.2-1) 4.3.2 I have a DB foo created and owned by postgres. No I created another role called bar and with the user postgres in the db foo I did: # grant all on foo to

[GENERAL] hi all

2009-02-17 Thread Kusuma Pabba
when creating tables, in my sql i have used create table CREATE TABLE `users` ( `user_id` int(11) NOT NULL auto_increment, `user_name` varchar(50) NOT NULL, `first_name` varchar(50) default NULL, `middle_name` varchar(50) default NULL, `last_name` varchar(50) default NULL, `password`

Re: [GENERAL] hi all

2009-02-17 Thread Grzegorz Jaśkiewicz
just outside of your question, you should read about normalization. it is in general very bad idea to have a table that holds all information possible . -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] hi all

2009-02-17 Thread Ashish Karalkar
Kusuma Pabba wrote: when creating tables, in my sql i have used create table CREATE TABLE `users` ( `user_id` int(11) NOT NULL auto_increment, `user_name` varchar(50) NOT NULL, `first_name` varchar(50) default NULL, `middle_name` varchar(50) default NULL, `last_name` varchar(50) default

Re: [GENERAL] hi all

2009-02-17 Thread Thomas Kellerer
Kusuma Pabba, 17.02.2009 13:54: while in pgsql i am thinking of to use the same as follows: CREATE TABLE users ( user_id int(11) NOT NULL serial, user_name varchar(50) NOT NULL, first_name varchar(50) default NULL, middle_name varchar(50) default NULL, last_name varchar(50) default NULL,

[GENERAL] leak in libpq, getpwuid

2009-02-17 Thread Grzegorz Jaśkiewicz
Hey folks, I am getting leaks on my machine, valgrind points to getpwuid_r called by libpq's PQConnectDb() ==11784== 32,772 bytes in 1 blocks are indirectly lost in loss record 31 of 31 ==11784==at 0x4004BA2: calloc (vg_replace_malloc.c:397) ==11784==by 0x63D9FCB: ??? ==11784==by

Re: [GENERAL] leak in libpq, getpwuid

2009-02-17 Thread Grzegorz Jaśkiewicz
oh, and note that I kind of rulled out linux libc/distro problem, same happens on both centos 4.7 and fedora 9. strangely. -- 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] leak in libpq, getpwuid

2009-02-17 Thread Bruce Momjian
Grzegorz Ja??kiewicz wrote: Hey folks, I am getting leaks on my machine, valgrind points to getpwuid_r called by libpq's PQConnectDb() ==11784== 32,772 bytes in 1 blocks are indirectly lost in loss record 31 of 31 ==11784==at 0x4004BA2: calloc (vg_replace_malloc.c:397) ==11784==

Re: [GENERAL] leak in libpq, getpwuid

2009-02-17 Thread Grzegorz Jaśkiewicz
thanks Bruce, In fact - the more weird fact is, that it still happens on fedora9+8.4, but I can't get it anymore on centos+8.3.5 -- 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] leak in libpq, getpwuid

2009-02-17 Thread Tom Lane
=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= gryz...@gmail.com writes: oh, and note that I kind of rulled out linux libc/distro problem, same happens on both centos 4.7 and fedora 9. That hardly constitutes a wide sample of linux distros ... regards, tom lane -- Sent via

Re: [GENERAL] hi all

2009-02-17 Thread Sam Mason
a few further comments: On Tue, Feb 17, 2009 at 06:54:53PM +0530, Ashish Karalkar wrote: CREATE TABLE users ( user_id serial NOT NULL , It's common to combine this with the PRIMARY KEY constraint from below to be: user_id SERIAL PRIMARY KEY, the NOT NULL check is implicit in this and

Re: [GENERAL] Attempting to connect

2009-02-17 Thread Owen Hartnett
At 3:34 PM -0800 2/15/09, Bob Pawley wrote: Finally Success Thanks everyone Here's one I ran into today - connection with the server was blocked by Kasperski Anti-Virus. The clue was that other machines on the network could connect, but this one couldn't. Funny thing was that we had

Re: [GENERAL] transfering tables into other schema

2009-02-17 Thread Ivan Sergio Borgonovo
On Tue, 17 Feb 2009 12:19:14 + Sam Mason s...@samason.me.uk wrote: I'd like to move all the 200 tables to a new schema and leave that one in the public schema. ALTER TABLE name SET SCHEMA new_schema; Make sure your functions don't contain any hard coded references to the old

Re: [GENERAL] transfering tables into other schema

2009-02-17 Thread Sam Mason
On Tue, Feb 17, 2009 at 06:20:54PM +0100, Ivan Sergio Borgonovo wrote: I can't get how this really work. You're saying that constraint, fk/pk relationships will be preserved automatically... what else? OK BEFORE: create table x ( xid primary key, ... ); create table y ( xid int

Re: [GENERAL] Good Delimiter for copy command

2009-02-17 Thread Mark Roberts
On Tue, 2009-02-17 at 17:17 +0100, Marco Colombo wrote: Which makes me wonder, does copy accept UTF-8 input? Is it possibile to use some unicode character which is unlikely to appear in the data set as delimiter? Something like U+FFFC. I'm also not able to get unicode characters to copy in

Re: [GENERAL] hi all

2009-02-17 Thread David Fetter
On Tue, Feb 17, 2009 at 04:40:58PM +, Sam Mason wrote: user_name varchar(50) NOT NULL, As a general design question; should user_name have a UNIQUE constraint on it? i.e. user_name VARCHAR(50) NOT NULL UNIQUE, Yes, it's good to have a UNIQUE constraint, but not this one. To

Re: [GENERAL] Good Delimiter for copy command

2009-02-17 Thread David Fetter
On Tue, Feb 17, 2009 at 05:17:40PM +0100, Marco Colombo wrote: Tom Lane wrote: Andrew Gould andrewlylego...@gmail.com writes: To the list: Does pg_dump escape characters that are the same as the delimiter? Yes. The OP has not actually explained why he needs to pick a nondefault

Re: [GENERAL] Good Delimiter for copy command

2009-02-17 Thread Marco Colombo
Tom Lane wrote: Andrew Gould andrewlylego...@gmail.com writes: To the list: Does pg_dump escape characters that are the same as the delimiter? Yes. The OP has not actually explained why he needs to pick a nondefault delimiter, unless maybe it is that he wants to feed the dump to some

Re: [GENERAL] Good Delimiter for copy command

2009-02-17 Thread Tom Lane
David Fetter da...@fetter.org writes: On Tue, Feb 17, 2009 at 05:17:40PM +0100, Marco Colombo wrote: Is it possibile to use some unicode character which is unlikely to appear in the data set as delimiter? Something like U+FFFC. No. The delimiter needs to be one byte long at the moment. The

[GENERAL] pg_dump -F f

2009-02-17 Thread Howard Cole
I notice in the pg_dump code that there is a demonstration version of dumping to multiple files (-F f). Is this a work in progress or was it abandoned for a good reason - and could it be used in a production environment? I ask because it would be really useful to me because I could do a

Re: [GENERAL] pg_dump -F f

2009-02-17 Thread Tom Lane
Howard Cole howardn...@selestial.com writes: I notice in the pg_dump code that there is a demonstration version of dumping to multiple files (-F f). Is this a work in progress or was it abandoned for a good reason - and could it be used in a production environment? I ask because it would be

[GENERAL] References on Text Mining (and PgSQL/FTS of course)

2009-02-17 Thread Mirko Pace
Hi all! I'm looking for references about text mining methods (or text categorization in general), as much detailed as possible: algorithms, test cases, examples... of course better if using PgSQL and/or FTS ;) Why: I'm studing a categorization method using Support Vector Machines (then

[GENERAL] could not write block

2009-02-17 Thread Kopljan Michael
recently appears to me the following error : could not write block 86 of relation 1663/121027/151994: Invalid argument What is that error and how to fix this? Michael.

Re: [GENERAL] hi all

2009-02-17 Thread Scott Marlowe
On Tue, Feb 17, 2009 at 5:54 AM, Kusuma Pabba kusu...@ncoretech.com wrote: while in pgsql i am thinking of to use the same as follows: CREATE TABLE users ( user_id int(11) NOT NULL serial, I see this construct in mysql all the time. Funny thing is most mysql users think it means an 11

Re: [GENERAL] transfering tables into other schema

2009-02-17 Thread Ivan Sergio Borgonovo
On Tue, 17 Feb 2009 17:36:32 + Sam Mason s...@samason.me.uk wrote: On Tue, Feb 17, 2009 at 06:20:54PM +0100, Ivan Sergio Borgonovo wrote: I can't get how this really work. You're saying that constraint, fk/pk relationships will be preserved automatically... what else? OK BEFORE:

Re: [GENERAL] transfering tables into other schema

2009-02-17 Thread Scott Marlowe
On Tue, Feb 17, 2009 at 3:31 PM, Ivan Sergio Borgonovo m...@webthatworks.it wrote: On Tue, 17 Feb 2009 17:36:32 + Sam Mason s...@samason.me.uk wrote: On Tue, Feb 17, 2009 at 06:20:54PM +0100, Ivan Sergio Borgonovo wrote: I can't get how this really work. You're saying that constraint,

Re: [GENERAL] Strange Grant behavior in postgres 8.3

2009-02-17 Thread Schwaighofer Clemens
Sorry for some confusion. I re-created the whole thing again with fresh users and a fresh database: (1) Create a new user and a new db, also create a table 'test' inside with user 'foo' $ createuser -U postgres -P -E foo $ createdb -U postgres -O foo -E utf8 foo_test (2) create a second user $

Re: [GENERAL] transfering tables into other schema

2009-02-17 Thread Schwaighofer Clemens
On Wed, Feb 18, 2009 at 07:31, Ivan Sergio Borgonovo m...@webthatworks.it wrote: alter table y set schema new_schema; test=# SELECT * from x(); ERROR: relation y does not exist CONTEXT: SQL statement select a,b from x join y on x.xid=y.xid PL/pgSQL function x line 2 at RETURN QUERY

Re: [GENERAL] Strange Grant behavior in postgres 8.3

2009-02-17 Thread John R Pierce
Schwaighofer Clemens wrote: So what do I do wrong? Even if I do the GRANT command as user 'foo' who is the database owner, I still cannot select with the user 'bar'. It only works if I set GRANT rights for the TABLE itself: as user 'foo' logged in = grant all on table test to bar; that is

Re: [GENERAL] Strange Grant behavior in postgres 8.3

2009-02-17 Thread Clemens Schwaighofer
On 02/18/2009 01:15 PM, John R Pierce wrote: Schwaighofer Clemens wrote: So what do I do wrong? Even if I do the GRANT command as user 'foo' who is the database owner, I still cannot select with the user 'bar'. It only works if I set GRANT rights for the TABLE itself: as user 'foo' logged in

[GENERAL] Appending \o output instead of overwriting the output file

2009-02-17 Thread Brent Wood
Hi, Using \o to redirect output to a file from the psql command line, is there any way to have the output appended to the output file, rather than overwriting it? Thanks, Brent Woood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand NIWA is the trading name of the National

Re: [GENERAL] transfering tables into other schema

2009-02-17 Thread Scott Marlowe
On Tue, Feb 17, 2009 at 8:42 PM, Schwaighofer Clemens clemens.schwaigho...@tequila.jp wrote: On Wed, Feb 18, 2009 at 07:31, Ivan Sergio Borgonovo m...@webthatworks.it wrote: alter table y set schema new_schema; test=# SELECT * from x(); ERROR: relation y does not exist CONTEXT: SQL

Re: [GENERAL] hi all

2009-02-17 Thread Craig Ringer
Kusuma Pabba wrote: when creating tables, in my sql i have used create table By my SQL I assume you mean MySQL? It took me a minute to figure out that you didn't mean in my sql code but rather in the product MySQL. Having read your proposed table definition: I very strongly recommend that

Re: [GENERAL] Appending \o output instead of overwriting the output file

2009-02-17 Thread Tom Lane
Brent Wood b.w...@niwa.co.nz writes: Using \o to redirect output to a file from the psql command line, is there any way to have the output appended to the output file, rather than overwriting it? This is pretty grotty, but it works: \o | cat target Maybe we should provide another way in

Re: [GENERAL] Strange Grant behavior in postgres 8.3

2009-02-17 Thread John R Pierce
Clemens Schwaighofer wrote: The other problem is, that there is no grant all on table db.* ... but I have to do that for each table seperate, or in a grant all on table a, b, I am not sure if there is an easier way, except perhaps through a select from the pg_ catalog for this db and get

[GENERAL] Download link for 'Bristlecone'

2009-02-17 Thread Abdul Rahman
Dear All, I searched a lot but failed to find any download link for Bristlecone. Kindly help me. Or let me know about any open source Load Tester for PostgreSQL based application. Ashish, according to my search Bristlecone is open source. Thanks in advance. Regards, Abdul Rehman.

Re: [GENERAL] hi all

2009-02-17 Thread Sim Zacks
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Grzegorz Jaśkiewicz wrote: just outside of your question, you should read about normalization. it is in general very bad idea to have a table that holds all information possible . I wouldn't say this table is not normalized. The only fields you

Re: [GENERAL] Appending \o output instead of overwriting the output file

2009-02-17 Thread Abdul Rahman
Simply use '\o filename' as you are doing so. Write the queries as much as you want. It will automatically append the result in the specified file. Untill you use '\o' command again with new file name.

Re: [GENERAL] Strange Grant behavior in postgres 8.3

2009-02-17 Thread Schwaighofer Clemens
On Wed, Feb 18, 2009 at 16:09, John R Pierce pie...@hogranch.com wrote: Clemens Schwaighofer wrote: The other problem is, that there is no grant all on table db.* ... but I have to do that for each table seperate, or in a grant all on table a, b, I am not sure if there is an easier