Re: [GENERAL] DISTINCT ON without ORDER BY

[Martijn van Oosterhout]

On Tue, Apr 21, 2009 at 12:11:26PM +, Jasen Betts wrote:
> > Is there a way to acheive the above result without a sort and without a
> > self-join?
> 
> anyway you could possibly write an agregate function that returns a
> copy of the row with the highest id?

Put that way it sounds like something for a window function. Not sure
if they can use a HashAggrgate though.

Have a nice day,
-- 
Martijn van Oosterhout  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

Re: [GENERAL] No connection could be made because the target machine actively refused it

[A. Kretschmer]

In response to Bill Todd :
> I am trying to connect to a PostgreSQL server from another machine on my 
> small home network. When I click the Test button in the ODBC data mnager 
> for my system DSN I get the error "No connection could be made because 
> the target machine actively refused it." That may not be the exact 
> message but it is close.
> 
> I have no problem connecting using localhost on the machine where 
> PostgresSQL is running. However, if I connect using the machine's IP 
> address (10.0.0.9) either locally or from another PC I get the error 
> above. I have disabled the Windows firewall and my antivirus software 
> but the error persists.
> 
> Any suggestions? I am new to PostgreSQL so assume I have done something 
> really dumb.

You have to edit your postgresql.conf:

listen_addresses = '*'


and your pg_hba.conf to enable remote access. Read the documentation and
the comments at the end of the pg_hba.conf - file.


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

Re: [GENERAL] What must be installed on client for ODBC connection

[Andrew Gould]

On Tue, Apr 21, 2009 at 8:58 PM, Bill Todd  wrote:

> Do  I have to install anything in addition to the ODBC driver on my PC in
> order to connect to a PostgreSQL server on another PC on my network?
>
> Bill
>
>
That depends on your goals.

You can use database products such as MS Access, Crystal Report Writer or
Paradox to access the PostgreSQL server via ODBC.  I think Kexi, Rekall and
OpenOffice's Base can also do this.  You will need to define data sources,
which are ODBC configurations to different databases on the server.  These
applications will allow you to perform queries, write reports or create
database front-end applications.

I think most of the common scripting languages have mechanisms by which you
can access database servers via ODBC.  This is one way that PHP web programs
manage data, for example.

What are you trying to do?

Sincerely,

Andrew

[GENERAL] No connection could be made because the target machine actively refused it

[Bill Todd]

I am trying to connect to a PostgreSQL server from another machine on my 
small home network. When I click the Test button in the ODBC data mnager 
for my system DSN I get the error "No connection could be made because 
the target machine actively refused it." That may not be the exact 
message but it is close.


I have no problem connecting using localhost on the machine where 
PostgresSQL is running. However, if I connect using the machine's IP 
address (10.0.0.9) either locally or from another PC I get the error 
above. I have disabled the Windows firewall and my antivirus software 
but the error persists.


Any suggestions? I am new to PostgreSQL so assume I have done something 
really dumb.


Bill

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

Re: [pgsql-advocacy] [GENERAL] PostgreSQL versus MySQL for GPS Data

[Scott Marlowe]

On Tue, Apr 21, 2009 at 9:19 PM, Steve Singer  wrote:
> On Tue, 21 Apr 2009, David Fetter wrote:
>
>> On Tue, Apr 21, 2009 at 08:15:00PM +0100, Peter Childs wrote:
>>>
>>> Hmm Interestingly OSM have just switched from MySQL to PostgreSQL.
>>
>> Can we get somebody from OSM to talk about this on the record?
>
> I've forwarded this request the to the OSM talk list.  Hopefully someone who
> can talk 'on the record' will step forward.
>
> The master OSM database used for editing used to by MySQL but most of the
> map rendering was done from Postgis hosted data.  Over the weekend they
> switched ,as part of an API upgrade,  the main editing database to
> Postgresql (but still not using complex geometry types).
>
> I think the reasoning had to do with them wanting transactions and the
> switch to InnoDB brought has some downsides, but I don't know which of the
> innodb downsides motivated the switch.

I believe it was the loss of full text indexing with innodb that drove
the switch.  That's what the wiki entry on postgres says

> I think the reference to MonetDB was part of an April fools joke.

Sounds like it.  Still kinda freaked me out at first.

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

Re: [pgsql-advocacy] [GENERAL] PostgreSQL versus MySQL for GPS Data

[Steve Singer]

On Tue, 21 Apr 2009, David Fetter wrote:


On Tue, Apr 21, 2009 at 08:15:00PM +0100, Peter Childs wrote:

Hmm Interestingly OSM have just switched from MySQL to PostgreSQL.


Can we get somebody from OSM to talk about this on the record?


I've forwarded this request the to the OSM talk list.  Hopefully someone who 
can talk 'on the record' will step forward.


The master OSM database used for editing used to by MySQL but most of the 
map rendering was done from Postgis hosted data.  Over the weekend they 
switched ,as part of an API upgrade,  the main editing database 
to Postgresql (but still not using complex geometry types).


I think the reasoning had to do with them wanting transactions and the 
switch to InnoDB brought has some downsides, but I don't know which of the 
innodb downsides motivated the switch.


I think the reference to MonetDB was part of an April fools joke.

Steve



Cheers,
David.
--
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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




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

[GENERAL] What must be installed on client for ODBC connection

[Bill Todd]

Do  I have to install anything in addition to the ODBC driver on my PC 
in order to connect to a PostgreSQL server on another PC on my network?


Bill

--
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] Can Autovaccuum also reindex

[Alvaro Herrera]

Crowe, Eric wrote:
> Greetings,
> 
> Hopefully this can be easily answered but I have not seen any reference
> to this within the online docs and mailing list archives.  Can or does
> autovaccuum also have the capability to reindex; or, is this something
> that needs to be placed within a custom cron job?  

Autovacuum never attempts anything that might require exclusive locks.

-- 
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] Can Autovaccuum also reindex

[Erik Jones]

On Apr 21, 2009, at 4:43 PM, Crowe, Eric wrote:


Greetings,

Hopefully this can be easily answered but I have not seen any  
reference to this within the online docs and mailing list archives.   
Can or does autovaccuum also have the capability to reindex; or, is  
this something that needs to be placed within a custom cron job?


Many thanks!

Eric Crowe



autovacuum will kick off VACUUMs and ANALYZEs but no REINDEXes.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






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

[GENERAL] Can Autovaccuum also reindex

[Crowe, Eric]

Greetings,

Hopefully this can be easily answered but I have not seen any reference
to this within the online docs and mailing list archives.  Can or does
autovaccuum also have the capability to reindex; or, is this something
that needs to be placed within a custom cron job?  

Many thanks!

Eric Crowe



-
This message (including any attachments) may contain confidential
information intended for a specific individual and purpose.  If you
are not the intended recipient, delete this message.  If you are
not the intended recipient, disclosing, copying, distributing, or
taking any action based on this message is strictly prohibited.

Re: [GENERAL] trouble with to_char('L')

[Alvaro Herrera]

Mikko escribió:
> On Tue, Apr 21, 2009 at 8:13 PM, Alvaro Herrera
>  wrote:
> > Maybe the problem here is that the chosen locales are not UTF8.  Does it
> > work if you set lc_numeric and lc_monetary to "Finnish_Finland.65001"
> > instead?  Those should match the server_encoding.
> 
> alter database testdb set lc_monetary(or numeric) to
> 'Finnish_Finland.65001' returns:
> ERROR:  invalid value for parameter "lc_monetary": "Finnish_Finland.65001"

Ouch ... I thought that was the way that Windows designated UTF8
locales, but maybe I am wrong.

> However, I noticed that both lc_collate and lc_ctype are set to
> Finnish_Finland.1252 by the installer. Should I have just run initdb
> with --locale fi_FI.UTF8 at the very start? The to_char('L') works
> fine with a database with win1252 encoding.

