date:20090804


Hi

Does postgres have an embedded mode to allow a database to be embedded 
with Java application without requiring seperate db manager, like the 
derby database does ?


Paul

--
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] Refer to another database

2009-08-04 Thread Harald Fuchs

In article <4a77c4af.2060...@gmx.de>,
Andreas Kalsch  writes:

> To be completely
> in context of a schema - so that I can use all tables without the
> prefix - I have to reset the search_path very often.

Why?  Just say "ALTER DATABASE foo SET search_path = public, bar, baz"
once and you're done.

-- 
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] character 0xe29986 of encoding "UTF8" has no equivalent in "LATIN2"

2009-08-04 Thread Andreas Kalsch


Alban,

what I do to simplify the data chain:

HTTP encoding > PHP string encoding > client connection > server - all 
is UTF8. Plus invalid byte check in PHP (or server).


What I have tested inside Postgres is entering a 3 byte UTF8 character 
to this function. And I have got an error. This is a character I will 
not filter out, if some Unicode artists will enter it. It is an 
international website and the simplification is just for indexing.


But I think that this will not solve the problem and I have to use 
Python or Perl to get it done.



Alban Hertroys schrieb:

On 4 Aug 2009, at 24:57, Andreas Kalsch wrote:

I think the real problem is: Where do you lose the original encoding 
the users input their data with? If you specify that encoding on the 
connection and send it to a database that can handle UTF-8 then you 
shouldn't be getting any conversion problems in the first place.
Nowhere - I will validate input data on the client side (PHP or 
Python) and send it to the server. Of course the only encoding I will 
use on any side is UTF8. I just wnated to use this Latin thing for 
simplification of characters.


Yes you are. How could your users input invalid characters in the 
first place if that were not the case? You're not suggesting they 
managed to enter characters in an encoding for which they weren't 
valid on their own systems, do you?[1]


You say your client is using PHP or Python, which suggests it's a 
website. That means the input goes like this: web browser -> website 
-> database. All three of those steps use some encoding and you can 
take them into account. That should prevent this problem altogether.


You have control over which encoding your client and the database use, 
and the web browser tells what encoding it used in the POST request so 
you can pass that along to the database when storing data or convert 
it in your client.


[1] There exists of course a small group of people who enjoy posting 
raw byte data to a web-form, but would it matter whether they'd get an 
error about their encoding or not? They do not intend to enter valid 
data after all ;)


Alban Hertroys

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


!DSPAM:933,4a7820e310131447310801!






--
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] parameters in functions and overlap with names of columns

2009-08-04 Thread Sam Mason

On Tue, Aug 04, 2009 at 02:20:00PM +0200, Ivan Sergio Borgonovo wrote:
> create or replace function(...
> declare
>   col1 varchar(32);

> Unfortunately I can't schema specify the column to avoid name
> overlap.

I think this is a limitation of plpgsql's parser; I tend to declare
local variables with an underscore prefix such as "_col1" in your
example.

> Is there another way other than just simply rename the variable?

I don't think so.

-- 
  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] Does derby have an embedded Mode like Derby ?

2009-08-04 Thread Sam Mason

On Tue, Aug 04, 2009 at 01:37:34PM +0100, Paul Taylor wrote:
> Does postgres have an embedded mode to allow a database to be embedded 
> with Java application without requiring seperate db manager, like the 
> derby database does ?

No, and I think the consensus is that this would be bad.  See:

  http://wiki.postgresql.org/wiki/FAQ#Can_PostgreSQL_be_embedded.3F

-- 
  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] Does derby have an embedded Mode like Derby ?


Sam Mason wrote:

On Tue, Aug 04, 2009 at 01:37:34PM +0100, Paul Taylor wrote:
  
Does postgres have an embedded mode to allow a database to be embedded 
with Java application without requiring seperate db manager, like the 
derby database does ?



No, and I think the consensus is that this would be bad.  See:

  http://wiki.postgresql.org/wiki/FAQ#Can_PostgreSQL_be_embedded.3F

  
Thats a shame, I wanted to write junit test for a java program that 
queried a database, requiring a full database to be available for unit 
tests is not really an environment I want to have.


Paul

--
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] Does derby have an embedded Mode like Derby ?

In response to Paul Taylor :

> Sam Mason wrote:
> > On Tue, Aug 04, 2009 at 01:37:34PM +0100, Paul Taylor wrote:
> >   
> >> Does postgres have an embedded mode to allow a database to be embedded 
> >> with Java application without requiring seperate db manager, like the 
> >> derby database does ?
> >
> > No, and I think the consensus is that this would be bad.  See:
> >
> >   http://wiki.postgresql.org/wiki/FAQ#Can_PostgreSQL_be_embedded.3F
>
> Thats a shame, I wanted to write junit test for a java program that 
> queried a database, requiring a full database to be available for unit 
> tests is not really an environment I want to have.

Has it occurred to you that testing a DB client when there's no
DB isn't really a very accurate or realistic test?

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
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] parameters in functions and overlap with names of columns

2009/8/4 Ivan Sergio Borgonovo :
> I've
>
> create or replace function(...
>
> declare
>  col1 varchar(32);
> ...
>
>  create table pippo(
>    col1 varchar(32),
> ...
>
> Unfortunately I can't schema specify the column to avoid name
> overlap.
>
> Is there another way other than just simply rename the variable?

yes - the most common is an using of prefix '_' for local plpgsql
variables. Other possibility is using qualified names.

Pavel

>
> thanks
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
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] Does derby have an embedded Mode like Derby ?


Bill Moran wrote:

In response to Paul Taylor :

  

Sam Mason wrote:


On Tue, Aug 04, 2009 at 01:37:34PM +0100, Paul Taylor wrote:
  
  
Does postgres have an embedded mode to allow a database to be embedded 
with Java application without requiring seperate db manager, like the 
derby database does ?


No, and I think the consensus is that this would be bad.  See:

  http://wiki.postgresql.org/wiki/FAQ#Can_PostgreSQL_be_embedded.3F
  
Thats a shame, I wanted to write junit test for a java program that 
queried a database, requiring a full database to be available for unit 
tests is not really an environment I want to have.



Has it occurred to you that testing a DB client when there's no
DB isn't really a very accurate or realistic test?

  
I am testing the code that extracts information from a read only 
database. These are UNIT tests so only interested in getting the right 
results given a particular set of data, anything else is a distraction.


Paul

--
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] Does derby have an embedded Mode like Derby ?

