Re: [GENERAL] I need to ecrypt one column to an output file

[Richard Huxton]

Ralph Smith wrote:

I need to do a simple query and output to a file.
No problem.

But how do I encrypt one column's output?

There are lots of developer related links here, and info to "use the 
/contrib pgcrypto", but I'm not a PG developer and I failed to find any 
info on HOW TO USE that library function...


Have you installed the pgcrypto module?
Have you read the guide to pgcrypto in the manual?
  http://www.postgresql.org/docs/8.3/static/pgcrypto.html

--
  Richard Huxton
  Archonet Ltd

--
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] Installation of contrib/tablefunc - problems

[Craig Ringer]

Stefan Schwarzer wrote:
>>> ld: file not found: ../../src/backend/postgres
>>
>> It appears to be trying to link to the postgres executable (which is
>> presumably built with --export-dynamic to usefully permit that). The
>> postgres executable doesn't appear to be there.
>>
>> At a guess, the module expects to be built from a particular location
>> within the postgresql source tree, probably contrib/tablefunc, and
>> you're building it somewhere else.
> 
> Yes, as indicated, I have a macports-postgres version, and try to get
> the tablefunc into that...

`tablefunc' is part of postgresql's contrib modules. It would normally
be offered by a ports system that includes postgresql.

> What can I do now? Do you have any suggestions? How should postgres be
> compiled in order to allow a later insertion of tablefunc?

If it's not available through macports, I'd ditch the macports version
of postgresql and build it, and its contrib modules, myself.

However, it looks like the MacPorts port simply omits that contrib
module. You should be able to re-enable it by editing the Portfile, then
rebuild the port. That's assuming there's not something preventing it
from being built on Mac OS X, of course.

The Portfile for postgresql contains a line:

set contribs"adminpack dblink fuzzystrmatch lo pg_buffercache
pg_trgm tsearch2 vacuumlo xml2"

so it's worth a try to append tablefunc to that list and rebuild the
port. It's been ages since I've used MacPorts (I don't have a mac, so I
only use it occasionally on a work box) so I can't give you detailed
instructions on how to do that.

Here's the current portfile:

http://trac.macports.org/projects/macports/browser/trunk/dports/databases/postgresql82/Portfile

--
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] Installation of contrib/tablefunc - problems

[Stefan Schwarzer]

ld: file not found: ../../src/backend/postgres


It appears to be trying to link to the postgres executable (which is
presumably built with --export-dynamic to usefully permit that). The
postgres executable doesn't appear to be there.

At a guess, the module expects to be built from a particular location
within the postgresql source tree, probably contrib/tablefunc, and
you're building it somewhere else.


Yes, as indicated, I have a macports-postgres version, and try to get  
the tablefunc into that...


What can I do now? Do you have any suggestions? How should postgres be  
compiled in order to allow a later insertion of tablefunc?


Thanks for any hints,

Stef

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

Re: [GENERAL] How to recovery data from folder data installation?

[Craig Ringer]

Orlando Solarte wrote:
> Hi.
>  
> I am trouble with my server postres on linux. It crashed!. I have folder
> postgres installation that was my server. Is possible recovery data from
> folder postgres installation? For example folder data?

Unfortunately you have provided very little information.

If you mean that the PostgreSQL database server crashed and will no
longer start up, then you would need to provide the messages PostgreSQL
prints to its log files when it tries and fails to start up.

If you mean that the server computer its self crashed, and you now have
a PostgreSQL data directory (from backups or recovered from the server's
 hard disk) then if the PostgreSQL data directory is undamaged it should
be possible to use it. You will need to use it with a version of
PostgreSQL with the same minor version as the one you were using on the
server, eg if you were using 8.1.4 you should get the latest PostgreSQL
in the 8.1 series (NOT 8.2 or 8.3) to try to read the data.

Make a copy first.

--
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] util/int8.h: missing int8_text() function

[Craig Ringer]

Michael Enke wrote:
> Hi all,
> I migrate from 8.1 to 8.3.
> In 8.2.7 and previous there are functions int4_text() and int8_text(),
> in 8.3.0 they are missing (not documented that they are removed in the
> release notes).
> What I'm supposed to use instead of int8_text() now?

Probably an explicit cast, such as

CAST(::int8 AS text)

CAST('' AS int8)

etc.

I don't know, but I'd guess that this is related to the removal of all
the implicit casts to text from 8.3 .

--
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] Installation of contrib/tablefunc - problems

[Craig Ringer]

Stefan Schwarzer wrote:

> ld: file not found: ../../src/backend/postgres

It appears to be trying to link to the postgres executable (which is
presumably built with --export-dynamic to usefully permit that). The
postgres executable doesn't appear to be there.

At a guess, the module expects to be built from a particular location
within the postgresql source tree, probably contrib/tablefunc, and
you're building it somewhere else.

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

[GENERAL] How to recovery data from folder data installation?

[Orlando Solarte]

Hi.
 
I am trouble with my server postres on linux. It crashed!. I have folder 
postgres installation that was my server. Is possible recovery data from folder 
postgres installation? For example folder data?
 
Any can help me
 
Note: Sorry my bad english.
 
Thank you! 
_
Invite your mail contacts to join your friends list with Windows Live Spaces. 
It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us

Re: [GENERAL] Suggestion for psql command interpretation

[Colin Wetherbee]

Tom Lane wrote:

Colin Wetherbee <[EMAIL PROTECTED]> writes:
I just thought I'd report it here in case it wasn't supposed to 
happen, but from what you say, it seems like it's a "feature".


Well, it's more of a historical hangover.  Personally I'd not have 
much problem with breaking backward compatibility on this point.


I'm not generally in the habit of making typos, so this doesn't affect 
me too much.


However, IMHO and as I mentioned previously, I don't think "\timimng" 
should succeed.  I'll leave the rest of the discussion up to you and 
the other developers, though. :)


Colin

--
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] Suggestion for psql command interpretation

[Tom Lane]

Colin Wetherbee <[EMAIL PROTECTED]> writes:
> I just thought I'd report it here in case it wasn't supposed to 
> happen, but from what you say, it seems like it's a "feature".

Well, it's more of a historical hangover.  Personally I'd not have
much problem with breaking backward compatibility on this point.

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] Suggestion for psql command interpretation

[Colin Wetherbee]

Tom Lane wrote:

Colin Wetherbee <[EMAIL PROTECTED]> writes:

I just made a typo in psql, and it resulted in something I
wouldn't have expected.



js=# \timimng Showing only tuples.


This is related to the discussion about whether to require a space 
between a backslash command name and its arguments ...


I see.

So, this was interpreted by psql in the same way "\t imimng" would 
have been?  Not that \t does anything with arguments, though...


I just thought I'd report it here in case it wasn't supposed to 
happen, but from what you say, it seems like it's a "feature".


Colin


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

[GENERAL] I need to ecrypt one column to an output file

[Ralph Smith]

I need to do a simple query and output to a file.
No problem.

But how do I encrypt one column's output?

There are lots of developer related links here, and info to "use the / 
contrib pgcrypto", but I'm not a PG developer and I failed to find any  
info on HOW TO USE that library function...


Thanks all,

Ralph Smith
[EMAIL PROTECTED]
=

Re: [GENERAL] precompilers

[Merlin Moncure]

On Tue, Apr 15, 2008 at 1:36 PM, Bruce Momjian <[EMAIL PROTECTED]> wrote:
> Ferry, Craig wrote:
>  > We are evaluating open source databases for use within our organization.
>  > Can anyone tell me if postgresql have a precompiler for Micro focus
>  > Cobol for Unix V 4.1 revision 040?   Is there a list of these someplace?
>  > I poked around on the postgres site.
>
>  I see some people mentioning they were using Micro Focus cobol and
>  Postgres:
>
> 
> http://search.postgresql.org/search?q=cobol+micro+focus&m=1&l=&d=-1&s=r

A quick scan there shows most of the stuff is 'odbc' based.
PostgreSQL has some unique features (advisory locks for example) that
make it a really great backend for ISAM style apps.  You can solve
this over straight odbc, with a performance cost.  This is why a
native solution would be greatly preferred.  I'm still working on my
old company to allow me to release the ISAM project code.  It was
written for Acucorp, but would work with minimal changes for MF cobol,
their linking interfaces are similar (MF is actually much better).

merlin

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

Re: [GENERAL] session_replication_role

[Terry Lee Tucker]

On Tuesday 15 April 2008 14:26, Chris Browne wrote:
> [EMAIL PROTECTED] (Terry Lee Tucker) writes:
> > Is there a distinction between "ORIGIN" and "LOCAL" as related to
> > session_replication_role, and if so, what is it? I am unable to
> > understand from the documentation any distinction between the two
> > settings.
>
> The intent is that a system that is the "origin" for replication
> changes (e.g. - a database where you'll be collecting
> INSERT/UPDATE/DELETE information to replicate elsewhere) would run in
> the "origin" role, normally.
>
> The distinction from a practical perspective will take place when
> stored functions that implement "replication stuff" detect what role
> the system is in, and may behave differently.
> --

Thanks for the reply. I was unable to detect any change in trigger operation 
when setting the variable to origin or local. I understand that you are 
saying that the distinction only exists if my code is written to operate 
differently based on the setting.

Thanks for the input...
-- 
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812  Fax: (336) 372-6812  Cell: (336) 404-6987
[EMAIL PROTECTED]
www.turbocorp.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] session_replication_role

[Chris Browne]

[EMAIL PROTECTED] (Terry Lee Tucker) writes:
> Is there a distinction between "ORIGIN" and "LOCAL" as related to 
> session_replication_role, and if so, what is it? I am unable to understand 
> from the documentation any distinction between the two settings.

The intent is that a system that is the "origin" for replication
changes (e.g. - a database where you'll be collecting
INSERT/UPDATE/DELETE information to replicate elsewhere) would run in
the "origin" role, normally.

The distinction from a practical perspective will take place when
stored functions that implement "replication stuff" detect what role
the system is in, and may behave differently.
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://linuxdatabases.info/info/multiplexor.html
Rules of the Evil Overlord #7. "When I've captured my adversary and he
says, "Look, before  you kill me, will you at least  tell me what this
is all  about?" I'll say, "No."  and shoot him. No,  on second thought
I'll shoot him then say "No."" 

-- 
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] Suggestion for psql command interpretation

[Tom Lane]

Colin Wetherbee <[EMAIL PROTECTED]> writes:
> I just made a typo in psql, and it resulted in something I wouldn't have 
> expected.

> js=# \timimng
> Showing only tuples.

This is related to the discussion about whether to require a space
between a backslash command name and its arguments ...

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] Help needed in PITR

[Andrea]

Hi all, i'll try a second time to post tips on the problem i'm facing

Fisrt sorry for my impatience; i first try to reed deeply as i can the 
docs and then i tryed to setup a test env to run a PITR, but it didn't 
work as is expected to.


Now i'm getting confused, so i really need help in trying to understand 
what i did wrong, or, more probably,  what i didn't understand

Here's what i did:
1) set up a crontab which copys the last-created WAL file in 
/home/postgres/WAL
2) set up a shell-script as a the archive_command: it copyes WAL files 
from pg_xlog dir in /home/postgres/WAL
3) set up a shell script for doing the backup. It connects to DB and 
then issues "SELECT pg_start_backup('full-bck');" , then it execute "tar 
--exclude data/pg_xlog -cvzpf archive.tar.gz data" , and finally "SELECT 
pg_stop_backup();".


For testing purposes i use another server in which i installed the same 
version of PostgreSQL. In it i deleted the DATA dir ( 
/var/lib/pgsql/data ); then i unpacked the archive.tar.gz ; i created 
the pg_xlog dir and, in it, also the archive_status one.
I setup the recovery.conf whit the recovery_command='cp 
/home/postgres/WAL/%f "%p"' . I copied all the WAL files fron the 
old-server to the new one in /home/postgres/WAL/.

When i try to start the DB it hangsup saying ( in the logs ):
.
LOG:  database system was interrupted at 2008-04-11 09:03:48 CEST
LOG:  starting archive recovery
LOG:  restore_command = "cp /home/postgres/WAL/%f %p"
cp: cannot stat `/home/postgres/WAL/0001.history': No such file or 
directory