Hmm, it should have disallowed the creation of an UTF8 database then.
Maybe that part is what is broken here.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
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] Yet another "drop table vs delete" question

[Jeff Davis]

On Tue, 2009-04-21 at 14:30 -0700, Christophe wrote:
> Indeed so, and I understand that part.  But since Session1 didn't try  
> to access 'bar', it can't distinguish that sequence from:
> 
> Session2:
>   BEGIN;
>   TRUNCATE bar;
>   COMMIT;
> 
> Session1:
>   BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>   SELECT * FROM foo;
>   SELECT * from bar;
>   COMMIT;

Add something else into the mix, like if the transaction in Session2
updates "foo", and I think it will cause the MVCC violation you're
looking for.

Session0:
  INSERT INTO foo VALUES(1);
  INSERT INTO bar VALUES(2);

Session1:
  BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT * FROM foo;

Session2:
  BEGIN;
  INSERT INTO foo VALUES(3);
  TRUNCATE bar;
  COMMIT;

Session1:
  SELECT * from bar;
  COMMIT;

Atomicity says that Session1 should either see 1 and 3 in foo, and
nothing in bar (if it happens after Session2); or it should see 1 in foo
and 2 in bar (if it happens first). So the rule that a SERIALIZABLE
transaction should get one consistent snapshot for its duration is
broken in this case.

I don't think it's an issue if only using READ COMMITTED (but I've been
wrong on similar issues in the past).

Regards,
Jeff Davis


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

Re: [GENERAL] Yet another "drop table vs delete" question

[Tom Lane]

Christophe  writes:
> On Apr 21, 2009, at 2:15 PM, Jeff Davis wrote:
>> In Session1, the serializable transaction sees an empty version of  
>> bar, even though it had tuples in at the time Session1 got its serializable
>> snapshot.

> Indeed so, and I understand that part.  But since Session1 didn't try  
> to access 'bar', it can't distinguish that sequence from:

Well, it could if the application has expectations about consistency
between the contents of 'foo' and 'bar'.  I think we have enough
interlocks to ensure that it's not possible for an explicit foreign-key
constraint to be violated that way, but the application could have
expectations that it's not formalized as an FK.  In general the point
of a serializable snapshot is to ensure that you see logically
simultaneous contents of all the tables you look at, and that will
definitely not be the case in this type of scenario.

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] Yet another "drop table vs delete" question

[asceta]

On 21 Kwi, 21:30, alvhe...@commandprompt.com (Alvaro Herrera) wrote:
> marek.patr...@gmail.com escribió:
>
> > I was wondering if dropping a table is more efficient in PostgreSQL
> > 8.x in comparison to deleting it's content ?
>
> "8.x" is a meaningless version number in Postgres.  Major versions (with
> new features, etc) are labeled by the first two elements, so 8.0, 8.1,
> and so on.
>
> To actually answer your question,
>
> > The thing is, postgresql may leave some invalid content behind in both
> > situations. The real question is - which of those two options leaves
> > less garbage to be vaccumed ? At this point I don't relay care about
> > cost based efficiency but cutting down pg background work.
>
> Try TRUNCATE.  That leaves the less garbage behind and takes the less
> time.
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your 
> subscription:http://www.postgresql.org/mailpref/pgsql-general

Thank you for all your help.

-- 
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] trouble with to_char('L')

[Mikko]

On Tue, Apr 21, 2009 at 8:13 PM, Alvaro Herrera
 wrote:
> Maybe the problem here is that the chosen locales are not UTF8.  Does it
> work if you set lc_numeric and lc_monetary to "Finnish_Finland.65001"
> instead?  Those should match the server_encoding.

alter database testdb set lc_monetary(or numeric) to
'Finnish_Finland.65001' returns:
ERROR:  invalid value for parameter "lc_monetary": "Finnish_Finland.65001"

However, I noticed that both lc_collate and lc_ctype are set to
Finnish_Finland.1252 by the installer. Should I have just run initdb
with --locale fi_FI.UTF8 at the very start? The to_char('L') works
fine with a database with win1252 encoding.

Mikko

-- 
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] Yet another "drop table vs delete" question

[Tom Lane]

Jeff Davis  writes:
> On Tue, 2009-04-21 at 13:59 -0700, Christophe wrote:
>> I'm sure there is a scenario under which a separate  
>> transaction could see non-MVCC behavior from TRUNCATE, but I'm
>> having trouble see what it is.

> Session1:
>   BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>   SELECT * FROM foo;

> Session2:
>   BEGIN;
>   TRUNCATE bar;
>   COMMIT;

> Session1:
>   SELECT * from bar;
>   COMMIT;

> In Session1, the serializable transaction sees an empty version of bar,
> even though it had tuples in at the time Session1 got its serializable
> snapshot.

Exactly.

> If Session2 does a DROP TABLE instead of TRUNCATE, Session1 will get an
> error when it tries to read "bar".

Actually, the scenario that I suppose the OP had in mind was to drop
and immediately recreate "bar" (probably in the same transaction).
If you do that, then session 1 will actually see the new version of
"bar" when it eventually gets around to examining the table --- this
is because system catalog accesses always follow SnapshotNow rules.

So there is really darn little difference between TRUNCATE and
drop/recreate.  The advantage of TRUNCATE is you don't have to
run around and manually re-establish indexes, foreign keys, etc.
It's probably also a tad faster because of less catalog churn.

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] Yet another "drop table vs delete" question

[Christophe]

On Apr 21, 2009, at 2:15 PM, Jeff Davis wrote:
In Session1, the serializable transaction sees an empty version of  
bar,

even though it had tuples in at the time Session1 got its serializable
snapshot.


Indeed so, and I understand that part.  But since Session1 didn't try  
to access 'bar', it can't distinguish that sequence from:


Session2:
 BEGIN;
 TRUNCATE bar;
 COMMIT;

Session1:
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 SELECT * FROM foo;
 SELECT * from bar;
 COMMIT;

I've been trying to come up with a scenario in which a TRUNCATE  
violates concurrency expectations; I'm sure one exists, but my brain  
isn't wrapping around 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] Yet another "drop table vs delete" question

[Jeff Davis]

On Tue, 2009-04-21 at 13:59 -0700, Christophe wrote:
> I'm sure there is a scenario under which a separate  
> transaction could see non-MVCC behavior from TRUNCATE, but I'm
> having  
> trouble see what it is.

Session1:
  BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT * FROM foo;

Session2:
  BEGIN;
  TRUNCATE bar;
  COMMIT;

Session1:
  SELECT * from bar;
  COMMIT;

In Session1, the serializable transaction sees an empty version of bar,
even though it had tuples in at the time Session1 got its serializable
snapshot.

If Session2 does a DROP TABLE instead of TRUNCATE, Session1 will get an
error when it tries to read "bar".

Regards,
Jeff Davis


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

Re: [GENERAL] Yet another "drop table vs delete" question

[Christophe]

On Apr 21, 2009, at 1:36 PM, Tom Lane wrote:

I was thinking of MVCC semantics, which is a different issue.


Indeed so, my error.  This is a bit of a drift off-topic, but  
rereading the docs, I'm now having trouble visualizing the real-world  
effect of the non-MVCC-safeness of TRUNCATE.  A transaction that  
queries the table before the TRUNCATE is going to pick up an ACCESS  
SHARED lock, which will prevent the TRUNCATE from running until it's  
released.  The TRUNCATE will pick up an ACCESS EXCLUSIVE lock that  
will block any subsequent queries until the transaction doing the  
TRUNCATE commits.  I'm sure there is a scenario under which a separate  
transaction could see non-MVCC behavior from TRUNCATE, but I'm having  
trouble see what it is.


