Re: [GENERAL] COPY ERROR

[Albe Laurenz]

paulo matadr wrote:
 When I try to import big file base.txt( 700MB),I get this:
 
 x=# create table arquivo_serasa_marco( varchar(3000));
 x=# COPY arquivo_serasa_marco from 
 '/usr/local/pgsql/data/base.txt';
 ERROR:  literal newline found in data
 HINT:  Use \n to represent newline.
 CONTEXT:  COPY arquivo_serasa_marco, line 2: 
 
 How can find solution for this?

The file does not seem to be in valid COPY format.

What is the format of the file?

Yours,
Laurenz Albe

-- 
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] How to implement word wrap

[Andrus]

Just realised that's not what you're after, but my first point still 
stands.


Thank you.
I tried to wrap words at 15 characters using code below.

Issues:

1. Table rows places same word to multiple lines. How to remove them so that 
every word appears only in single row?
2. In last select   sum(word||' ')  causes error. How to concatenate words 
bact to row (inverse of unnest() function ?


Andrus.


create temp table words( id serial, word text ) on commit drop;
insert into words (word) select * from unnest(string_to_array('Quick brown 
fox runs in forest.',' '));


create temp table results on commit drop as
select
 first.id as first,
 last.id as last,
 sum(length(a.word)+1) as charcount
from words a, words first, words last
where a.id between first.id and last.id
group by 1,2
having sum(length(a.word)+1)15;

create temp table maxr on commit drop as
select
 first,
 max(charcount) as charcount
from results
group by 1;

create temp table rows on commit drop as
select
 first,
 last
from results
join maxr using (first,charcount)
order by 1;

select
 rows.first,
 sum(word||' ')
from rows, words
where words.id between first and last
group by 1
order by 1, words.id 



--
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] Get the list of permissions/privileges on schema

[dipti shah]

Thanks Ashesh, I ran below command and it is listing all privileges of
objects under mydb schema. Actually, I want to know what are the permissions
user1 has on mydb schema. Could you please tell me how to do this?

mydb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn where
pc.relnamespace=pn.oid and pn.nspname='mydb';
 relname  |relacl
--+---
  mylog   |
{postgres=arwdDxt/postgres,=arwdDxt/postgres}
  techtable   | {postgres=arwdDxt/postgres,=ar/postgres}
  techtable_log   |
  hrtable | {postgres=arwdDxt/postgres,=ar/postgres}
  hrtable_log |
(5 rows)


mydb= select current_user;
 current_user
--
 user1
(1 row)

mydb=

Thanks,
Dipti

On Thu, Mar 25, 2010 at 2:44 PM, Ashesh Vashi ashesh.va...@enterprisedb.com
 wrote:

 You should look into the pg_class table : relacl attribute for the
 permissions on any object.

 --
 Thanks  Regards,

 Ashesh Vashi
 EnterpriseDB INDIA: Enterprise Postgres Companyhttp://www.enterprisedb.com

 On Thu, Mar 25, 2010 at 2:37 PM, dipti shah shahdipti1...@gmail.comwrote:

 Hi,

 Could any one please tell me how to get list of all the permissions on the
 schema (or any postgresql objects), stored them somewhere before executing
 stored procedure and then restore them?

 Thanks,
 Dipti

Re: [GENERAL] insert into test_b (select * from test_a) with different column order

[Ole Tange]

On Mon, Mar 29, 2010 at 5:09 PM, Leif Biberg Kristensen
l...@solumslekt.org wrote:
 On Monday 29. March 2010 16.51.35 Ole Tange wrote:

 I would like to do this:

   insert into test_b (select * from test_a);

 Per the SQL standard, there's no inherent order between columns. That said,
 you'll usually get the columns in the order that they were created, but
 there's no guarantee for it.

And the create order in my case is (for all practical purposes) random.

 Actually, when you do a SELECT * FROM ... you
 make a totally unwarranted assumption that the columns will come out in any
 specific order.

I had hoped the INSERT would be intelligent enough to use the column
names and match on these.

 So, the answer to your question is to specify the columns
 explicitly in your query, as

 insert into test_b (select col_b, col_a from test_a);

This will not work for me as I do not know in advance what columns
exist in test_a or test_b. I only know they are called the same (and
have the same datatypes).

So is there a dynamic way in which I can generate the INSERT statement
given the name of the two tables?

Maybe something like listing all columns in test_b in the order that
test_b wants them and from this create the SELECT statement and
execute it?


/Ole

-- 
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] How to implement word wrap

[Alban Hertroys]

On 30 Mar 2010, at 11:32, Andrus wrote:

 Just realised that's not what you're after, but my first point still stands.
 
 Thank you.
 I tried to wrap words at 15 characters using code below.


Really, write a stored procedure that accepts (text, line_length) and returns 
SETOF text. You could even add hyphenation for the appropriate language if you 
go that route. For the latter it's probably best to write it in C so you can 
link hyphenation libraries to your code.

Another approach that may be viable is to use windowing functions, but I'm not 
so sure it's possible to have a window that is being defined by the data it's 
running over (eg. a window defined by the length of an accumulated line of 
text).

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4bb1d23410411798520618!



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

[GENERAL] Emphasizing current item in subclass of QAbstractItemView

[Davor J.]

By default, there is some barely visible dotted rectangle around the 
QItemSelectionModel::currentIndex (). Has anyone suggestions how to change 
this efficiently. (i.e. I think adjusting the model data with setData() and 
Qt::FontRole or Qt::BackgroundRole or something similar isn't the right way 
to do this.) So has anyone any other suggestions?

Kind regards,
Davor 



-- 
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] insert into test_b (select * from test_a) with different column order

[Szymon Guz]

 This will not work for me as I do not know in advance what columns
 exist in test_a or test_b. I only know they are called the same (and
 have the same datatypes).

 So is there a dynamic way in which I can generate the INSERT statement
 given the name of the two tables?