LOG:  restored log file "00010001006A" from archive
LOG:  unexpected pageaddr 1/602B2000 in log file 1, segment 106, offset 
2826240

LOG:  invalid primary checkpoint record
LOG:  restored log file "00010001006A" from archive
LOG:  unexpected pageaddr 1/6028 in log file 1, segment 106, offset 
2621440

LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 22672) was terminated by signal 6
LOG:  aborting startup due to startup process failure
LOG:  logger shutting down
.

Someone can help in understanding where is the problems ???

Thanks in advance,

--Andrea


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

[GENERAL] util/int8.h: missing int8_text() function

[Michael Enke]

Hi all,
I migrate from 8.1 to 8.3.
In 8.2.7 and previous there are functions int4_text() and int8_text(),
in 8.3.0 they are missing (not documented that they are removed in the release 
notes).
What I'm supposed to use instead of int8_text() now?
(I use my own datatype which is similar to varchar/text
 and for a  CREATE CAST (int8 as myType) WITH FUNCTION myFunction(int8) AS 
ASSIGNMENT;
 I call the int8_text() function.)

Thank you,
Michael

--
Wincor Nixdorf International GmbH
Sitz der Gesellschaft: Paderborn
Registergericht Paderborn HRB 3507
Geschäftsführer: Eckard Heidloff (Vorsitzender), Stefan Auerbach, Dr. Jürgen 
Wunram
Vorsitzender des Aufsichtsrats: Karl-Heinz Stiller
Steuernummer: 339/5884/0020 - Ust-ID Nr.: DE812927716 - WEEE-Reg.-Nr. DE44477193

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige 
Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie 
bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte 
Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet.