2009-08-04 Thread Thomas Kellerer


Paul Taylor, 04.08.2009 15:48:
Thats a shame, I wanted to write junit test for a java program that 
queried a database, requiring a full database to be available for unit 
tests is not really an environment I want to have.


Well if you want to test the database, you will need a full database. 



But you can easily setup and start a Postgres server through a batch file/shell script. 
I have a small batch file that simply uses the binary ZIP distribution to run initdb and create the initial databases. We are using this to bundle Postgres e.g. with a Tomcat web application. 


You don't really need to run an installer and/or create registry entries (for 
windows). This would then resemble more the Derby network server setup.

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] parameters in functions and overlap with names of columns

2009-08-04 Thread Ivan Sergio Borgonovo

On Tue, 4 Aug 2009 16:01:58 +0200
Pavel Stehule  wrote:

> 2009/8/4 Ivan Sergio Borgonovo :
> > I've
> >
> > create or replace function(...
> >
> > declare
> >  col1 varchar(32);
> > ...
> >
> >  create table pippo(
> >    col1 varchar(32),
> > ...
> >
> > Unfortunately I can't schema specify the column to avoid name
> > overlap.
> >
> > Is there another way other than just simply rename the variable?
> 
> yes - the most common is an using of prefix '_' for local plpgsql
> variables. Other possibility is using qualified names.

Just to be sure... by qualified names you mean schema qualified name
or table qualified names in case of columns... right...

For a second I had the hope there was another way other than having a
col1, a _col1 and a __col1 too ;)

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
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] Does derby have an embedded Mode like Derby ?

In response to Paul Taylor :

> Bill Moran wrote:
> > In response to Paul Taylor :
> >
> >> Sam Mason wrote:
> >> 
> >>> On Tue, Aug 04, 2009 at 01:37:34PM +0100, Paul Taylor wrote:
> >>>   
> >>>   
>  Does postgres have an embedded mode to allow a database to be embedded 
>  with Java application without requiring seperate db manager, like the 
>  derby database does ?
>  
> >>> No, and I think the consensus is that this would be bad.  See:
> >>>
> >>>   http://wiki.postgresql.org/wiki/FAQ#Can_PostgreSQL_be_embedded.3F
> >>>   
> >> Thats a shame, I wanted to write junit test for a java program that 
> >> queried a database, requiring a full database to be available for unit 
> >> tests is not really an environment I want to have.
> >
> > Has it occurred to you that testing a DB client when there's no
> > DB isn't really a very accurate or realistic test?
>
> I am testing the code that extracts information from a read only 
> database. These are UNIT tests so only interested in getting the right 
> results given a particular set of data, anything else is a distraction.

Then replace the DB client class with a class that returns fabricated
data for the purpose of your test.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
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] Does derby have an embedded Mode like Derby ?

2009-08-04 Thread Bayless Kirtley






Paul Taylor, 04.08.2009 15:48:
Thats a shame, I wanted to write junit test for a java program that 
queried a database, requiring a full database to be available for unit 
tests is not really an environment I want to have.


Well if you want to test the database, you will need a full database.

But you can easily setup and start a Postgres server through a batch 
file/shell script. I have a small batch file that simply uses the binary 
ZIP distribution to run initdb and create the initial databases. We are 
using this to bundle Postgres e.g. with a Tomcat web application.
You don't really need to run an installer and/or create registry entries 
(for windows). This would then resemble more the Derby network server 
setup.


Thomas


Of course you can always use Derby for testing the Postgres later. I have 
found HSQLDB to be closer to Postgres than Derby is. I do seem to recall the 
Netbeans group recommends using sever mode during development then switch to 
imbedded for distribution. I have done it both ways with equal success and 
similar efforts.


Bayless


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

[GENERAL] InitDB 8.3.7 fails on Windows - Failed system call was MapViewOfFileEx

2009-08-04 Thread Abraham, Danny

Hi,

InitDB of 8.3.7 fails.
Windows XP.
Compiled with VC2008, SP1
 
creating template1 database in ... 

2009-08-04 11:46:53.954 GMTFATAL:  could not create shared memory segment: 8
2009-08-04 11:46:53.970 GMTDETAIL:  Failed system call was MapViewOfFileEx.

child process exited with exit code 1

Any idea?

Thanks

Danny

-- 
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] parameters in functions and overlap with names of columns

2009-08-04 Thread Alvaro Herrera

Ivan Sergio Borgonovo wrote:
> On Tue, 4 Aug 2009 16:01:58 +0200
> Pavel Stehule  wrote:
> 
> > 2009/8/4 Ivan Sergio Borgonovo :

> > > Is there another way other than just simply rename the variable?
> > 
> > yes - the most common is an using of prefix '_' for local plpgsql
> > variables. Other possibility is using qualified names.
> 
> Just to be sure... by qualified names you mean schema qualified name
> or table qualified names in case of columns... right...

In this case I think it also means you can qualify the variable names
with the function name; and/or declare named blocks inside the function,
and qualify the variables with the block name.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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] Does derby have an embedded Mode like Derby ?


Bayless Kirtley wrote:





Paul Taylor, 04.08.2009 15:48:
Thats a shame, I wanted to write junit test for a java program that 
queried a database, requiring a full database to be available for 
unit tests is not really an environment I want to have.


Well if you want to test the database, you will need a full database.

But you can easily setup and start a Postgres server through a batch 
file/shell script. I have a small batch file that simply uses the 
binary ZIP distribution to run initdb and create the initial 
databases. We are using this to bundle Postgres e.g. with a Tomcat 
web application.
You don't really need to run an installer and/or create registry 
entries (for windows). This would then resemble more the Derby 
network server setup.


Thomas


Of course you can always use Derby for testing the Postgres later. I 
have found HSQLDB to be closer to Postgres than Derby is. I do seem to 
recall the Netbeans group recommends using sever mode during 
development then switch to imbedded for distribution. I have done it 
both ways with equal success and similar efforts.


Bayless


Thanks  I am trying to use Derby but fallen foul of Postgres array 
datatype which Derby doesn't support, Ive just had a quick look at 
HSQLDB and it doesn't appear to support it either.