--
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] Yet another "drop table vs delete" question

[Chris Browne]

x...@thebuild.com (Christophe) writes:
> On Apr 21, 2009, at 12:28 PM, Peter Eisentraut wrote:
>> Yes, but if you are asking that question, you probably really want
>> to use
>> TRUNCATE.
>
> The advantage of DROP TABLE being, of course, that DROP TABLE is
> transactionally-safe, while TRUNCATE is not.

TRUNCATE mayn't have been transactionally safe back in PostgreSQL 8.0,
but it is, now.

   http://www.postgresql.org/docs/8.3/static/sql-truncate.html
-- 
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/linuxdistributions.html
Rules  of the  Evil Overlord  #151. "I  will not  set myself  up  as a
god. That perilous position is reserved for my trusted lieutenant."


-- 
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] Yet another "drop table vs delete" question

[Tom Lane]

Christophe  writes:
> On Apr 21, 2009, at 1:20 PM, Tom Lane wrote:
>> They're both going to drop data that
>> might conceivably be visible in the snapshot of some concurrent
>> transaction that hasn't yet touched the table (else it would have  
>> lock)
>> but might wish to do so later.

> Unless I'm deeply misunderstanding something (always a possibility),  
> DROP TABLE and TRUNCATE are not symmetrical in this regard.  Once a  
> transaction has issued a DROP TABLE, all other transactions that  
> attempt to modify it are going to block on the first transaction's  
> ACCESS EXCLUSIVE lock until it commits or aborts.  In the case of  
> TRUNCATE, the other transactions will see the table as being empty  
> from the moment in the first transaction issues the TRUNCATE, and will  
> see the rows reappear if the first transaction rolls back.  Yes?

No.  They both take exclusive lock.  Oh, and they are both safe in
the sense that the data is still there if the calling transaction
fails or rolls back.  Perhaps that was what you meant by "transactional
safety"?  I was thinking of MVCC semantics, which is a different issue.

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] Yet another "drop table vs delete" question

[Christophe]

On Apr 21, 2009, at 1:20 PM, Tom Lane wrote:

They're both going to drop data that
might conceivably be visible in the snapshot of some concurrent
transaction that hasn't yet touched the table (else it would have  
lock)

but might wish to do so later.


Unless I'm deeply misunderstanding something (always a possibility),  
DROP TABLE and TRUNCATE are not symmetrical in this regard.  Once a  
transaction has issued a DROP TABLE, all other transactions that  
attempt to modify it are going to block on the first transaction's  
ACCESS EXCLUSIVE lock until it commits or aborts.  In the case of  
TRUNCATE, the other transactions will see the table as being empty  
from the moment in the first transaction issues the TRUNCATE, and will  
see the rows reappear if the first transaction rolls back.  Yes?


--
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] COPY 'invalid byte sequence for encoding "UTF8": 0xff'

[Steve Crawford]

hmm, I was shelling out and using psql and piping the data to another
file.  Not using the dbi stuff with perl.

Guess i can use a regular expression and review the link you sent me
and escape them my self.
  
Don't *ahem* quote me on this as I haven't been using Perl for a while 
but it should be something similar to:

$quoted_data = $dbh->quote($data, PG_BYTEA)

If not, I'm sure the documentation for the "quote" function will set 
things straight.


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] Yet another "drop table vs delete" question

[Tom Lane]

Christophe  writes:
> On Apr 21, 2009, at 12:28 PM, Peter Eisentraut wrote:
>> Yes, but if you are asking that question, you probably really want  
>> to use TRUNCATE.

> The advantage of DROP TABLE being, of course, that DROP TABLE is  
> transactionally-safe, while TRUNCATE is not.

The above is complete nonsense.  They're both going to drop data that
might conceivably be visible in the snapshot of some concurrent
transaction that hasn't yet touched the table (else it would have lock)
but might wish to do so later.

If you use DELETE, you'll be transactionally safe, but the cost is
concomitantly a lot higher than either DROP or TRUNCATE.

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] PostgreSQL versus MySQL for GPS Data

[David Fetter]

On Tue, Apr 21, 2009 at 08:15:00PM +0100, Peter Childs wrote:
> Hmm Interestingly OSM have just switched from MySQL to PostgreSQL.

Can we get somebody from OSM to talk about this on the record?

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
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] round behavior differs between 8.1.5 and 8.3.7

[Robert Morton]

Thanks for the explanation, that's exactly the level of detail I need.
-Robert

On Tue, Apr 21, 2009 at 12:00 PM, Tom Lane  wrote:

> Robert Morton  writes:
> > Perhaps there are platform differences, since the version I am using was
> > built with Microsoft Visual Studio:
>
> Ah, now you tell us.
>
> round(float8) just calls the platform's rint() function.  At least on
> platforms that have rint(), which maybe Windows doesn't.  In that case
> it's going to come down to src/port/rint.c, which is not particularly
> careful about the exactly-0.5 case.  I think fully-standards-conformant
> versions of rint() are probably going to use a "round to nearest even
> integer" rule in such cases.  But by and large, float8 arithmetic *is*
> going to have platform-specific behaviors; you're living in a fantasy
> world if you think otherwise.
>
>regards, tom lane
>

Re: [GENERAL] PostgreSQL versus MySQL for GPS Data

[Alvaro Herrera]

Scott Marlowe escribió:
> On Tue, Apr 21, 2009 at 1:15 PM, Peter Childs  wrote:
> > Hmm Interestingly OSM have just switched from MySQL to PostgreSQL.
> 
> On the news blog page it mentioned switching to MonetDB.  I saw
> nothing about pgsql there.  Do they store it in pgsql for manipulation
> then export to MonetDB?

That's the April 1st news though ... the real news is here
http://wiki.openstreetmap.org/wiki/OSM_Protocol_Version_0.6#Database_improvements

-- 
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] Yet another "drop table vs delete" question

[Alvaro Herrera]

marek.patr...@gmail.com escribió:
> I was wondering if dropping a table is more efficient in PostgreSQL
> 8.x in comparison to deleting it's content ?

"8.x" is a meaningless version number in Postgres.  Major versions (with
new features, etc) are labeled by the first two elements, so 8.0, 8.1,
and so on.

To actually answer your question,

> The thing is, postgresql may leave some invalid content behind in both
> situations. The real question is - which of those two options leaves
> less garbage to be vaccumed ? At this point I don't relay care about
> cost based efficiency but cutting down pg background work.

Try TRUNCATE.  That leaves the less garbage behind and takes the less
time.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
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 versus MySQL for GPS Data

[Stephen Frost]

* Scott Marlowe (scott.marl...@gmail.com) wrote:
> On Tue, Apr 21, 2009 at 1:15 PM, Peter Childs  wrote:
> > Hmm Interestingly OSM have just switched from MySQL to PostgreSQL.
> 
> On the news blog page it mentioned switching to MonetDB.  I saw
> nothing about pgsql there.  Do they store it in pgsql for manipulation
> then export to MonetDB?

Err, why do I get the feeling that the date on the post wrt MonetDB and
cherokee might play some role?

Based on the wiki, they're using PG 8.3 now (as of April 2009, which
does seem rather recent) and it replaced MySQL.

http://wiki.openstreetmap.org/wiki/Servers/smaug

http://wiki.openstreetmap.org/wiki/Servers/db

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Yet another "drop table vs delete" question

[Christophe]

On Apr 21, 2009, at 12:28 PM, Peter Eisentraut wrote:
Yes, but if you are asking that question, you probably really want  
to use

TRUNCATE.


The advantage of DROP TABLE being, of course, that DROP TABLE is  
transactionally-safe, while TRUNCATE is not.


--
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] Yet another "drop table vs delete" question

[Peter Eisentraut]