This e-mail may contain confidential information. If you are not the intended 
recipient (or have received this e-mail in error) please notify the sender 
immediately and destroy this e-mail. Any unauthorised copying, disclosure or 
distribution of the material in this e-mail is strictly forbidden.


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

[GENERAL] strange dump problem

[Clemens Schwaighofer]

local system: 8.2.7
remote system: 8.2.5

problem:

I have a small database with some tables. one of the tables had no
primary key, but a column with a fitting sequence (was created with serial).

so I added a primary key to it and it is visible and working.

but, when I dump some of the tables (4, 1 generic thats get inherit, 3
normal ones), only the other two are dumped in a normal format:

...
display_oid integer not null,
...

create sequence ...

etc,

the one table where I added the primary afterwards gets dumped like this:

...
mobile_oid integer DEFAULT nextval('mobile_mobile_oid_seq'::regclass)
NOT NULL,
...

and no sequence is created, etc

when I try to import this into the remote system it failes and does not
want to create the one table, because it cannot find the sequence
'mobile_mobile_oid_seq'

the data was dumped with:

pg_dump -U postgres -c -f output.sql -t  -t ... database_name

is there any explenation why postgres would do that?

-- 
[ Clemens Schwaighofer  -=:~ ]
[ IT Engineer/Manager, TEQUILA\ Japan IT Group   ]
[6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.co.jp   ]


signature.asc
Description: OpenPGP digital signature

[GENERAL] Unacceptable postgres performance vs. Microsoft sqlserver

[[EMAIL PROTECTED]]

Let me just start off by saying that I *want* to use postgresql.
That's my goal.  I do not want to use SQLServer.  I'm posting this
message not to slam postgres, but to ask for someone to help me figure
out what I'm doing wrong.

I've used postgres for several years as the backend to web
applications. I'm not entirely new to it, but I've never needed to
bother with performance tuning.  Netflix is running a contest in which
they will give you a set of movie ratings and you try to develop an
algorithm to predict future ratings.  I've imported this data into a
postgresql database, and the performance is abysmal.  Here's my setup:

Core 2 Quad with 4GB RAM and two SATAII hard drives in RAID 0

I install debian linux and postgresql 8.1 (this is the latest version
that is available through the debian package manager).  I import the
Netflix data into a table with the following characteristics:

Create table rating (movieid int, userid int, rating int4, ratingdate
date)

There are 180 million rows.  I've done the import 3 times, it takes on
average 815 seconds.  I'm not too worried about that.  Now for a test,
I run the query, select count(*) from rating;

This takes an average of 172 seconds.  Call it three minutes.

Now, on the same machine, I install windows XP and Microsoft SQL
server 2000.  The import took 742 seconds.  The count(*) query took 22
seconds.

22 seconds.  What's gong on?

Another test.  In postgres I added an index to the userid column and
then counted distinct userids.  The average run time over three
queries was 4666 seconds, or 78 minutes.  Unbelievable.

On SQL Server, with *no* index, the same query takes on average 414
seconds, or about 7 minutes.  Ten times faster!

I'm sure someone will flame me and say that I'm a worthless noob and
if only I was smart I would know what I'm doing wrong.  So let me just
repeat: I want to use postgres.  That's my goal.  Please tell me what
I can do to make the performance of this acceptable.  It's not a
complicated setup.  One table.  A couple of count(*) queries.


-- 
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] Unacceptable postgres performance vs. Microsoft sqlserver

[[EMAIL PROTECTED]]

On Apr 14, 12:34 pm, Chris Browne <[EMAIL PROTECTED]> wrote:
> If the entire purpose of your application is to run COUNT(*)

haha.  no.  I see your point.

I'll be doing statistical functions on group-by's.  So I'll go back
and give it another try with queries like those.  And I'll use some of
the functions that other people here have suggeted.  I hope this
works!

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: [GENERAL] Unacceptable postgres performance vs. Microsoft sqlserver

[[EMAIL PROTECTED]]

On Apr 14, 2:17 pm, [EMAIL PROTECTED] ("David Wilson") wrote:
> For instance, your count of distinct userids is probably not using the
> index you just created. If it still isn't using it after you ANALYZE
> the table, try rewriting the query using group by (select count(*)
> from (select userid from mytable group by userid) tmp). I recently had
> a similar performance issue on a 75m row table, and the above helped.
>
thanks.  There's a lot of good info in your post.  I'll give it a try.

-- 
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] Which Python library - psycopg2 or pygresql?

[Greg Smith]

On Tue, 15 Apr 2008, Dawid Kuroczko wrote:


So I thought, "lets learn a bit of Python", and I stumbled upon
a choice of these two libraries.  Whch would you suggest?


Use psycopg2.  It's better maintained and has a better feature set at this 
point.  I would specifically recommend that you look at all the example 
programs that come with the software.  There's lots of stuff there that is 
easier to pick up that way than by reading the documentation, if it's even 
covered in the docs at all.



PS: I don't want to start a flame war!


Unless D'Arcy suddenly appears with a new pygresql rev that blows everyone 
away I think this is a safe topic.  Not impossible but not too likely I 
think.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
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] Suggestion for psql command interpretation

[Colin Wetherbee]

Colin Wetherbee wrote:
I just made a typo in psql, and it resulted in something I wouldn't have 
expected.


Oh, um, this was in the 8.3.1 psql.

Colin


--
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] Not Geography

[Obe, Regina]

Try this 
 
http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01
 
