Re: [GENERAL] alter table add column - specify where the column will go?

[Alexander Farber]

Easier to read... login, logout


On Wed, Nov 24, 2010 at 8:45 AM, Szymon Guz  wrote:
>  no
> Why do you want to do anything like that?
>

-- 
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 add column - specify where the column will go?

[Dmitriy Igrishin]

Hey Alexander,

Ordinal positions of columns can't be set manually by ALTER TABLE.

2010/11/24 Alexander Farber 

> Hello,
>
> is there a syntax to add a column not at the last place, but
> somewhere inbetween or do I have to dump/restore the table?
>
> For example if I'd like to add last_logout right after last_login:
>
>  \d pref_users;
>Table "public.pref_users"
>   Column   |Type |   Modifiers
> +-+---
>  id | character varying(32)   | not null
>  first_name | character varying(32)   |
>  last_name  | character varying(32)   |
>  female | boolean |
>  avatar | character varying(128)  |
>  city   | character varying(32)   |
>  lat| real|
>  lng| real|
>  last_login | timestamp without time zone | default now()
>  last_ip| inet|
>  medals | smallint| default 0
>
> Thank you
> Alex
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

Re: [GENERAL] alter table add column - specify where the column will go?

[Thomas Kellerer]

Alexander Farber, 24.11.2010 08:42:

is there a syntax to add a column not at the last place


No, because the order of the column is irrelevant (just as there is no order on 
the rows in a table)
Simply select them in the order you like to have.

Thomas


--
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 add column - specify where the column will go?

[Szymon Guz]

On 24 November 2010 08:42, Alexander Farber wrote:

> Hello,
>
> is there a syntax to add a column not at the last place, but
> somewhere inbetween or do I have to dump/restore the table?
>
> For example if I'd like to add last_logout right after last_login:
>
>  \d pref_users;
>Table "public.pref_users"
>   Column   |Type |   Modifiers
> +-+---
>  id | character varying(32)   | not null
>  first_name | character varying(32)   |
>  last_name  | character varying(32)   |
>  female | boolean |
>  avatar | character varying(128)  |
>  city   | character varying(32)   |
>  lat| real|
>  lng| real|
>  last_login | timestamp without time zone | default now()
>  last_ip| inet|
>  medals | smallint| default 0
>
> Thank you
> Alex
>
>
 no

Why do you want to do anything like that?


regards
Szymon Guz

[GENERAL] alter table add column - specify where the column will go?

[Alexander Farber]

Hello,

is there a syntax to add a column not at the last place, but
somewhere inbetween or do I have to dump/restore the table?

For example if I'd like to add last_logout right after last_login:

 \d pref_users;
Table "public.pref_users"
   Column   |Type |   Modifiers
+-+---
 id | character varying(32)   | not null
 first_name | character varying(32)   |
 last_name  | character varying(32)   |
 female | boolean |
 avatar | character varying(128)  |
 city   | character varying(32)   |
 lat| real|
 lng| real|
 last_login | timestamp without time zone | default now()
 last_ip| inet|
 medals | smallint| default 0

Thank you
Alex

-- 
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] plpyhton

[c k]

Hello,
Does calling a pl/python function from each database connection load the
python interpreter each time? what are the effects of using pl/python
function in a environment where no. of concurrent connections are more and
each user calls a pl/python function?

Please give the details about how pl/python functions are executed.
Thanks and regards,

CPK

Re: [GENERAL] Passing a String with special character as an input

[John R Pierce]

On 11/23/10 12:34 PM, akp geek wrote:

Hi -

   This is related to my earlier post. For the function I am 
passing a string. But the string some time has a single quote inside 
the string like "IT's a String Test" , How can I handle that, can you 
please help?




at the SQL level, pass it as a parameter.

like, in perl...

my $sth = $dbh->prepare('select test_repl(?);');
$sth->execute("It's a String Test");

(or my $sth->execute('It\'s a String Test'); ...)







--
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] Passing a String with special character as an input

[Pavel Stehule]

Hello

2010/11/23 akp geek :
> Hi -
>            This is related to my earlier post. For the function I am passing
> a string. But the string some time has a single quote inside the string like
> "IT's a String Test" , How can I handle that, can you please help?

on stored procedure level you can do nothing

in SQL level, you have to duble quotes

like INSERT INTO data VALUES('Peter''s book');

regards

Pavel Stehule


>
>
> CREATE OR REPLACE FUNCTION test_repl(x character varying)
>   RETURNS character varying AS
> $BODY$
> DECLARE
> ret_var varchar(4000);
> a record;
> begin
> ret_var := x;
> for a in select * from lookup
> loop
>   ret_var := replace(ret_var,a.code,a.codeword);
> end loop;
> return ret_var;
> END;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE
>   COST 100;
> ALTER FUNCTION test_repl(character varying) OWNER TO postgres;