On Tuesday 21 April 2009 20:26:24 marek.patr...@gmail.com wrote:
> I was wondering if dropping a table is more efficient in PostgreSQL
> 8.x in comparison to deleting it's content ?

Yes, but if you are asking that question, you probably really want to use 
TRUNCATE.


-- 
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 versus MySQL for GPS Data

[Stephen Frost]

* Scott Marlowe (scott.marl...@gmail.com) wrote:
> On Tue, Apr 21, 2009 at 1:15 PM, Peter Childs  wrote:
> > Hmm Interestingly OSM have just switched from MySQL to PostgreSQL.
> 
> On the news blog page it mentioned switching to MonetDB.  I saw
> nothing about pgsql there.  Do they store it in pgsql for manipulation
> then export to MonetDB?

I thought they had always used PG for some piece of what they're doing,
and just used MySQL for some other piece of it.  I'm not sure which is
which though.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] PostgreSQL versus MySQL for GPS Data

[Scott Marlowe]

On Tue, Apr 21, 2009 at 1:15 PM, Peter Childs  wrote:
> Hmm Interestingly OSM have just switched from MySQL to PostgreSQL.

On the news blog page it mentioned switching to MonetDB.  I saw
nothing about pgsql there.  Do they store it in pgsql for manipulation
then export to MonetDB?

-- 
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 versus MySQL for GPS Data

[Peter Childs]

2009/3/19 Shane Ambler :
> Thomas Kellerer wrote:
>>
>> Harald Armin Massa, 17.03.2009 15:00:
>>>
>>> That is: what table size would you or anybody consider really, really
>>> large actually?
>>
>> I recently attended and Oracle training by Tom Kyte and he said (partially
>> joking though) that a database is only large when the size
>> is measured in terrabytes :) So really, really large would mean something
>> like 100 petabytes
>>
>>
>> My personal opinion is that a "large" database has more than ~10 million
>> rows in more than ~10 tables.
>>
>> Thomas
>>
>>
> I would say that as far as GPS data goes the street maps of the world
> would be pretty big.
>
> openstreetmap.org is still a work in progress but their current db dumps
> gzip down to 6.4GB. It was a while back that I noseyed around with it
> but I do recall that it compressed well and was very large uncompressed.
> Don't recall how many rows it contained.
>
> I wonder what an almost complete world street map like google maps comes
> in at?
>
>
>

Hmm Interestingly OSM have just switched from MySQL to PostgreSQL.

I think this is a big pat on the back for PostgreSQL and a sign that
PostgreSQL is now gaining the level of users that it always should
have had

The 6.4Gb is BZipped XML, its over 150G of XML and is not actually the
total size of the OSM database, as that has extra historical and who
done it data as well, plus index etc. I would want to have at least
1/2TB  minimum to put it on a machine probably more.

Peter.

Peter.

-- 
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] COPY 'invalid byte sequence for encoding "UTF8": 0xff'

[Steve Crawford]

Chris Worley wrote:

...

For example, if I create a
table with a single bytea column called "foo" and insert the a record with
value "^F^O^O" then dump it, the dump will have the following:

COPY byteatest (foo) FROM stdin;
\\006\\017\\017
\.



How does pg_dump and pg_restonre handle everything with no errors?
  
See above. It escapes the data so it can be represented in a query. The 
Ctrl-F in the bytea column is converted for insertion purposes to its 
3-digit octal equivalent of 006 represented as single escaped character 
in the data as \\006. The Ctrl-O is \\017. Both \\006 and \\017 are 
single characters. You _can_ escape all characters - ie instead of 'A'  
you could use '\\101' but you are only _required_ to escape those 
certain characters listed in the documentation. Again, see 
http://www.postgresql.org/docs/8.3/static/datatype-binary.html.

The column is basically telnet buffers which contains escape sequences
so i have bytes such as 0xff, 0x1b...   Piping the output through
iconv helped formatting some of the data, but it appears I am still
left with some, such as 0xff.
  
This makes no sense. Binary data could be an executable, a video, a 
photo, audio data, etc. but it is not linguistic characters where a 
character-set interpretation is appropriate. You may pipe it through 
iconv and find there are fewer errors in the attempted import but that 
does not imply that output is in any way improved by the transformation. 
(I can also feed sox to a JPEG photo, tell it that the data is mu-law, 
and have it create an MP3 but the result would just be painful noise.)

It would be nice to know what sql pg_dump uses to create the contents
inside of the copy command.  Any idea what that is?
  


Yes, it escapes those bytea characters that require escaping and leaves 
the others alone. Here is the same example from before with the string 
"^F^O^Obar" (foo in control-characters, bar in lowercase) in the bytea 
column:


COPY byteatest (foo) FROM stdin;
\\006\\017\\017bar
\.

You need to do the same in your program. Most client interfaces have an 
escape-bytea function that will handle it for you. In C you could use 
PQescapeByteaConn while PHP uses pg_escape_bytea. Find the appropriate 
function for your language.


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] round behavior differs between 8.1.5 and 8.3.7

[Tom Lane]

Robert Morton  writes:
> Perhaps there are platform differences, since the version I am using was
> built with Microsoft Visual Studio:

Ah, now you tell us.

round(float8) just calls the platform's rint() function.  At least on
platforms that have rint(), which maybe Windows doesn't.  In that case
it's going to come down to src/port/rint.c, which is not particularly
careful about the exactly-0.5 case.  I think fully-standards-conformant
versions of rint() are probably going to use a "round to nearest even
integer" rule in such cases.  But by and large, float8 arithmetic *is*
going to have platform-specific behaviors; you're living in a fantasy
world if you think otherwise.

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] round behavior differs between 8.1.5 and 8.3.7

[Robert Morton]

Perhaps there are platform differences, since the version I am using was
built with Microsoft Visual Studio:

SELECT version()
PostgreSQL 8.3.7, compiled by Visual C++ build 1400
The v8.1.5 server I'm using was compiled with GCC:
PostgreSQL 8.1.5 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)

I would like to understand how this difference affects rounding in more
detail so that I may explain caveats to customers.

Thanks,
Robert

On Mon, Apr 20, 2009 at 5:36 PM, Adrian Klaver  wrote:

>  On Monday 20 April 2009 5:20:47 pm Adrian Klaver wrote:
> > On Monday 20 April 2009 2:21:31 pm Robert Morton wrote:
> > > Howdy,
> > > None of the discussions about rounding so far have addressed what
> appears
> > > to be a significant change that occurred at some point between
> PostgreSQL
> > > v8.1.5 and v8.3.7.  Can someone explain to me the difference between
> the
> > > two resultsets below?  Additionally I would like to understand what
> > > option will consistently provide a banker's-round in v8.3.7, if
> possible.
> > >
> > > Here is the query, followed by the resultset for each version:
> > >  SELECT
> > >round(3.5::numeric) as "rn3.5",
> > >round(3.5::float8) as "rf3.5",
> > >dround(3.5::numeric) as "dn3.5",
> > >dround(3.5::float8) as "df3.5",
> > >CAST(3.5::numeric as INTEGER) as "cn3.5",
> > >CAST(3.5::float8 as INTEGER) as "cf3.5",
> > >round(4.5::numeric) as "rn4.5",
> > >round(4.5::float8) as "rf4.5",
> > >dround(4.5::numeric) as "dn4.5",
> > >dround(4.5::float8) as "df4.5",
> > >CAST(4.5::numeric as INTEGER) as "cn4.5",
> > >CAST(4.5::float8 as INTEGER) as "cf4.5"
> > >
> > > v8.1.5:
> > > rn3.5,rf3.5,dn3.5,df3.5,cn3.5,cf3.5,rn4.5,rf4.5,dn4.5,df4.5,cn4.5,cf4.5
> > > 4,4,4,4,4,4,5,4,4,4,5,4
> > > v8.3.7:
> > > rn3.5,rf3.5,dn3.5,df3.5,cn3.5,cf3.5,rn4.5,rf4.5,dn4.5,df4.5,cn4.5,cf4.5
> > > 4,3,3,3,4,3,5,4,4,4,5,4
> > >
> > >
> > > Thanks,
> > > Robert
> >
> > Well it wasn't 8.3.5 :) because:
> >
> > postgres=# SELECT version();
> > version
> >
> ---
> >- PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by
> GCC
> > gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)
> > (1 row)
> >
> >
> > postgres=# SELECT
> > postgres-# round(3.5::numeric) as "rn3.5",
> > postgres-# round(3.5::float8) as "rf3.5",
> > postgres-# dround(3.5::numeric) as "dn3.5",
> > postgres-# dround(3.5::float8) as "df3.5",
> > postgres-# CAST(3.5::numeric as INTEGER) as "cn3.5",
> > postgres-# CAST(3.5::float8 as INTEGER) as "cf3.5",
> > postgres-# round(4.5::numeric) as "rn4.5",
> > postgres-# round(4.5::float8) as "rf4.5",
> > postgres-# dround(4.5::numeric) as "dn4.5",
> > postgres-# dround(4.5::float8) as "df4.5",
> > postgres-# CAST(4.5::numeric as INTEGER) as "cn4.5",
> > postgres-# CAST(4.5::float8 as INTEGER) as "cf4.5"
> > postgres-# ;
> >  rn3.5 | rf3.5 | dn3.5 | df3.5 | cn3.5 | cf3.5 | rn4.5 | rf4.5 | dn4.5 |
> > df4.5 | cn4.5 | cf4.5
> >
> ---+---+---+---+---+---+---+---+---+---
> >+---+--- 4 | 4 | 4 | 4 | 4 | 4 | 5 |
> > 4 | 4 | 4 | 5 | 4
> > (1 row)
> >
> >
> >
> >
> > --
> > Adrian Klaver
> > akla...@comcast.net
>
>
> I upgraded to 8.3.7 and I still don't see what you see. There must be
> something
> else going here.
>
> postgres=# SELECT version();
>version
>
> 
>  PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.4
> (Ubuntu
> 4.2.4-1ubuntu3)
> (1 row)
>
> postgres=# SELECT
> round(3.5::numeric) as "rn3.5",
> round(3.5::float8) as "rf3.5",
> dround(3.5::numeric) as "dn3.5",
> dround(3.5::float8) as "df3.5",
> CAST(3.5::numeric as INTEGER) as "cn3.5",
> CAST(3.5::float8 as INTEGER) as "cf3.5",
> round(4.5::numeric) as "rn4.5",
> round(4.5::float8) as "rf4.5",
> dround(4.5::numeric) as "dn4.5",
> dround(4.5::float8) as "df4.5",
> CAST(4.5::numeric as INTEGER) as "cn4.5",
> CAST(4.5::float8 as INTEGER) as "cf4.5"
>  ;
>  rn3.5 | rf3.5 | dn3.5 | df3.5 | cn3.5 | cf3.5 | rn4.5 | rf4.5 | dn4.5 |
> df4.5 |
> cn4.5 | cf4.5
>
> ---+---+---+---+---+---+---+---+---+---+---+---
> 4 | 4 | 4 | 4 | 4 | 4 | 5 | 4 | 4 |
> 4 |
> 5 | 4
> (1 row)
>
>
> --
> Adrian Klaver
> akla...@comcast.net
>

[GENERAL] Yet another "drop table vs delete" question

[marek . patrzek]

I was wondering if dropping a table is more efficient in PostgreSQL
8.x in comparison to deleting it's content ?

To put you in the picture - I want to schedule via cron job some daily
data materialization. With the resource I got there are only those two
ways:

1) DROP statement:
a) DROP TABLE tmp;
b) CREATE TABLE tmp AS select * from ;

2) DELETE statement:
a) DELETE FROM tmp;
b) INSERT INTO tmp SELECT * from ;

tmp table doesn't have any index nor constraints.

The thing is, postgresql may leave some invalid content behind in both
situations. The real question is - which of those two options leaves
less garbage to be vaccumed ? At this point I don't relay care about
cost based efficiency but cutting down pg background work.

-- 
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] COPY 'invalid byte sequence for encoding "UTF8": 0xff'

[Chris Worley]

On Tue, Apr 21, 2009 at 1:39 PM, Steve Crawford
 wrote:
> Chris Worley wrote:
>>
>> Hello,
>>
>> I get the following error when running a sql script containing a COPY
>> command:
>>
>> ERROR:  invalid byte sequence for encoding "UTF8": 0xff...
>>
>> The data I have contains binary data from a tcp dump
>>
>> Does anybody know how the dump pulls a column with binary data?  It is
>> a varchar column
>
> No, if it it binary data, you need a column of type bytea and you need to
> escape the appropriate characters in your input. For example, if I create a
> table with a single bytea column called "foo" and insert the a record with
> value "^F^O^O" then dump it, the dump will have the following:
>
> COPY byteatest (foo) FROM stdin;
> \\006\\017\\017
> \.

How does pg_dump and pg_restonre handle everything with no errors?
The column is basically telnet buffers which contains escape sequences
so i have bytes such as 0xff, 0x1b...   Piping the output through
iconv helped formatting some of the data, but it appears I am still
left with some, such as 0xff.

It would be nice to know what sql pg_dump uses to create the contents
inside of the copy command.  Any idea what that is?

-chris worley

>
> See http://www.postgresql.org/docs/8.3/static/datatype-binary.html for the
> characters that need to be escaped.
>
> 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] COPY 'invalid byte sequence for encoding "UTF8": 0xff'

[Steve Crawford]

Chris Worley wrote:

Hello,

I get the following error when running a sql script containing a COPY command:

ERROR:  invalid byte sequence for encoding "UTF8": 0xff...

The data I have contains binary data from a tcp dump

Does anybody know how the dump pulls a column with binary data?  It is
a varchar column
No, if it it binary data, you need a column of type bytea and you need 
to escape the appropriate characters in your input. For example, if I 
create a table with a single bytea column called "foo" and insert the a 
record with value "^F^O^O" then dump it, the dump will have the following:


COPY byteatest (foo) FROM stdin;
\\006\\017\\017
\.

See http://www.postgresql.org/docs/8.3/static/datatype-binary.html for 
the characters that need to be escaped.


Cheers,
Steve


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

[GENERAL] COPY 'invalid byte sequence for encoding "UTF8": 0xff'

[Chris Worley]

Hello,

I get the following error when running a sql script containing a COPY command:

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

What I have done is write a perl script that selects out certain data
from a database and places it tab separated output between a line with
COPY from stdin and .\.  Just like the pg_dump output looks.

The data I have contains binary data from a tcp dump.  After running
my perl script I put the result through this command, to escape the
octets.  (thought i needed to do this)

cat data.dump | iconv -c -f utf8 -t utf8 > fixed.dump

Does anybody know how the dump pulls a column with binary data?  It is
a varchar column.

-chris worley

-- 
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] Doubt about join queries

[jc_mich]

Thanks a lot! It works as good as I wish

Best regards.


Michael Nolan wrote:
> 
> On Tue, Apr 21, 2009 at 12:04 PM, jc_mich  wrote:
> 
>>
>> The problem is not the formula, the problem is the join. I've summarized
>> it
>> because is a large formula and I've got right results
>>
>>
> You're quite correct that the problem is the join, because neither of the
> examples you gave have any code to limit the number of store records
> returned.
> 
> You may need to include something like the following code fragment.  (I'm
> using a 'distance' formula as a proxy for the latitude/longitude formula.)
> 
> where store_no = (select store_no from store order by
> distance(store,customer) limit 1)
> --
> Mike Nolan
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Doubt-about-join-queries-tp23142980p23160612.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] trouble with to_char('L')