You can write a procedure e.g. in pl/pgsql that will check the column names
from a system view like pg_* (I don't remember now) and create the query
from the column names and some sql keywords into a text variable. Later you
can use EXECUTE for executing such a query from a variable.


regards
Szymon Guz

[GENERAL] Get the list of permissions on schema for current user

[dipti shah]

Hi,

I ran below command to list out all privileges of objects if mydb schema.
Actually, I want to know what are the permissions user1 has on mydb
schema. Could you please tell me how to do this?

mydb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn where
pc.relnamespace=pn.oid and pn.nspname='mydb';
 relname  |relacl
--+---
  mylog   |
{postgres=arwdDxt/postgres,=arwdDxt/postgres}
  techtable   | {postgres=arwdDxt/postgres,=ar/postgres}
  techtable_log   |
  hrtable | {postgres=arwdDxt/postgres,=ar/postgres}
  hrtable_log |
(5 rows)


mydb= select current_user;
 current_user
--
 user1
(1 row)

mydb=

Thanks, Dipti

[GENERAL] Running/cumulative count using windows

[Oliver Kohll - Mailing Lists]

Hello,

I'm still reasonably new to windowing functions, having used a few since 8.4 
came out. I wonder if anyone can help with this one.

I've got a table of email addresses in a CRM system similar to the following:

CREATE TABLE test(
  signup_date timestamp,
  email_address varchar(1000)
);
INSERT INTO test(signup_date, email_address) VALUES(now(), 't...@test.com');
INSERT INTO test(signup_date, email_address) VALUES(now(), 't...@test1.com');
INSERT INTO test(signup_date, email_address) VALUES(now() - '1 
month'::interval, 't...@test2.com');

I'd like a running count, or cumulative count of the number of signups per 
month. I'm pretty sure a window function would do it but I can't work it out.

So a plain count by month would be

SELECT date_part('year',signup_date) as year, date_part('month',signup_date) as 
month, count(*)
FROM test
GROUP BY year, month
ORDER BY year, month;

giving

 year | month | count 
--+---+---
 2010 | 2 | 1
 2010 | 3 | 2

How would you make the count a cumulative one? The output should then be

 year | month | count 
--+---+---
 2010 | 2 | 1
 2010 | 3 | 3


Regards
Oliver Kohll


oli...@agilebase.co.uk / +44(0)845 456 1810 / skype:okohll
www.agilebase.co.uk - software
www.gtwm.co.uk - company

Re: [GENERAL] Running/cumulative count using windows

[A. Kretschmer]

In response to Oliver Kohll - Mailing Lists :
 Hello,
 
 I'm still reasonably new to windowing functions, having used a few since 8.4
 came out. I wonder if anyone can help with this one.
 
 I've got a table of email addresses in a CRM system similar to the following:
 
 CREATE TABLE test(
   signup_date timestamp,
   email_address varchar(1000)
 );
 INSERT INTO test(signup_date, email_address) VALUES(now(), 't...@test.com');
 INSERT INTO test(signup_date, email_address) VALUES(now(), 't...@test1.com');
 INSERT INTO test(signup_date, email_address) VALUES(now() - '1
 month'::interval, 't...@test2.com');
 
 I'd like a running count, or cumulative count of the number of signups per
 month. I'm pretty sure a window function would do it but I can't work it out.
 
 So a plain count by month would be
 
 SELECT date_part('year',signup_date) as year, date_part('month',signup_date) 
 as
 month, count(*)
 FROM test
 GROUP BY year, month
 ORDER BY year, month;
 
 giving
 
  year | month | count 
 --+---+---
  2010 | 2 | 1
  2010 | 3 | 2
 
 How would you make the count a cumulative one? The output should then be
 
  year | month | count 
 --+---+---
  2010 | 2 | 1
  2010 | 3 | 3
 

test=*# select * from test;
signup_date | email_address
+
 2010-03-30 13:12:17.908418 | t...@test.com
 2010-03-30 13:12:17.908418 | t...@test1.com
 2010-02-28 13:12:17.908418 | t...@test2.com
(3 rows)

test=*# select extract (year from signup_date)::text || '/' ||
extract(month from signup_date)::text, count(email_address),
sum(count(email_address)) over (ROWS UNBOUNDED PRECEDING) from test
group by 1 order by 1;
 ?column? | count | sum
--+---+-
 2010/2   | 1 |   1
 2010/3   | 2 |   3
(2 rows)


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: - Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

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

[GENERAL] different behaviour between select and delete when constraint_exclusion = partition

[Marc Cousin]

Hi, I don't know if what's below is a bug or simply not implemented. And I 
don't really know if, when in doubt, like now, I'd rather pollute general or 
bugs :)

Anyway here is the problem : when using constraint_exclusion=partition, a 
delete query scans all partitions, when the same query rewritten as a select 
is removing partitions as expected. When constraint_exclusion=on, the 
partition removal works as expected with the delete too. I've tested it on 8.4 
and 9.0

Anyway here is the test case to demonstrate this :

CREATE TABLE test (
a integer
);
CREATE TABLE a1 (CONSTRAINT a1_a_check CHECK ((a = 1))
)
INHERITS (test);
CREATE TABLE a2 (CONSTRAINT a2_a_check CHECK ((a = 2))
)
INHERITS (test);
CREATE TABLE a3 (CONSTRAINT a3_a_check CHECK ((a = 3))
)
INHERITS (test);

Everything is empty…


marc=# SHOW constraint_exclusion ;
 constraint_exclusion 
--
 partition
(1 row)

This works :

marc=# EXPLAIN SELECT * FROM test WHERE a=1;
 QUERY PLAN  
-
 Result  (cost=0.00..80.00 rows=24 width=4)
   -  Append  (cost=0.00..80.00 rows=24 width=4)
 -  Seq Scan on test  (cost=0.00..40.00 rows=12 width=4)
   Filter: (a = 1)
 -  Seq Scan on a1 test  (cost=0.00..40.00 rows=12 width=4)
   Filter: (a = 1)
(6 rows)


This doesn't :

marc=# EXPLAIN DELETE FROM test WHERE a=1;
  QUERY PLAN   
---
 Delete  (cost=0.00..160.00 rows=48 width=6)
   -  Seq Scan on test  (cost=0.00..40.00 rows=12 width=6)
 Filter: (a = 1)
   -  Seq Scan on a1 test  (cost=0.00..40.00 rows=12 width=6)
 Filter: (a = 1)
   -  Seq Scan on a2 test  (cost=0.00..40.00 rows=12 width=6)
 Filter: (a = 1)
   -  Seq Scan on a3 test  (cost=0.00..40.00 rows=12 width=6)
 Filter: (a = 1)
(9 rows)


When putting constraint_exclusion to on :

marc=# SET constraint_exclusion TO on;
SET
marc=# EXPLAIN DELETE FROM test WHERE a=1;
  QUERY PLAN   
---
 Delete  (cost=0.00..80.00 rows=24 width=6)
   -  Seq Scan on test  (cost=0.00..40.00 rows=12 width=6)
 Filter: (a = 1)
   -  Seq Scan on a1 test  (cost=0.00..40.00 rows=12 width=6)
 Filter: (a = 1)
(5 rows)



Still, I don't know if this qualifies as a bug.

Cheers, 

Marc

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

[GENERAL] createuser: could not connect to database postgres: FATAL: password authentication failed for user postgres

[moataz Elmasry]

Hi List

This question has already been asked many times, but I didn't find the 
answer I'm looking for, so here goes


I use postgresql 8.3 on suse 11.2

I already installed postgresql many times wit postgis support on 
debian/ubuntu machines. Never had problems, now I'm installing it for 
the first time on suse linux. After installing, I changed the following 
in postgresql.conf for a production environment

shared_buffers=128MB
checkpoint_segments=20
maintenance_work_mem=256MB
autovacum=off

And then I restarted postgresql and switched to postgres user, and then:
createuser gisuser
After typing password the console gives the error message createuser: 
could not connect to database postgres: FATAL:  password authentication 
failed for user postgres


So I change the user postgres password using the command passwd as root 
and I give in the same password and I repeat the createuser command (or 
createdb whatever), only to get the same error message


Can someone see what the problem might be?

Best regards
Moataz


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

[GENERAL] createuser: could not connect to database postgres: FATAL: password authentication failed for user postgres

[moataz Elmasry]

Hi List

This question has already been asked many times, but I didn't find the 
answer I'm looking for, so here goes


I use postgresql 8.3 on suse 11.2

I already installed postgresql many times wit postgis support on 
debian/ubuntu machines. Never had problems, now I'm installing it for 
the first time on suse linux. After installing, I changed the following 
in postgresql.conf for a production environment

shared_buffers=128MB
checkpoint_segments=20
maintenance_work_mem=256MB
autovacum=off

And then I restarted postgresql and switched to postgres user, and then:
createuser gisuser
After typing password the console gives the error message createuser: 
could not connect to database postgres: FATAL:  password authentication 
failed for user postgres


So I change the user postgres password using the command passwd as root 
and I give in the same password and I repeat the createuser command (or 
createdb whatever), only to get the same error message


Can someone see what the problem might be?

Best regards
Moataz

--
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] Processor speed relative to postgres transactions per second

[Merlin Moncure]

On Mon, Mar 29, 2010 at 12:42 PM, Chris Barnes
compuguruchrisbar...@hotmail.com wrote:

 We have two camps that think that the speed of cpu processors is/aren't
 relative to the number of transactions that postgres that can performed per
 second.

 I am of the opinion that is we throw the faster processors at the database
 machine, there will be better performance.

which tastes better, a round fruit or a oval fruit? :-).