-- 
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] What is Dialect

[Craig Ringer]

On 11/22/2010 03:32 PM, Adarsh Sharma wrote:

Dear all,

I am reading about Dialects of different databases. Yet I can't
understand what is the need of dialect in Postgres or any other like
Hibernate uses Dialect of all Databases for ORM.
What is it &
How can we create our own Dialect ?


If you have to ask that question, you probably shouldn't do it. Seriously.

Perhaps you can explain *why* you want to make a new dialect in a bit 
more detail? What you are trying to achieve? Most things you might want 
to do via the dialect are better done with a UserType or by fixing your 
schema.


BTW, there is already a PostgreSQL dialect in Hibernate. It's 
automatically selected when Hibernate is used with PostgreSQL.


--
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] Problem with replace function in postgres

[akp geek]

I used pg_catalog.replace, it worked. Thanks for the help

On Tue, Nov 23, 2010 at 3:32 PM, Pavel Stehule wrote:

> Hello
>
> it working on my comp
>
> postgres=# select mystr, test_repl(mystr) replaced_strfrom str_table;
>   mystr   |   replaced_str
> ---+--
>  This road is INDEFLY CLSD | This road is indefinitely closed
>  That is INDEFLY open  | That is indefinitely open
>  That is a CLSD road   | That is a closed road
> (3 rows)
>
> I don't see a problem in your code, I am thinking so it's ok.
>
> try to debug code with RAISE NOTICE statement
>
> for a in select * from lookup
> loop
> RAISE NOTICE 'lookup rec: %', a;
> RAISE NOTICE 'ret_var before replace: %', ret_var;
>   ret_var := replace(ret_var,a.code,a.codeword);
> RAISE NOTICE 'ret_var after replace: %', ret_var;
> end loop;
> return ret_var;
>
> you can ensure using just buildin "replace" function - use
>
> ret_var := pg_catalog.replace(...)
>
> Regards
>
> Pavel Stehule
> maybe it can help to identify a problem
>
>
>
>
>
> 2010/11/23 akp geek :
> > Dear all -
> > I am having trouble with replace function inside a
> function.
> >  If I do the same replace function outside it works fine, Following
> example
> > clearly explains my problem. Can you please help?
> >
> > create table str_table
> > ( mystr varchar(1000));
> > insert into str_table values ( 'This road is INDEFLY CLSD');
> > insert into str_table values('That is INDEFLY open');
> > insert into str_table values('That is a CLSD road');
> >
> > select * from str_table;
> > create table lookup
> > ( code varchar(100),
> >   codeword varchar(100));
> >
> > insert into lookup values(  'INDEFLY','indefinitely');
> > insert into lookup values(  'CLSD','closed');
> > select 'This road is INDEFLY CLSD', test_repl('This road is INDEFLY CLSD
> > INDEFLY') replaced_str
> > from str_table;
> > RESULT SET "  This road is INDEFLY CLSD;This road is indefinitely closed
> > INDEFLY  "
> > Regards
>

Re: [GENERAL] "could not accept SSPI security context"

[Brar Piening]

On Mon, 22 Nov 2010 13:43:14 +0100, Magnus Hagander 
 wrote:

Hmm. That's a simple SEC_E_LOGON_DENIED. Simply meaning
usedname/password is incorrect. The security eventlog on the server
(or domain controller) might have more information around it. If not,
I'm not sure what's wrong there - if it happens only in npgsql it must
be related to that. Or perhaps - based on your reproduction - the .net
app is running with a different user than you think?



If you've got access to the sources of your client app that uses Npgsql 
you might want to put :


NpgsqlEventLog.Level = LogLevel.Debug;
NpgsqlEventLog.LogName = @"C:\somePath\NpgsqlEventLog.txt";

in the code before the first call of NpgsqlConnection.Open() to find out 
details about the user name that's actually connecting.



Just look for

Entering PGUtil.WriteString()
String written: user.
Entering PGUtil.WriteString()
String written: YOURCONNECTINGUSERNAME.

after

Entering NpgsqlStartupPacket.NpgsqlStartupPacket()
Entering NpgsqlStartupPacket.WriteToStream()
Entering NpgsqlStartupPacket.WriteToStream_Ver_3()

Regards,

Brar

--
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] very basic SQL question

[Dan Kortschak]

Thanks for that - yes very helpful. Good to know what is possible.

Dan