[Alvaro Herrera]

Mikko escribió:
> psql (PostgreSQL) 8.3.7
> 
> server_version 8.3.7
> server_encoding UTF8
> client_encoding win1252
> lc_numeric Finnish, Finland
> lc_monetary Finnish, Finland
> 
> testdb=# SELECT to_char(3.1415::numeric(5,2), '999D99L');
> 
> ERROR:  invalid byte sequence for encoding "UTF8": 0x80
> HINT:  This error can also happen if the byte sequence does not match
> the encoding expected by the server, which is controlled by
> "client_encoding".

FWIW 0x80 is the Euro symbol in Win1252 according to
http://en.wikipedia.org/wiki/Windows-1252

Maybe the problem here is that the chosen locales are not UTF8.  Does it
work if you set lc_numeric and lc_monetary to "Finnish_Finland.65001"
instead?  Those should match the server_encoding.

-- 
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] Doubt about join queries

[Michael Nolan]

On Tue, Apr 21, 2009 at 12:04 PM, jc_mich  wrote:

>
> The problem is not the formula, the problem is the join. I've summarized it
> because is a large formula and I've got right results
>
>
You're quite correct that the problem is the join, because neither of the
examples you gave have any code to limit the number of store records
returned.

You may need to include something like the following code fragment.  (I'm
using a 'distance' formula as a proxy for the latitude/longitude formula.)

where store_no = (select store_no from store order by
distance(store,customer) limit 1)
--
Mike Nolan

Re: [GENERAL] Re-Install data folder failure

[ray]

On Apr 20, 1:29 pm, ray  wrote:
> I have been having troubles getting 8.3 running on Windows XP.  My
> last couple of attempts have been to install 8.3 in a folder off the
> root rather than off of 'Program Files'.  Since I ‘uninstalled’ it, I
> have not been able to get a good installation.  I have tried many
> times.  I use Control Panels Add&Remove to remove 8.3 and then I go
> through the registry and remove any remaining entries tjat address
> 'postgres'.  When I start a new install, it always points to c:
> \program files\postg… which I change on the setup page to the new c:
> \pghome.  I have a leftover environmental variable pgdata which points
> to c:\pghome\8.3\data.  But this folder never gets built anymore.
> That is, there is not data folder.
>
> Any ideas on how I can get this built?
>

The method I used to resolve this was to:
1)  Use Control Panel to Remove the previous installation.  It left
behind the 'data' folder.
2)  Install with the msi file choosing not to install as a service.
3)  After installation was complete, the log stated:
 MSI (c) (38:10) [07:57:45:317]: Product: PostgreSQL 8.3 --
Installation completed successfully.
4)  I set the environment variable 'LD_LIBRARY_PATH' to 'C:\pgHome
\8.3\lib'.
5)  Set the environment variable PGDATA to 'C:\pgHome\8.3\data'.
6)  Renamed the old 'data' folder to 'data_old'.
7)  From the command prompt, I executed 'initdb' with no parameters
allowing PGDATA to drive the location, which responded with:
  Success. You can now start the database server using: . . .
8)  Executed:  'pg_ctl -l firstlogfile.txt start' which produced a
logfile in the bin folder (from where the command was issued).
9)  Executed 'createdb' to produce a new database under my logon name.
10)  From pgAdmin3, the new server was visible.
11)  From pgAdmin3, I set 3 more connections which established 3
servers.
12)  From pgAdmin3, I dropped the original server setup from the
command line.
13)  Each server has 2 databases: postgres and myname.

Now, if I can just find out if these are persistant and useful . . .

Ray


-- 
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] Part way there, how do I complete it

[ray]

On Apr 16, 1:35 pm, ray  wrote:
> On Apr 15, 5:50 pm, ray  wrote:> On Apr 15, 4:17 pm, 
> ray  wrote:
>
> I am trying to re-installed PostgreSQL.  I have chosen a different
> process.  I am installing it as a service.  After much time at the
> 'Starting Services' window, it says that I have insufficient
> previleges to start services.  I am an administrator on the machine
> and I start and stop services.
>
> Any ideas what may be going wrong and what I might do to get them
> working?
>
> Ray


The method I used to resolve this was to:
1)  Use Control Panel to Remove the previous installation.  It left
behind the 'data' folder.
2)  Install with the msi file choosing not to install as a service.
3)  After installation was complete, the log stated:
 MSI (c) (38:10) [07:57:45:317]: Product: PostgreSQL 8.3 --
Installation completed successfully.
4)  I set the environment variable 'LD_LIBRARY_PATH' to 'C:\pgHome
\8.3\lib'.
5)  Set the environment variable PGDATA to 'C:\pgHome\8.3\data'.
6)  Renamed the old 'data' folder to 'data_old'.
7)  From the command prompt, I executed 'initdb' with no parameters
allowing PGDATA to drive the location, which responded with:
  Success. You can now start the database server using: . . .
8)  Executed:  'pg_ctl -l firstlogfile.txt start' which produced a
logfile in the bin folder (from where the command was issued).
9)  Executed 'createdb' to produce a new database under my logon name.
10)  From pgAdmin3, the new server was visible.
11)  From pgAdmin3, I set 3 more connections which established 3
servers.
12)  From pgAdmin3, I dropped the original server setup from the
command line.
13)  Each server has 2 databases: postgres and myname.

Now, if I can just find out if these are persistant and useful . . .

Ray

-- 
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] Re-Install data folder failure

[ray]

On Apr 21, 9:55 am, plk.zu...@gmail.com (Filip Rembiałkowski) wrote:
> 2009/4/20 ray 
>
>
>
>
>
> > I have been having troubles getting 8.3 running on Windows XP.  My
> > last couple of attempts have been to install 8.3 in a folder off the
> > root rather than off of 'Program Files'.  Since I ‘uninstalled’ it, I
> > have not been able to get a good installation.  I have tried many
> > times.  I use Control Panels Add&Remove to remove 8.3 and then I go
> > through the registry and remove any remaining entries tjat address
> > 'postgres'.  When I start a new install, it always points to c:
> > \program files\postg… which I change on the setup page to the new c:
> > \pghome.  I have a leftover environmental variable pgdata which points
> > to c:\pghome\8.3\data.  But this folder never gets built anymore.
> > That is, there is not data folder.
>
> > Any ideas on how I can get this built?
>
> > I am installing this from an administrator account and I have a
> > postgres account that is a power user.  I have tried but can not
> > install from that account.  The PostgreSQL notes say that it is no
> > longer necessary to operate from a non-administrator account so I
> > tried to do things from the admin account and when it doesn’t work, I
> > try it from the postgres power user account.  So far, neither have
> > worked.  But I am guessing that is due to the no ‘data’ folder.
>
> >  Ray
>
> which installer are you using? (please share exact url where you downloaded
> it)? which version is it? did you remove postgres account after
> uninstalling? (the installer should create it IIRC)
>
> to build data directory you can use initdb tool.
>
> --
> Filip Rembiałkowski
> JID,mailto:filip.rembialkow...@gmail.comhttp://filip.rembialkowski.net/- Hide 
> quoted text -
>
> - Show quoted text -

Filip,

Thank you for showing me the holes in what I thought would be a
complete account.  I appreciate your detailed review.

I downloaded 8.3.7 from
http://wwwmaster.postgresql.org/download/mirrors-ftp/binary/v8.3.7/win32/postgresql-8.3.7-1.zip.
I downloaded a couple of times from the same and diffferent mirrors.
Sometimes the installation would state that there was an error and
that the installation file may have been bad.  I did not collect any
information as to which sites this occured from.