As far as tools for viewing the data go, I would recommend Quantum GIS if you 
are a beginner.
 
http://qgis.org/index.php?option=com_content&task=view&id=65&Itemid=71
 
Hope that helps,
Regina



From: Bob Pawley [mailto:[EMAIL PROTECTED]
Sent: Tue 4/15/2008 1:54 PM
To: Obe, Regina; PostgreSQL
Subject: Re: [GENERAL] Not Geography


I have PostgreSQL, c/w the geometry attachment, installed on Win XP. I need to 
get started using it. I am best able to do that by example, however a simple 
(geometry for dummies) description or a tool would also help.
 
I am on the Postgis list but everyone there seems to be so geographically 
oriented that I wanted to query a larger audience.
 
Bob
 

- Original Message - 
From: Obe, Regina   
To: Bob Pawley   ; PostgreSQL 
  
Sent: Tuesday, April 15, 2008 10:50 AM
Subject: RE: [GENERAL] Not Geography

>I have Postgresql database that I would like to link to the geometry 
side of
>Postgis. (This has little comparison to geography)

>Does anyone know of a tool or documentation that could get me started??

>Bob

Bob,

I don't quite understand your question.  Are you not sure how to 
install PostGIS, how to integrate it in your existing datbase, or once you 
install how to get started using it?  How to do it depends on the operating 
system you are using.  Once you have it installed you may want to join the 
Postgis newsgroup for further help
 
http://postgis.refractions.net/mailman/listinfo/postgis-users

  
 
Hope that helps,
Regina
 
 
 
 









The substance of this message, including any attachments, may be 
confidential, legally privileged and/or exempt from disclosure pursuant to 
Massachusetts law. It is intended solely for the addressee. If you received 
this in error, please contact the sender and delete the material from any 
computer. 







Help make the earth a greener place. If at all possible resist printing 
this email and join us in saving paper. 





-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

[GENERAL] Suggestion for psql command interpretation

[Colin Wetherbee]

I just made a typo in psql, and it resulted in something I wouldn't have 
expected.


js=# \timimng
Showing only tuples.

js=# \t
Tuples only is off.

I also wouldn't have expected \timimng to have been interpreted as 
\timing, which I tried to type in the first place, but perhaps a typo 
like this should throw an error?


Colin

--
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] Not Geography

[Bob Pawley]

[GENERAL] Not GeographyI have PostgreSQL, c/w the geometry attachment, 
installed on Win XP. I need to get started using it. I am best able to do that 
by example, however a simple (geometry for dummies) description or a tool would 
also help.

I am on the Postgis list but everyone there seems to be so geographically 
oriented that I wanted to query a larger audience.

Bob

  - Original Message - 
  From: Obe, Regina 
  To: Bob Pawley ; PostgreSQL 
  Sent: Tuesday, April 15, 2008 10:50 AM
  Subject: RE: [GENERAL] Not Geography


  >I have Postgresql database that I would like to link to the geometry side of
  >Postgis. (This has little comparison to geography)

  >Does anyone know of a tool or documentation that could get me started??

  >Bob

  Bob,
  I don't quite understand your question.  Are you not sure how to install 
PostGIS, how to integrate it in your existing datbase, or once you install how 
to get started using it?  How to do it depends on the operating system you are 
using.  Once you have it installed you may want to join the Postgis newsgroup 
for further help

  http://postgis.refractions.net/mailman/listinfo/postgis-users


  Hope that helps,
  Regina









--


  The substance of this message, including any attachments, may be 
confidential, legally privileged and/or exempt from disclosure pursuant to 
Massachusetts law. It is intended solely for the addressee. If you received 
this in error, please contact the sender and delete the material from any 
computer. 



--


  Help make the earth a greener place. If at all possible resist printing this 
email and join us in saving paper. 

Re: [GENERAL] Not Geography

[Obe, Regina]

>I have Postgresql database that I would like to link to the geometry side of
>Postgis. (This has little comparison to geography)

>Does anyone know of a tool or documentation that could get me started??

>Bob

Bob,
I don't quite understand your question.  Are you not sure how to install 
PostGIS, how to integrate it in your existing datbase, or once you install how 
to get started using it?  How to do it depends on the operating system you are 
using.  Once you have it installed you may want to join the Postgis newsgroup 
for further help
 
http://postgis.refractions.net/mailman/listinfo/postgis-users
  
 
Hope that helps,
Regina
 
 
 
 




-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

[GENERAL] session_replication_role

[Terry Lee Tucker]

Version 8.3.1:

Is there a distinction between "ORIGIN" and "LOCAL" as related to 
session_replication_role, and if so, what is it? I am unable to understand 
from the documentation any distinction between the two settings.

TIA
-- 
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812  Fax: (336) 372-6812  Cell: (336) 404-6987
[EMAIL PROTECTED]
www.turbocorp.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] precompilers

[Bruce Momjian]

Ferry, Craig wrote:
> We are evaluating open source databases for use within our organization.
> Can anyone tell me if postgresql have a precompiler for Micro focus
> Cobol for Unix V 4.1 revision 040?   Is there a list of these someplace?
> I poked around on the postgres site.

I see some people mentioning they were using Micro Focus cobol and
Postgres:

http://search.postgresql.org/search?q=cobol+micro+focus&m=1&l=&d=-1&s=r

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
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] Which Python library - psycopg2 or pygresql?

[Karsten Hilbert]

On Tue, Apr 15, 2008 at 09:21:19AM -0700, Steve Crawford wrote:

>> So I thought, "lets learn a bit of Python", and I stumbled upon
>> a choice of these two libraries.  Whch would you suggest?
>> How do they differ?
>>   
> Well, pygresql seems unmaintained since mid 2006 and the psycopg2 site  
> is currently and regularly down. Neither inspires confidence.
The psycopg2 site is (supposedly) actively being worked on
since the 2.0.7 release yesterday :-)

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

[Merlin Moncure]

On Tue, Apr 15, 2008 at 10:37 AM, Ferry, Craig <[EMAIL PROTECTED]> wrote:
> We are evaluating open source databases for use within our organization.
> Can anyone tell me if postgresql have a precompiler for Micro focus Cobol
> for Unix V 4.1 revision 040?   Is there a list of these someplace?  I poked
> around on the postgres site.

I don't think so.  A few years back I wrote a driver which linked to
the AcuCorp AcuCobol runtime that allowed PostgreSQL to appear as a
ISAM filesystem.  I've never heard of PostgreSQL running as a backend
for cobol in any other context (except for perhaps some odbc
wrappers).