On Tue, 2010-11-23 at 10:27 +0100, Matthieu Huin wrote:
> A similar question was discussed here about 3 weeks ago : 
> http://archives.postgresql.org/pgsql-general/2010-11/msg00110.php
> 
> The "UPSERT" facility not being implemented yet, I don't think it is 
> possible to do much better than what you came up with.
> 
> Assuming the column(s) holding the distinctive sequence data are defined 
> with the UNIQUE constraint, you could adapt the example function given 
> in the link to the documentation in the previous thread to achieve what 
> you want.
> 
> Hope this helps,
> 
> Matthieu



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

[GENERAL] Passing a String with special character as an input

[akp geek]

Hi -

   This is related to my earlier post. For the function I am passing
a string. But the string some time has a single quote inside the string like
"IT's a String Test" , How can I handle that, can you please help?



CREATE OR REPLACE FUNCTION test_repl(x character varying)
  RETURNS character varying AS
$BODY$
DECLARE
ret_var varchar(4000);
a record;
begin
ret_var := x;

for a in select * from lookup
loop
  ret_var := replace(ret_var,a.code,a.codeword);
end loop;
return ret_var;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION test_repl(character varying) OWNER TO postgres;

Re: [GENERAL] Problem with replace function in postgres

[Pavel Stehule]

Hello

it working on my comp

postgres=# select mystr, test_repl(mystr) replaced_strfrom str_table;
   mystr   |   replaced_str
---+--
 This road is INDEFLY CLSD | This road is indefinitely closed
 That is INDEFLY open  | That is indefinitely open
 That is a CLSD road   | That is a closed road
(3 rows)

I don't see a problem in your code, I am thinking so it's ok.

try to debug code with RAISE NOTICE statement

for a in select * from lookup
loop
RAISE NOTICE 'lookup rec: %', a;
RAISE NOTICE 'ret_var before replace: %', ret_var;
  ret_var := replace(ret_var,a.code,a.codeword);
RAISE NOTICE 'ret_var after replace: %', ret_var;
end loop;
return ret_var;

you can ensure using just buildin "replace" function - use

ret_var := pg_catalog.replace(...)

Regards

Pavel Stehule
maybe it can help to identify a problem





2010/11/23 akp geek :
> Dear all -
>                 I am having trouble with replace function inside a function.
>  If I do the same replace function outside it works fine, Following example
> clearly explains my problem. Can you please help?
>
> create table str_table
> ( mystr varchar(1000));
> insert into str_table values ( 'This road is INDEFLY CLSD');
> insert into str_table values('That is INDEFLY open');
> insert into str_table values('That is a CLSD road');
>
> select * from str_table;
> create table lookup
> ( code varchar(100),
>   codeword varchar(100));
>
> insert into lookup values(  'INDEFLY','indefinitely');
> insert into lookup values(  'CLSD','closed');
> select 'This road is INDEFLY CLSD', test_repl('This road is INDEFLY CLSD
> INDEFLY') replaced_str
> from str_table;
> RESULT SET "  This road is INDEFLY CLSD;This road is indefinitely closed
> INDEFLY  "
> Regards

-- 
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] Escaping string for LIKE

[Dmitriy Igrishin]

Hey David,

I guess, that you should escape, like this

dmitigr=> SELECT regexp_replace('di_ma%', '(%|_)', E'\\1', 'g');
 regexp_replace

 di\_ma\%


2010/11/23 David Sheldon 

> If I have a string, and I want to search for everything that starts with
> that string then I could do:
>
> SELECT * FROM users WHERE name LIKE string || '%';
>
> Now, if string contains \, _ or % then it won't do what I expect.
>
> Is there a built-in function that I can call to escape the string so that
> it isn't special for LIKE, or is it a case of regexing and putting a \
> before each \, _ or %?
>
> David
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

Re: [GENERAL] Problem with replace function in postgres

[akp geek]

CREATE OR REPLACE FUNCTION test_repl(x character varying)
  RETURNS character varying AS
$BODY$
DECLARE
ret_var varchar(4000);
a record;
begin
ret_var := x;

for a in select * from lookup
loop
  ret_var := replace(ret_var,a.code,a.codeword);
end loop;
return ret_var;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION test_repl(character varying) OWNER TO postgres;


On Tue, Nov 23, 2010 at 3:07 PM, Pavel Stehule wrote:

> Hello
>
> please, can you send a source code of your function?
>
> Regards
>
> Pavel Stehule
>
> 2010/11/23 akp geek :
> > Dear all -
> > I am having trouble with replace function inside a
> function.
> >  If I do the same replace function outside it works fine, Following
> example
> > clearly explains my problem. Can you please help?
> >
> > create table str_table
> > ( mystr varchar(1000));
> > insert into str_table values ( 'This road is INDEFLY CLSD');
> > insert into str_table values('That is INDEFLY open');
> > insert into str_table values('That is a CLSD road');
> >
> > select * from str_table;
> > create table lookup
> > ( code varchar(100),
> >   codeword varchar(100));
> >
> > insert into lookup values(  'INDEFLY','indefinitely');
> > insert into lookup values(  'CLSD','closed');
> > select 'This road is INDEFLY CLSD', test_repl('This road is INDEFLY CLSD
> > INDEFLY') replaced_str
> > from str_table;
> > RESULT SET "  This road is INDEFLY CLSD;This road is indefinitely closed
> > INDEFLY  "
> > Regards
>

Re: [GENERAL] Problem with replace function in postgres

[Pavel Stehule]

Hello

please, can you send a source code of your function?

Regards

Pavel Stehule

2010/11/23 akp geek :
> Dear all -
>                 I am having trouble with replace function inside a function.
>  If I do the same replace function outside it works fine, Following example
> clearly explains my problem. Can you please help?
>
> create table str_table
> ( mystr varchar(1000));
> insert into str_table values ( 'This road is INDEFLY CLSD');
> insert into str_table values('That is INDEFLY open');
> insert into str_table values('That is a CLSD road');
>
> select * from str_table;
> create table lookup
> ( code varchar(100),
>   codeword varchar(100));
>
> insert into lookup values(  'INDEFLY','indefinitely');
> insert into lookup values(  'CLSD','closed');
> select 'This road is INDEFLY CLSD', test_repl('This road is INDEFLY CLSD
> INDEFLY') replaced_str
> from str_table;
> RESULT SET "  This road is INDEFLY CLSD;This road is indefinitely closed
> INDEFLY  "
> Regards

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

[GENERAL] Problem with replace function in postgres

[akp geek]

Dear all -

I am having trouble with replace function inside a function.
 If I do the same replace function outside it works fine, Following example
clearly explains my problem. Can you please help?


create table str_table
( mystr varchar(1000));
insert into str_table values ( 'This road is INDEFLY CLSD');

insert into str_table values('That is INDEFLY open');

insert into str_table values('That is a CLSD road');


select * from str_table;

create table lookup
( code varchar(100),
  codeword varchar(100));

insert into lookup values(  'INDEFLY','indefinitely');

insert into lookup values(  'CLSD','closed');

select 'This road is INDEFLY CLSD', test_repl('This road is INDEFLY CLSD
INDEFLY') replaced_str
from str_table;

RESULT SET "  This road is INDEFLY CLSD;This road is indefinitely closed
INDEFLY  "

Regards

Re: [GENERAL] very basic SQL question

[Matthieu Huin]

A similar question was discussed here about 3 weeks ago : 
http://archives.postgresql.org/pgsql-general/2010-11/msg00110.php


The "UPSERT" facility not being implemented yet, I don't think it is 
possible to do much better than what you came up with.


Assuming the column(s) holding the distinctive sequence data are defined 
with the UNIQUE constraint, you could adapt the example function given 
in the link to the documentation in the previous thread to achieve what 
you want.


Hope this helps,

Matthieu

Le 23/11/2010 04:39, Dan Kortschak a écrit :

Hi,

I'm using Pg for bioinformatic work and I want to be able to insert,
uniquely, biological sequences into a table returning the sequence id -
this part is fine. However, if the sequence already exists in the table
I want to return to id.

At the moment it seems to me that I should do a

SELECT id FROM table WHERE seq = '';

and if that gives NULL then

INSERT  RETURNING id;

But this seems long winded. Is there a better way to achieve what I
want?

thanks




--
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] Postgres forums ... take 2

[Thom Brown]

On 22 November 2010 14:05, Bruce Momjian  wrote:
> Magnus Hagander wrote:
>> On Sat, Nov 20, 2010 at 16:54, Bruce Momjian  wrote:
>> > Elliot Chance wrote:
>> >> > Also, if someone registers on the forum, do they get a major domo 
>> >> > registration email? ?And if so, would this be set to receive no emails 
>> >> > upon registration? ?I'm not clear as to how this step would work 
>> >> > because, at the moment, mailing list subscribers have to subscribe on a 
>> >> > list-by-list basis. ?So registration to the forum site wouldn't 
>> >> > necessarily mean they'd want to join any particular mailing list. 
>> >> > ?Similarly, could they unregister easily? ?And anyone who attempts to 
>> >> > post to a mailing list they aren't subscribed to requires moderation, 
>> >> > so we don't wish to exacerbate this.
>> >>
>> >> No they are not registered on the mailing list, but they actually don't 
>> >> need to be, let me explain:
>> >> 1. John Smith has a postgres related question and finds the forums, he 
>> >> signs up and posts his question.
>> >> 2. His post is then emailed to the mailing list under a generic 
>> >> registered address like "mailingl...@postgresql.com.au"
>> >> 3. Bob House reads Johns question on the mailing list and simply sends an 
>> >> email reply.
>> >> 4. The email reply is piped into the forum and matches the topic based on 
>> >> the email subject (thats how it currently does it.)
>> >> 5. John gets an email from phpBB along the lines of "Bob House has 
>> >> replied to your post, click here" (all forums do this) he reads the 
>> >> response and is happy.
>> >>
>> >> This is the best balance of no-fuss and expert response, keeping in mind 
>> >> that:
>> >> * John can still sign up to the mailing list like anyone else if he wants 
>> >> to.
>> >> * All of John's forums communications are in the postgres mailing list 
>> >> archive now.
>> >
>> > Yes, I think this is fine. ?We would need to be more careful that a
>> > non-group _reply_ would now be going to a public place.
>>
>> I doubt people will really remember that. However, the forums could be
>> given a big disclaimer on posts saying that private replies may show
>> up public, or it could even add it to the footer of the message (sure,
>> nobody reads that, but at least we tried..)
>
> Yes, that seems logical.

Elliot, it appears that there's been considerable input from various
parties regarding the forums (bikeshedding in action), but it spans 3
mailing list conversations.  You may wish to set up a wiki page to
collate what has been discussed, proposed and agreed so far.  If you
haven't used the wiki before, you will need to set up a community
account if you don't already have one, which you can do so at
http://www.postgresql.org/community/signup and then log in to the wiki
located at http://wiki.postgresql.org/ .

This way, everyone can see the progress that's been made as it can be
difficult to follow what has already been discussed.  Also, people can
review what you believe has been decided to see if it sounds sane
enough to actually follow.  Just a suggestion :)

And thanks for the effort you have already put into this.  I
personally support such a development.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

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

[GENERAL] Escaping string for LIKE

[David Sheldon]

If I have a string, and I want to search for everything that starts with 
that string then I could do:


SELECT * FROM users WHERE name LIKE string || '%';

Now, if string contains \, _ or % then it won't do what I expect.

Is there a built-in function that I can call to escape the string so 
that it isn't special for LIKE, or is it a case of regexing and putting 
a \ before each \, _ or %?


David

--
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] postmaster.pid does not exist. cannot start postgres server on ubuntu