Paul


--
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] parameters in functions and overlap with names of columns

Ivan Sergio Borgonovo  writes:
> Pavel Stehule  wrote:
>> yes - the most common is an using of prefix '_' for local plpgsql
>> variables. Other possibility is using qualified names.

> Just to be sure... by qualified names you mean schema qualified name
> or table qualified names in case of columns... right...

Right.  The plpgsql parser is (just barely) smart enough to not try
to substitute a local variable name for the second part of a qualified
name "a.b".  So you could write "tab.col1" in your queries.

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] parameters in functions and overlap with names of columns

2009/8/4 Alvaro Herrera :
> Ivan Sergio Borgonovo wrote:
>> On Tue, 4 Aug 2009 16:01:58 +0200
>> Pavel Stehule  wrote:
>>
>> > 2009/8/4 Ivan Sergio Borgonovo :
>
>> > > Is there another way other than just simply rename the variable?
>> >
>> > yes - the most common is an using of prefix '_' for local plpgsql
>> > variables. Other possibility is using qualified names.
>>
>> Just to be sure... by qualified names you mean schema qualified name
>> or table qualified names in case of columns... right...
>
> In this case I think it also means you can qualify the variable names
> with the function name; and/or declare named blocks inside the function,
> and qualify the variables with the block name.

yes
Pavel

>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
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] InitDB 8.3.7 fails on Windows - Failed system call was MapViewOfFileEx

2009-08-04 Thread Magnus Hagander

On Tue, Aug 4, 2009 at 16:48, Abraham, Danny wrote:
> Hi,
>
> InitDB of 8.3.7 fails.
> Windows XP.
> Compiled with VC2008, SP1

Please note that VC2008 is not a supported build environment for the
backend. you need to get VC2005.

> creating template1 database in ...
>
> 2009-08-04 11:46:53.954 GMTFATAL:  could not create shared memory segment: 8
> 2009-08-04 11:46:53.970 GMTDETAIL:  Failed system call was MapViewOfFileEx.

That's "not enough storage is available". Kind of sounds as if you
were running out of address space. Three questions:
1) Anything modified at all in the source or share files, like
postgresql.conf.sample?
2) Any antivirus software on the machine?
3) Any chance you're simply out of memory? :-)

-- 
 Magnus Hagander
 Self: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] character 0xe29986 of encoding "UTF8" has no equivalent in "LATIN2"

2009-08-04 Thread Alban Hertroys


On 4 Aug 2009, at 15:02, Andreas Kalsch wrote:


Alban,

what I do to simplify the data chain:

HTTP encoding > PHP string encoding > client connection > server -  
all is UTF8. Plus invalid byte check in PHP (or server).


You're missing my point. You start dealing with the encoding of the  
data you receive too late. Here's what happens:


1). User enters data in your form using his web browser. This user has  
set his OS to some locale and encoding, say it's WIN1252-JP.


2). User presses "submit" and the web-browser generates a POST request  
that CONTAINS the encoding the user used to enter his data.


3). Your scripts process this data as if it were UTF-8, but it ISN'T  
UTF-8, it's WIN1252-JP.


4). You try to store this (now) mixedly encoded data into an UTF-8  
encoded database and it fails for obvious reasons.



To solve this you can try to filter out "wrong" characters, but they  
are not wrong. They're just differently encoded than you think they are.


What you should be doing is look at the encoding specified in the POST  
request and either:


a.) convert the data to UTF-8 before you start processing it (both PHP  
and Python have functions to do that) or,


b.) pass the encoding on to your database queries so that the database  
knows how to convert them to UTF-8.


Of course it can't hurt to verify that the encoding specified in the  
POST request does indeed match the posted data, but that's a different  
story.


P.S. Please don't top post and keep some context of what you're  
replying to. Your messages are a bit confusing the way you write them.


What I have tested inside Postgres is entering a 3 byte UTF8  
character to this function. And I have got an error. This is a  
character I will not filter out, if some Unicode artists will enter  
it. It is an international website and the simplification is just  
for indexing.


But I think that this will not solve the problem and I have to use  
Python or Perl to get it done.



Alban Hertroys schrieb:

On 4 Aug 2009, at 24:57, Andreas Kalsch wrote:

I think the real problem is: Where do you lose the original  
encoding the users input their data with? If you specify that  
encoding on the connection and send it to a database that can  
handle UTF-8 then you shouldn't be getting any conversion  
problems in the first place.
Nowhere - I will validate input data on the client side (PHP or  
Python) and send it to the server. Of course the only encoding I  
will use on any side is UTF8. I just wnated to use this Latin  
thing for simplification of characters.


Yes you are. How could your users input invalid characters in the  
first place if that were not the case? You're not suggesting they  
managed to enter characters in an encoding for which they weren't  
valid on their own systems, do you?[1]


You say your client is using PHP or Python, which suggests it's a  
website. That means the input goes like this: web browser ->  
website -> database. All three of those steps use some encoding and  
you can take them into account. That should prevent this problem  
altogether.


You have control over which encoding your client and the database  
use, and the web browser tells what encoding it used in the POST  
request so you can pass that along to the database when storing  
data or convert it in your client.


[1] There exists of course a small group of people who enjoy  
posting raw byte data to a web-form, but would it matter whether  
they'd get an error about their encoding or not? They do not intend  
to enter valid data after all ;)


Alban Hertroys

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








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






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,4a784d8810131832511190!



--
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] parameters in functions and overlap with names of columns

2009/8/4 Ivan Sergio Borgonovo :
> On Tue, 4 Aug 2009 16:01:58 +0200
> Pavel Stehule  wrote:
>
>> 2009/8/4 Ivan Sergio Borgonovo :
>> > I've
>> >
>> > create or replace function(...
>> >
>> > declare
>> >  col1 varchar(32);
>> > ...
>> >
>> >  create table pippo(
>> >    col1 varchar(32),
>> > ...
>> >
>> > Unfortunately I can't schema specify the column to avoid name
>> > overlap.
>> >
>> > Is there another way other than just simply rename the variable?
>>
>> yes - the most common is an using of prefix '_' for local plpgsql
>> variables. Other possibility is using qualified names.
>
> Just to be sure... by qualified names you mean schema qualified name
> or table qualified names in case of columns... right...
>
> For a second I had the hope there was another way other than having a
> col1, a _col1 and a __col1 too ;)
>

Maybe prefix _ isn't nice, but it is 100% safe. I use both - prefix
for variables and aliases for columns. The collision of column and
variable names is over lot of very strange bugs, and any way that can
protect us is perfect.

Pavel

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

-- 
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] Does derby have an embedded Mode like Derby ?