merlin

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

[GENERAL] Not Geography

[Bob Pawley]

I have Postgresql database that I would like to link to the geometry side of 
Postgis. (This has little comparison to geography)


Does anyone know of a tool or documentation that could get me started??

Bob



--
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] Which Python library - psycopg2 or pygresql?

[Filip Rembiałkowski]

Just a side note:
pyPgSQL is broken with standard_conforming_strings = on
(see groups.google.com/group/trac-dev)


2008/4/15, Dawid Kuroczko <[EMAIL PROTECTED]>:
> So I thought, "lets learn a bit of Python", and I stumbled upon
>  a choice of these two libraries.  Whch would you suggest?
>  How do they differ?
>
>  By the looks of descriptions I am slightly inclined towards
>  psycopg2, but I would feel better if I talked with people
>  who actually used these libraries.
>
>Regards,
>  Dawid
>
>  PS: I don't want to start a flame war!  I just feel I need a bit
>  of knowledge-push to get me going. ;-)
>
>
>  --
>  Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>  To make changes to your subscription:
>  http://www.postgresql.org/mailpref/pgsql-general
>


-- 
Filip Rembiałkowski

-- 
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] Which Python library - psycopg2 or pygresql?

[Steve Crawford]

Dawid Kuroczko wrote:

So I thought, "lets learn a bit of Python", and I stumbled upon
a choice of these two libraries.  Whch would you suggest?
How do they differ?
  
Well, pygresql seems unmaintained since mid 2006 and the psycopg2 site 
is currently and regularly down. Neither inspires confidence.


As to differences, here's one:

Using pygresql
...
result=db.query('select false as booltest')
boolean_result = result.dictresult()[0]['booltest']
print boolean_result
if boolean_result:
 print "The result was true"
else:
 print "The result was false"

This prints:
f
The result was true

Huh? Seems that pygresql treats boolean as character 't' or 'f', python 
evaluates both as 'true' and hilarity ensues. (Yes, I just spent some 
"quality time" tracking a bug in a script that used pygresql and had a 
loop with a test of a boolean column.)


Using psycopg2:
...
cur.execute('select false as booltest')
boolean_result = cur.fetchall()[0][0]
print boolean_result
if boolean_result:
 print "The result was true"
else:
 print "The result was false"

This prints:
False
The result was false

There was a brief discussion at the PG users group last week and the 
bias was toward psycopg2.


Cheers,
Steve


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

Re: [GENERAL] Which Python library - psycopg2 or pygresql?

[Erik Jones]

On Apr 15, 2008, at 10:27 AM, Dawid Kuroczko wrote:

So I thought, "lets learn a bit of Python", and I stumbled upon
a choice of these two libraries.  Whch would you suggest?
How do they differ?

By the looks of descriptions I am slightly inclined towards
psycopg2, but I would feel better if I talked with people
who actually used these libraries.


Most definitely psycopg2, it's pretty much the standard dbapi  
compliant Postgres driver library for Python.


Erik Jones

DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.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] Storage sizes for dates/times (documentation bug?)

[Martijn van Oosterhout]

On Tue, Apr 15, 2008 at 04:23:42PM +0200, Karsten Hilbert wrote:
>   When you delete a tagged type some cruft is left behind
>   (not in the system catalog though).
> 
> Perhaps I confuse this with some limitation of a previous
> implementation of the enum type. Also perhaps I was
> misguided into thinking tags cannot be modified by the
> "don't delete from table of tags" part.

Oh, it means that if you DROP the type it will leave some stuff behind.
You can ofcourse handle *value* of that type just like any other value.
The 'tag table' in this case would be the list of timezones. I'll see
if I can clarify it.

> > Truly, taggedtypes are a really useful feature but I think the chance
> > of them being in the main tree approximatly nil, which is enough reason
> > to stay away from them.
> Agree. Another one is non-indexability which I'd truly need.

Well, you can index them ofcourse, but you need to indicate explicitly
what you want to index: the timestamp or the timestamp shifted to the
timezone. I felt the module couldn't make this decision on its own.
Indexing the type directly would be more work, not sure if there's
enough demand for it.

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature

[GENERAL] Which Python library - psycopg2 or pygresql?

[Dawid Kuroczko]

So I thought, "lets learn a bit of Python", and I stumbled upon
a choice of these two libraries.  Whch would you suggest?
How do they differ?

By the looks of descriptions I am slightly inclined towards
psycopg2, but I would feel better if I talked with people
who actually used these libraries.

   Regards,
 Dawid

PS: I don't want to start a flame war!  I just feel I need a bit
of knowledge-push to get me going. ;-)

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

[GENERAL] precompilers

[Ferry, Craig]

We are evaluating open source databases for use within our organization.
Can anyone tell me if postgresql have a precompiler for Micro focus
Cobol for Unix V 4.1 revision 040?   Is there a list of these someplace?
I poked around on the postgres site.

 

Thanks

 

Craig

 



 

 Craig Ferry
 Sr. Database Administrator

 WESCO Distribution, Inc.

 Suite 700

 225 West Station Square Drive

 Pittsburgh, PA 15219
 P:412-454-2835
 F:412-222-7535

 [EMAIL PROTECTED]  



 

Re: [GENERAL] Storage sizes for dates/times (documentation bug?)

[Sam Mason]

On Tue, Apr 15, 2008 at 06:48:27AM -0700, Adrian Klaver wrote:
> On Tuesday 15 April 2008 6:31 am, Sam Mason wrote:
> > My reasoning goes something like this:  The WITH and WITHOUT clauses
> > seem to be the opposite of my naive understanding of their purpose.  I'd
> > think that if you specify WITH TIME ZONE then it means that the timezone
> > is important to me, and I want to deal with it myself.  Whereas, the
> > WITHOUT TIME ZONE clause would suggest that the timezone isn't important
> > to me, and anything the database can do to make the problem go away the
> > better.  What the spec says, and PG does, is actually the opposite.  The
> > fact that this confusion can occur (and seems to occur reasonably often
> > based on previous posts to the mailing lists) suggests that the docs
> > should highlight the differences more clearly.
> >
> > I'd also hazard a guess that we don't hear about it more because most
> > people just work within a single time zone and hence don't even notice
> > the difference between the two.
> 
> My only comment is on this assertion. Any location that has DST rules has two 
> time zones.  For instance I live in  US PST/PDT.  Without timezone support 
> doing date/time math across time zone boundaries is asking for problems. 

Yes, knowing the difference between the two variants is needed to write
correct code.