I removed the postgres account from the OS before the last
installation process.  A new postges OS account was not setup by the
installer.  After connecting to a new server, a postgres database was
shown along with another that used my OS logon name.

Ray


-- 
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] trouble with to_char('L')

[Mikko]

psql (PostgreSQL) 8.3.7

server_version 8.3.7
server_encoding UTF8
client_encoding win1252
lc_numeric Finnish, Finland
lc_monetary Finnish, Finland

testdb=# SELECT to_char(3.1415::numeric(5,2), '999D99L');

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

If connected to postgres database the query returns 3,14.

Mikko

-- 
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] Doubt about join queries

[jc_mich]

The problem is not the formula, the problem is the join. I've summarized it
because is a large formula and I've got right results

Here is the complete code code, stores and clients have a longitude and
latitude, and ponderator is a demand variable:

SELECT distances.client_id, min(distances.dist) FROM(
SELECT stores.id AS store_id, clients.id AS client_id,
 
6378*stores.ponderator*acos(cos((90-clients.lat)*(pi()/180))*cos((90-stores.lat)*(pi()/180))+sin((90-clients.lat)*(pi()/180))*sin((90-stores.lat)*(pi()/180))*cos((clients.lon-stores.lon)*(pi()/180)))
AS dist
 FROM stores, clients
 WHERE 1=1
 ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id;

I only want to assign to each store a client in function of the minimum
distance between them.

Thanks

Best regards
 

Filip Rembiałkowski-3 wrote:
> 
> 2009/4/20 jc_mich 
> 
>>
>> Hello
>>
>> I have a table with clients and other with stores, I want to calculate
>> minimum distances between stores and clients, the client name and its
>> closer
>> store.
>>
>> At this moment I can only get clients ids and minimum distances grouping
>> by
>> client id, but when I try to join their respective store id, postgres
>> requires me to add store id in group clause and it throws as many rows as
>> the product of number clients and stores. This result is wrong, I only
>> expect the minimum distance for every client.
>>
>> My code looks like this:
>>
>> SELECT distances.client_id, min(distances.distance) FROM(
>> SELECT stores.id AS store_id, clients.id AS client_id,
>> sqrt(power(store.x)+power(store.y)) AS distance
>> FROM stores, clients
>> WHERE 1=1
>> ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id;
>>
>> Also I've tried this:
>> SELECT clients.id, MIN(distances.distance)
>> FROM stores, clients LEFT JOIN(SELECT clients.id AS client_id, stores.id,
>> sqrt(power(stores.x)+power(stores.y)) AS distance
>> FROM stores, clients
>> WHERE 1=1) distances
>> ON distances.client_id = clients.id GROUP BY clients.id
>>
> 
> 
> It would be much easier if you show actual database schema.
> 
> It is not clear what is the meaning of stores.x and stores.y variables -
> what do they measure. If they are just coordinates, then where are client
> coordinates stored?
> 
> 
> 
> 
> -- 
> Filip Rembiałkowski
> JID,mailto:filip.rembialkow...@gmail.com
> http://filip.rembialkowski.net/
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Doubt-about-join-queries-tp23142980p23159394.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Custom types and pg_dump

[Roderick A. Anderson]

Tom Lane wrote:

"Roderick A. Anderson"  writes:
While and after doing a import ("\i file_dump") I notice one table had 
two attributes with a type of "unknown".


Is it a table, or a view?  This is hardly unheard-of for views,
consider


Table.  The columns are to hold a credit card number and its expiration 
date.



regression=# create view v1 as select 'unmarked string' as c1;
WARNING:  column "c1" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
CREATE VIEW
regression=# \d v1
   View "public.v1"
 Column |  Type   | Modifiers 
+-+---
 c1 | unknown | 
View definition:

 SELECT 'unmarked string' AS c1;

It's possible but much less common to have such things in plain tables.

So the question is; is it possible to have a custom type in a cluster 
that is accessible/usable in a database but isn't picked up by pg_dump?


This has got nothing to do with missing types.


OK.  I don't have access to the system the dump comes from.  I assumed 
:-) that there might be a type that did integrity checks or validated 
the data during inserts or updates and the pg_dump just did an "Unknown" 
for some reason.


It has been too long since I dealt with CC data but I seem to remember 
there were some checks for number of digits, beginning digits, 
expiration date, etc. (a quick check with Google confirms this.)


I was thinking they might have actually put that logic in the model 
instead of the controller.  Therefore it was a "missing" type 
declaration issue.


Sorry for being vague.  I know the application (which I'm pretty sure 
has been modified) and the company that the dump came from but don't 
feel comfortable sharing.  I'll go look at the original database stuff 
from the application to see if I can find anything that sheds some light 
on this.


Thanks.


\\||/
Rod
--

--
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] Custom types and pg_dump

[Tom Lane]

"Roderick A. Anderson"  writes:
> While and after doing a import ("\i file_dump") I notice one table had 
> two attributes with a type of "unknown".

Is it a table, or a view?  This is hardly unheard-of for views,
consider

regression=# create view v1 as select 'unmarked string' as c1;
WARNING:  column "c1" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
CREATE VIEW
regression=# \d v1
   View "public.v1"
 Column |  Type   | Modifiers 
+-+---
 c1 | unknown | 
View definition:
 SELECT 'unmarked string' AS c1;

It's possible but much less common to have such things in plain tables.

> So the question is; is it possible to have a custom type in a cluster 
> that is accessible/usable in a database but isn't picked up by pg_dump?

This has got nothing to do with missing types.

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] Custom types and pg_dump

[Roderick A. Anderson]

A quick request for a pointer to look or an answer.

Background:

I was asked to test the results of a pg_dump of a database.

While and after doing a import ("\i file_dump") I notice one table had 
two attributes with a type of "unknown".  I'm not sure of the Pg version 
it cam from but I was putting it into an 8.3.6 database from the PGDG 
yum repo on a CentOS 5.2 system.


So the question is; is it possible to have a custom type in a cluster 
that is accessible/usable in a database but isn't picked up by pg_dump?


Again a pointer of where to look would be fine.  (I've never seen this 
mentioned, not that is unusual for me, in the pg_dump documentation.)



TIA,
Rod
--

--
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] Doubt about join queries

[Filip Rembiałkowski]

2009/4/20 jc_mich 