Thomas Kellerer wrote:

Paul Taylor, 04.08.2009 15:48:
Thats a shame, I wanted to write junit test for a java program that 
queried a database, requiring a full database to be available for 
unit tests is not really an environment I want to have.


Well if you want to test the database, you will need a full database.
It was a simple question, does Postgres have an embedded mode (which is 
still a full database) but unfortunately it does not.


But you can easily setup and start a Postgres server through a batch 
file/shell script. I have a small batch file that simply uses the 
binary ZIP distribution to run initdb and create the initial 
databases. We are using this to bundle Postgres e.g. with a Tomcat web 
application.
You don't really need to run an installer and/or create registry 
entries (for windows). This would then resemble more the Derby network 
server setup.
Yeah, but this is messy and low because you have to wait the database to 
be fully initilized before running the test. And for unit tests you 
should be start the database for each test, but wouldnt be possible with 
your method it would be to slow. Furthermore this is a Windows only 
solution, there would be  additional work to do  to setup for Mac or Linux


thanks anyway

Paul






--
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] character 0xe29986 of encoding "UTF8" has no equivalent in "LATIN2"

2009-08-04 Thread Andreas Kalsch

I know what you are talking about, but I am not sure how many websites 
really check for incoming encoding. Usually you trust that the client 
will use the same encoding for sending data as the server has sent. 
(This is what I mean with my simplified chain)


It's some extra work to do converting from exotic sets and it's the way 
to go for a site with high traffic. I would prefer to do it on the 
PHP/Python side to send in one encoding to the database server, because 
it could be that you additionally send own strings.



Alban Hertroys schrieb:

On 4 Aug 2009, at 15:02, Andreas Kalsch wrote:


Alban,

what I do to simplify the data chain:

HTTP encoding > PHP string encoding > client connection > server - 
all is UTF8. Plus invalid byte check in PHP (or server).


You're missing my point. You start dealing with the encoding of the 
data you receive too late. Here's what happens:


1). User enters data in your form using his web browser. This user has 
set his OS to some locale and encoding, say it's WIN1252-JP.


2). User presses "submit" and the web-browser generates a POST request 
that CONTAINS the encoding the user used to enter his data.


3). Your scripts process this data as if it were UTF-8, but it ISN'T 
UTF-8, it's WIN1252-JP.


4). You try to store this (now) mixedly encoded data into an UTF-8 
encoded database and it fails for obvious reasons.



To solve this you can try to filter out "wrong" characters, but they 
are not wrong. They're just differently encoded than you think they are.


What you should be doing is look at the encoding specified in the POST 
request and either:


a.) convert the data to UTF-8 before you start processing it (both PHP 
and Python have functions to do that) or,


b.) pass the encoding on to your database queries so that the database 
knows how to convert them to UTF-8.


Of course it can't hurt to verify that the encoding specified in the 
POST request does indeed match the posted data, but that's a different 
story.


P.S. Please don't top post and keep some context of what you're 
replying to. Your messages are a bit confusing the way you write them.


What I have tested inside Postgres is entering a 3 byte UTF8 
character to this function. And I have got an error. This is a 
character I will not filter out, if some Unicode artists will enter 
it. It is an international website and the simplification is just for 
indexing.


But I think that this will not solve the problem and I have to use 
Python or Perl to get it done.



Alban Hertroys schrieb:

On 4 Aug 2009, at 24:57, Andreas Kalsch wrote:

I think the real problem is: Where do you lose the original 
encoding the users input their data with? If you specify that 
encoding on the connection and send it to a database that can 
handle UTF-8 then you shouldn't be getting any conversion problems 
in the first place.
Nowhere - I will validate input data on the client side (PHP or 
Python) and send it to the server. Of course the only encoding I 
will use on any side is UTF8. I just wnated to use this Latin thing 
for simplification of characters.


Yes you are. How could your users input invalid characters in the 
first place if that were not the case? You're not suggesting they 
managed to enter characters in an encoding for which they weren't 
valid on their own systems, do you?[1]


You say your client is using PHP or Python, which suggests it's a 
website. That means the input goes like this: web browser -> website 
-> database. All three of those steps use some encoding and you can 
take them into account. That should prevent this problem altogether.


You have control over which encoding your client and the database 
use, and the web browser tells what encoding it used in the POST 
request so you can pass that along to the database when storing data 
or convert it in your client.


[1] There exists of course a small group of people who enjoy posting 
raw byte data to a web-form, but would it matter whether they'd get 
an error about their encoding or not? They do not intend to enter 
valid data after all ;)


Alban Hertroys

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








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






Alban Hertroys

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


!DSPAM:933,4a784d8610131719966171!






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

[GENERAL] Re: character 0xe29986 of encoding "UTF8" has no equivalent in "LATIN2"

2009-08-04 Thread Greg Stark

On Tue, Aug 4, 2009 at 4:02 PM, Alban
Hertroys wrote:
> P.S. Please don't top post and keep some context of what you're replying to.
> Your messages are a bit confusing the way you write them.


These arguments are more convincing if you don't leave the remainder
of the OP's message quoted right below them...

-- 
greg
http://mit.edu/~gsstark/resume.pdf

-- 
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] Does derby have an embedded Mode like Derby ?


Bill Moran wrote:

In response to Paul Taylor :

  

Bill Moran wrote:


In response to Paul Taylor :

  

Sam Mason wrote:



On Tue, Aug 04, 2009 at 01:37:34PM +0100, Paul Taylor wrote:
  
  
  
Does postgres have an embedded mode to allow a database to be embedded 
with Java application without requiring seperate db manager, like the 
derby database does ?



No, and I think the consensus is that this would be bad.  See:

  http://wiki.postgresql.org/wiki/FAQ#Can_PostgreSQL_be_embedded.3F
  
  
Thats a shame, I wanted to write junit test for a java program that 
queried a database, requiring a full database to be available for unit 
tests is not really an environment I want to have.