[Matthew Walden]

Jen,

Regarding the first point, is postgres actually running?  You can check this
by typing the following at a terminal -

ps -ef|grep postgres

Reload configuration is used to tell PostgreSQL to read in the configuration
file for any chances since the database started.  If you want to start the
database you can use pg_ctl.  More info on its use would be best gained from
the documentation.  It looks like the feedback is correct and you don't have
postgres working.  Either that or you you need to set the $PGDATA
environment variable or specify the data directory when using pg_ctl reload.

I can't really comment on the login script because I don't know its contents
but if you're trying to automatically start the database I would look at
init.d and the associated run level folders (again more info in Linux
documentation - its far too lengthy to explain here).

-- 
View my Linkedin profile 

On Tue, Nov 23, 2010 at 4:12 PM, Jennifer Trey wrote:

> I am getting the following error when I go to Application -> PostgreSQL ->
> Reload Configuration
>
> "
> Please enter your password if requested.
> pg_ctl: PID file "/home/Jen/Installed/PostgreSQL/data/postmaster.pid" does
> not exist
> Is server running?
>
> Press  to continue...
> "
>
> I have installed Postgresql from the Enterprise DB installer for Ubuntu.
>
> It has worked before. I just restarted my computer and now face this
> issue.
>
> Off topic:
> I have also had a different kind of issue, where a script added to
> /etc/profile.d  called Startup.sh
> causes postgre to fail to startup. This script is used on login to set
> global environment variables, but I get the error that Postgre cannot
> execute the script.
> I tried chmod 777 on the script but still no luck. If I remove it, postgre
> will start up so its not a biggy..
> but is not a bug ?
>
> Cheers, Jen
>
>
>

[GENERAL] Syntax error near returning

[Jonathan Tripathy]

Hi Everyone,

When I create a prepared statement like this in java (using JDBC):

pStmt = conn.prepareStatement(qry);

everything works ok. However when I want a scrollable resultset and use this:

pStmt = conn.prepareStatement(qry,ResultSet.TYPE_SCROLL_INSENSITIVE);

I get a syntax error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "RETURNING"