postgres can become i/o bound or cpu bound depending on the
application, or specific things you are doing.  if your application is
highly latency sensitive, then more cpu power is always nice.

cpu and i/o have completely different cost/performance scaling metrics:
cpu is very cheap to scale up to a point (when you hit limits of x86
at current levels) then becomes extremely expensive.  cpu bound
problems tend to degrade relatively well when your limit is hit.

i/o is expensive to scale but has relatively linear relationship
between cost and performance.  i/o bottleneck can bring your server to
a crawl, and sometimes comes out of nowhere when you nudge the work
the db has to do just a hair exceeding your system's ability to cope.

merlin

-- 
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] different behaviour between select and delete when constraint_exclusion = partition

[Tom Lane]

Marc Cousin cousinm...@gmail.com writes:
 Anyway here is the problem : when using constraint_exclusion=partition, a 
 delete query scans all partitions, when the same query rewritten as a select 
 is removing partitions as expected. When constraint_exclusion=on, the 
 partition removal works as expected with the delete too. I've tested it on 
 8.4 
 and 9.0

Hmm, this seems like a shortcoming in the constraint_exclusion=partition
feature.  The reason it doesn't work is that inheritance expansion of a
DELETE/UPDATE target rel is handled entirely differently from expansion
of a SELECT source. But it seems like it would be desirable if it did
work.

regards, tom lane

-- 
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] createuser: could not connect to database postgres: FATAL: password authentication failed for user postgres

[Andy Colson]

On 3/30/2010 8:55 AM, moataz Elmasry wrote:

Hi List

This question has already been asked many times, but I didn't find the
answer I'm looking for, so here goes

I use postgresql 8.3 on suse 11.2

I already installed postgresql many times wit postgis support on
debian/ubuntu machines. Never had problems, now I'm installing it for
the first time on suse linux. After installing, I changed the following
in postgresql.conf for a production environment
shared_buffers=128MB
checkpoint_segments=20
maintenance_work_mem=256MB
autovacum=off

And then I restarted postgresql and switched to postgres user, and then:
createuser gisuser
After typing password the console gives the error message createuser:
could not connect to database postgres: FATAL: password authentication
failed for user postgres

So I change the user postgres password using the command passwd as root
and I give in the same password and I repeat the createuser command (or
createdb whatever), only to get the same error message

Can someone see what the problem might be?

Best regards
Moataz



It could be the pg_hba file.  Default has local set to trust... maybe 
suse changed to it md5 or something... ya know.. to be secure :-)


-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] User action accounting

[Joshua Berry]

Hello All,

I have a few PHP/Clarion based applications that don't currently track who
created and modified records. I'd like to be able to track all user and
timestamp pairs for INSERT/UPDATEs by way of triggers.

The problem is that I currently use the same role name for each instance of
the application, so current_user is not particularly helpful.  So I have a
few ideas that I wanted to bounce off the experts here:
1. Should I use seperate PG roles for each user? Is there a way of
permitting user names queried against a RADIUS server to inherit a role
allowing the needed permissions (trusting that the RADIUS server is secured)
and allowing the requested name to be used without having to maintain two
lists of accounts?
2. Should I stay with using the same role for the application, but somehow
store a per session variable that would have the user's login name and be
accessible by the triggers?

Anyhow, the goal is to be able to note which of the 40 users
created/modified records in the backend. I'm sure that this has been solved
by each person and has been asked a million times... I'm just not sure where
to begin with Google/postgresql.net queries! Please feel free to reply with
a helpful search query or URL.

Kind Regards,
-Joshua

Joshua Berry

Re: [GENERAL] hstore equality-index performance question

[Stefan Keller]

You are right, my negligence.

I'm trying to optimize the latter query:
# SELECT id, (kvp-'a') FROM mytable WHERE kvp ? 'a';

...or something like this (which also involves the '-' operator)
# SELECT id FROM mytable WHERE (kvp-'a') = 'x';

-S.

2010/3/29 Sergey Konoplev gray...@gmail.com:
 My question is, if one can get also index support for the '-' operator?

 I am not sure what do you mean.

  SELECT id, (kvp-'a') FROM mytable;

 ... can be accelerated nevertheless by adding following where clause:

  SELECT id, (kvp-'a') FROM mytable WHERE kvp ? 'a';

 = Is this correct?

 These queries could return completely different result sets. First
 query returns all the records with the value of kvp-'a' if kvp has
 'a' key and NULL otherwise. Second one returns only those records
 where kvp has 'a' key.

 --
 Sergey Konoplev

 Blog: http://gray-hemp.blogspot.com /
 Linkedin: http://ru.linkedin.com/in/grayhemp /
 JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802


-- 
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] hstore equality-index performance question

[Tom Lane]

Stefan Keller sfkel...@gmail.com writes:
 I'm trying to optimize the latter query:
 # SELECT id, (kvp-'a') FROM mytable WHERE kvp ? 'a';

The hstore gist and gin opclasses contain support for that.

 ...or something like this (which also involves the '-' operator)
 # SELECT id FROM mytable WHERE (kvp-'a') = 'x';

You could transform this into a gist/gin indexable query

kvp @ ('a' = 'x')

although I think the actually indexed part of it is just the search for
rows that contain key 'a', so it's not really any better than

kvp ? 'a' AND (kvp-'a') = 'x'

performance-wise.

regards, tom lane

-- 
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 action accounting

[Andy Colson]

On 3/30/2010 10:03 AM, Joshua Berry wrote:

Hello All,

I have a few PHP/Clarion based applications that don't currently track
who created and modified records. I'd like to be able to track all user
and timestamp pairs for INSERT/UPDATEs by way of triggers.

The problem is that I currently use the same role name for each instance
of the application, so current_user is not particularly helpful.  So I
have a few ideas that I wanted to bounce off the experts here:
1. Should I use seperate PG roles for each user? Is there a way of
permitting user names queried against a RADIUS server to inherit a role
allowing the needed permissions (trusting that the RADIUS server is
secured) and allowing the requested name to be used without having to
maintain two lists of accounts?
2. Should I stay with using the same role for the application, but
somehow store a per session variable that would have the user's login
name and be accessible by the triggers?

Anyhow, the goal is to be able to note which of the 40 users
created/modified records in the backend. I'm sure that this has been
solved by each person and has been asked a million times... I'm just not
sure where to begin with Google/postgresql.net http://postgresql.net
queries! Please feel free to reply with a helpful search query or URL.

Kind Regards,
-Joshua

Joshua Berry



When your app/users connect to the db, do they connect as the same user, 
or each with a different username?


Do you have your own users table?

-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] createuser: could not connect to database postgres: FATAL: password authentication failed for user postgres

[moataz Elmasry]

Oh boy, thnx a mil. That solved my problem  :) .
I also found another tip somewhere to change the password not using 
passwd but the sql command
alter role myuser encrypted password 'mypassword' and that works also 
with md5.
and you are right. suse changed 'trust' to 'md5' and reverting it also 
solved the problem


Again, thnx alot
Moataz



Andy Colson wrote:

On 3/30/2010 8:55 AM, moataz Elmasry wrote:

Hi List

This question has already been asked many times, but I didn't find the
answer I'm looking for, so here goes

I use postgresql 8.3 on suse 11.2

I already installed postgresql many times wit postgis support on
debian/ubuntu machines. Never had problems, now I'm installing it for
the first time on suse linux. After installing, I changed the following
in postgresql.conf for a production environment
shared_buffers=128MB
checkpoint_segments=20
maintenance_work_mem=256MB
autovacum=off

And then I restarted postgresql and switched to postgres user, and then:
createuser gisuser
After typing password the console gives the error message createuser:
could not connect to database postgres: FATAL: password authentication
failed for user postgres

So I change the user postgres password using the command passwd as root
and I give in the same password and I repeat the createuser command (or
createdb whatever), only to get the same error message

Can someone see what the problem might be?

Best regards
Moataz



It could be the pg_hba file.  Default has local set to trust... maybe 
suse changed to it md5 or something... ya know.. to be secure :-)


-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] createuser: could not connect to database postgres: FATAL: password authentication failed for user postgres

[moataz Elmasry]

Oh boy, thnx a mil. That solved my problem :).
I also found another tip somewhere to change the password not using 
passwd but the sql command
alter role myuser encrypted password 'mypassword' and that works also 
with md5.
and you are right. suse changed 'trust' to 'md5' and reverting it also 
solved the problem


Again, thnx alot
Moataz

Andy Colson wrote:

On 3/30/2010 8:55 AM, moataz Elmasry wrote:
  

Hi List

This question has already been asked many times, but I didn't find the
answer I'm looking for, so here goes

I use postgresql 8.3 on suse 11.2

I already installed postgresql many times wit postgis support on
debian/ubuntu machines. Never had problems, now I'm installing it for
the first time on suse linux. After installing, I changed the following
in postgresql.conf for a production environment
shared_buffers=128MB
checkpoint_segments=20
maintenance_work_mem=256MB
autovacum=off

And then I restarted postgresql and switched to postgres user, and then:
createuser gisuser
After typing password the console gives the error message createuser:
could not connect to database postgres: FATAL: password authentication
failed for user postgres

So I change the user postgres password using the command passwd as root
and I give in the same password and I repeat the createuser command (or
createdb whatever), only to get the same error message

Can someone see what the problem might be?

Best regards
Moataz




It could be the pg_hba file.  Default has local set to trust... maybe 
suse changed to it md5 or something... ya know.. to be secure :-)


-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 action accounting

[Joshua Berry]

On Tue, Mar 30, 2010 at 10:46 AM, Andy Colson a...@squeakycode.net wrote:

 When your app/users connect to the db, do they connect as the same user, or
 each with a different username?


The application instances each connect to the database with the same
username. The application currently uses an ODBC connection which has hard
coded username values. If each user has their own workstation, this would be
easy, but I want to be able to specify the username when the application
begins. I'm not worried about the security aspect; I just want to present
users with an easy way to specify who they are to aid in tracking.


 Do you have your own users table?


There is a users table currently used for another purpose, but it could be
reused/extended.

If I go the route of keeping the same role for each application instance, it
would be great if I could avoid having to pass the username into each query
and instead have a per-session or per-connection variable that the trigger
could access. Sounds easy, but I've never tried it before and things not
usually as easy as they seem.

Regards,

-Joshua

Re: [GENERAL] Dblink vs calling a function that returns void

[Merlin Moncure]

On Mon, Mar 29, 2010 at 12:12 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Boszormenyi Zoltan z...@cybertec.at writes:
 I need to call a function via dblink that returns a void, i.e.
 technically nothing.

 You're overthinking the problem.  Imagine void is just a datatype
 (which it is...)  This should work:

but it isn't! void returning functions may not be queried over the
binary protocol (why?), so if you used dblink w/void returning
functions, and dblink later supported binary results, your code would
break (i'm sure such a thing would be optional, but the point stands).

merlin

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

[GENERAL] Converting time interval to double precision of time unit

[Mike Toews]

I'm using 8.3, and I'm trying to work with the interval type, and I
can't seem to get things right. I've been all over the docs[1,2], and
there is no mention on how this can be done.

While I can get:
SELECT '3 day 2 hour 34 minute'::interval

.. how can then get the fractional hours of this time interval in
double precision (or seconds, minutes, years, decades, etc.)?

Do I really need to extract the time subcomponents and do the math myself?

For example:
SELECT extract(day from interval)*24 + extract(hour from interval) +
extract(minute from interval)/60 as hours
FROM (SELECT '3 day 2 hour 34 minute'::interval) AS foo;

This seem like a bad hack, and I can't believe a function doesn't
already exist to properly cast a time interval to a fractional unit of
time, so I thought I'd check up to see if there is a better solution.

Thanks,

-Mike

[1] http://www.postgresql.org/docs/8.3/static/functions-datetime.html
[2] http://www.postgresql.org/docs/8.3/static/datatype-datetime.html

-- 
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 action accounting

[Andy Colson]

On 3/30/2010 11:13 AM, Joshua Berry wrote:


On Tue, Mar 30, 2010 at 10:46 AM, Andy Colson a...@squeakycode.net
mailto:a...@squeakycode.net wrote:

When your app/users connect to the db, do they connect as the same
user, or each with a different username?


The application instances each connect to the database with the same
username. The application currently uses an ODBC connection which has
hard coded username values. If each user has their own workstation, this
would be easy, but I want to be able to specify the username when the
application begins. I'm not worried about the security aspect; I just
want to present users with an easy way to specify who they are to aid in
tracking.

Do you have your own users table?


There is a users table currently used for another purpose, but it
could be reused/extended.

If I go the route of keeping the same role for each application
instance, it would be great if I could avoid having to pass the username
into each query and instead have a per-session or per-connection
variable that the trigger could access. Sounds easy, but I've never
tried it before and things not usually as easy as they seem.

Regards,

-Joshua


I ask because there is a CURRENT_UESR you can use in a trigger.  It is 
who you connect to the db as.  Which in your case all users would have 
the same name.  But.. there is also a set role:


http://www.postgresql.org/docs/8.4/static/sql-set-role.html

So after you connect you could fire off a set role bob, and the 
triggers would use 'bob' as current_user.


Or something like that.  You'd also have to create all the users on the 
pg side (create role...).  I have not done this, its just in theory it 
should work.


-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] Dblink vs calling a function that returns void

[Tom Lane]

Merlin Moncure mmonc...@gmail.com writes:
 You're overthinking the problem.  Imagine void is just a datatype
 (which it is...)  This should work:

 but it isn't! void returning functions may not be queried over the
 binary protocol (why?),

Probably because we never made a send function for type void.  Might
be worth fixing someday.

regards, tom lane

-- 
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 action accounting

[Steve Atkins]

On Mar 30, 2010, at 8:03 AM, Joshua Berry wrote:

 Hello All,
 
 I have a few PHP/Clarion based applications that don't currently track who 
 created and modified records. I'd like to be able to track all user and 
 timestamp pairs for INSERT/UPDATEs by way of triggers.
 
 The problem is that I currently use the same role name for each instance of 
 the application, so current_user is not particularly helpful.  So I have a 
 few ideas that I wanted to bounce off the experts here:
 1. Should I use seperate PG roles for each user? Is there a way of permitting 
 user names queried against a RADIUS server to inherit a role allowing the 
 needed permissions (trusting that the RADIUS server is secured) and allowing 
 the requested name to be used without having to maintain two lists of 
 accounts?
 2. Should I stay with using the same role for the application, but somehow 
 store a per session variable that would have the user's login name and be 
 accessible by the triggers?
 
 Anyhow, the goal is to be able to note which of the 40 users created/modified 
 records in the backend. I'm sure that this has been solved by each person and 
 has been asked a million times... I'm just not sure where to begin with 
 Google/postgresql.net queries! Please feel free to reply with a helpful 
 search query or URL.