Has it occurred to you that testing a DB client when there's no
DB isn't really a very accurate or realistic test?
  
I am testing the code that extracts information from a read only 
database. These are UNIT tests so only interested in getting the right 
results given a particular set of data, anything else is a distraction.



Then replace the DB client class with a class that returns fabricated
data for the purpose of your test.
  

Won't work because I am writing SQL and I want to test the SQL is correct


--
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] Does derby have an embedded Mode like Derby ?

2009-08-04 Thread Thomas Kellerer

Paul Taylor wrote on 04.08.2009 17:04:

Well if you want to test the database, you will need a full database.
It was a simple question, does Postgres have an embedded mode (which is
still a full database) but unfortunately it does not.

I'm just trying to help get your test environment into proper shape. If you are not open for other
solutions, then ignore the rest of this email

Yeah, but this is messy and low because you have to wait the database to
be fully initilized before running the test.

Depends on what you mean with "initialize". You only need to run initdb once. After that you can
e.g. use DbUnit to populate the tables needed for testing.

If you do that, you can simply have an empty database cluster directory that you unzip before
running the tests suite.

And for unit tests you
should be start the database for each test, but wouldnt be possible with
your method it would be to slow.
Well if you use something like DbUnit, you can simply leave the postmaster running and re-initialize
the tables for each test (something you would need to do with Derby as well).

Furthermore this is a Windows only
solution, there would be additional work to do to setup for Mac or Linux
No I don't think it's windows only. The batch file simply calls the already existings commandline
programs from the Postgres bin directory. They names are identical across all platforms. The only
thing would be the zipped database cluster as that is platform specific.

Btw: I find the H2 database a lot closer to Postgres than HSQLDB. The author is actively trying to
add compatibility levels for various DBMS. It might be worth looking into that as well.

But if you e.g. have triggers or stored procedures you'll have to use "the real
thing".

We have e.g. unit test for one of our application that we ran on MySQL just for the fun of it, and
it failed miserable due to the different locking behaviour between Postgres and MySQL. I would
expect similar problems with heavy transactional tests with any embedded engine as well.

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] Does derby have an embedded Mode like Derby ?

2009-08-04 Thread Karsten Hilbert

On Tue, Aug 04, 2009 at 04:04:42PM +0100, Paul Taylor wrote:

> >You don't really need to run an installer and/or create registry
> >entries (for windows). This would then resemble more the Derby
> >network server setup.
> Yeah, but this is messy and low because you have to wait the
> database to be fully initilized before running the test. And for
> unit tests you should be start the database for each test, but
> wouldnt be possible with your method it would be to slow.

One could drop and re-create the databases as needed. No
need to re-initdb the cluster or even stop the PostgreSQL
process. That wouldn't be slow.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

-- 
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] Does derby have an embedded Mode like Derby ?

2009-08-04 Thread Richard Sickler

On Tue, Aug 4, 2009 at 8:37 AM, Paul Taylor wrote:
> Bill Moran wrote:
>>
>> In response to Paul Taylor :
>>
>>
>>>
>>> Bill Moran wrote:
>>>

 In response to Paul Taylor :


>
> Sam Mason wrote:
>
>>
>> On Tue, Aug 04, 2009 at 01:37:34PM +0100, Paul Taylor wrote:
>>
>>>
>>> Does postgres have an embedded mode to allow a database to be
>>> embedded with Java application without requiring seperate db manager, 
>>> like
>>> the derby database does ?
>>>
>>
>> No, and I think the consensus is that this would be bad.  See:
>>
>>  http://wiki.postgresql.org/wiki/FAQ#Can_PostgreSQL_be_embedded.3F
>>
>
> Thats a shame, I wanted to write junit test for a java program that
> queried a database, requiring a full database to be available for unit 
> tests
> is not really an environment I want to have.
>

 Has it occurred to you that testing a DB client when there's no
 DB isn't really a very accurate or realistic test?

>>>
>>> I am testing the code that extracts information from a read only
>>> database. These are UNIT tests so only interested in getting the right
>>> results given a particular set of data, anything else is a distraction.
>>>
>>
>> Then replace the DB client class with a class that returns fabricated
>> data for the purpose of your test.
>>
>
> Won't work because I am writing SQL and I want to test the SQL is correct
>
>

Can you do what you need to in a virtual machine?  I use several
virtual machines in my development environment. One for cranking out
code.  One as a "clean" test environment.

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

-- 
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] Does derby have an embedded Mode like Derby ?

2009-08-04 Thread pepone.onrez

>Thats a shame, I wanted to write junit test for a java program that queried a 
>database, requiring a full database to be available for unit tests is not 
>really an >environment I want to have.

Why not? if you doesn't test the real thing you test is meaningless.
you should test the real database your are going to use with a setup
as similar to the deployment setup you are going to use in production.

-- 
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] Does derby have an embedded Mode like Derby ?

In response to Paul Taylor :

> Bill Moran wrote:
> >
> > Then replace the DB client class with a class that returns fabricated
> > data for the purpose of your test.
> >   
> Won't work because I am writing SQL and I want to test the SQL is correct

Well, be warned that not all alternatives to PostgreSQL will have the
same SQL compliance as Postgres ... so substituting another db backend
is going to be less than reliable.

I hope you don't take these next comments as an attack or anything, but
I think your whole approach to testing is flawed.  The fact that tests
are complicated to set up and take a while to run is just life.  I mean,
who cares if they take a while to run?  Computers don't need to sleep, so
have them run overnight.  And any test that's actually comprehensive is
going to take effort to write anyway.  Thing is, you'll only be setting
up the PG startup/teardown stuff once, then any test that needs it can
call those functions/scripts/whatever.  If you need more computers or a
different OS, then virtualize.  All the tools are there for you.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

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

[GENERAL] Line Number

2009-08-04 Thread Bob Pawley

I haven''t required line numbers on my query pages before now.

I'm using PGAdmin version 1.8.4.

Is this something that can be turned on?

Bob

Re: [GENERAL] Line Number

2009-08-04 Thread Joshua D. Drake

On Tue, 2009-08-04 at 09:30 -0700, Bob Pawley wrote:
> I haven''t required line numbers on my query pages before now.
>  
> I'm using PGAdmin version 1.8.4.
>  
> Is this something that can be turned on?

Maybe but it should be asked here:

http://www.pgadmin.org/support/list.php


Joshua D. Drake

-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


-- 
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] Line Number

2009-08-04 Thread Raymond O'Donnell

On 04/08/2009 17:30, Bob Pawley wrote:
> I haven''t required line numbers on my query pages before now.
>  
> I'm using PGAdmin version 1.8.4.
>  
> Is this something that can be turned on?

Hi Bob,

Do you mean the source code of your queries, in the Edit Query window?
If so, it doesn't show line numbers in the left column (which would be
handy enough IMHO); but the status bar does tell you the current
row/column of the cursor, which is the next best thing.

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
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] Does derby have an embedded Mode like Derby ?


Bill Moran wrote:

In response to Paul Taylor :

  

Bill Moran wrote:


Then replace the DB client class with a class that returns fabricated
data for the purpose of your test.
  
  

Won't work because I am writing SQL and I want to test the SQL is correct



Well, be warned that not all alternatives to PostgreSQL will have the
same SQL compliance as Postgres ... so substituting another db backend
is going to be less than reliable.

I hope you don't take these next comments as an attack or anything, but
I think your whole approach to testing is flawed.  The fact that tests
are complicated to set up and take a while to run is just life.  I mean,
who cares if they take a while to run?  Computers don't need to sleep, so
have them run overnight.  And any test that's actually comprehensive is
going to take effort to write anyway.  Thing is, you'll only be setting
up the PG startup/teardown stuff once, then any test that needs it can
call those functions/scripts/whatever.  If you need more computers or a
different OS, then virtualize.  All the tools are there for you.

  
No, I don't take it personally but I think you are missing the point 
these are UNIT test not INTEGRATION tests. As such they need to be run 
everytime a developer wants to commit some changes to the codebase and 
therefore should be quick and unobstrusive to run.


Paul


--
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] Does derby have an embedded Mode like Derby ?

In response to Paul Taylor :

> Bill Moran wrote:
> > In response to Paul Taylor :
> >
> >> Bill Moran wrote:
> >> 
> >>> Then replace the DB client class with a class that returns fabricated
> >>> data for the purpose of your test.
> >>>   
> >>>   
> >> Won't work because I am writing SQL and I want to test the SQL is correct
> >> 
> >
> > Well, be warned that not all alternatives to PostgreSQL will have the
> > same SQL compliance as Postgres ... so substituting another db backend
> > is going to be less than reliable.
> >
> > I hope you don't take these next comments as an attack or anything, but
> > I think your whole approach to testing is flawed.  The fact that tests
> > are complicated to set up and take a while to run is just life.  I mean,
> > who cares if they take a while to run?  Computers don't need to sleep, so
> > have them run overnight.  And any test that's actually comprehensive is
> > going to take effort to write anyway.  Thing is, you'll only be setting
> > up the PG startup/teardown stuff once, then any test that needs it can
> > call those functions/scripts/whatever.  If you need more computers or a
> > different OS, then virtualize.  All the tools are there for you.
>
> No, I don't take it personally but I think you are missing the point 
> these are UNIT test not INTEGRATION tests.

I don't feel like I've missed the point, but I wonder if your definition
of "unit" test includes something that I'm not familiar with.  We do
extensive unit tests that require a DB backend to work.  I mean, if
you're unit testing the DB client class, it needs a back end to talk
to.

> As such they need to be run 
> everytime a developer wants to commit some changes to the codebase and 
> therefore should be quick and unobstrusive to run.

And I don't see how that precludes having a DB there that the developer
can connect to for testing.  We have a DB server specifically for this
purpose so that devs can use it to test out their changes before
checking code in.  More specifically, we have one virtual machine that
serves as a dedicated DB server for developer testing.

I mean, if it won't work for you, OK.  I'm just offering suggestions, and
honestly am confused as to what your actual holdup is.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

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

[GENERAL] timestamp with time zone, retrieving input offset/timezone

2009-08-04 Thread Der Tung

Hello there,

 

We are creating an Application that needs to handle timestamps in different
timezones in particular:

 

Input and output in timezone of the User is no problem with

Set Time Zone and At Time Zone

 

But know I have two Users A and B in different  timezones.

When A saves a timestamp I want B to:

-  Get the timestamp displayed in his timezone 

-  Get the timestamp displayed in the timezone a originally saved it
in

 

Does the type "timestamp with time zone" contain the original timezone or at
least the offset to UTC it was saved in, or does it just save the UTC time?

 

If it saves the timezone/offset, how can I access it? 

 

Regards,

Der Tung

[GENERAL] Recovery from crashed DB seems to occur progressively

2009-08-04 Thread Bruno Harbulot


Hello,