I'm not even using RETURNING in my query. I think that my issue mayh be related 
to this post:

http://archives.postgresql.org/pgsql-jdbc/2010-04/msg00060.php 

Any ideas?

Any help would be appreciated. Thanks

 

 

Re: [GENERAL] Discerning user vs trigger based updates

[Tom Lane]

"Gauthier, Dave"  writes:
> I have an update trigger on a table that in turn updates other records of 
> that same table.  I need to be able to discern between when a user is 
> updating a record in the table vs when the update trigger is reflexively 
> calling itself.  Is there an elegant way to do this?

No.  Generally that sort of design is a foot-gun ... you should
reconsider the schema arrangement that requires this.

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

[GENERAL] Discerning user vs trigger based updates

[Gauthier, Dave]

Hi:

I have an update trigger on a table that in turn updates other records of that 
same table.  I need to be able to discern between when a user is updating a 
record in the table vs when the update trigger is reflexively calling itself.  
Is there an elegant way to do this?

Thanks in Advance

[GENERAL] plpyhton

[c k]

Hello,
Does calling a pl/python function from each database connection load the
python interpreter each time? what are the effects of using pl/python
function in a environment where no. of concurrent connections are more and
each user calls a pl/python function?

Please give the details about how pl/python functions are executed.
Thanks and regards,

CPK

[GENERAL] postmaster.pid does not exist. cannot start postgres server on ubuntu

[Jennifer Trey]

I am getting the following error when I go to Application -> PostgreSQL ->
Reload Configuration

"
Please enter your password if requested.
pg_ctl: PID file "/home/Jen/Installed/PostgreSQL/data/postmaster.pid" does
not exist
Is server running?

Press  to continue...
"

I have installed Postgresql from the Enterprise DB installer for Ubuntu.

It has worked before. I just restarted my computer and now face this issue.

Off topic:
I have also had a different kind of issue, where a script added to
/etc/profile.d  called Startup.sh
causes postgre to fail to startup. This script is used on login to set
global environment variables, but I get the error that Postgre cannot
execute the script.
I tried chmod 777 on the script but still no luck. If I remove it, postgre
will start up so its not a biggy..
but is not a bug ?

Cheers, Jen

Re: [GENERAL] json data type

[Tom Lane]

Armand Turpel  writes:
> A project  in which i'm involved, make use of json data type for storing 
> some data sets. I have read that there is some work in progress to 
> implement json datatype support in postgres. So my question is; when and 
> in which version of postgres we can expect this implementation?

There's been discussion of a couple of different patches on
pgsql-hackers in the past few months --- see the archives if you want
details.  AFAICT there's not yet consensus on which way to proceed.
So while it's possible there will be something of the sort in 9.1,
I wouldn't bet on it.

As was mentioned, there's nothing stopping you from storing JSON stuff
as text right now.  If you have any thoughts on what you would need
expanded JSON support to actually *do*, feel free to comment in the
pgsql-hackers threads.

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] Connection pool

[Vick Khera]

On Tue, Nov 23, 2010 at 4:42 AM, Mark Gabriel Paylaga
 wrote:
> When two requests are received at the same time, both of the requests  will
> use the same db connection resulting in an error:
> Started transaction while transaction still
> active
>
>

You have multiple threads sharing the same connection?  You can't do that.

-- 
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] very basic SQL question

[Sam Mason]

On Tue, Nov 23, 2010 at 02:09:19PM +1030, Dan Kortschak wrote:
> I want to be able to insert,
> uniquely, biological sequences into a table returning the sequence id -
> this part is fine. However, if the sequence already exists in the table
> I want to return to id.

The term you're looking for is "UPSERT", the following looks relevant:

  
http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql

-- 
  Sam  http://samason.me.uk/

-- 
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] Debug advice when postgres connection maxing out

[Robert Gravsjö]

On 2010-11-22 20.41, anypossibility wrote:

Server: OS X 10.5
PostgreSQL version: 8.3


We experience this connection maxing out once in the full moon.
The request from client reaches to the server but client never receive response 
back.
The queries are very simple update on one record or select one record using 
primary key (checked current_query from pg_stat_activity).
Once this started, I normally disconnect all the client (quit client programs) 
however, the processes don't die on postgres server.
So I tried to quit PostgreSQL server using pg_ctl, no response. The only way to 
terminate is to restart the server (I will try kill -s INT next time.. i didn't 
know better).
Is there anything you can tell me from this symptoms or anything I can do to 
generate useful log to analyze?


If track_activities is on in your postgresql.conf you can check 
pg_stats_activity to see what query creates the problem.