>
> Hello
>
> I have a table with clients and other with stores, I want to calculate
> minimum distances between stores and clients, the client name and its
> closer
> store.
>
> At this moment I can only get clients ids and minimum distances grouping by
> client id, but when I try to join their respective store id, postgres
> requires me to add store id in group clause and it throws as many rows as
> the product of number clients and stores. This result is wrong, I only
> expect the minimum distance for every client.
>
> My code looks like this:
>
> SELECT distances.client_id, min(distances.distance) FROM(
> SELECT stores.id AS store_id, clients.id AS client_id,
> sqrt(power(store.x)+power(store.y)) AS distance
> FROM stores, clients
> WHERE 1=1
> ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id;
>
> Also I've tried this:
> SELECT clients.id, MIN(distances.distance)
> FROM stores, clients LEFT JOIN(SELECT clients.id AS client_id, stores.id,
> sqrt(power(stores.x)+power(stores.y)) AS distance
> FROM stores, clients
> WHERE 1=1) distances
> ON distances.client_id = clients.id GROUP BY clients.id
>


It would be much easier if you show actual database schema.

It is not clear what is the meaning of stores.x and stores.y variables -
what do they measure. If they are just coordinates, then where are client
coordinates stored?




-- 
Filip Rembiałkowski
JID,mailto:filip.rembialkow...@gmail.com
http://filip.rembialkowski.net/

Re: [GENERAL] Re-Install data folder failure

[Filip Rembiałkowski]

2009/4/20 ray 

> I have been having troubles getting 8.3 running on Windows XP.  My
> last couple of attempts have been to install 8.3 in a folder off the
> root rather than off of 'Program Files'.  Since I ‘uninstalled’ it, I
> have not been able to get a good installation.  I have tried many
> times.  I use Control Panels Add&Remove to remove 8.3 and then I go
> through the registry and remove any remaining entries tjat address
> 'postgres'.  When I start a new install, it always points to c:
> \program files\postg… which I change on the setup page to the new c:
> \pghome.  I have a leftover environmental variable pgdata which points
> to c:\pghome\8.3\data.  But this folder never gets built anymore.
> That is, there is not data folder.
>
> Any ideas on how I can get this built?
>
> I am installing this from an administrator account and I have a
> postgres account that is a power user.  I have tried but can not
> install from that account.  The PostgreSQL notes say that it is no
> longer necessary to operate from a non-administrator account so I
> tried to do things from the admin account and when it doesn’t work, I
> try it from the postgres power user account.  So far, neither have
> worked.  But I am guessing that is due to the no ‘data’ folder.
>
>  Ray
>
>

which installer are you using? (please share exact url where you downloaded
it)? which version is it? did you remove postgres account after
uninstalling? (the installer should create it IIRC)

to build data directory you can use initdb tool.




-- 
Filip Rembiałkowski
JID,mailto:filip.rembialkow...@gmail.com
http://filip.rembialkowski.net/

[GENERAL] truncate capability in 8.1

[Larry Kincaid]

Hi-
Using postgresql 8.1 on RHEL 5.3, would like to be able to update (e.g.,
overwrite) a large object in place.  Ultimately, I'd like to delete the
original large object, add a new one, but keep the original Oid.  I've
looked at using lo_open, lo_read, lo_write, but if the new file is smaller
than the original file, I need to truncate the original.  Unfortunately,
lo_truncate isn't implemented until 8.3 so I was hoping to get some ideas
from other folks on the best way to do this using capability available in
8.1.  Are there any "gotchas" directly manipulating the pglargeobject table?


- larry

Re: [GENERAL] This is my first template

[Alvaro Herrera]

Geoff Caplan wrote:

> where $2 is a well-formed ISO date.
>
> Query works as expected when there are records in the result set. When 
> the result set is empty, PG throws an error:
>
> date/time field value out of range: "2011-4-31"

Is April 31st really a well-formed ISO date?  I wonder what jhey smoke
there in ISO meetings.

My guess is that you're doing date arithmetic wrong somewhere.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

[Diego Schulz]

On Mon, Apr 20, 2009 at 9:25 AM, paulo matadr  wrote:
> I get  this error when make a select below
>
> "SELECT * FROM batch.funcionalidade_iniciada  where proi_id = x"
>
> ERROR: missing chunk number 0 for toast value 458755
> SQL state: XX000


Looks like your database repository is corrupted and/or your disk is failing.

I'd suggest you to

1) look at the logs for error messages
2) fix hardware problems (if any)
3) if you don't have UPS, buy one
4) restore your database from last backup



regards,

diego

-- 
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] DISTINCT ON without ORDER BY

[Jasen Betts]

On 2009-04-19, Martijn van Oosterhout  wrote:
> Hi,
>
> I was going through the queries of an SQL application and came across
> queries like:
>
> SELECT * FROM foo
> WHERE id in (SELECT max(id) FROM foo GROUP BY bar);
>
> I thought, here's a case where this could be better written using
> DISTINCT ON, since then you avoid the self-join:
>
> SELECT DISTINCT ON (bar) * FROM
> ORDER BY bar, id DESC;
>
> However, this was slower because the original query could use a hash
> aggregate whereas the new query needed to do a sort. The way DISTINCT
> ON is defined it requires an ORDER BY whereas semantically the ordering
> on the first attribute is just a by product of the old implementation.
>
> Is there a way to acheive the above result without a sort and without a
> self-join?

anyway you could possibly write an agregate function that returns a
copy of the row with the highest id?



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

Re: [GENERAL] This is my first template

[milos . babic]

There are 30 days in April.

On Apr 21, 2009 2:34pm, Merlin Moncure  wrote:

On Tue, Apr 21, 2009 at 7:02 AM, Geoff Caplan ge...@uviva.com> wrote:



> Hi folks



>


> Looks like there's something I'm not understanding about date/time  
queries



> on a date field.



>



> I have a param query that includes the statement:



>



> AND active_from_date
>



> where $2 is a well-formed ISO date.



>


> Query works as expected when there are records in the result set. When  
the



> result set is empty, PG throws an error:



>



> date/time field value out of range: "2011-4-31"





"2011-4-31" is not a well formed ISO date.





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] This is my first template

[Merlin Moncure]

On Tue, Apr 21, 2009 at 7:02 AM, Geoff Caplan  wrote:
> Hi folks
>
> Looks like there's something I'm not understanding about date/time queries
> on a date field.
>
> I have a param query that includes the statement:
>
> AND active_from_date <= $2
>
> where $2 is a well-formed ISO date.
>
> Query works as expected when there are records in the result set. When the
> result set is empty, PG throws an error:
>
> date/time field value out of range: "2011-4-31"

"2011-4-31" is not a well formed ISO date.

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] This is my first template

[Sam Mason]

On Tue, Apr 21, 2009 at 12:02:02PM +0100, Geoff Caplan wrote:
> Looks like there's something I'm not understanding about 
> date/time queries on a date field.

[...]

> date/time field value out of range: "2011-4-31"

As far as I'm aware; April only has 30 days.  You're asking PG for an
invalid date and this is its way of telling you 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

[GENERAL] This is my first template

[Geoff Caplan]

Hi folks

Looks like there's something I'm not understanding about 
date/time queries on a date field.


I have a param query that includes the statement:

AND active_from_date <= $2

where $2 is a well-formed ISO date.

Query works as expected when there are records in the result 
set. When the result set is empty, PG throws an error:


date/time field value out of range: "2011-4-31"

I'm using the default datestyle setting, which should work 
here, I think? Experimenting with other settings hasn't helped.


This query runs hundreds of times a day and I don't want the 
error clogging up my logs. But I don't want to suppress the 
error as the query is business-critical and I need to know 
if anything goes wrong.


I'd welcome suggestions as to how I can get this query to 
run without throwing a date/time error when the result set 
is empty.


Thanks...

--
Geoff Caplan
Uviva Ltd

begin:vcard
fn:Geoff Caplan
n:Caplan;Geoff
org:Uviva Ltd
adr:Dartington Hall;;Foxhole;Totnes;Devon;TQ9 6EB;UK
email;internet:ge...@uviva.com
tel;work:01803 840 840
x-mozilla-html:FALSE
url:http://www.uviva.com
version:2.1
end:vcard


-- 
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] trouble with to_char('L')

[Albe Laurenz]

Mikko wrote:
> my database has UTF8 encoding and Finnish locale, the client_encoding
> and the console is set to WIN1252. I created a table with a single
> NUMERIC(5,2) column and inserted a few values. Running a query 'SELECT
> to_char(money, '999D99L') FROM table' through psql gives the following
> error message:
> 
> ERROR:  invalid byte sequence for encoding "UTF8": 0x80
> HINT:  This error can also happen if the byte sequence does not match
> the encoding expected by the server, which is controlled by
> "client_encoding".
> 
> The graphical Query tool returns a set of empty rows. The query works
> ok without the 'L'.

That is strange.

What is your psql version?

What is the output of the following commands:

SHOW server_version;
SHOW server_encoding;
SHOW client_encoding;
SHOW lc_numeric;
SHOW lc_monetary;
SELECT to_char(3.1415::numeric(5,2), '999D99L');

Yours,
Laurenz Albe

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