I'm using version 8.3.5. The partition in which the 'pgsql' directory 
was became full. This database is normally used via an Hibernate/JDBC 
layer and one of the most common request is "SELECT * FROM MYTABLE LIMIT 
10", from a webpage.
I tried to delete some rows manually from psql ("delete from MYTABLE 
where id <= ...") and this caused PostgreSQL server to crash .



PANIC:  could not write to file "pg_xlog/xlogtemp.29275": No space left on 
device
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: WARNING: terminating 
connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the 
current transaction and exit, because another server process exited abnormally 
and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat 
your command.
Failed.
!> \q 


I moved the entire directory to a larger partition and created a symlink 
to it.
I used "pg_resetxlog -f" on the data directory (I had to force as it 
wouldn't work otherwise). This seems to have restored the database, and 
I'm now able to start the server again. However, the rows seem to come 
back "progressively".


- I do a "SELECT count(ID) FROM MYTABLE" from psql and get 2 rows.
- I reload the webpage (which uses Hibernate) and I get the 10 entries 
(as expected).

- I do a "SELECT count(ID) FROM MYTABLE" from psql and get 23 rows.
- I reload the webpage and I get another 10 entries (as expected).
- I do a "SELECT count(ID) FROM MYTABLE" from psql and get 49 rows.
... and so on...
More rows seem to be recovered when I reload from the Hibernate layer, 
which doesn't seem to do anything more than just a SELECT.



Does anyone have any idea what may cause this? Is there a way I could 
make it restore all the rows in one step? I've tried using REINDEX, but 
it didn't make any difference.



Best wishes,

Bruno.


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

[GENERAL] ECPG: insert array?

2009-08-04 Thread Edward Amsden

I am using ECPG in a C application, and I would like to insert a C array of 
integers into an array in a PostgreSQL table. The documentation does not talk 
about this at all. Is it possible and if so, how?
 
 
--
Edward Amsden
FXCM
IT Intern
eams...@fxcm.com 
Cell: 937 638 6959

[GENERAL] CHECK constraint fails when it's not supposed to

2009-08-04 Thread Richard Yen

Hi All,

I encountered an odd issue regarding check constraints complaining
when they're not really violated.

For this particular machine, I am running 8.3.7, but on a machine
running 8.3.5, it seems to have succeeded.  I also upgraded a third
machine from 8.3.5 to 8.3.7, and the query succeeded (so I'm
thinking
it's not related to different postgres versions)

I have a table called "m_class" and the definition is something like
this:

> tii=# \d m_class
>   Table "public.m_class"
>  Column  |   Type
> |  Modifiers
> -+--
> +--
>  id  | integer  | not null
> default nextval(('m_class_id_seq'::text)::regclass)
>  class_type  | smallint | not null
>  title   | character varying(100)   | not null
> ...snip...
> date_setup  | timestamp with time zone | not null
> default ('now'::text)::date
>  date_start  | timestamp with time zone | not null
>  date_end| timestamp with time zone | not null
>  term_length | interval | not null
> default '5 years'::interval
> ...snip...
>  max_portfolio_file_size | integer  |
> Indexes:
> "m_class_pkey" PRIMARY KEY, btree (id)
> "m_class_account_idx" btree (account)
> "m_class_instructor_idx" btree (instructor)
> Check constraints:
> "end_after_start_check" CHECK (date_end >= date_start)
> "end_within_term_length" CHECK (date_end <= (date_start +
> term_length))
> "min_password_length_check" CHECK
> (length(enrollment_password::text) >= 4)
> "positive_term_length" CHECK (term_length > '00:00:00'::interval)
> "start_after_setup_check" CHECK (date_start >= date_setup)
> ...snip...

When I run my update, it fails:
> tii=# begin; update only "public"."m_class" set date_end='2009-09-03
> 05:38:24.030331-07',term_length='177 days 17:59:09.868431' where
> id='2652020';
> BEGIN
> ERROR:  new row for relation "m_class" violates check constraint
> "end_within_term_length"
> tii=# rollback;
> ROLLBACK

The data reads:
> tii=# select date_start, date_end, term_length, '2009-09-03
> 05:38:24.030331-07'::timestamptz - date_start AS new_term_length
> from m_class where id = 2652020;
>  date_start  |  date_end   |
> term_length | new_term_length
> -+-
> +-+--
>  2009-03-09 11:39:14.1619-07 | 2009-04-08 11:39:14.1619-07 | 30
> days | 177 days 17:59:09.868431


Based on new_term_length, the update should succeed.  However, it
doesn't.  Would anyone have an explanation?

Thanks for your help!
--Richard

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

[GENERAL] Error when assigning default value for function parameter

2009-08-04 Thread Gert

I'm a bit in the dark:
I use Windows XP
I use pgAdmin ver 1.8.4
I use PostGres 8.3.6

All the documentation points out that it is allowed to assign default
values to parameters, but I get a syntax error here.

I would appreciate feedback.

CREATE OR REPLACE FUNCTION myfunc(a integer , b integer DEFAULT 100)
RETURNS INTEGER AS $$
select  200;
 $$ language sql;

ERROR:  syntax error at or near "DEFAULT"
LINE 1: ...TE OR REPLACE FUNCTION ___myfunc(a int, b integer DEFAULT
10...

-- 
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] Error when assigning default value for function parameter

Hello

2009/8/4 Gert :
> I'm a bit in the dark:
> I use Windows XP
> I use pgAdmin ver 1.8.4
> I use PostGres 8.3.6
>

You have to upgrade to 8.4. Default parameter's values are implemented in 8.4.

regards
Pavel Stehule

> All the documentation points out that it is allowed to assign default
> values to parameters, but I get a syntax error here.
>
> I would appreciate feedback.
>
> CREATE OR REPLACE FUNCTION myfunc(a integer , b integer DEFAULT 100)
> RETURNS INTEGER AS $$
>    select  200;
>  $$ language sql;
>
> ERROR:  syntax error at or near "DEFAULT"
> LINE 1: ...TE OR REPLACE FUNCTION ___myfunc(a int, b integer DEFAULT
> 10...
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
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] Error when assigning default value for function parameter

2009-08-04 Thread Frank Heikens

You can't use default values in a function, that option is available  
as of version 8.4. You are using 8.3.


Regards,
Frank


Op 4 aug 2009, om 19:16 heeft Gert het volgende geschreven:


I'm a bit in the dark:
I use Windows XP
I use pgAdmin ver 1.8.4
I use PostGres 8.3.6

All the documentation points out that it is allowed to assign default
values to parameters, but I get a syntax error here.

I would appreciate feedback.

CREATE OR REPLACE FUNCTION myfunc(a integer , b integer DEFAULT 100)
RETURNS INTEGER AS $$
   select  200;
$$ language sql;

ERROR:  syntax error at or near "DEFAULT"
LINE 1: ...TE OR REPLACE FUNCTION ___myfunc(a int, b integer DEFAULT
10...

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


Frank Heikens
frankheik...@mac.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] CHECK constraint fails when it's not supposed to

Richard Yen  writes:
> When I run my update, it fails:
>> tii=# begin; update only "public"."m_class" set date_end='2009-09-03
>> 05:38:24.030331-07',term_length='177 days 17:59:09.868431' where
>> id='2652020';
>> BEGIN
>> ERROR:  new row for relation "m_class" violates check constraint
>> "end_within_term_length"
>> tii=# rollback;

Hmm, I get this:

regression=# select '2009-03-09 11:39:14.1619-07'::timestamptz + '177 days 
17:59:09.868431'::interval;
   ?column?
---
 2009-09-03 05:38:24.030331-07
(1 row)

which is apparently exactly the same as your date_end value, but I bet
it's not quite the same after allowing for floating-point roundoff error.
If this database wasn't built with the exact-integer-timestamps option
then you can't assume that timestamp calculations are exact to the
microsecond.

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] Does derby have an embedded Mode like Derby ?

2009-08-04 Thread Guy Rouillier


Paul Taylor wrote:
I am testing the code that extracts information from a read only 
database. These are UNIT tests so only interested in getting the right 
results given a particular set of data, anything else is a distraction.


I don't understand your test environment.  If all your code is doing is 
extracting info from a database, why would you want to have database 
management embedded in your test?  Simply have a test database already 
configured and running, then run your JUnit tests against that existing 
database.  That simple approach would obviously work across all platforms.


The only reason I can see for doing DDL in a unit test would be if the 
end product will be doing such DDL.


--
Guy Rouillier

--
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] timestamp with time zone, retrieving input offset/timezone

2009-08-04 Thread Scott Marlowe

On Tue, Aug 4, 2009 at 5:09 AM, Der Tung wrote:
> Hello there,
>
> We are creating an Application that needs to handle timestamps in different
> timezones in particular:
>
> Input and output in timezone of the User is no problem with
>
> Set Time Zone and At Time Zone
>
> But know I have two Users A and B in different  timezones.
>
> When A saves a timestamp I want B to:
>
> -  Get the timestamp displayed in his timezone
>
> -  Get the timestamp displayed in the timezone a originally saved it
> in
>
>
>
> Does the type “timestamp with time zone” contain the original timezone or at
> least the offset to UTC it was saved in, or does it just save the UTC time?

Just UTC time.
If you want the offset saved, you'll have to do that yourself.

-- 
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] Does derby have an embedded Mode like Derby ?