In practise I'd guess that people don't do much testing across DST
changes and if they do the calcs are generally on the order of days
so an hour probably doesn't go amiss.  The code will do all the right
things for them, but yes, it isn't correct.


  Sam

-- 
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] Storage sizes for dates/times (documentation bug?)

[Sam Mason]

On Tue, Apr 15, 2008 at 04:04:20PM +0200, Karsten Hilbert wrote:
> On Tue, Apr 15, 2008 at 02:31:22PM +0100, Sam Mason wrote:
> > On Tue, Apr 15, 2008 at 02:46:14PM +0200, Karsten Hilbert wrote:
> > > Of course, the actual time stored in the database in UTC is
> > > quite correct - it was indeed 3pm in location B when it was
> > > 7am in London. But we need to know the original local time
> > > (and also be able to know UTC since we want to correlate
> > > times).
> > 
> > I was under the impression that "timestamp without time zone" does
> > precisely this.
> It doesn't. It keeps the time *value* untouched. But it
> doesn't even store *any* timezone information with it.

But I was under the impression that you didn't want any time zone
information.  You wanted to know that that an appointment was at 3PM at
the patients local time, attempting to correct this for the local time
zone of any analyst is invalid.

> So,
> unless I *know* the original timezone by any other means I
> don't have *any* clue as to what point in time a particular
> timestamp value is. It less useful than "with time zone". The
> latter at least allows me to know the true (UTC-adjusted)
> time of an event without jumping through any hoops.

I must be missing something then, can you explain why the original time
zone matters?

> A different angle:
> 
> Customer orders item at 23:15 on March 30. Item is on
> special offer March 30th only. DST change happens on March
> 30 to March-31. Dealer looks at orders and sees "item
> ordered March 31st 0:15" and does NOT apply the rebate for
> March 30th.

Except it probably wouldn't quite happen like this :) your time zone
is normally recorded as something like (for me) "Europe/London" which
then says that between these two dates this time zone applies, and
within these other two dates this other correction applies.  So, in your
example, I'm not sure if there would be any visible difference between
with and without time zones.  If you actually hardcoded your timezone as
GMT+6, or whatever, then yes it may be different.  But only if you went
around at midnight March 31st, changing computers to be GMT+5 (or would
it be 7, I can never remember!).

> Of course, it's the app developers fault, but the use case
> for keeping the original timezone (so it can be reapplied)
> is clearly there.

In some cases yes I'd agree, but I have a feeling the number of cases is
surprisingly small in practise.


  Sam

-- 
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] PostgreSQL 8.3 XML parser seems not to recognize the DOCTYPE element in XML files

[Bruce Momjian]

Added to TODO:

* Allow XML to accept more liberal DOCTYPE specifications

  http://archives.postgresql.org/pgsql-general/2008-02/msg00347.php


---

Lawrence Oluyede wrote:
> As specified in the W3C Recommendation for XML the DOCTYPE element is
> perfectly valid in a document.
> I have a bunch of XML files generated by the boost library which
> contains a doctype like this:
> 
> 
> 
> which lies within the bound of the recommendation
> (http://www.w3.org/TR/xml/#sec-prolog-dtd):
> 
> "Note that it is possible to construct a well-formed document
> containing a doctypedecl that neither points to an external subset nor
> contains an internal subset."
> 
> PostgreSQL 8.3 instead doesn't allow the insertion of XML with doctype
> in its new native data type returning this error message:
> 
> """
> ERROR:  invalid XML content
> DETAIL:  Entity: line 2: parser error : StartTag: invalid element name
> 
>  ^
> 
> ** Error **
> 
> ERROR: invalid XML content
> SQL state: 2200N
> Detail: Entity: line 2: parser error : StartTag: invalid element name
> 
> """
> 
> This kind of behavior surprises me because pgsql has been compiled
> with the following flags on the development machine:
>  ./configure --with-python --with-openssl --with-pam --with-libxml
> --with-libxslt --enable-thread-safety --enable-debug
> 
> During the configuration stage it creates a Makefile binding the
> system version of the libxml2 library which is 2.6.30, the same
> library I use through Python (which parses correctly the XML file with
> the doctype).
> 
> Any hints?
> 
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
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] Storage sizes for dates/times (documentation bug?)

[Karsten Hilbert]

On Tue, Apr 15, 2008 at 03:43:05PM +0200, Martijn van Oosterhout wrote:

> On Tue, Apr 15, 2008 at 02:46:14PM +0200, Karsten Hilbert wrote:
> > Yes, I know about tagged types but have shyed away from them
> > so far courtesy of them not being adjustable after the fact.
> 
> What do you mean by this? Adjustable in what way?

This

svana.org/kleptog/pgsql/taggedtypes.html

is currently down for me but Google has a cache. I was
probably wrongly remembering this:

When you delete a tagged type some cruft is left behind
(not in the system catalog though).

Perhaps I confuse this with some limitation of a previous
implementation of the enum type. Also perhaps I was
misguided into thinking tags cannot be modified by the
"don't delete from table of tags" part.

> Truly, taggedtypes are a really useful feature but I think the chance
> of them being in the main tree approximatly nil, which is enough reason
> to stay away from them.
Agree. Another one is non-indexability which I'd truly need.

> > Should I be using a custom domain for this ?
> 
> You just need to store the pair (time,zone), I don't think domains are
> flexible enough for that. A complex type maybe (but then you've just
> reinvented taggedtypes, inefficiently :) )
Which I'd rather avoid.

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] generate_series woes

[Merlin Moncure]

On Mon, Apr 14, 2008 at 5:21 AM, Harald Fuchs <[EMAIL PROTECTED]> wrote:
> I think there's something sub-optimal with generate_series.
>  In the following, "documents" is a table with more than 12 rows,
>  vacuumed and analyzed before the queries.

everything is working exactly as intended.  while it's obvious to you
that the generate series function returns a particular number of rows
based on your supplied inputs, it's not (yet) obvious to the planner.
your genser function supplies the hint the planner needs and it
adjusts the plan.  most set returning functions (particularly
non-immutable ones) are not so easy to determine the # of rows from
the input parameters anyways.

merlin

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

Re: [GENERAL] Storage sizes for dates/times (documentation bug?)

[Karsten Hilbert]

On Tue, Apr 15, 2008 at 02:31:22PM +0100, Sam Mason wrote:

> On Tue, Apr 15, 2008 at 02:46:14PM +0200, Karsten Hilbert wrote:
> > Of course, the actual time stored in the database in UTC is
> > quite correct - it was indeed 3pm in location B when it was
> > 7am in London. But we need to know the original local time
> > (and also be able to know UTC since we want to correlate
> > times).
> 
> I was under the impression that "timestamp without time zone" does
> precisely this.
It doesn't. It keeps the time *value* untouched. But it
doesn't even store *any* timezone information with it. So,
unless I *know* the original timezone by any other means I
don't have *any* clue as to what point in time a particular
timestamp value is. It less useful than "with time zone". The
latter at least allows me to know the true (UTC-adjusted)
time of an event without jumping through any hoops.

> I'd also hazard a guess that we don't hear about it more because most
> people just work within a single time zone and hence don't even notice
> the difference between the two.

Any DST change will highlight the difference quite clearly.
I don't even have to change locations. Any tstz stored
before a DST changeover will (quite logically) show up as
shifted one hour after the changeover. This happens twice a
year.

A different angle:

Customer orders item at 23:15 on March 30. Item is on
special offer March 30th only. DST change happens on March
30 to March-31. Dealer looks at orders and sees "item
ordered March 31st 0:15" and does NOT apply the rebate for
March 30th.

Of course, it's the app developers fault, but the use case
for keeping the original timezone (so it can be reapplied)
is clearly there.

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] Storage sizes for dates/times (documentation bug?)

[Adrian Klaver]

On Tuesday 15 April 2008 6:31 am, Sam Mason wrote:
> On Tue, Apr 15, 2008 at 02:46:14PM +0200, Karsten Hilbert wrote:
> > Of course, the actual time stored in the database in UTC is
> > quite correct - it was indeed 3pm in location B when it was
> > 7am in London. But we need to know the original local time
> > (and also be able to know UTC since we want to correlate
> > times).
>
> I was under the impression that "timestamp without time zone" does
> precisely this.  It would be nicer if the docs highlighted the
> differences, and reasons behind, the semantics between the two, instead
> of focusing so much on the syntax.  The "WITH TIME ZONE" variant is
> described nicely:
>
>For timestamp with time zone, the internally stored value is always
>in UTC (Universal Coordinated Time, traditionally known as Greenwich
>Mean Time, GMT). An input value that has an explicit time zone
>specified is converted to UTC using the appropriate offset for that
>time zone. If no time zone is stated in the input string, then it is
>assumed to be in the time zone indicated by the system's timezone
>parameter, and is converted to UTC using the offset for the timezone
>zone.
>
>When a timestamp with time zone value is output, it is always
>converted from UTC to the current timezone zone, and displayed as
>local time in that zone. To see the time in another time zone, either
>change timezone or use the AT TIME ZONE construct (see Section
>9.9.3).
>
> But there doesn't seem to be any similar description of the "WITHOUT
> TIME ZONE" option.  It mentions:
>
>   the date/time fields in the input value [...] is not adjusted for time
>   zone.
>
> But that's about all I could find.  I think that the actual semantics
> should be described and maybe a paragraph should be written highlighting
> differences with an example.  I'd be happy to write this if people
> agree.
>
> My reasoning goes something like this:  The WITH and WITHOUT clauses
> seem to be the opposite of my naive understanding of their purpose.  I'd
> think that if you specify WITH TIME ZONE then it means that the timezone
> is important to me, and I want to deal with it myself.  Whereas, the
> WITHOUT TIME ZONE clause would suggest that the timezone isn't important
> to me, and anything the database can do to make the problem go away the
> better.  What the spec says, and PG does, is actually the opposite.  The
> fact that this confusion can occur (and seems to occur reasonably often
> based on previous posts to the mailing lists) suggests that the docs
> should highlight the differences more clearly.
>
> I'd also hazard a guess that we don't hear about it more because most
> people just work within a single time zone and hence don't even notice
> the difference between the two.

My only comment is on this assertion. Any location that has DST rules has two 
time zones.  For instance I live in  US PST/PDT.  Without timezone support 
doing date/time math across time zone boundaries is asking for problems. 

>
>
>   Sam

-- 
Adrian Klaver
[EMAIL PROTECTED]

-- 
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] Storage sizes for dates/times (documentation bug?)

[Martijn van Oosterhout]

On Tue, Apr 15, 2008 at 02:46:14PM +0200, Karsten Hilbert wrote:
> Yes, I know about tagged types but have shyed away from them
> so far courtesy of them not being adjustable after the fact.

What do you mean by this? Adjustable in what way?

Truly, taggedtypes are a really useful feature but I think the chance
of them being in the main tree approximatly nil, which is enough reason
to stay away from them.
 
> Should I be using a custom domain for this ?

You just need to store the pair (time,zone), I don't think domains are
flexible enough for that. A complex type maybe (but then you've just
reinvented taggedtypes, inefficiently :) )

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature

[GENERAL] Installation of contrib/tablefunc - problems

[Stefan Schwarzer]

Hi there,

small problem with installing the tablefunc libs.

I have a 8.3.1 running on my Mac 10.5.

Now, I would like to add tablefunc.

Last time, I did it the following way:

# in the downloaded postgres package directory
$ ./compile

# in the tablefunc dir
$ USE_PGXS=1 sudo make
$ USE_PGXS=1 sudo make install
$ psql -U ss_admin geodataportal < tablefunc.sql
#(it's then being inserted into the public schema. Good?!)


But, now, this doesn't work anymore. I get this error message:

$ USE_PGXS=1 sudo make
sed 's,MODULE_PATHNAME,$libdir/tablefunc,g' tablefunc.sql.in  
>tablefunc.sql
gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - 
Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict- 
aliasing -fwrapv  -I. -I../../src/include   -c -o tablefunc.o  
tablefunc.c
gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - 
Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict- 
aliasing -fwrapv  -bundle -o tablefunc.so tablefunc.o - 
bundle_loader ../../src/backend/postgres

ld: file not found: ../../src/backend/postgres
collect2: ld returned 1 exit status
make: *** [tablefunc.so] Error 1
rm tablefunc.o


Can anyone give me a hint what I should do?

Thanks a lot!

Stef

--
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] Storage sizes for dates/times (documentation bug?)

[Sam Mason]

On Tue, Apr 15, 2008 at 02:46:14PM +0200, Karsten Hilbert wrote:
> Of course, the actual time stored in the database in UTC is
> quite correct - it was indeed 3pm in location B when it was
> 7am in London. But we need to know the original local time
> (and also be able to know UTC since we want to correlate
> times).