Maybe http://www.postgresql.org/docs/8.3/static/monitoring-stats.html 
can give you some more ideas.


--
Regards,
Robert "roppert" Gravsjö

--
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] json data type

[Armand Turpel]

On 23/11/2010 09:34, Mike Christensen wrote:

I'd be interested in
knowing what functionality would come with a JSON data type, like
could you query for all rows with an X property of>  5 or something,
eg {Y: 1, X: 6}


Yes, as far as i know, you can make queries on json content

#
Scanned by MailMarshal - Marshal's comprehensive email content security solution. 
#


--
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] Connection pool

[Mark Gabriel Paylaga]

Hi,
Regarding connection pooling...
I may actually not need connection pooling...

I have one service to write to db. The service receives one or more requests
at the same time from different modules.

When two requests are received at the same time, both of the requests  will
use the same db connection resulting in an error:
Started transaction while transaction still
active

So what I did was to have dedicated db connections for each type of
request.  All of these requests are also put in a queue for their respective
types so that they will wait for the previous request to finish.

Is this an acceptable solution?  Because I tried pgbouncer and it actually
does not solve the scenario above


On Mon, Sep 6, 2010 at 10:12 PM, Mark Gabriel Paylaga wrote:

> Thanks! I'll look into it.
>
> On Mon, Sep 6, 2010 at 2:54 PM, Sergey Konoplev  wrote:
> > Hi,
> >
> > On 6 September 2010 10:34, Mark Paylaga  wrote:
> >> Hi sorry if this has been asked already.
> >> Is there any mechanism to do connection pooling for libpqxx already?
> >> Or any new developments for this?
> >>
> >> Our one dbwriter service instance recieves requests simultaneously to
> write to the database. We need separate db connections for each request. The
> simple way to do it is to hardcode and create x number of connections. Is
> there a way to just use connection pooling?
> >
> > There are two programs implementing connection pulling for PG. No need
> > to implement it in your application.
> >
> > - pgbouncer (http://wiki.postgresql.org/wiki/PgBouncer)
> > - pgpool (http://pgpool.projects.postgresql.org/)
> >
> >
> >>
> >>  Can anyone point me to the right direction? Thanks.
> >>
> >> Mark
> >>
> >>
> >>
> >> --
> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >>
> >
> >
> >
> > --
> > 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
> >
>

Re: [GENERAL] finding rows with invalid characters

[Dmitriy Igrishin]

Sim, are you sure that encoding of the dump and the database
in which you are trying to restore it are same ?

2010/11/23 Sim Zacks 

>  Thanks for your help.
>
> I think a trigger will actually be the easiest. The way i can tell if there
> is invalid data is simply to do an Upper(text) and if it has invalid data it
> fails.
>
> I dumped the fixed database. Now I have a years worth of backups that I
> can't restore a specific table from. It most probably will never mean
> anything. Every once in a while, I get asked to check what was in the db
> against what is in there, but this table will probably never be audited.
>
>
>  Sim
>
>
>
>  On 11/23/2010 10:33 AM, Dmitriy Igrishin wrote:
>
> Hey Sim,
>
> 2010/11/23 Sim Zacks 
>
>>  On 11/21/2010 05:55 PM, Dmitriy Igrishin wrote:
>>
>> Hey Sim,
>>
>> Maybe this helps:
>>
>> http://blog.tapoueh.org/articles/blog/_Getting_out_of_SQL_ASCII,_part_2.html
>>
>>  That worked to find some of them. I still needed to find a bunch of
>> others manually, such as 0xa0 and 0xd725 which weren't found with that
>> function. I finally figured out that
>> select * from emaildetails where emailbody like '%\xa0%' and
>> select * from emaildetails where emailbody like '%\xd7\x25%' would show me
>> all those rows.
>>
>> My 2 big problems now are:
>>
>> A) how to make sure that these chars are not inserted in the future. The
>> database should prevent them from being inserted.
>>
> Consider to use domains -- generic-based types with constraints -- instead
> of generic types.
> Use regular expressions in constraints.
> As alternative, you can use triggers for more complex validation. But
> domains in you case
> IMO will work good.
>
>
>> B) How to fix the backups that I have so that I can restore them. As I
>> mentioned, they are being taken with -Fc
>>
> Oops. Why not dump fixed database ?
>
>>
>> Sim
>>
>>
>>
>>  2010/11/21 Sim Zacks 
>>
>>> I am using PG 8.2.17 with UTF8 encoding.
>>> "PostgreSQL 8.2.17 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.1
>>> (Gentoo 4.1.1)"
>>>
>>> One of my tables somehow has invalid characters in it:
>>>
 ERROR:  invalid byte sequence for encoding "UTF8": 0xa9
 HINT:  This error can also happen if the byte sequence does not match
 the encoding expected by the server, which is controlled by
 "client_encoding".