I create a one-row temporary table with information about the current user in 
it at the beginning of each connection and audit triggers that need to know the 
current application user use that table. (There's also an underlying 
non-temporary table so that stuff doesn't break during ad-hoc updates).

I'm not sure whether that's a good approach, but it seems to work well and 
means the database doesn't need to be aware of the users accessing it (which is 
more than just authentication, but also creating and revoking users).

The main downside is that you can't use it with any sort of connection pooling.

Cheers,
  Steve


-- 
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] set statement_timeout does not work

[Jun Wang]

Hi,

I try to set the statement_timeout so that select pg_stop_backup();
will not hang if archive command failed. Below are the command and

Can somebody help on this?

Thanks.


* From: Jun Wang junw2...@gmail.com
* To: pgsql-general@postgresql.org
* Subject: set statement_timeout does not work
* Date: Mon, 29 Mar 2010 19:33:55 -0700
* Message-id: deff9e831003291933k63585027h5afcc1451f91d...@mail.gmail.com


Hi,

I try to set the statement_timeout so that select pg_stop_backup();
will not hang if archive command failed. Below are the command and
errors.

psql.exe -d mydb -h myhost -p 5432 -U postgres -w -c set
statement_timeout = 1000; select pg_stop_backup();
WARNING:  pg_stop_backup still waiting for archive to complete (60
seconds elapsed)
WARNING:  pg_stop_backup still waiting for archive to complete (120
seconds elapsed)
WARNING:  pg_stop_backup still waiting for archive to complete (240
seconds elapsed)
WARNING:  pg_stop_backup still waiting for archive to complete (480
seconds elapsed)


I also tried to run the two commands seperately as below. It also does not work.

psql.exe -d mydb -h myhost -p 5432 -U postgres -w -c set
statement_timeout = 1000;
psql.exe -d mydb -h myhost -p 5432 -U postgres -w -c select pg_stop_backup();

If I change the statement_timeout setting of postgresql.conf, it
works. But it will afftect all the queries.

How to use psql to do it?

Thanks.

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] Converting time interval to double precision of time unit

[Alban Hertroys]

On 30 Mar 2010, at 18:29, Mike Toews wrote:

 I'm using 8.3, and I'm trying to work with the interval type, and I
 can't seem to get things right. I've been all over the docs[1,2], and
 there is no mention on how this can be done.
 
 While I can get:
 SELECT '3 day 2 hour 34 minute'::interval
 
 .. how can then get the fractional hours of this time interval in
 double precision (or seconds, minutes, years, decades, etc.)?
 
 Do I really need to extract the time subcomponents and do the math myself?

You shouldn't try to do that. How do you expect to convert an interval type to 
a timestamp without having a timestamp to base it on? It's a relative quantity 
with a variable value depending on it's base value. For a meaningful answer it 
requires information about DST changes, different month lengths, leap years, 
etc, which it won't have if you don't tell where you're basing your interval 
off.

If instead you base your interval on a relevant base-timestamp, then you can 
simply extract epoch from the result, although thats in seconds and not 
(fractional) hours, but that's a linear relationship.

For example,

SELECT extract(epoch from now() + interval '3 days 2 hours 34 minutes')


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4bb236cf10412084085775!



-- 
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] set statement_timeout does not work

[Scott Marlowe]

On Mon, Mar 29, 2010 at 8:33 PM, Jun Wang junw2...@gmail.com wrote:
 Hi,

 I try to set the statement_timeout so that select pg_stop_backup();
 will not hang if archive command failed. Below are the command and
 errors.


Try it by putting the commands in a file and running it like

psql . -f mysqlfile.sql

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

[GENERAL] Wiki Updates - 9.0

[Tyler Hains]

At what point does the Wiki start getting updated for new releases? It
seems there are some significant changes needed to this page for version
9:

http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connect
ion_Pooling

 

I take it that the user accounts for the Wiki are only granted to
trusted people to keep the info accurate? I’d happily help with general
stuff and let people fill in the details – but didn’t see a way to
register…

 

Thanks,

Tyler Hains

ProfitPoint, Inc.

www.profitpointinc.com

 

Re: [GENERAL] Wiki Updates - 9.0

[Raymond O'Donnell]

On 30/03/2010 18:52, Tyler Hains wrote:

 I take it that the user accounts for the Wiki are only granted to
 trusted people to keep the info accurate? I’d happily help with general
 stuff and let people fill in the details – but didn’t see a way to
 register…

As I understand it, you just need to have a community account, which you
get here:

  http://www.postgresql.org/community/signup

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
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] Wiki Updates - 9.0

[Greg Smith]

Tyler Hains wrote:


At what point does the Wiki start getting updated for new releases? It 
seems there are some significant changes needed to this page for 
version 9:


http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling 





We're currently revamping everything at 
http://wiki.postgresql.org/wiki/Clustering ; eventually every 
replication product page their should end up looking like 
http://wiki.postgresql.org/wiki/Slony , that's been the prototype for 
revamping the look and it's basically done at this point.  Between now 
and 9.0 a few months from now, all of the replication solutions there 
will get updated to follow that format, and from there we can assemble a 
new table.


I take it that the user accounts for the Wiki are only granted to 
trusted people to keep the info accurate? I’d happily help with 
general stuff and let people fill in the details – but didn’t see a 
way to register...




No, just sign up for a community account as described on the main page 
of the wiki.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
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] Converting time interval to double precision of time unit

[Tom Lane]

Alban Hertroys dal...@solfertje.student.utwente.nl writes:
 On 30 Mar 2010, at 18:29, Mike Toews wrote:
 I'm using 8.3, and I'm trying to work with the interval type, and I
 can't seem to get things right. I've been all over the docs[1,2], and
 there is no mention on how this can be done.
 
 While I can get:
 SELECT '3 day 2 hour 34 minute'::interval
 
 .. how can then get the fractional hours of this time interval in
 double precision (or seconds, minutes, years, decades, etc.)?
 
 Do I really need to extract the time subcomponents and do the math myself?

 You shouldn't try to do that. How do you expect to convert an interval type 
 to a timestamp without having a timestamp to base it on? It's a relative 
 quantity with a variable value depending on it's base value. For a meaningful 
 answer it requires information about DST changes, different month lengths, 
 leap years, etc, which it won't have if you don't tell where you're basing 
 your interval off.

 If instead you base your interval on a relevant base-timestamp, then you can 
 simply extract epoch from the result, although thats in seconds and not 
 (fractional) hours, but that's a linear relationship.

I think what Mike is actually looking for is

SELECT extract(epoch from interval '3 days 2 hours 34 minutes');
 date_part 
---
268440
(1 row)

although your point about the uncertainty of the conversion for units of
days or larger is certainly well-taken.

regards, tom lane

-- 
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] Converting time interval to double precision of time unit

[Mike Toews]

On 30 March 2010 11:55, Tom Lane t...@sss.pgh.pa.us wrote:
 I think what Mike is actually looking for is

 SELECT extract(epoch from interval '3 days 2 hours 34 minutes');
  date_part
 ---
    268440

Yet better, if I define 1 hour as 3600 seconds (this is only incorrect
if the interval spans over a leap second), then the fractional hours
are:

SELECT extract(epoch from interval '3 days 2 hours 34 minutes')/3600 as hours;

Thanks!

-Mike

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

[GENERAL] Running Windows on a Mac partition

[John Gage]

I just wondered if I could access the same 8.4.2 server from the  
Windows partition (XP via Bootcamp) as I do from the Mac partition  
on my Mac?


Thanks,

John

P.S.  In other words, do I have to duplicate everything on the two  
machines?


--
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] Running Windows on a Mac partition

[Scott Marlowe]

On Tue, Mar 30, 2010 at 1:57 PM, John Gage jsmg...@numericable.fr wrote:
 I just wondered if I could access the same 8.4.2 server from the Windows
 partition (XP via Bootcamp) as I do from the Mac partition on my Mac?

Assuming both virtual machines (or the virtual machine and the host)
are up at the same time, it's more of a networking issue that anything
else.  As long as the pg_hba.conf and postgresql.conf files have
entries allowing outside machines to connect via TCP/IP you should be
able to just point your windows partition over to the IP of the Mac
partition and be set.

-- 
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] Running Windows on a Mac partition

[Joshua Berry]

On Tue, Mar 30, 2010 at 4:46 PM, Scott Marlowe scott.marl...@gmail.comwrote:

 On Tue, Mar 30, 2010 at 1:57 PM, John Gage jsmg...@numericable.fr wrote:
  I just wondered if I could access the same 8.4.2 server from the Windows
  partition (XP via Bootcamp) as I do from the Mac partition on my Mac?

 Assuming both virtual machines (or the virtual machine and the host)
 are up at the same time, it's more of a networking issue that anything
 else.  As long as the pg_hba.conf and postgresql.conf files have
 entries allowing outside machines to connect via TCP/IP you should be
 able to just point your windows partition over to the IP of the Mac
 partition and be set.


I haven't used Bootcamp for a few years, but I believe that it is not
platform for running concurrent virtual machines, but rather a method of
dual booting Windows and Mac OS. Meaning, that either one or the other
would be running at any given point of time.

Re: [GENERAL] Running Windows on a Mac partition

[Scott Marlowe]

On Tue, Mar 30, 2010 at 4:20 PM, Joshua Berry yob...@gmail.com wrote:

 On Tue, Mar 30, 2010 at 4:46 PM, Scott Marlowe scott.marl...@gmail.com
 wrote:

 On Tue, Mar 30, 2010 at 1:57 PM, John Gage jsmg...@numericable.fr wrote:
  I just wondered if I could access the same 8.4.2 server from the Windows
  partition (XP via Bootcamp) as I do from the Mac partition on my Mac?

 Assuming both virtual machines (or the virtual machine and the host)
 are up at the same time, it's more of a networking issue that anything
 else.  As long as the pg_hba.conf and postgresql.conf files have
 entries allowing outside machines to connect via TCP/IP you should be
 able to just point your windows partition over to the IP of the Mac
 partition and be set.

 I haven't used Bootcamp for a few years, but I believe that it is not
 platform for running concurrent virtual machines, but rather a method of
 dual booting Windows and Mac OS. Meaning, that either one or the other
 would be running at any given point of time.

The impression I was under was that both OSes were active and you just
flipped between the two with a sort of super alt-tab command.

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

[GENERAL] Migration - not null default '0' - not null default 0 - confused

[Wang, Mary Y]

Hi,

I'm confused.  I'm in the process of migrating to 8.3.8.  I used pg_dump and 
pg_restore command for migration.
Here is my problem.
Here is my old table prior migration:
 \d activity_log
Table activity_log
 Attribute | Type | Modifier
---+--+--
 day   | integer  | not null default '0'
 hour  | integer  | not null default '0'
 group_id  | integer  | not null default '0'
 browser   | character varying(8) | not null default 'OTHER'
 ver   | double precision | not null default '0.00'
 platform  | character varying(8) | not null default 'OTHER'
 time  | integer  | not null default '0'
 page  | text |
 type  | integer  | not null default '0'
 user_id   | integer  | not null default '0'


Here is my table after migration:
\d activity_log;
  Table public.activity_log
  Column  | Type |  Modifiers
--+--+-
 day  | integer  | not null default 0
 hour | integer  | not null default 0
 group_id | integer  | not null default 0
 browser  | character varying(8) | not null default 'OTHER'::character varying
 ver  | double precision | not null default 0::double precision
 platform | character varying(8) | not null default 'OTHER'::character varying
 time | integer  | not null default 0
 page | text |
 type | integer  | not null default 0
 user_id  | integer  | not null default 0

Now, the source code doesn't work any more. Here is the SQL - INSERT INTO 
activity_log (day,hour,group_id,browser,ver,platform,time,page,type,user_id) 
VALUES 
(20100330,'16','','MOZILLA','5.0','Win','1269990036','/index.php','0','');
and pgsql returned ERROR:  invalid input syntax for integer: .  My 
understanding is that if the value is null, then it should set both the 
group_id=0 and user_id=0.  But it didn't do it.   With the old table, this SQL 
statement would work.

Any suggestions on what I need to do for the not null default values?

I'm running on Postgres 8.3.8 and RHEL 3.9.

Thanks
Mary Wang





-- 
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] Migration - not null default '0' - not null default 0 - confused

[Adrian Klaver]

On Tuesday 30 March 2010 4:23:39 pm Wang, Mary Y wrote:
 Hi,

 I'm confused.  I'm in the process of migrating to 8.3.8.  I used pg_dump
 and pg_restore command for migration. Here is my problem.
 Here is my old table prior migration:
  \d activity_log
 Table activity_log
  Attribute | Type | Modifier
 ---+--+--
  day   | integer  | not null default '0'
  hour  | integer  | not null default '0'
  group_id  | integer  | not null default '0'
  browser   | character varying(8) | not null default 'OTHER'
  ver   | double precision | not null default '0.00'
  platform  | character varying(8) | not null default 'OTHER'
  time  | integer  | not null default '0'
  page  | text |
  type  | integer  | not null default '0'
  user_id   | integer  | not null default '0'


 Here is my table after migration:
 \d activity_log;
   Table public.activity_log
   Column  | Type |  Modifiers
 --+--+-
 day  | integer  | not null default 0
  hour | integer  | not null default 0
  group_id | integer  | not null default 0
  browser  | character varying(8) | not null default 'OTHER'::character
 varying ver  | double precision | not null default 0::double
 precision platform | character varying(8) | not null default
 'OTHER'::character varying time | integer  | not null
 default 0
  page | text |
  type | integer  | not null default 0
  user_id  | integer  | not null default 0

 Now, the source code doesn't work any more. Here is the SQL - INSERT INTO
 activity_log
 (day,hour,group_id,browser,ver,platform,time,page,type,user_id) VALUES
 (20100330,'16','','MOZILLA','5.0','Win','1269990036','/index.php','0','');
 and pgsql returned ERROR:  invalid input syntax for integer: .  My
 understanding is that if the value is null, then it should set both the
 group_id=0 and user_id=0.  But it didn't do it.   With the old table, this
 SQL statement would work.

 Any suggestions on what I need to do for the not null default values?

 I'm running on Postgres 8.3.8 and RHEL 3.9.

 Thanks
 Mary Wang

8.3 tightened up type casting. You cannot INSERT a '0' without casting it to an 
integer i.e '0'::integer. 




-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
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] Migration - not null default '0' - not null default 0 - confused

[Wang, Mary Y]