I was under the impression that "timestamp without time zone" does
precisely this.  It would be nicer if the docs highlighted the
differences, and reasons behind, the semantics between the two, instead
of focusing so much on the syntax.  The "WITH TIME ZONE" variant is
described nicely:

   For timestamp with time zone, the internally stored value is always
   in UTC (Universal Coordinated Time, traditionally known as Greenwich
   Mean Time, GMT). An input value that has an explicit time zone
   specified is converted to UTC using the appropriate offset for that
   time zone. If no time zone is stated in the input string, then it is
   assumed to be in the time zone indicated by the system's timezone
   parameter, and is converted to UTC using the offset for the timezone
   zone.

   When a timestamp with time zone value is output, it is always
   converted from UTC to the current timezone zone, and displayed as
   local time in that zone. To see the time in another time zone, either
   change timezone or use the AT TIME ZONE construct (see Section
   9.9.3).

But there doesn't seem to be any similar description of the "WITHOUT
TIME ZONE" option.  It mentions:

  the date/time fields in the input value [...] is not adjusted for time
  zone.

But that's about all I could find.  I think that the actual semantics
should be described and maybe a paragraph should be written highlighting
differences with an example.  I'd be happy to write this if people
agree.

My reasoning goes something like this:  The WITH and WITHOUT clauses
seem to be the opposite of my naive understanding of their purpose.  I'd
think that if you specify WITH TIME ZONE then it means that the timezone
is important to me, and I want to deal with it myself.  Whereas, the
WITHOUT TIME ZONE clause would suggest that the timezone isn't important
to me, and anything the database can do to make the problem go away the
better.  What the spec says, and PG does, is actually the opposite.  The
fact that this confusion can occur (and seems to occur reasonably often
based on previous posts to the mailing lists) suggests that the docs
should highlight the differences more clearly.

I'd also hazard a guess that we don't hear about it more because most
people just work within a single time zone and hence don't even notice
the difference between the two.


  Sam

-- 
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] Storage sizes for dates/times (documentation bug?)

[Karsten Hilbert]

On Mon, Apr 14, 2008 at 09:39:57PM -0400, Tom Lane wrote:

> considering alignment...).  ISTM that we have defined timestamptz
> in such a way that it solves many real-world problems, and timestamp
> also solves real-world problems,
No doubt about it.

> but the use-case for a timestamp plus an explicit time
> zone is much less clear.

GNUmed is a medical record. We store data about patients.
Our public database is accessed from within different time
zones. Imagine a user from Los Angeles and another one from
San Francisco. Later on it is not readily apparent wherefrom
a particular entry was made unless we store the originating
timezone and/or location (which we do). Even if the location
is stored one cannot *easily* derive the appropriate
timezone from it (and thereby the local time of entering
data).

This is particularly important in medicine - expected
hormone levels (say, cortisol) are markedly different at
different times of the day. Say, when a doctor in London
enters a cortisol level measured at 7:00am his time which is
later displayed in a location B with local time = GMT + 8
hours the level will appear to have been taken at 3:00pm --
for which the level is out of bounds.

Of course, the actual time stored in the database in UTC is
quite correct - it was indeed 3pm in location B when it was
7am in London. But we need to know the original local time
(and also be able to know UTC since we want to correlate
times). What we do now is to explicitely store the original
timezone with the timestamp for individual doctor-patient
encounters which is a bit of leap-of-faith but works (one
would actually have to store the timezone for each and every
timestamptz field). We then use that information to say
"SELECT ... AT TIME ZONE ... FROM ...".

Another scenario: Recently Germany underwent the annual DST
change. Suddenly times in the EMR entered before DST
appeared shifted one hour forward. Which, technically, is
correct - it's the same UTC time as before the DST onset,
just shifted by one more hour. They'll appear shifted back
to correct times when we go back to non-DST time. But then
DST-entered times will appear shifted back, too, until DST
starts again.

Of course, all this is solvable by explicitely keeping track
of which timestamps mean what but it'd be a whole lot easier
if one could just say:

select value, ts_blood_drawn at original time zone
from lab_results
where type = 'cortisol';

I (for one) would happily store more bytes if that's what it
takes to reliably get at correct results (given the above
circumstances).

Yes, I know about tagged types but have shyed away from them
so far courtesy of them not being adjustable after the fact.

Should I be using a custom domain for this ?

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] Copying large object in a stored procedure

[Csaba Nagy]

[snip]
> DECLARE
> v_NewOID BIGINT;
   ^^
small correction: v_NewOID should be of type OID:

DECLARE
v_NewOID OID;

BIGINT would of course work too as long as there is implicit cast from
it to/from OID, which seems to be the case on 8.2.

Cheers,
Csaba.



-- 
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] Copying large object in a stored procedure

[Csaba Nagy]

> Is there an easy way to copy a large object to a new one, having a new
> OID and it's content independent from the original ?

So my current solution would be:

CREATE OR REPLACE FUNCTION copy_blob(p_blobId OID)
RETURNS OID AS '
DECLARE
v_NewOID BIGINT;
BEGIN
SELECT lo_create(0) INTO v_NewOID;

DELETE FROM pg_largeobject WHERE loid = v_NewOID;

INSERT INTO pg_largeobject (loid, pageno, data)
SELECT v_NewOID, pageno, data 
FROM pg_largeobject
WHERE loid = p_blobId;

RETURN v_NewOID;
END;
' LANGUAGE plpgsql;


I would still be grateful if anybody knows a better solution using plain
SQL/plpgsql...

Cheers,
Csaba.



-- 
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] Storage sizes for dates/times (documentation bug?)

[Karsten Hilbert]

On Mon, Apr 14, 2008 at 09:10:52PM -0400, Tom Lane wrote:

> Actually, your mistake is in imagining that timestamptz represents the
> timezone explicitly ... it doesn't.

Which really is a great pity :-(

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

[GENERAL] Copying large object in a stored procedure

[Csaba Nagy]

Hi all,

Is there an easy way to copy a large object to a new one, having a new
OID and it's content independent from the original ?

The large object API functions would only allow to stream the original
to the client and stream it back to the server for the copy, which is
not really optimal... I would like to do it all on the server side. The
server side functions would work to export the original to a file and
then import it again, but that needs superuser privileges which rules
that out.

I could simply copy the contents of the pg_largeobject table, but then I
need  a new OID for that. I can use the server side large object API to
create an empty large object, delete it, then use the obtained OID for
the copy... but that also sounds somewhat suboptimal.

Is there any simple way to copy a large object ?

Cheers,
Csaba.




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