2009-08-04 Thread Bayless Kirtley

- Original Message - 
From: "Paul Taylor" 

To: 
Sent: Tuesday, August 04, 2009 9:56 AM
Subject: Re: [GENERAL] Does derby have an embedded Mode like Derby ?

Of course you can always use Derby for testing the Postgres later. I have
found HSQLDB to be closer to Postgres than Derby is. I do seem to recall
the Netbeans group recommends using sever mode during development then
switch to imbedded for distribution. I have done it both ways with equal
success and similar efforts.

Bayless

Thanks  I am trying to use Derby but fallen foul of Postgres array
datatype which Derby doesn't support, Ive just had a quick look at HSQLDB
and it doesn't appear to support it either.

Paul

Yes, I'm afraid you're gonna be out of luck on finding the array type in any
of
the smaller embedded databases. Honestly, the beg project I've been on for a
year or so has used Postgres right through full development and testing.
It's
not hard to start and stop the database whenever I need to although I
usually
just leave it running.

Bayless

--
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] Does derby have an embedded Mode like Derby ?

2009-08-04 Thread Eric Schwarzenbach

Bayless Kirtley wrote:
> Yes, I'm afraid you're gonna be out of luck on finding the array type
> in any
> of
> the smaller embedded databases. Honestly, the beg project I've been on
> for a
> year or so has used Postgres right through full development and testing.
> It's
> not hard to start and stop the database whenever I need to although I
> usually
> just leave it running.
>
Even if you find one supporting all the same SQL syntax that you are
using, another database implementation could, in some rare
circumstances, return slightly different data for the same query. In
particular odd situations like this arise around nulls. Conceivably this
could happen between versions of PostgreSQL and running such unit tests
to test your code's expectations against a real database guards against
this possibility.

Eric


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

[GENERAL] Generating GRANT/REVOKE on functions from catalog

2009-08-04 Thread Doug Gorley

I am attempting to script the generation of grant/revoke statements for 
a database, and I'm having some trouble when it comes to functions.


consider the following function:

create function add(a integer, b integer)
returns integer
as $$
select $1 + $2;
$$ language SQL;

The statement I need to generate is:

revoke all on function public.add(integer, integer) from someuser;

I'm attempting to use the pg_proc table in the system catalogs, and I'm 
good up to the point where I need the parameter types.  Can anyone give 
me a hand with this?


Thanks,

--

*Doug Gorley* | doug.gor...@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] Error when assigning default value for function parameter

2009-08-04 Thread Gert

Thanks Pavel & Frank.

Installed version 8.4, work perfect.

Apologies. I did check ver 8.3 documentation earlier as well, but can
confirm that I clearly read what I wanted to hear, and not what was
actually there.

Thanks again!

-- 
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] Generating GRANT/REVOKE on functions from catalog

Doug Gorley  writes:
> The statement I need to generate is:

> revoke all on function public.add(integer, integer) from someuser;

> I'm attempting to use the pg_proc table in the system catalogs, and I'm 
> good up to the point where I need the parameter types.  Can anyone give 
> me a hand with this?

Personally, I'd cast the function OID to regprocedure, instead of
doing it the hard way ...

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] Generating GRANT/REVOKE on functions from catalog

2009-08-04 Thread Doug Gorley

That looks like exactly what I want.  Is there an easy way to cast that 
to a string so that I can concatenate it into a GRANT statement?



*Doug Gorley* | doug.gor...@gmail.com 


Tom Lane wrote:

Doug Gorley  writes:
  

The statement I need to generate is:



  

revoke all on function public.add(integer, integer) from someuser;



  
I'm attempting to use the pg_proc table in the system catalogs, and I'm 
good up to the point where I need the parameter types.  Can anyone give 
me a hand with this?



Personally, I'd cast the function OID to regprocedure, instead of
doing it the hard way ...

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] Does derby have an embedded Mode like Derby ?


Guy Rouillier wrote:

Paul Taylor wrote:
I am testing the code that extracts information from a read only 
database. These are UNIT tests so only interested in getting the 
right results given a particular set of data, anything else is a 
distraction.


I don't understand your test environment.  If all your code is doing 
is extracting info from a database, why would you want to have 
database management embedded in your test?  Simply have a test 
database already configured and running, then run your JUnit tests 
against that existing database.  That simple approach would obviously 
work across all platforms.


The only reason I can see for doing DDL in a unit test would be if the 
end product will be doing such DDL.


Firstly the database side is quite straightforward, these issues with 
triggers and locking just don't apply for what I am doing. Secondly this 
is an opensource project and to enable others to contribute easily it is 
much easier if they can download the code and run mvn package to compile 
and test. Once you start introducing external database setups, and 
database configs things can easily start going wrong, and you can't 
share databases when doing automated testing


Paul

--
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] Generating GRANT/REVOKE on functions from catalog