Ok.  Thanks. In that case, I'm going to have a lot of type casting issues.  
What's the best way to fix all tables? Write a script to alter those tables?
Any suggestions?

Mary Wang


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@gmail.com] 
Sent: Tuesday, March 30, 2010 4:32 PM
To: pgsql-general@postgresql.org
Cc: Wang, Mary Y
Subject: Re: [GENERAL] Migration - not null default '0' - not null default 0 - 
confused

On Tuesday 30 March 2010 4:23:39 pm Wang, Mary Y wrote:
 Hi,

 I'm confused.  I'm in the process of migrating to 8.3.8.  I used 
 pg_dump and pg_restore command for migration. Here is my problem.
 Here is my old table prior migration:
  \d activity_log
 Table activity_log
  Attribute | Type | Modifier
 ---+--+--
  day   | integer  | not null default '0'
  hour  | integer  | not null default '0'
  group_id  | integer  | not null default '0'
  browser   | character varying(8) | not null default 'OTHER'
  ver   | double precision | not null default '0.00'
  platform  | character varying(8) | not null default 'OTHER'
  time  | integer  | not null default '0'
  page  | text |
  type  | integer  | not null default '0'
  user_id   | integer  | not null default '0'


 Here is my table after migration:
 \d activity_log;
   Table public.activity_log
   Column  | Type |  Modifiers
 --+--+
 --+--+-
 day  | integer  | not null default 0
  hour | integer  | not null default 0
  group_id | integer  | not null default 0
  browser  | character varying(8) | not null default 'OTHER'::character
 varying ver  | double precision | not null default 0::double
 precision platform | character varying(8) | not null default
 'OTHER'::character varying time | integer  | not null
 default 0
  page | text |
  type | integer  | not null default 0
  user_id  | integer  | not null default 0

 Now, the source code doesn't work any more. Here is the SQL - INSERT 
 INTO activity_log
 (day,hour,group_id,browser,ver,platform,time,page,type,user_id) VALUES 
 (20100330,'16','','MOZILLA','5.0','Win','1269990036','/index.php','0',
 ''); and pgsql returned ERROR:  invalid input syntax for integer: 
 .  My understanding is that if the value is null, then it should 
 set both the
 group_id=0 and user_id=0.  But it didn't do it.   With the old table, this
 SQL statement would work.

 Any suggestions on what I need to do for the not null default values?

 I'm running on Postgres 8.3.8 and RHEL 3.9.

 Thanks
 Mary Wang

8.3 tightened up type casting. You cannot INSERT a '0' without casting it to an 
integer i.e '0'::integer. 




-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
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] Migration - not null default '0' - not null default 0 - confused

[Adrian Klaver]

On Tuesday 30 March 2010 4:49:42 pm Wang, Mary Y wrote:
 Ok.  Thanks. In that case, I'm going to have a lot of type casting issues. 
 What's the best way to fix all tables? Write a script to alter those
 tables? Any suggestions?

 Mary Wang




This might help:
http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html


-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
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] pgfoundry registration

[Jeff Davis]

On Thu, 2010-03-25 at 13:05 +0100, Krzysztof Nienartowicz wrote:
 Hello,
 Is registration to pgFoundry closed on purpose? I get confirmation
 emails but am greeted with:
 
 
 snip
 Access denied
 
 Credentials you entered do not correspond to valid account.
 /snip
 Could you help or advise, please?

Try asking on the mailing list pgsql-www.

Regards,
Jeff Davis


-- 
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] Migration - not null default '0' - not null default 0 - confused

[Jeff Davis]

On Tue, 2010-03-30 at 16:32 -0700, Adrian Klaver wrote:
 8.3 tightened up type casting. You cannot INSERT a '0' without casting it to 
 an 
 integer i.e '0'::integer. 

I don't think that's accurate:

  postgres=# select version();

version  
  

--
 PostgreSQL 9.0devel on x86_64-unknown-linux-gnu, compiled by GCC
gcc-4.3.real (Ubuntu 4.3.2-1ubuntu12) 4.3.2, 64-bit
(1 row)

  postgres=# create table ints(i int);
  CREATE TABLE
  postgres=# insert into ints values('0');
  INSERT 0 1


Regards,
Jeff Davis


-- 
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] Migration - not null default '0' - not null default 0 - confused

[Steve Atkins]

On Mar 30, 2010, at 4:23 PM, Wang, Mary Y wrote:

 Hi,
 
 I'm confused.  I'm in the process of migrating to 8.3.8.  I used pg_dump and 
 pg_restore command for migration.
 Here is my problem.
 Here is my old table prior migration:
 \d activity_log
Table activity_log
 Attribute | Type | Modifier
 ---+--+--
 day   | integer  | not null default '0'
 hour  | integer  | not null default '0'
 group_id  | integer  | not null default '0'
 browser   | character varying(8) | not null default 'OTHER'
 ver   | double precision | not null default '0.00'
 platform  | character varying(8) | not null default 'OTHER'
 time  | integer  | not null default '0'
 page  | text |
 type  | integer  | not null default '0'
 user_id   | integer  | not null default '0'
 
 
 Here is my table after migration:
 \d activity_log;
  Table public.activity_log
  Column  | Type |  Modifiers
 --+--+-
 day  | integer  | not null default 0
 hour | integer  | not null default 0
 group_id | integer  | not null default 0
 browser  | character varying(8) | not null default 'OTHER'::character varying
 ver  | double precision | not null default 0::double precision
 platform | character varying(8) | not null default 'OTHER'::character varying
 time | integer  | not null default 0
 page | text |
 type | integer  | not null default 0
 user_id  | integer  | not null default 0
 
 Now, the source code doesn't work any more. Here is the SQL - INSERT INTO 
 activity_log (day,hour,group_id,browser,ver,platform,time,page,type,user_id) 
 VALUES 
 (20100330,'16','','MOZILLA','5.0','Win','1269990036','/index.php','0','');
 and pgsql returned ERROR:  invalid input syntax for integer: .  

You're trying to insert an empty string into group_id. An empty string is not a 
valid integer.

 My understanding is that if the value is null, then it should set both the 
 group_id=0 and user_id=0.

No, that's not the case. You can't insert a null into a not-null field. Also, 
you're not trying to insert a null unto group_id, you're trying to insert an 
empty string.

  But it didn't do it.   With the old table, this SQL statement would work.

I don't think it did. Maybe you changed something else at the same time?

abacus= create table foo (bar integer not null default '0');
CREATE TABLE
abacus= insert into foo (bar) values ('');
ERROR:  invalid input syntax for integer: 

 
 Any suggestions on what I need to do for the not null default values?

It's nothing at all to do with them, I don't think - it's just that you're 
trying to insert bad data into the table.

You can either use the literal string default (with no quotes) to insert the 
default value into a field, or don't list the field in the list of fields to 
insert at all.

Cheers,
  Steve


-- 
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] Migration - not null default '0' - not null default 0 - confused

[Adrian Klaver]

On Tuesday 30 March 2010 4:59:30 pm Jeff Davis wrote:
 On Tue, 2010-03-30 at 16:32 -0700, Adrian Klaver wrote:
  8.3 tightened up type casting. You cannot INSERT a '0' without casting it
  to an integer i.e '0'::integer.

 I don't think that's accurate:

   postgres=# select version();

 version

 ---
- --
  PostgreSQL 9.0devel on x86_64-unknown-linux-gnu, compiled by GCC
 gcc-4.3.real (Ubuntu 4.3.2-1ubuntu12) 4.3.2, 64-bit
 (1 row)

   postgres=# create table ints(i int);
   CREATE TABLE
   postgres=# insert into ints values('0');
   INSERT 0 1


 Regards,
   Jeff Davis