>>> I have already manually found a number of the bad rows by running queries
>>> with text functions (upper) between groups of IDs until I found the specific
>>> bad row.
>>>
>>> 1) Is there a quicker way to get a list of all rows with invalid
>>> characters
>>> 2) Shouldn't the database prevent these rows from being entered in the
>>> first place?
>>> 3) I have backups of this database (using -Fc) and I noticed that on
>>> restore, this table is not restored because of this error. Is there a way to
>>> fix the existing backups, or tell the restore to ignore bad rows instead of
>>> erroring out the whole table?
>>>
>>> Thanks
>>> Sim
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>>
>>
>> --
>> // Dmitriy.
>>
>>
>>
>>
>
>
> --
> // Dmitriy.
>
>
>
>


-- 
// Dmitriy.

Re: [GENERAL] finding rows with invalid characters

[Sim Zacks]

Thanks for your help.

I think a trigger will actually be the easiest. The way i can tell if 
there is invalid data is simply to do an Upper(text) and if it has 
invalid data it fails.


I dumped the fixed database. Now I have a years worth of backups that I 
can't restore a specific table from. It most probably will never mean 
anything. Every once in a while, I get asked to check what was in the db 
against what is in there, but this table will probably never be audited.



Sim



On 11/23/2010 10:33 AM, Dmitriy Igrishin wrote:


Hey Sim,

2010/11/23 Sim Zacks mailto:s...@compulab.co.il>>

On 11/21/2010 05:55 PM, Dmitriy Igrishin wrote:

Hey Sim,

Maybe this helps:
http://blog.tapoueh.org/articles/blog/_Getting_out_of_SQL_ASCII,_part_2.html

That worked to find some of them. I still needed to find a bunch
of others manually, such as 0xa0 and 0xd725 which weren't found
with that function. I finally figured out that
select * from emaildetails where emailbody like '%\xa0%' and
select * from emaildetails where emailbody like '%\xd7\x25%' would
show me all those rows.

My 2 big problems now are:

A) how to make sure that these chars are not inserted in the
future. The database should prevent them from being inserted.

Consider to use domains -- generic-based types with constraints -- 
instead of generic types.

Use regular expressions in constraints.
As alternative, you can use triggers for more complex validation. But 
domains in you case

IMO will work good.


B) How to fix the backups that I have so that I can restore them.
As I mentioned, they are being taken with -Fc

Oops. Why not dump fixed database ?


Sim




2010/11/21 Sim Zacks mailto:s...@compulab.co.il>>

I am using PG 8.2.17 with UTF8 encoding.
"PostgreSQL 8.2.17 on i686-pc-linux-gnu, compiled by GCC gcc
(GCC) 4.1.1 (Gentoo 4.1.1)"

One of my tables somehow has invalid characters in it:

ERROR:  invalid byte sequence for encoding "UTF8": 0xa9
HINT:  This error can also happen if the byte sequence
does not match the encoding expected by the server, which
is controlled by "client_encoding".

I have already manually found a number of the bad rows by
running queries with text functions (upper) between groups of
IDs until I found the specific bad row.

1) Is there a quicker way to get a list of all rows with
invalid characters
2) Shouldn't the database prevent these rows from being
entered in the first place?
3) I have backups of this database (using -Fc) and I noticed
that on restore, this table is not restored because of this
error. Is there a way to fix the existing backups, or tell
the restore to ignore bad rows instead of erroring out the
whole table?

Thanks
Sim

-- 
Sent via pgsql-general mailing list

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




-- 
// Dmitriy.








--
// Dmitriy.

Re: [GENERAL] json data type

[Mike Christensen]

> A project  in which i'm involved, make use of json data type for storing
> some data sets. I have read that there is some work in progress to implement
> json datatype support in postgres. So my question is; when and in which
> version of postgres we can expect this implementation?

I actually store a pretty good amount of JSON serialized data for my
website, kitchenpc.com which uses Postgres 9.0.  I don't have any
problems with this, I just use a text data type.  I'd be interested in
knowing what functionality would come with a JSON data type, like
could you query for all rows with an X property of > 5 or something,
eg {Y: 1, X: 6}

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] json data type

[Armand Turpel]

Hi,

A project  in which i'm involved, make use of json data type for storing 
some data sets. I have read that there is some work in progress to 
implement json datatype support in postgres. So my question is; when and 
in which version of postgres we can expect this implementation?


Thanks,
atu



#
Scanned by MailMarshal - Marshal's comprehensive email content security solution. 
#


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