My mistake. I could of swore I had problems with this when I first tried 8.3. 
What happens when you rely on memory.

-- 
Adrian Klaver
adrian.kla...@gmail.com

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

[GENERAL] plPgSQL + CDIR/INET types ...

[Marc G. Fournier]

Has anyone either played with, or gotten to work, a plPgSQL function that 
would take:  192.168.1.1/24 and determine the start and end IP from that? 
Or even start IP + # of IPs in the subnet?



Marc G. FournierHub.Org Hosting Solutions S.A.
scra...@hub.org http://www.hub.org

Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org

--
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] plPgSQL + CDIR/INET types ...

[Steve Atkins]

On Mar 30, 2010, at 7:09 PM, Marc G. Fournier wrote:

 
 Has anyone either played with, or gotten to work, a plPgSQL function that 
 would take:  192.168.1.1/24 and determine the start and end IP from that? Or 
 even start IP + # of IPs in the subnet?

If that was a valid cidr value (like 192.168.1.0/24) you could just use host() 
or broadcast() to get the first and last addresses (and subtraction to get the 
size of the subnet).

You could probably convert it to the equivalent cidr range with some combo of 
set_masklen() and masklen().

Cheers,
  Steve




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

[GENERAL] Unsubscribe

[Wojtek]

unsubscribe

Re: [GENERAL] User action accounting

[Craig Ringer]

Joshua Berry wrote:
 Hello All,
 
 I have a few PHP/Clarion based applications that don't currently track
 who created and modified records. I'd like to be able to track all user
 and timestamp pairs for INSERT/UPDATEs by way of triggers.
 
 The problem is that I currently use the same role name for each instance
 of the application, so current_user is not particularly helpful.  So I
 have a few ideas that I wanted to bounce off the experts here:
 1. Should I use seperate PG roles for each user? Is there a way of
 permitting user names queried against a RADIUS server to inherit a role
 allowing the needed permissions (trusting that the RADIUS server is
 secured) and allowing the requested name to be used without having to
 maintain two lists of accounts?

I'm not sure about RADIUS, but Pg can auth users against Kerberos and
against LDAP, or against anything that'll talk to PAM. You should be
able to use RADIUS via PAM if nothing else.

( Side note: it looks like LDAP auth doesn't support storage of role
memberships or mapping of Pg roles to unix user group memberships. It'd
be really rather handy... )

Anyway, one way or the other I'd personally strongly suggest option (1).
It allows you to vary the rights granted to users using the database's
priv logic instead of having to roll your own whenever you want to limit
user rights. Especially now that Pg supports column privs, this is a big
bonus.

You can maintain the created-by/when and modified by/when columns using
triggers, and deny anybody the right to insert/update these columns so
nothing except the trigger may affect them.

By the way, if your trigger-maintained `last mod user' and `last mod
time' tables confuse an app that likes to use SELECT * and doesn't
ignore appended columns, there's a workaround. Rename the original table
the app uses, and make a view with that name that selects only the
columns the app expects to see in the table. Add appropriate UPDATE and
DELETE rules to the view so the app doesn't realise it's a view. Now
you've got that last-user/time information, but the app that uses the
table can't see it to be confused by it.

This can be particularly important if the app isn't aware of column
privs and tries to update all columns, but you have 'originally created
by' cols or the like that the app doesn't have the rights to update.

--
Craig Ringer

-- 
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] Running Windows on a Mac partition

[John Gage]

a method of
dual booting Windows and Mac OS. Meaning, that either one or the  
other

would be running at any given point of time.


The impression I was under was that both OSes were active and you just
flipped between the two with a sort of super alt-tab command.


In the case of Bootcamp, only one OS is in operation at the same time  
(there are other solutions, Parallels is one, that run simultaneously,  
but they are not as fast).


However, each OS, while it is running has access to the other's file  
system, though these file systems are different to some extent (which  
is configurable).



--
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] Running Windows on a Mac partition

[Craig Ringer]

John Gage wrote:
 I just wondered if I could access the same 8.4.2 server from the Windows
 partition (XP via Bootcamp) as I do from the Mac partition on my Mac?

If I understand correctly, Boot Camp doesn't permit both Mac OS X and
Windows to run at the same time, right? You can run one or the other,
and to switch you must reboot?

If that's the case, then you can't run one server instance and share it
via the network between the two personalities.

You *might* be able to share the data directory if the Mac OS X system
can read/write the Windows NTFS partition or vice versa. This is
probably risky, as I wouldn't trust Mac OS X's NTFS support to be safe
to run a database on, nor any Apple-provided HFS+ driver for XP to be
safe for that purpose. I wouldn't run Pg on FAT32 either.

Personally, what I'd do would be create a virtual machine image with
something like VMWare - something that is supported on both Mac OS X and
on Windows. Put it somewhere both systems can access it - probably the
Windows NTFS partition. Then, whichever OS you're using, start the
virtual machine with Pg on it and use that server over the virtual
network between the VM and the real host.

Alternately, you could just point Pg at a data-dir on storage that both
systems can access, as described earlier. I'd be pretty wary of doing
this, though.

--
Craig Ringer

-- 
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] Running Windows on a Mac partition

[John Gage]

Unfortunately, but no unexpectedly, I have been moderately stupid in  
this question.


Using Bootcamp, the OS's are *not* running simultaneously.  Hence, the  
server, which is on the Mac is not running when Win is running.  There  
is the possibility for the Mac to *read* files in Win, but that is as  
far as the cross-talk goes.  Win does not know that Mac exists.


That is Bootcamp.

There are other solutions, Parallels is one, that permit Win and Mac  
to run simultaneously.  Win in that case is a virtual machine.  In  
that case the two OS's talk to each other.  But it is still a cludgy  
environment, and I don't want to spend the money on another Win OEM OS.


Sorry to have not really done my homework on this question, and thank  
you for your suggestions.  Clearly, TCP/IP would work...if the Mac  
were running at the same time.


As a footnote, given the entirely different virus susceptibilities of  
the two systems, it is probably better to run a separate server and  
data file(s) on each machine.


John


On Mar 30, 2010, at 11:46 PM, Scott Marlowe wrote:

On Tue, Mar 30, 2010 at 1:57 PM, John Gage jsmg...@numericable.fr  
wrote:
I just wondered if I could access the same 8.4.2 server from the  
Windows
partition (XP via Bootcamp) as I do from the Mac partition on my  
Mac?


Assuming both virtual machines (or the virtual machine and the host)
are up at the same time, it's more of a networking issue that anything
else.  As long as the pg_hba.conf and postgresql.conf files have
entries allowing outside machines to connect via TCP/IP you should be
able to just point your windows partition over to the IP of the Mac
partition and be set.



--
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] Running Windows on a Mac partition

[John Gage]

This response came in as I was mea culpa-ing.

Everything here is correct to the best of my knowledge.

And I am very glad to be warned not to go between the two OS's.

Thank you,

John


Personally, what I'd do would be create a virtual machine image with
something like VMWare - something that is supported on both Mac OS X  
and

on Windows. Put it somewhere both systems can access it - probably the
Windows NTFS partition. Then, whichever OS you're using, start the
virtual machine with Pg on it and use that server over the virtual
network between the VM and the real host.

Alternately, you could just point Pg at a data-dir on storage that  
both

systems can access, as described earlier. I'd be pretty wary of doing
this, though.

--
Craig Ringer



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