Re: [GENERAL] Playing with PostgreSQL and Access VBA

[Tony Caduto]

In a real application that uses a client/server type database you should 
NEVER construct queries that return a million rows.
You have to keep in mind that each record has to be sent accross the 
wire via TCP/IP and it is going to take a lot of time to bring back 1 
million rows and I am sure your network admin would notice increased 
network traffic.

In a real application you would use something like the Delphi Tdataset 
based components which will only bring back the records that can be seen 
on the screen, then when you scroll, it brings back more.

you can manually create something like this using Postgresql handy LIMIT 
and OFFSET sql keywords in your query to build a paging system.

Access is actually a very poor choice for client/server application 
development, like I said before, invest some time learning Delphi, it's 
not difficult at all and you won't have these problems.
You can get a copy of Delphi 7 personal edition and you can use the zeos 
components with it.  http://www.zeoslib.net

Tony
.
 
When I had it loop and add 1000 rows, it was ok...   When I told it to 
add a million rows then after 250,000 the Access application hung 
(but not the whole machine, and not the


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Finding cardinality of an index

[[EMAIL PROTECTED]]

I know two ways:

1) Use DbVisualizer, very handy, but a bit slow.  For a give table go to
the indexes tab and it's show you the cardinality

2) Use this sql

SELECT relname, relkind, reltuples as cardinality, relpages 
FROM pg_class 
WHERE relname LIKE 'mytablename%';

David
- Original message -
From: "Bill Chandler" <[EMAIL PROTECTED]>
To: "pgsql-general" 
Date: Thu, 21 Apr 2005 08:11:09 -0700 (PDT)
Subject: [GENERAL] Finding cardinality of an index

All,

Is there a way to determine cardinality (size) of an
index?  In general how to you query the 'attributes'
(for lack of a better word) of an index.

thanks,

Bill

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Encoding problem

[Fritz Bayer]

[EMAIL PROTECTED] (Andreas Seltenreich) wrote in message news:<[EMAIL 
PROTECTED]>...
> Fritz Bayer schrob:
> 
> > The problem is that alls the "ü" characters get displayed as "".
> >
> > Why is that so?
> 
> This could happen when your locale isn't properly set up. If, for
> example, LC_CTYPE is set to C, your pager thinks this character isn't
> printable and tries to do something smart with it. You can display the
> current setup by running "locale".
> 
> Does it work when you export LC_CTYPE=de_DE before running psql?
> 

I noticed that no locales have been generated and that the variable
was set to C. I created the locales for LATIN1 and ISO8859-15 and set
the variable to the value you suggested.

I tried it and now I get ü instead. 

To give you more clues I checked the encoding which seems to be
UNICODE.

 show client_encoding;
NOTICE:  Current client encoding is 'UNICODE'
SHOW VARIABLE

So I tried to set it to LATIN1 using 

\encoding latin1

But now I get

select * from user_requests;
ERROR:  Could not convert UTF-8 to ISO8859-1



> regards,
> Andreas
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq

---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] UltraSPARC versus AMD

[Richard_D_Levine]

I just got done comparing SPECMarks (on spec.org) between Sun's AMD entry
level servers versus similarly configured UltraSPARCs versus desktop AMD
based machines.  Sun's AMD machines are twice as fast as their UItraSPARCs,
for approximately the same price.  What a hoot.

Rick


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Table modifications with dependent views - best

[Tom Lane]

Bruno Wolff III <[EMAIL PROTECTED]> writes:
> I wasn't able to find where this is spelled out in the documentation,
> but I believe all DDL commands except DROP DATABASE can be rolled back now.

I don't think there's any all-in-one-place statement about it, but
anything that doesn't explicitly object to being put inside a
transaction block can be rolled back.  Grepping for
PreventTransactionChain, I see that the current suspects are

CLUSTER (only the multi-table variants)
CREATE DATABASE
DROP DATABASE
REINDEX DATABASE
CREATE TABLESPACE
DROP TABLESPACE
VACUUM

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Migrating MySQL app to postgres?

[David Roussel]

The only linux app I can find that does the job is SugarCRM.
However, they use mysql as their backend. Might it be relatively
easy for me to change the code so it will work with
postgres? What's involved?
Rich, what's wrong with the app as it is?  Why change anything?
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] UltraSPARC versus AMD

[Scott Marlowe]

On Fri, 2005-04-22 at 09:48, [EMAIL PROTECTED] wrote:
> I just got done comparing SPECMarks (on spec.org) between Sun's AMD entry
> level servers versus similarly configured UltraSPARCs versus desktop AMD
> based machines.  Sun's AMD machines are twice as fast as their UItraSPARCs,
> for approximately the same price.  What a hoot.

Wow.  I'd certainly like to see the numbers and such from your
benchmarks.  I have to say I'm not surprised, the 64 bit AMD chips are
quite impressive pieces of hardware.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] ShmemAlloc: out of memory

[Tom Lane]

JM <[EMAIL PROTECTED]> writes:
>   i need help on this.. i'm experiencing this problem too often and i 
> need a 
> permanent solution.

> WARNING:  ShmemAlloc: out of memory
> ERROR:  FreeSpaceMap hashtable out of memory

You probably need to increase max_locks_per_transaction some more.
It would also be a good idea to keep an eye on pg_locks and try to
figure out what's grabbing so many locks ...

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] Playing with PostgreSQL and Access VBA

[Typing80wpm]

It was something of a struggle to get Access to talk to PostgresSQL using 
ODBC.
 
It was even more of a struggle, since I know nothing about Access or VBA, 
to create a "Form" in Access, and a Button on the Form, to execute the VBA code, 
below.
 
If it were not for the Internet, and Google, I could never have scrounged 
up the proper syntax of these VBA commands.  What I wanted to do is to 
write some sort of batch program to just add a million rows to a table, and 
benchmark it. 
 
When I had it loop and add 1000 rows, it was ok...   When I told 
it to add a million rows then after 250,000 the Access application hung (but 
not the whole machine, and not the PosgreSQL).   I suspect that it 
hung because of something to do with the Windows screen saver.  The other 
thing I noticed, as it was adding all those records, is that Access updates the 
bottom of the form with a record count.  That sort of screen I/O in Windows 
really slows things down, and when you turn it off, IF you can turn it off, then 
file operations speed up enormously.  I think I am going to look for a book 
on VBA and access.
 
I was very impressed by what one member said regarding Delphi, but, when I 
looked at pricing,... well I would have to lie through my teeth to get the cheap 
academic version, and the personal version sounds like it doesnt have the file 
access abilities for PostgreSQL. And the prices for enterprise versions at 
programmersparadise.com  like $4000, sort of puts me off.  
 
So here is my weird Access VBA that tried to add a million rows.
 
I really just want to learn some new skills.  Thats why I am doing 
these things.
 
=start of code
 
Option Compare Database
 
Private Sub Command2_Click()'sqlcmd = "insert into public_customers 
(company, cust_num) values ('test', 11)"'sqlcmd = "insert into 
public_customers (company, cust_num) values ('test'," + Str$(countit) + 
")"MsgBox sqlcmd'CurrentDb.Execute sqlcmd, 
dbFailOnError countit = 1401000 ' 
doitagain: countit = countit + 1sqlcmd = "insert into 
public_customers (company, cust_num) values ('test'," + Str$(countit) + 
")"MsgBox sqlcmd 'MsgBox "hello world" 
 
'Application.DisplayAlerts = FalseDoCmd.SetWarnings FalseDim rs As 
New ADODB.Recordset'rs.Open (sqlcmd), CurrentProject.Connectionrs.Open 
("SELECT * FROM public_customers"), 
CurrentProject.Connectionrs.MoveFirstBaseSalary = 
rs.Fields("company")MsgBox BaseSalarydoitagain: countit = 
countit + 1sqlcmd = "insert into public_customers (company, cust_num) values 
('test'," + Str$(countit) + ")"
 
DoCmd.RunSQL sqlcmdIf countit < 1401100 Then GoTo 
doitagainrs.CloseSet rs = Nothing
 
End Sub==end of code

Re: [GENERAL] plperl function fails to "fire" Slony trigger

[Jan Wieck]

On 4/22/2005 2:08 PM, Tom Lane wrote:
Sven Willenberger <[EMAIL PROTECTED]> writes:
We have a replication set up between 2 servers using Slony; both are
runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are
made to a replicated table, the replication does not occur; apparently
this is due to spi_exec somehow not allowing/causing the slony trigger
function to fire.
Yuck :-(.  The only idea that comes to mind is that 8.0 changed the
timing of trigger firing --- the triggers are probably firing while your
function still has control, whereas in earlier releases they'd only fire
after it returns.  Could this be breaking some assumption Slony makes
about the order of operations?
			regards, tom lane
Slony triggers are AFTER ROW triggers. All they do is one SPI_execp() to 
insert the log row. The only way that could possibly be suppressed is by 
bypassing the executor and doing direct heap_ access.

So how does plperl manage that?
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] Migrating MySQL app to postgres?

[Rich Shepard]

On Fri, 22 Apr 2005, Scott Marlowe wrote:
I like Python, but it's felt half-finished for quite some time now. So, I
use PHP and / or Java (just learning it really) for most stuff.
PHP, for all it's warts and blemishes, has been a stable language (not
the definition, the VM / implementation) with usable connect libs for
quite some time.
Ruby on Rails has promise, but also feels like it's really just getting
started.
  I don't make my living writing software so I'm neither a language bigot 
nor
a language collector. If it works, it's good enough. :-)
Thanks,
Rich
--
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
   Voice: 503-667-4517   Fax: 503-667-8863
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Migrating MySQL app to postgres?

[Joshua D. Drake]

  Guess now's the time. I'm trying to find a copy of pygresql to use, but
it's not easy (www.pygresql.org leads to druid.net and there's nothing 
on the
page about pygresql). I'm learning python to use on a major project here 
Use pyscopg instead of pygresql.
Sincerely,
Joshua D. Drake


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Migrating MySQL app to postgres?

[Rich Shepard]

On Fri, 22 Apr 2005, Scott Marlowe wrote:
Unfortunately, there is no use of an abstraction layer in SugarCRM, and the
few comments I found in their forum about alternate databases indicated a
current focus on feature enhancements and sales / services, not supporting
alternate databases.
Scott,
  Thanks for the insight. Up until the mid-1990s (when I migrated from DOS to
linux), I used my own CRM written in Paradox. I tuned it over time to do
everything I wanted, and it was open and used every day. I just never did
port that to postgres.
  Guess now's the time. I'm trying to find a copy of pygresql to use, but
it's not easy (www.pygresql.org leads to druid.net and there's nothing on the
page about pygresql). I'm learning python to use on a major project here (but
the core libraries will remain in C) so I'd like to use that for the UI on
this project, too. I'm still not sure about the reports, but one step at a
time. That is, will the pygresql display nicely formatted reports based on
sql queries to the backend? Need to find out.
Again, thanks,
Rich
--
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
   Voice: 503-667-4517   Fax: 503-667-8863
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] UltraSPARC versus AMD

[Joshua D. Drake]

[EMAIL PROTECTED] wrote:
I just got done comparing SPECMarks (on spec.org) between Sun's AMD entry
level servers versus similarly configured UltraSPARCs versus desktop AMD
based machines.  Sun's AMD machines are twice as fast as their UItraSPARCs,
for approximately the same price.  What a hoot.
Not that surprising. UltraSparcs haven't been "fast" in a long time. 
They just scale really well.

Sincerely,
Joshua D. Drake

Rick
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Migrating MySQL app to postgres?

[Joshua D. Drake]

Rich Shepard wrote:
  I need (and want) contact managment software for marketing/sales tracking
in my business. The only linux app I can find that does the job is 
SugarCRM.
However, they use mysql as their backend.

  Might it be relatively easy for me to change the code so it will work 
with
postgres? What's involved?
Depends on how it was written. If they used Pear it shouldn't be "too" 
difficult. It will take work no matter what.

Sincerely,
Joshua D. Drake

TIA,
Rich

--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Migrating MySQL app to postgres?

[Scott Marlowe]

On Fri, 2005-04-22 at 11:51, Joshua D. Drake wrote:
> Rich Shepard wrote:
> >   I need (and want) contact managment software for marketing/sales tracking
> > in my business. The only linux app I can find that does the job is 
> > SugarCRM.
> > However, they use mysql as their backend.
> > 
> >   Might it be relatively easy for me to change the code so it will work 
> > with
> > postgres? What's involved?
> 
> Depends on how it was written. If they used Pear it shouldn't be "too" 
> difficult. It will take work no matter what.

Unfortunately, there is no use of an abstraction layer in SugarCRM, and
the few comments I found in their forum about alternate databases
indicated a current focus on feature enhancements and sales / services,
not supporting alternate databases.  

I do get the feeling it's in the plans, but won't likely happen this go
around, with 3.0 beta.

I would say that the problem isn't that it doesn't support PostgreSQL,
but that it ONLY supports MySQL, which means it may be hard to port to
other databases due to extensive uses of "mysqlisms" which are common in
large mysql only databases, and the longer they wait to abstract out the
db access, the harder the conversion becomes.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Migrating MySQL app to postgres?

[Rich Shepard]

  I need (and want) contact managment software for marketing/sales tracking
in my business. The only linux app I can find that does the job is SugarCRM.
However, they use mysql as their backend.
  Might it be relatively easy for me to change the code so it will work with
postgres? What's involved?
TIA,
Rich
--
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
   Voice: 503-667-4517   Fax: 503-667-8863
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] libpq Unicode support?

[Ale Raza]

Wondering if libpq lib support unicode? 

Ale.


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Postgresql Windows ODBC

[Dann Corbit]

 

 









From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Friday, April 22, 2005 5:07
AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Postgresql
Windows ODBC



 



Tonight I shall install the Postgresql
ODBC driver which I downloaded, and experiment.





 





Is anyone using ODBC? 

>> 

We use PostgreSQL for lots of
things.  For instance, there is a tool we wrote that synchronizes database
systems from any platform and database type into any alternative platform and
database type.  So (for instance) you can take VSAM files and create an Oracle
data warehouse from it.  We use PostgreSQL for intermediate steps and for
doing customized metadata storage.  It performs admirably.  We are
using OLEDB as the interface with VB.NET.  We are using a driver we wrote
ourselves, but I imagine that the sourceforge one would serve well for most
purposes. (PostgreSQL can also be the source or target database and some
customers are using it for that).

<< 

 

 I was looking at Realbasic standard
edition (not professional), and thinking of downloading the demo and trying it
with the ODBC.  Anyone here use Realbasic at all?





 





In Windows, what would you say is the
easiest language to use which can talk to Postgresql? 

>> 

Use the language that you are most
familiar with and that your customers will be able to maintain.

<< 

 

Just curious    e.g. I
have Liberty Basic which is very easy, but does not easily support ODBC...





 





Is anyone working with some form of
Python on Windows.  I downloaded Python, but it seems to run only in some
DOS window. I mentioned this on a Dreamcard/Revolution board, and they said
"Oh, you want PYTHON  CARD."





 





Anyway, I would be interested to learn of
an easy language which can access Postgresql, together with some decent
examples or tutorials to get me going.  I have a feeling that Realbasic
will do the trick.

>> 

I am guessing that there is some easy way
to make it work.  Surely, there will be some kind of standardized
interface like ODBC, OLEDB or a .NET provider.  If not, there may be a
product very similar to RealBasic that you can use.  A web search might be
helpful. Try this:

http://www.google.com/search?hl=en&q=realbasic+AND+%28odbc+OR+oledb+OR+%28%22.net+provider%22%29%29&btnG=Google+Search

<< 

Re: [GENERAL] libpq Unicode support?

[Bruce Momjian]

Ale Raza wrote:
> Tom, Thanks for reply. I want to pass UTF-16 data. Is there any special
> build of libpq for UTF-16. I did not build libpq locally. 

We do not support UTF-16 at this time.  Hopefully we will in 8.1.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] libpq Unicode support?

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian  writes:
> > We do not support UTF-16 at this time.  Hopefully we will in 8.1.
> 
> Oh?  Who's working on it, or even interested?  Was there discussion
> of adding it to TODO?
> 
> I think it would be an extremely nontrivial change, which is why
> I am not pleased with making casual promises that it will appear
> soon (or indeed at all).

TODO has:

o Add support for Unicode

  To fix this, the data needs to be converted to/from UTF16/UTF8
  so the Win32 wcscoll() can be used, and perhaps other functions
  like towupper().  However, UTF8 already works with normal
  locales but provides no ordering or character set classes.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] PHP and Postgresql on Windows

[typing80wpm]

I searched on goolge and found my answer, I think.
 
From what I see (below), all I need to do is a WAMP install from one of the CDs in the back of the PHP books I bought, and then un-comment the extension=php_pgsql.dll
 
 
 
http://www.sitepoint.com/article/use-postgresql-php-windows/3
 
Connect to PostgreSQL from PHP
Open your php.ini file (most likely located in c:\winnt), find the section called Dynamic Extensions and un-comment the line that contains "extension=php_pgsql.dll". Save and close php.ini and restart Apache. If the Web server complains and says it's unable to load the dynamic library, you probably have entered the wrong path for extension_dir in php.ini (section Paths and Directories). Another possible -â though unlikely -- cause is that the file php_pgsql.dll isn't present in the extensions directory.
 
Now you should be able to run the following little PHP script:
 
$conn = pg_connect("host=localhost     port=5432     dbname=template1     user=postgres     password="); $sql = "SELECT current_date AS today;"; $result = pg_query($conn, $sql); $row = pg_fetch_object($result, 0); echo "Today is: " .$row->today; ?>
 
 
The script isn't too exciting, but at least it shows you how to connect to the database and execute a query.
 

Re: [GENERAL] libpq Unicode support?

[Tom Lane]

Bruce Momjian  writes:
> Tom Lane wrote:
>> Oh?  Who's working on it, or even interested?  Was there discussion
>> of adding it to TODO?

> TODO has:

> o Add support for Unicode

>   To fix this, the data needs to be converted to/from UTF16/UTF8
>   so the Win32 wcscoll() can be used, and perhaps other functions
>   like towupper().  However, UTF8 already works with normal
>   locales but provides no ordering or character set classes.

That's completely unrelated --- it's talking about making correct use of
Windows' locale support in one small bit inside the server.

To make libpq UTF-16 capable, we'd have to change its API for all
strings; either make the strings counted rather than null-terminated,
or make the string elements wchar instead of char.  After that we'd
have to hack the FE/BE protocol too (or more likely, require libpq
to translate UTF-16 to UTF-8 before sending to the server).  I don't
foresee anyone doing any of this, at least not in the near term.

Putting a UTF-16 to UTF-8 translation in front of libpq seems a lot
more practical.

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Migrating MySQL app to postgres?

[Scott Marlowe]

On Fri, 2005-04-22 at 12:27, Rich Shepard wrote:
> On Fri, 22 Apr 2005, Scott Marlowe wrote:
> 
> > Unfortunately, there is no use of an abstraction layer in SugarCRM, and the
> > few comments I found in their forum about alternate databases indicated a
> > current focus on feature enhancements and sales / services, not supporting
> > alternate databases.
> 
> Scott,
> 
>Thanks for the insight. Up until the mid-1990s (when I migrated from DOS to
> linux), I used my own CRM written in Paradox. I tuned it over time to do
> everything I wanted, and it was open and used every day. I just never did
> port that to postgres.
> 
>Guess now's the time. I'm trying to find a copy of pygresql to use, but
> it's not easy (www.pygresql.org leads to druid.net and there's nothing on the
> page about pygresql). I'm learning python to use on a major project here (but
> the core libraries will remain in C) so I'd like to use that for the UI on
> this project, too. I'm still not sure about the reports, but one step at a
> time. That is, will the pygresql display nicely formatted reports based on
> sql queries to the backend? Need to find out.

Good luck with it.  Have you considered doing the project in another
language?  I like Python, but it's felt half-finished for quite some
time now.  So, I use PHP and / or Java (just learning it really) for
most stuff.  

Of course, Java's felt half finished most of its life to me as well, but
it's finally feeling mature.

PHP, for all it's warts and blemishes, has been a stable language (not
the definition, the VM / implementation) with usable connect libs for
quite some time.  IF you're an old C programmer, it's quite easy to
write readable / maintainable code.  If you're a beginner, it's a pretty
good way to learn REALLY bad habits...

Ruby on Rails has promise, but also feels like it's really just getting
started.

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] [SQL] How to install Postgres that supports 64-bit

[Scott Marlowe]

On Fri, 2005-04-22 at 00:30, Dinesh Pandey wrote:
> How to install Postgres 8.0.1 that supports 64-bit integer/date-time.
> 
>  
> 
> # ./configure --prefix=/usr/local/pgsql
> --with-tclconfig=/usr/local/lib --with-tcl 
> 
> checking build system type... sparc-sun-solaris2.8
> 
> checking host system type... sparc-sun-solaris2.8
> 
> checking which template to use... solaris
> 
> checking whether to build with 64-bit integer date/time support... no
> 
> checking whether NLS is wanted... no
> 
> checking for default port number... 5432
> 
> checking for gcc... gcc
> 
>  
> 
> while installation I am getting this message:â checking whether to
> build with 64-bit integer date/time support... noâ
> 

 --enable-integer-datetimes  enable 64-bit integer date/time support


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Migrating MySQL app to postgres?

[Rich Shepard]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Fri, 22 Apr 2005, David Roussel wrote:
Rich, what's wrong with the app as it is?  Why change anything?
David,
  Because I don't have MySQL installed, have no need for it, and have used
postgres for about eight years so I know it well.
Rich
- -- 
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
   Voice: 503-667-4517   Fax: 503-667-8863
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.7 (GNU/Linux)

iD8DBQFCaXtzugEIjC001L0RAt03AJ9nU0Vp2Ite2Zku4pm15jruZn2AnACfeX0u
OKZYu2CfLdLqLElRHfsD0Nw=
=1ueS
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Migrating MySQL app to postgres?

[Rich Shepard]

On Fri, 22 Apr 2005, Joshua D. Drake wrote:
Depends on how it was written. If they used Pear it shouldn't be "too"
difficult. It will take work no matter what.
  Thanks, Josh. I should have mentioned that it's written using php as the
middleware.
Rich
--
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
   Voice: 503-667-4517   Fax: 503-667-8863
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] CURRENT_TIMESTAMP and actual time

[Jim C. Nasby]

Take a look at the tod() function at the very end of
http://svn.rrs.decibel.org/viewcvs.cgi/trunk/rrs_functions.sql?rev=61&view=markup

On Wed, Apr 20, 2005 at 04:17:05PM -0500, Christopher J. Bottaro wrote:
> Hi,
> I understand that CURRENT_TIMESTAMP marks the beginning of the current
> transaction.  I want it to be the _actual_ time.  How do I do this? 
> timeofday() returns a string, how do I convert that into a TIMESTAMP?
> 
> Is it possible to create a column with DEFAULT value evaluated to the actual
> current time (i.e. not the CURRENT_TIMESTAMP which is the beginning of the
> current transaction).
> 
> What I do now to get it to work is do a COMMIT right before the insert, that
> way CURRENT_TIMESTAMP is (pretty close to) the actual time.  ...but that is
> so crappy and doesn't work if I actually need to use transactional features
> (i.e. rollback).
> 
> Thanks for the help,
> -- C
> 
> 
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
> 

-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] libpq Unicode support?

[Tom Lane]

Ale Raza <[EMAIL PROTECTED]> writes:
> Tom, Thanks for reply. I want to pass UTF-16 data. Is there any special
> build of libpq for UTF-16. I did not build libpq locally. 

Nope, you're out of luck on UTF-16.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Finding cardinality of an index

[Jim C. Nasby]

http://www.postgresql.org/docs/8.0/static/catalog-pg-class.html

On Thu, Apr 21, 2005 at 08:11:09AM -0700, Bill Chandler wrote:
> All,
> 
> Is there a way to determine cardinality (size) of an
> index?  In general how to you query the 'attributes'
> (for lack of a better word) of an index.
> 
> thanks,
> 
> Bill
> 
> __
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
> 
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
> 

-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] UltraSPARC versus AMD

[Richard_D_Levine]

[EMAIL PROTECTED] wrote on 04/22/2005 10:08:46 AM:

> On Fri, 2005-04-22 at 09:48, [EMAIL PROTECTED] wrote:
> > I just got done comparing SPECMarks (on spec.org) between Sun's AMD
entry
> > level servers versus similarly configured UltraSPARCs versus desktop
AMD
> > based machines.  Sun's AMD machines are twice as fast as their
UItraSPARCs,
> > for approximately the same price.  What a hoot.
>
> Wow.  I'd certainly like to see the numbers and such from your
> benchmarks.  I have to say I'm not surprised, the 64 bit AMD chips are
> quite impressive pieces of hardware.

The benchmarks aren't mine, they're standard performance evaluation (SPEC)
defined by spec.org

http://www.spec.org

Click on the desired benchmark (I was referring to the CPU benchmarks) and
click on "published results".

The manufacturers (Sun, Dell, HP, etc.) buy the benchmarks, configure their
best compiler for speed, run them, and submit the results.  My point is
that Sun ran the benchmarks, under very strict rules set forth by SPEC.

Rick

>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if
your
>   joining column's datatypes do not match


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] libpq Unicode support?

[Karsten Hilbert]

Tom Lane wrote:
> To make libpq UTF-16 capable, we'd have to change its API for all
> strings; either make the strings counted rather than null-terminated,
> or make the string elements wchar instead of char.  After that we'd
> have to hack the FE/BE protocol too (or more likely, require libpq
> to translate UTF-16 to UTF-8 before sending to the server).  I don't
> foresee anyone doing any of this, at least not in the near term.
Is there any *real* loss of functionality in not supporting
UTF-16 ? If so *should* it be supported in, say, 9.0 ? If not,
should there be a FAQ item saying why not ?

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

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Migrating MySQL app to postgres?

[Joshua D. Drake]

Guy Fraser wrote:
One of my associates swears SMARTY is the best thing since sliced 
bread. I think it uses PHP an PEAR, but is more abstract. I looked 
at it and it did some cool things with only a few lines of code.
Smarty is nice. However unless you run an optimizer it is slower than
snot. PEAR doesn't help the situation either.
Sincerely,
Joshua D. Drake
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Migrating MySQL app to postgres?

[Guy Fraser]

One of my associates swears SMARTY is the best thing since sliced 
bread. I think it uses PHP an PEAR, but is more abstract. I looked 
at it and it did some cool things with only a few lines of code.

Being an old dog, I have built many different "libraries" in C 
and PHP and prefer to use them. The last project I worked on, 
we made it so it could be used with MySQL, but was primarily 
designed to work with PostgreSQL. I don't like MySQL, and I 
was the primary developer.

Give smarty a look.

On Fri, 2005-22-04 at 10:27 -0700, Rich Shepard wrote:
> On Fri, 22 Apr 2005, Scott Marlowe wrote:
> 
> > Unfortunately, there is no use of an abstraction layer in SugarCRM, and the
> > few comments I found in their forum about alternate databases indicated a
> > current focus on feature enhancements and sales / services, not supporting
> > alternate databases.
> 
> Scott,
> 
>Thanks for the insight. Up until the mid-1990s (when I migrated from DOS to
> linux), I used my own CRM written in Paradox. I tuned it over time to do
> everything I wanted, and it was open and used every day. I just never did
> port that to postgres.
> 
>Guess now's the time. I'm trying to find a copy of pygresql to use, but
> it's not easy (www.pygresql.org leads to druid.net and there's nothing on the
> page about pygresql). I'm learning python to use on a major project here (but
> the core libraries will remain in C) so I'd like to use that for the UI on
> this project, too. I'm still not sure about the reports, but one step at a
> time. That is, will the pygresql display nicely formatted reports based on
> sql queries to the backend? Need to find out.
> 
> Again, thanks,
> 
> Rich



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Migrating MySQL app to postgres?

[Rich Shepard]

On Fri, 22 Apr 2005, Chris Browne wrote:
It generally starts with seeing if the code has nonportable bits, such
as queries that are "MySQL SQL" that aren't really SQL.
   Thanks, Chris.
   I learned of AnteilCRM and will be using that just as soon as I figure out
a php error so I can restart httpd.
Rich
--
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
   Voice: 503-667-4517   Fax: 503-667-8863
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] libpq Unicode support?

[Tom Lane]

Karsten Hilbert <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> To make libpq UTF-16 capable, we'd have to change its API for all
>> strings; either make the strings counted rather than null-terminated,
>> or make the string elements wchar instead of char.  After that we'd
>> have to hack the FE/BE protocol too (or more likely, require libpq
>> to translate UTF-16 to UTF-8 before sending to the server).  I don't
>> foresee anyone doing any of this, at least not in the near term.

> Is there any *real* loss of functionality in not supporting
> UTF-16 ?

Functionality, no: UTF-16 and UTF-8 are functionally equivalent by definition.

I think the reason that it's started to come up lately is that Windows
supports UTF-16 better than UTF-8 (whereas the reverse is true on most
Unixish platforms).

If libpq were the only available API then I'd be more concerned about
making it handle this somehow.  But if you're working in, say, Java
then this issue is all taken care of for you anyway.  There are enough
other Unix-centricities in libpq that this hardly seems the worst.

Possibly someone will be motivated to start a project to design a
Windows client library from scratch ...

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[GENERAL] pg_dump serial UNIQUE NOT NULL PRIMARY KEY

[Michael Hipp]

Hello, I'm a fairly new user of PostgreSQL 8.0.2 and I'm creating a table like 
this:

CREATE TABLE cases (
id serial PRIMARY KEY,
Works fine, but when I pg_dump the database, what shows up in the dump is:
CREATE TABLE cases (
id serial NOT NULL,
The documentation seems to say that 'serial NOT NULL' is not at all the same 
as 'serial PRIMARY KEY':

( http://www.postgresql.org/docs/8.0/static/datatype.html#DATATYPE-SERIAL )
"Note:  Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer 
automatic. If you wish a serial column to be in a unique constraint or a 
primary key, it must now be specified, same as with any other data type."

Any help appreciated,
Michael Hipp
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] libpq Unicode support?

[Bruce Momjian]

Karsten Hilbert wrote:
> Tom Lane wrote:
> > To make libpq UTF-16 capable, we'd have to change its API for all
> > strings; either make the strings counted rather than null-terminated,
> > or make the string elements wchar instead of char.  After that we'd
> > have to hack the FE/BE protocol too (or more likely, require libpq
> > to translate UTF-16 to UTF-8 before sending to the server).  I don't
> > foresee anyone doing any of this, at least not in the near term.
> Is there any *real* loss of functionality in not supporting

> UTF-16 ? If so *should* it be supported in, say, 9.0 ? If not,
> should there be a FAQ item saying why not ?

Is there a reason you have to use UTF16?  Can't you convert to UTF8 on
input?  (I have no idea myself.)  Do other databases support both UTf8
and UTF16?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] pg_dump serial UNIQUE NOT NULL PRIMARY KEY

[Michael Fuhr]

On Fri, Apr 22, 2005 at 04:44:05PM -0500, Michael Hipp wrote:
>
> Hello, I'm a fairly new user of PostgreSQL 8.0.2 and I'm creating a table 
> like this:
> 
> CREATE TABLE cases (
> id serial PRIMARY KEY,
> 
> Works fine, but when I pg_dump the database, what shows up in the dump is:
> 
> CREATE TABLE cases (
> id serial NOT NULL,
> 
> The documentation seems to say that 'serial NOT NULL' is not at all the 
> same as 'serial PRIMARY KEY':

Later in the dump file you should see something like this:

ALTER TABLE ONLY cases
ADD CONSTRAINT cases_pkey PRIMARY KEY (id);

Presumably that's because adding the primary key constraint after
populating the table is more efficient than populating the table
with the constraint (and the associated index) in place.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Migrating MySQL app to postgres?

[Chris Browne]

[EMAIL PROTECTED] (Rich Shepard) writes:
>I need (and want) contact managment software for marketing/sales
> tracking in my business. The only linux app I can find that does the
> job is SugarCRM.  However, they use mysql as their backend.
>
>Might it be relatively easy for me to change the code so it will
> work with postgres? What's involved?

It generally starts with seeing if the code has nonportable bits, such
as queries that are "MySQL SQL" that aren't really SQL.

If there's a lot of that, you'll need to do some rewriting; how tough
that will be will depend on how deeply the application logic depends
on MySQL's logic.

If the vendor has done a great deal of work to tune their application
to the way MySQL works, then this will be a seriously irritating
chore.

That's something we have seen with RT/3; it was initially written for
MySQL, and the requirement of continuing compatibility means that the
authors are unable to accept the sorts of SQL changes that lead to
some queries running hundreds of times faster because the jump to
better use of SQL would break operability with MySQL.
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78.  "I will not tell my Legions of Terror
"And he must  be taken alive!" The command will be:  ``And try to take
him alive if it is reasonably practical.''"


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] libpq Unicode support?

[David Roussel]

 Do other databases support both UTf8 and UTF16?
Oracle supports UTF-8, UTF-16 an some other special UFT encodings.  I 
think some of them are pre UTF-8 becoming ratified, hence they are 
partially compatible.

It's an install time option for an Oracle database.  ASCII databases 
can be upgraded to UTF-8, but not vice versa, and it affects all 
schema's in the database.

I had an oracle system that was non-unicode, some body wanted to 
support the euro currency symbol. We tried it, it inserted fine, but 
came back in a select as another character.  The only option was custom 
escaping all over the place, or migrating oracle.  Given the amount of 
regression testing that would be needed for all the apps on the oracle 
system (200 users, 16 processor box, billions of dollars worth of 
transactions) it was not worth the effort.  People had to type 'EUR' 
instead of €.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] plperl function fails to "fire" Slony trigger

[Sven Willenberger]

We have a replication set up between 2 servers using Slony; both are
runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are
made to a replicated table, the replication does not occur; apparently
this is due to spi_exec somehow not allowing/causing the slony trigger
function to fire. The following 2 queries achieve the same result (one
in SQL and the other in plperl) however only the one in SQL ends up
being replicated:

create or replace function test_insert(int) returns text as $func$
my $query = "insert into inter_rootdecks(id,npanxx,carrier,inter) select
$_[0],npanxx,carrier,inter from inter_rootdecks where id = 20;";
$rv = spi_exec_query($query);
return "done";
$func$ LANGUAGE plperl;

create function test_insert(int) RETURNS text as $func$
INSERT INTO inter_rootdecks (id,npanxx,carrier,inter) select
$1,npanxx,carrier,inter from inter_rootdecks where id = 20;
SELECT 'done'::text;
$func$ LANGUAGE SQL;


Both are very basic insert statements, the difference being that the one
written in SQL ends up being replicated, the one written in plperl
utilizing spi_exec_query does not. In both cases the insert succesfully
completes on the source server.

Is there anything else that needs to happen in a plperl function such
that applicable triggers fire?

Sven


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] libpq Unicode support?

[Ben]

Why would you? UTF-16 and UTF-8 are just different representations for the 
same domain of characters.

On Fri, 22 Apr 2005, Ale Raza wrote:

> Are we not going to lose some characters if we are putting a UTF-16 to UTF-8
> translation in front of libpq?
> 
> Ale.
> 
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Friday, April 22, 2005 12:14 PM
> To: Bruce Momjian
> Cc: Ale Raza; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] libpq Unicode support? 
> 
> 
> Bruce Momjian  writes:
> > Tom Lane wrote:
> >> Oh?  Who's working on it, or even interested?  Was there discussion
> >> of adding it to TODO?
> 
> > TODO has:
> 
> > o Add support for Unicode
> 
> >   To fix this, the data needs to be converted to/from UTF16/UTF8
> >   so the Win32 wcscoll() can be used, and perhaps other functions
> >   like towupper().  However, UTF8 already works with normal
> >   locales but provides no ordering or character set classes.
> 
> That's completely unrelated --- it's talking about making correct use of
> Windows' locale support in one small bit inside the server.
> 
> To make libpq UTF-16 capable, we'd have to change its API for all
> strings; either make the strings counted rather than null-terminated,
> or make the string elements wchar instead of char.  After that we'd
> have to hack the FE/BE protocol too (or more likely, require libpq
> to translate UTF-16 to UTF-8 before sending to the server).  I don't
> foresee anyone doing any of this, at least not in the near term.
> 
> Putting a UTF-16 to UTF-8 translation in front of libpq seems a lot
> more practical.
> 
>   regards, tom lane
> 
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
>   joining column's datatypes do not match
> 



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] postgres user work with any password!

[Bruno Wolff III]

On Fri, Apr 22, 2005 at 11:14:35 -0300,
  Leandro Repolho <[EMAIL PROTECTED]> wrote:
> Hello guys, i installed my postgresql 8.0.2 successfuly and its
> working fine, but my postgres user works with any password, how can i
> solve ?
> I already tryed to change the password trough the pgAdmin3 but didn´t worked!

It sounds like you are using the "trust" authentication mechanism. What does
pg_hba.conf say?

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] UltraSPARC versus AMD

[Chris Browne]

[EMAIL PROTECTED] writes:
> I just got done comparing SPECMarks (on spec.org) between Sun's AMD entry
> level servers versus similarly configured UltraSPARCs versus desktop AMD
> based machines.  Sun's AMD machines are twice as fast as their UItraSPARCs,
> for approximately the same price.  What a hoot.

It's not surprising.

The "interesting" efforts that Sun has been putting into CPUs lately
have gone into the new "Niagara" thing which isn't going into low end
UltraSPARCs.

AMD has gotten a lot of the ex-Digital folk, and ex-Alpha
technologies, and has been able to get that stuff into lower priced
systems, from whence is what we can see...
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78.  "I will not tell my Legions of Terror
"And he must  be taken alive!" The command will be:  ``And try to take
him alive if it is reasonably practical.''"


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Table modifications with dependent views - best

[Bruno Wolff III]

On Fri, Apr 22, 2005 at 11:34:29 +0100,
  David Roussel <[EMAIL PROTECTED]> wrote:
> 
> > I usually put DDL statements in a transaction, for a couple of
> > reasons: so that a mistake doesn't leave me with half-done work
> > (any error will cause the entire transaction to roll back), and to
> > make the changes atomic for the benefit of other transactions.
> 
> Can you do that in postgres?  Will it really make the DDL atomic?

I wasn't able to find where this is spelled out in the documentation,
but I believe all DDL commands except DROP DATABASE can be rolled back now.
For older versions of Postgres there were more DDL statements that were not
transaction safe.

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Migrating MySQL app to postgres?

[Rich Shepard]

On Fri, 22 Apr 2005, Guy Fraser wrote:
One of my associates swears SMARTY is the best thing since sliced bread. I
think it uses PHP an PEAR, but is more abstract. I looked at it and it did
some cool things with only a few lines of code.
  Thanks, Guy. I'll use Anteil OpenCMS and see how I like it. Can always
modify that, too. Otherwise, I'll probably roll my own with python and
postgres.
Rich
--
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
   Voice: 503-667-4517   Fax: 503-667-8863
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] Multiple RULES on Views

[David Wheeler]

On Apr 22, 2005, at 2:09 AM, Sebastian Böck wrote:
Multiple rules on the same table and same event type are
applied in alphabetical name order.
Ah, I'd missed that. Curious that it worked for me, then, since my 
rules were named insert_one, promote_one, and nothing_one, as 
nothing_one comes after promote_one alphabetically, and promote_one ran 
but nothing_one did not...

Regards,
David
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] libpq Unicode support?

[Karsten Hilbert]

On Fri, Apr 22, 2005 at 05:28:28PM -0400, Bruce Momjian wrote:

> > UTF-16 ? If so *should* it be supported in, say, 9.0 ? If not,
> > should there be a FAQ item saying why not ?
> 
> Is there a reason you have to use UTF16?
No. I don't currently use either one (that is I am using a
"unicode" database with appropriate "set client_encoding"s
which works as expected. I am just wondering whether we should
add a FAQ item why UTF16 doesn't need to be supported.

> Can't you convert to UTF8 on input?
I likely could would I have to.

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

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] plperl function fails to "fire" Slony trigger

[Alvaro Herrera]

On Fri, Apr 22, 2005 at 03:09:13PM -0400, Sven Willenberger wrote:
> On Fri, 2005-04-22 at 14:43 -0400, Alvaro Herrera wrote:
> > On Fri, Apr 22, 2005 at 02:24:57PM -0400, Jan Wieck wrote:
> > > On 4/22/2005 2:08 PM, Tom Lane wrote:
> > > 
> > > >Sven Willenberger <[EMAIL PROTECTED]> writes:
> > > >>We have a replication set up between 2 servers using Slony; both are
> > > >>runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are
> > > >>made to a replicated table, the replication does not occur; apparently
> > > >>this is due to spi_exec somehow not allowing/causing the slony trigger
> > > >>function to fire.
> > > >
> > > >Yuck :-(.  The only idea that comes to mind is that 8.0 changed the
> > > >timing of trigger firing --- the triggers are probably firing while your
> > > >function still has control, whereas in earlier releases they'd only fire
> > > >after it returns.  Could this be breaking some assumption Slony makes
> > > >about the order of operations?
> > > 
> > > Slony triggers are AFTER ROW triggers. All they do is one SPI_execp() to 
> > > insert the log row. The only way that could possibly be suppressed is by 
> > > bypassing the executor and doing direct heap_ access.
> > > 
> > > So how does plperl manage that?
> > 
> > It doesn't; it only uses SPI.  I guess we would need the original
> > function to see what is really happening.
> > 
> 
> If by the "original function" you mean the plperl function, it was in
> the thread parent; in essense it runs an "insert into tablename (cols)
> select vals from " query. The Slony trigger is:
> "_T2_logtrigger_10" AFTER INSERT OR DELETE OR UPDATE ON inter_rootdecks
> FOR EACH ROW EXECUTE PROCEDURE "_T2".logtrigger('_T2', '10', 'kkkv')
> 
> If by "orginal function" you mean the logtrigger (slony) function it
> would appear from a cursory glance at the source that logtrigger
> accesses the heap (HeapTuple). It would appear the same manipulation is
> utilized by plperl's SPI calls. How this all interacts with the
> "executor" is, admittedly, beyond the scope of my comprehension at this
> point ...

Hmm.  There are no direct heap manipulations that I can see in plperl.
The HeapTuple functions that it uses are merely to check the catalog in
lookup of the function, AFAICS.

I don't have Slony installed here, so I can't run a simple test
directly, but I think the problem lies elsewhere.

The only idea that comes to mind is that the new tuples somehow fail the
snapshot test of the Slony trigger ... not sure if that makes any sense,
because the trigger should affect all new tuples, I imagine.

-- 
Alvaro Herrera (<[EMAIL PROTECTED]>)
"El número de instalaciones de UNIX se ha elevado a 10,
y se espera que este número aumente" (UPM, 1972)

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] PHP and Postgresql on Windows

[typing80wpm]

What would I need to do to make PHP, installed on my desktop, talk to Postgresql.  I have several CDS with WAMP installs (Windows Apache Mysgl PHP).   Would I use some special driver to allow PHP to connect to Postgresql?  I am going to search on google as soon as I post, but just curious if someone has a word of wisdom. Thanks

Re: [GENERAL] pg_dump serial UNIQUE NOT NULL PRIMARY KEY

[Tom Lane]

Michael Hipp <[EMAIL PROTECTED]> writes:
> Hello, I'm a fairly new user of PostgreSQL 8.0.2 and I'm creating a table 
> like 
> this:

> CREATE TABLE cases (
>  id serial PRIMARY KEY,

> Works fine, but when I pg_dump the database, what shows up in the dump is:

> CREATE TABLE cases (
>  id serial NOT NULL,

pg_dump prefers to add the PRIMARY KEY via an ALTER TABLE, after it's
got done loading data into the table.  This is basically a speed hack
(see the manual's advice about bulk data loading).

If you don't see an appropriate ALTER down near the end of the dump,
then you've got grounds for complaint ...

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] libpq Unicode support?

[Ale Raza]

Tom, Thanks for reply. I want to pass UTF-16 data. Is there any special
build of libpq for UTF-16. I did not build libpq locally. 

Ale

-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Friday, April 22, 2005 11:10 AM
To: Ale Raza
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] libpq Unicode support? 


Ale Raza <[EMAIL PROTECTED]> writes:
> Wondering if libpq lib support unicode? 

What sort of "support" have you got in mind?  It passes UTF-8 data
through just fine.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] libpq Unicode support?

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian  writes:
> > Tom Lane wrote:
> >> Oh?  Who's working on it, or even interested?  Was there discussion
> >> of adding it to TODO?
> 
> > TODO has:
> 
> > o Add support for Unicode
> 
> >   To fix this, the data needs to be converted to/from UTF16/UTF8
> >   so the Win32 wcscoll() can be used, and perhaps other functions
> >   like towupper().  However, UTF8 already works with normal
> >   locales but provides no ordering or character set classes.
> 
> That's completely unrelated --- it's talking about making correct use of
> Windows' locale support in one small bit inside the server.
> 
> To make libpq UTF-16 capable, we'd have to change its API for all
> strings; either make the strings counted rather than null-terminated,
> or make the string elements wchar instead of char.  After that we'd
> have to hack the FE/BE protocol too (or more likely, require libpq
> to translate UTF-16 to UTF-8 before sending to the server).  I don't
> foresee anyone doing any of this, at least not in the near term.
> 
> Putting a UTF-16 to UTF-8 translation in front of libpq seems a lot
> more practical.

So the Win32 fix and the libpq translation are two different issues. 
Hmm.

Agreed we don't want to support both UTF8 and UTF16 in the backend.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] plperl function fails to "fire" Slony trigger

[Tom Lane]

Sven Willenberger <[EMAIL PROTECTED]> writes:
> We have a replication set up between 2 servers using Slony; both are
> runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are
> made to a replicated table, the replication does not occur; apparently
> this is due to spi_exec somehow not allowing/causing the slony trigger
> function to fire.

Yuck :-(.  The only idea that comes to mind is that 8.0 changed the
timing of trigger firing --- the triggers are probably firing while your
function still has control, whereas in earlier releases they'd only fire
after it returns.  Could this be breaking some assumption Slony makes
about the order of operations?

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] psqlODBC and Access

[Typing80wpm]

I successfully installed the PostgreSQL ODBC driver, and got Access to link 
to a table as external data.
 
It took me a while to figure out what the proper parameters might be in 
configuring the ODBC 
 
in the control panel in Administrative Tools, and then to fill in the 
proper fields in Access when defining the external data linked table.
 
In Administrative Tools, ODBC, the Database name is the database I created 
in Postgresql, which I called lantimes.  The Server was a REAL MYSTERY, 
until I read something in google which suggested that it should be 
localhost.
 
I was in despair for a while, until I saw it suddenly work.
 
Another problem I encountered, in the two tables I created in pgAdmin III , 
involves two different numeric fields, where I get the message from Access 
"Field is of type MONEY but _expression_ is double precision."    
The field was defined in the sql statement in PostgreSQL as MONEY. I was simply 
copying an exercise from a SQL primer.  Should I not use MONEY in defining 
such fields to be used with ACCESS? Is there a different TYPE that I should use 
in my definition?  Thanks!
 
By the way, what would you suggest as a tool to administer the PostgreSQL 
databases. I mean, something sort of graphical which would display tables and 
names and relationships.
 
 

Re: [GENERAL] plperl function fails to "fire" Slony trigger

[Sven Willenberger]

On Fri, 2005-04-22 at 14:43 -0400, Alvaro Herrera wrote:
> On Fri, Apr 22, 2005 at 02:24:57PM -0400, Jan Wieck wrote:
> > On 4/22/2005 2:08 PM, Tom Lane wrote:
> > 
> > >Sven Willenberger <[EMAIL PROTECTED]> writes:
> > >>We have a replication set up between 2 servers using Slony; both are
> > >>runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are
> > >>made to a replicated table, the replication does not occur; apparently
> > >>this is due to spi_exec somehow not allowing/causing the slony trigger
> > >>function to fire.
> > >
> > >Yuck :-(.  The only idea that comes to mind is that 8.0 changed the
> > >timing of trigger firing --- the triggers are probably firing while your
> > >function still has control, whereas in earlier releases they'd only fire
> > >after it returns.  Could this be breaking some assumption Slony makes
> > >about the order of operations?
> > 
> > Slony triggers are AFTER ROW triggers. All they do is one SPI_execp() to 
> > insert the log row. The only way that could possibly be suppressed is by 
> > bypassing the executor and doing direct heap_ access.
> > 
> > So how does plperl manage that?
> 
> It doesn't; it only uses SPI.  I guess we would need the original
> function to see what is really happening.
> 

If by the "original function" you mean the plperl function, it was in
the thread parent; in essense it runs an "insert into tablename (cols)
select vals from " query. The Slony trigger is:
"_T2_logtrigger_10" AFTER INSERT OR DELETE OR UPDATE ON inter_rootdecks
FOR EACH ROW EXECUTE PROCEDURE "_T2".logtrigger('_T2', '10', 'kkkv')

If by "orginal function" you mean the logtrigger (slony) function it
would appear from a cursory glance at the source that logtrigger
accesses the heap (HeapTuple). It would appear the same manipulation is
utilized by plperl's SPI calls. How this all interacts with the
"executor" is, admittedly, beyond the scope of my comprehension at this
point ...

Sven


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] libpq Unicode support?

[Bruce Momjian]

Karsten Hilbert wrote:
> On Fri, Apr 22, 2005 at 05:28:28PM -0400, Bruce Momjian wrote:
> 
> > > UTF-16 ? If so *should* it be supported in, say, 9.0 ? If not,
> > > should there be a FAQ item saying why not ?
> > 
> > Is there a reason you have to use UTF16?
> No. I don't currently use either one (that is I am using a
> "unicode" database with appropriate "set client_encoding"s
> which works as expected. I am just wondering whether we should
> add a FAQ item why UTF16 doesn't need to be supported.

Well, we need to support UTF16 on Win32 only because Win32 libc
libraries doesn't support UTF8, but other than that UTF16 isn't much of
an issue for our users.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Postgresql Windows ODBC

[Jeff Eckermann]

<[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]
> Tonight I shall install the Postgresql ODBC driver which I downloaded, and
> experiment.
>
> Is anyone using ODBC?  I was looking at Realbasic standard edition  (not
> professional), and thinking of downloading the demo and trying it with the 
> ODBC.
> Anyone here use Realbasic at all?
>
> In Windows, what would you say is the easiest language to use which can 
> talk
> to Postgresql? Just curiouse.g. I have Liberty Basic  which is 
> very
> easy, but does not easily support ODBC...
>
> Is anyone working with some form of Python on Windows.  I downloaded 
> Python,
> but it seems to run only in some DOS window. I mentioned this on a
> Dreamcard/Revolution board, and they said "Oh, you want PYTHON   CARD."
>
> Anyway, I would be interested to learn of an easy language which can 
> access
> Postgresql, together with some decent examples or tutorials to get me 
> going.
> I have a feeling that Realbasic will do the  trick.
>

Your question is too broad to be easily answered.  What kind of application 
do you want to create, to meet what need for whom?  In what kind of business 
environment will it be running? 



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] plperl function fails to "fire" Slony trigger

[Alvaro Herrera]

On Fri, Apr 22, 2005 at 02:24:57PM -0400, Jan Wieck wrote:
> On 4/22/2005 2:08 PM, Tom Lane wrote:
> 
> >Sven Willenberger <[EMAIL PROTECTED]> writes:
> >>We have a replication set up between 2 servers using Slony; both are
> >>runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are
> >>made to a replicated table, the replication does not occur; apparently
> >>this is due to spi_exec somehow not allowing/causing the slony trigger
> >>function to fire.
> >
> >Yuck :-(.  The only idea that comes to mind is that 8.0 changed the
> >timing of trigger firing --- the triggers are probably firing while your
> >function still has control, whereas in earlier releases they'd only fire
> >after it returns.  Could this be breaking some assumption Slony makes
> >about the order of operations?
> 
> Slony triggers are AFTER ROW triggers. All they do is one SPI_execp() to 
> insert the log row. The only way that could possibly be suppressed is by 
> bypassing the executor and doing direct heap_ access.
> 
> So how does plperl manage that?

It doesn't; it only uses SPI.  I guess we would need the original
function to see what is really happening.

-- 
Alvaro Herrera (<[EMAIL PROTECTED]>)
"Limítate a mirar... y algun día veras"

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] Pentium4/Xeon versus AMD

[P. Joshua Rovero]

Chris Browne wrote:
[EMAIL PROTECTED] writes:
I just got done comparing SPECMarks (on spec.org) between Sun's AMD entry
level servers versus similarly configured UltraSPARCs versus desktop AMD
based machines.  Sun's AMD machines are twice as fast as their UItraSPARCs,
for approximately the same price.  What a hoot.
The focus should not just be on SPARC vs AMD64.  The AMD64 chips
outperform Intel P4 and Xeon handily as well, using ooomph per
dollar, mph per Megahertz, or most other measures.  I'm
certainly not complaining, it just appears AMD64 is an outstanding
value these days.
We purchased 2 laptops, one P4, one AMD64 from a linux-friendly
vendor to use as software demo boxes.  The AMD CPU speed is about
60% of the Intel P4 CPU speed, both have same amount of RAM & Disk,
both have the same video controller.  The intel laptop was more
expensive, but the AMD64 box clearly outperforms it with both
postgresql and our weather/flight tracking applications.
Josh
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] libpq Unicode support?

[Ale Raza]

Are we not going to lose some characters if we are putting a UTF-16 to UTF-8
translation in front of libpq?

Ale.

-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Friday, April 22, 2005 12:14 PM
To: Bruce Momjian
Cc: Ale Raza; pgsql-general@postgresql.org
Subject: Re: [GENERAL] libpq Unicode support? 


Bruce Momjian  writes:
> Tom Lane wrote:
>> Oh?  Who's working on it, or even interested?  Was there discussion
>> of adding it to TODO?

> TODO has:

> o Add support for Unicode

>   To fix this, the data needs to be converted to/from UTF16/UTF8
>   so the Win32 wcscoll() can be used, and perhaps other functions
>   like towupper().  However, UTF8 already works with normal
>   locales but provides no ordering or character set classes.

That's completely unrelated --- it's talking about making correct use of
Windows' locale support in one small bit inside the server.

To make libpq UTF-16 capable, we'd have to change its API for all
strings; either make the strings counted rather than null-terminated,
or make the string elements wchar instead of char.  After that we'd
have to hack the FE/BE protocol too (or more likely, require libpq
to translate UTF-16 to UTF-8 before sending to the server).  I don't
foresee anyone doing any of this, at least not in the near term.

Putting a UTF-16 to UTF-8 translation in front of libpq seems a lot
more practical.

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Record as a parameter to a function

[Carlos Correia]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Fuhr wrote:
| On Fri, Apr 22, 2005 at 03:31:59AM +0100, Carlos Correia wrote:
|
|>Michael Fuhr wrote:
|>| On Fri, Apr 22, 2005 at 01:44:24AM +0100, Carlos Correia wrote:
|>|
|>|>carlos=# insert into test (c1) values( 'test');
|>|>ERROR:  NEW used in query that is not in a rule
|>|>CONTEXT:  PL/pgSQL function "tftest" line 2 at perform
|>|
|>| PostgreSQL 8.0 and later have improved support for composite types.
|>
|>Thanks... but does this means that you can't do that with version 7.4.x?
|
|
| Not as far as I know, or at least not the same way.
|
Well... I guess I'll have to do it the hard way (no big deal)
Thank you very much ;-)
Carlos
- --
MEMÓRIA PERSISTENTE, Lda.
Tel.: 219 291 591 - GSM:  967 511 762
e-mail: [EMAIL PROTECTED] - URL: http://www.m16e.com
AIM: m16e - ICQ: 257488263 - Jabber: [EMAIL PROTECTED]
Skype.com username (VoIP): m16e.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.0 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCaXOQ90uzwjA1SJURAq8FAJ9Bz+NZpjdG3Ig3QaFXyMGY0JNs2wCaAmNy
InMlLAsINv/mEBCDtt6YWwc=
=2NMd
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] PHP and Postgresql on Windows

[David Roussel]

Try PEAR DB.
http://pear.php.net/package/DB
It's a database abstraction layer, which means that you can change DB 
later, with less hasle.

Have a look at how phpPgAdmin works.  Download the source and have a 
look.
http://phppgadmin.sourceforge.net/

On 22 Apr 2005, at 20:04, [EMAIL PROTECTED] wrote:
What would I need to do to make PHP, installed on my desktop, talk to 
Postgresql.  I have several CDS with WAMP installs (Windows Apache 
Mysgl PHP).   Would I use some special driver to allow PHP to connect 
to Postgresql?  I am going to search on google as soon as I post, but 
just curious if someone has a word of wisdom. Thanks
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] Table Partition

[Scott Marlowe]

On Fri, 2005-04-22 at 05:51, Dinesh Pandey wrote:
> How to create this table  (with partition) in Postgres.
> 
>  
> 
> --ORACLE
> 
> CREATE TABLE A (col1 NUMBER NOT NULL, 
> 
>col2  DATENOT NULL, 
> 
>col3  VARCHAR2(500)
> 
>   )
> 
>   PARTITION BY RANGE (col2) (
> 
>PARTITION partition_one VALUES LESS THAN
> (TO_DATE('01/04/2001', 'DD/MM/')) TABLESPACE MYTABSPACE
> 
>   );

Unfortunately, postgresql does not currently support partitioning.

If you want this, you'll have to "roll your own" so to speak.  You'll
need to create two different tables in two different tablespaces, create
a view on top of them, and then create on update triggers to put the
data into the right table when it comes in.

it's a pain, but it works.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] artificial keys or not?

[Scott Ribe]

> ..so the only question is:
> 
> drop the the serials as PKs and just use short text handles? our
> database size is arround 290 mb. there shouldn't be speed issues as long
> as proper indexes exit, right? some of the conditions are dynamic
> though, so there can't be an proper index in any case. any other concerns?

Someone (a manager) somewhere (probably marketing) sometime (when you least
expect it) will demand that those short text names be changed, possibly for
a completely irrational reason. I know there are still text books that claim
that artificial keys are evil for some inscrutable reason, but hard
experience has taught me to never, ever, under any circumstance, make a
primary key out of data that comes from humans, and to be very suspicious of
using data that will be visible to humans.


-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] libpq Unicode support?

[Peter Eisentraut]

Ale Raza wrote:
> Are we not going to lose some characters if we are putting a UTF-16
> to UTF-8 translation in front of libpq?

No, they are just different encodings of the same character set.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] libpq Unicode support?

[Tom Lane]

Ale Raza <[EMAIL PROTECTED]> writes:
> Wondering if libpq lib support unicode? 

What sort of "support" have you got in mind?  It passes UTF-8 data
through just fine.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] libpq Unicode support?

[Tom Lane]

Bruce Momjian  writes:
> We do not support UTF-16 at this time.  Hopefully we will in 8.1.

Oh?  Who's working on it, or even interested?  Was there discussion
of adding it to TODO?

I think it would be an extremely nontrivial change, which is why
I am not pleased with making casual promises that it will appear
soon (or indeed at all).

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[GENERAL] postgres user work with any password!

[Leandro Repolho]

Hello guys, i installed my postgresql 8.0.2 successfuly and its
working fine, but my postgres user works with any password, how can i
solve ?
I already tryed to change the password trough the pgAdmin3 but didn´t worked!

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] psqlodbc MSAccess and Postgresql

[Jeff Eckermann]

<[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]
> Thanks for the help with /dt... Mike, your post helped me to discover psql
> command window, I had been trying out the pgAdmin ...
>
> I found an interesting article regarding MSAccess as a front end to
> Postgresql, so I downloaded the psqlodbc installer.  I will be getting up 
> the nerve
> to run the install and go into windows odbc and install that driver so  I 
> can
> use MSAccess with Postgresql ... any tips or caveats appreciated

Be sure to check out the FAQs and How-tos at the psqlodbc project site:
http://gborg.postgresql.org/project/psqlodbc/projdisplay.php

You will find that that Access and PostgreSQL get on well together.  You can 
get by with the default driver settings for the most part; I suggest that 
you make sure that the "row versioning" and "true = -1" options are set to 
true.  If you want to use the "memo" datatype in Access, then you will need 
to check "text = longvarchar" option.

If you have further questions, try posting to the pgsql-odbc list.

>
> This is the link which is guiding me
> _http://database.sarang.net/database/postgres/postodbc/faq.html_
> (http://database.sarang.net/database/postgres/postodbc/faq.html)
>
>
> 



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Postgresql Windows ODBC

[Tony Caduto]

In my opinion the easiest language for any database development is 
Borland Delphi bar NONE.
You can get the pro version for for 499.99 as a upgrade or a personal 
editon is also available for free or little cost.
You might even be able to get a copy of Delphi 7 on ebay for much less.

For Postgres development I use the Zeos components(www.zeoslib.net) and 
PostgresDAC(www.micoolap.com).

Here is a quick example of how to execute a query with params in Delphi 
with a tdataset compatible query component.

myquery.clear;
myquery.add('select firstname from companyinfo where lastname = :PARAM1');
parambyname('PARAM1').asstring:= 'smith';
myquery.open;
At this point if you have bound the query to a grid you will see the 
data, or you could loop through the result set like this:

myquery.first;
while not myquery.eof do
  begin
showmessage(myquery.fieldbyname('firstname').asstring);  
myquery.next;
  end;

The above code also works on Oracle, MS SQL, Mysql, DB2 etc etc as long 
as you use a tdataset compatible component set.

I have used C#, VB.net and VB classic and NONE of them are as easy and 
intuitive to use for database development as Delphi  NONE.

Delphi 2005 also includes Delphi.net and C# along with native win32 
development in pascal, you can also use the Kylix with the Zeos 
components for development on Linux, and while there has not been a 
update to Kylix in awhile, it still works great.

You can do lots more stuff with Delphi as well, just about anything you 
could do in C or C++ minus device drivers.

Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com

?
 
In Windows, what would you say is the easiest language to use which 
can talk to Postgresql? Just curiouse.g. I have Liberty Basic 
which is very easy, but does not easily support ODBC...
 
Is anyone working with some form of Python on Windows.  I downloaded 
Python, but it seems to run only in some DOS window. I mentioned this 
on a Dreamcard/Revolution board, and they said "Oh, you want PYTHON  
CARD."
 
Anyway, I would be interested to learn of an easy language which can 
access Postgresql, together with some decent examples or tutorials to 
get me going.  I have a feeling that Realbasic will do the trick.



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Install development headers/libraries Win32 v 8.0.2

[John DeSoi]

On Apr 21, 2005, at 7:27 PM, Mark Miller wrote:
How do I install the headers and libraries for extending postgreSql?  
They are not installed by the windows installer (see the following  
post:  
http://archives.postgresql.org/pgsql-hackers-win32/2005-03/ 
msg00169.php ).

Install MinGW and then compile from the source distribution.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] Postgres Db to XML/XML Schema Generator

[Walsh, Richard (Richard)]

Hi,
I am working on a 
project where we have to define database tables and data in XML, have it 
validated by an XML Schema and have it loaded into the database. We are 
using our EJBs to load the data into the database so I am not so concerned with 
that. What I am concerned with is generating a correct XML Schema from the 
Database tables. 
 
Are there any 
tools/utilities that I can use to generate an XML Document,  XML Schema or 
both from my Postgres Database?
 
Reason I ask is that 
if we can generate the XML Schema automatically from our database schema it will 
mean that we can have our XML validation rules driven from our database schema. 
It would also mean that once the database tables change we can change the XML 
Schema very easily rather then having to manually update the XML schema by 
hand.
 
Any information on 
Database TO XML/XML Schema and XML to Database Utilities would be very useful. I 
have already used the utilities at http://www.hitsw.com/xml_utilites/index.html. 
Also, any utilities suggested must be under a GPL license.
 
I have looked 
through the archives and have not found any utility, but did find a thread 
talking about the creation of an XML utility like the one that I am looking 
for.
 
Any help much 
appreciated.
 
thanks,Richie.

Re: [GENERAL] Postgresql Windows ODBC

[John DeSoi]

On Apr 22, 2005, at 8:07 AM, [EMAIL PROTECTED] wrote:
Tonight I shall install the Postgresql ODBC driver which I downloaded, 
and experiment.
 
Is anyone using ODBC?  I was looking at Realbasic standard edition 
(not professional), and thinking of downloading the demo and trying it 
with the ODBC.  Anyone here use Realbasic at all?
 
In Windows, what would you say is the easiest language to use which 
can talk to Postgresql? Just curious    e.g. I have Liberty Basic 
which is very easy, but does not easily support ODBC...
 
Is anyone working with some form of Python on Windows.  I downloaded 
Python, but it seems to run only in some DOS window. I mentioned this 
on a Dreamcard/Revolution board, and they said "Oh, you want PYTHON  
CARD."
 
Anyway, I would be interested to learn of an easy language which can 
access Postgresql, together with some decent examples or tutorials to 
get me going.  I have a feeling that Realbasic will do the trick.

My take is that the professional version is well worth the extra $300 
if you are really serious about doing database development. It has 
support for PostgreSQL and a lot of other databases already included. 
If I was developing an application for some one else to use, ODBC would 
be my last choice because of the configuration and setup hassles.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] Optimising Union Query.

[Dawid Kuroczko]

On 4/22/05, Rob Kirkbride <[EMAIL PROTECTED]> wrote:
> I've got a query that takes quite some time to complete. I'm not an SQL
> expert so I'm not sure how to improve things.
> I've done a explain analyze and as I expected the database has to check
> every row in each of the three tables below but I'm wondering if I can
> do it much quicker by a use of an index or something. Each of the three
> tables could have several thousand entries in. Basically the tables
> contain data recorded against time then every hour a script deletes
> entries that more than so many hours old.
> 
> select l.name,l.id from pa i,locations l where i.location=l.id union
> select l.name,l.id from andu i,locations l where i.location=l.id union
> select l.name,l.id from idu i,locations l where i.location=l.id;

Would it be OK if there were duplicates returned?  I.e if select from pa
table and andu table returned same row, would it be ok if there would
be two rows in "final" output because of one?

If so, change "union" to "UNION ALL".

If you put only "UNION", server gets resutls from _all_ selects, removes
duplicates and returns your query.  If you put "UNION ALL" it simply
does three selects and returns all the rows returned.  Not having to
look for duplicates makes it WAY faster.

  Regards,
 Dawid

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] psqlodbc MSAccess and Postgresql

[Relyea, Mike]

I'm currently building an 
Access FE to my PostgreSQL BE.  So far it works great.  If you're 
going to be deploying this to multiple users, I'd suggest using a DSN-less 
connection.  That way you don't have to set up a DSN on each user's 
PC.
 
Mike


From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED] On Behalf Of 
[EMAIL PROTECTED]Sent: Friday, April 22, 2005 12:28 
AMTo: pgsql-general@postgresql.orgSubject: [GENERAL] 
psqlodbc MSAccess and Postgresql 

Thanks for the help with /dt... Mike, your post helped me to discover psql 
command window, I had been trying out the pgAdmin ...   
 
I found an interesting article regarding MSAccess as a front end to 
Postgresql, so I downloaded the psqlodbc installer.  I will be getting up 
the nerve to run the install and go into windows odbc and install that driver so 
I can use MSAccess with Postgresql ... any tips or caveats appreciated
 
This is the link which is guiding me
http://database.sarang.net/database/postgres/postodbc/faq.html
 
 

[GENERAL] Optimising Union Query.

[Rob Kirkbride]

Hi,
I've got a query that takes quite some time to complete. I'm not an SQL 
expert so I'm not sure how to improve things.
I've done a explain analyze and as I expected the database has to check 
every row in each of the three tables below but I'm wondering if I can 
do it much quicker by a use of an index or something. Each of the three 
tables could have several thousand entries in. Basically the tables 
contain data recorded against time then every hour a script deletes 
entries that more than so many hours old.

select l.name,l.id from pa i,locations l where i.location=l.id union 
select l.name,l.id from andu i,locations l where i.location=l.id union 
select l.name,l.id from idu i,locations l where i.location=l.id;

Thanks for any help,
Rob

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[GENERAL] Postgresql Windows ODBC

[Typing80wpm]

Tonight I shall install the Postgresql ODBC driver which I downloaded, and 
experiment.
 
Is anyone using ODBC?  I was looking at Realbasic standard edition 
(not professional), and thinking of downloading the demo and trying it with the 
ODBC.  Anyone here use Realbasic at all?
 
In Windows, what would you say is the easiest language to use which can 
talk to Postgresql? Just curious    e.g. I have Liberty Basic 
which is very easy, but does not easily support ODBC...
 
Is anyone working with some form of Python on Windows.  I downloaded 
Python, but it seems to run only in some DOS window. I mentioned this on a 
Dreamcard/Revolution board, and they said "Oh, you want PYTHON  
CARD."
 
Anyway, I would be interested to learn of an easy language which can access 
Postgresql, together with some decent examples or tutorials to get me 
going.  I have a feeling that Realbasic will do the 
trick.

Re: [GENERAL] Record as a parameter to a function

[Michael Fuhr]

On Fri, Apr 22, 2005 at 03:31:59AM +0100, Carlos Correia wrote:
> Michael Fuhr wrote:
> | On Fri, Apr 22, 2005 at 01:44:24AM +0100, Carlos Correia wrote:
> |
> |>carlos=# insert into test (c1) values( 'test');
> |>ERROR:  NEW used in query that is not in a rule
> |>CONTEXT:  PL/pgSQL function "tftest" line 2 at perform
> |
> | PostgreSQL 8.0 and later have improved support for composite types.
> 
> Thanks... but does this means that you can't do that with version 7.4.x?

Not as far as I know, or at least not the same way.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Table modifications with dependent views - best practices?

[Michael Fuhr]

On Fri, Apr 22, 2005 at 11:34:29AM +0100, David Roussel wrote:
> 
> > I usually put DDL statements in a transaction, for a couple of
> > reasons: so that a mistake doesn't leave me with half-done work
> > (any error will cause the entire transaction to roll back), and to
> > make the changes atomic for the benefit of other transactions.
> 
> Can you do that in postgres?  Will it really make the DDL atomic?

Yes, although locking will probably prevent concurrent access and
can cause deadlock.  DDL statements like DROP, CREATE, and ALTER
acquire an AccessExclusiveLock on the objects they're modifying,
so the transaction doing the DDL will block until no other transactions
hold locks on those objects, and other transactions' attempts to
use those objects will block until the DDL transaction commits or
rolls back.  If the DDL transaction rolls back, then nobody else
will ever have seen the changes; if it commits then the changes all
become visible at the same time.

Try it and see what happens.  You might see blocking and you might
be able to cause deadlock, but you shouldn't ever see some changes
but not others.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] Table Partition

[Dinesh Pandey]

How to create this table  (with
partition) in Postgres.

 

--ORACLE

CREATE TABLE A (col1     NUMBER NOT NULL, 

   col2      DATE    NOT
NULL, 

   col3      VARCHAR2(500)

  )

  PARTITION BY RANGE (col2) (

   PARTITION partition_one VALUES LESS
THAN (TO_DATE('01/04/2001', 'DD/MM/')) TABLESPACE MYTABSPACE

  );

 

 

 

 

 

Thanks



 

Re: [GENERAL] Table modifications with dependent views - best

[David Roussel]

> I usually put DDL statements in a transaction, for a couple of
> reasons: so that a mistake doesn't leave me with half-done work
> (any error will cause the entire transaction to roll back), and to
> make the changes atomic for the benefit of other transactions.

Can you do that in postgres?  Will it really make the DDL atomic?

I know that is oracle any DDL will implicityly commit any existing
transaction, start another one, and commit again at the end of the
statement.

I think it is similar in SQL Server too.

Can postgress really do DDL on multiple tables and indexes atomically?

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] ShmemAlloc: out of memory

[Dinesh Pandey]

http://www.postgresql.org/docs/8.0/interactive/kernel-resources.html#SYSVIPC


Thanks
Dinesh Pandey



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of JM
Sent: Friday, April 22, 2005 2:49 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] ShmemAlloc: out of memory

hi all,

i need help on this.. i'm experiencing this problem too often and i
need a 
permanent solution.

im using 7.3.4

 config file 

tcpip_socket = true
max_connections = 150
superuser_reserved_connections = 2

port = 5432
#shared_buffers = 45800
shared_buffers = 47900
sort_mem = 4
#max_locks_per_transaction=160
max_locks_per_transaction=200

#fsync = true
#wal_sync_method = fsync

#
#   Locale settings
#
# (initialized by initdb -- may be changed)
LC_MESSAGES = 'en_US.UTF-8'
LC_MONETARY = 'en_US.UTF-8'
LC_NUMERIC = 'en_US.UTF-8'
LC_TIME = 'en_US.UTF-8'

... config ends

im having problems on our DB..

# update table_fan set active_flag='n' where num='12345678';

WARNING:  ShmemAlloc: out of memory
ERROR:  FreeSpaceMap hashtable out of memory


temporary solution:
  restart the DB and all things went back to normal. trying the same SQL
would 
result to an update..

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] ShmemAlloc: out of memory

[JM]

hi all,

i need help on this.. i'm experiencing this problem too often and i 
need a 
permanent solution.

im using 7.3.4

 config file 

tcpip_socket = true
max_connections = 150
superuser_reserved_connections = 2

port = 5432
#shared_buffers = 45800
shared_buffers = 47900
sort_mem = 4
#max_locks_per_transaction=160
max_locks_per_transaction=200

#fsync = true
#wal_sync_method = fsync

#
#   Locale settings
#
# (initialized by initdb -- may be changed)
LC_MESSAGES = 'en_US.UTF-8'
LC_MONETARY = 'en_US.UTF-8'
LC_NUMERIC = 'en_US.UTF-8'
LC_TIME = 'en_US.UTF-8'

... config ends

im having problems on our DB..

# update table_fan set active_flag='n' where num='12345678';

WARNING:  ShmemAlloc: out of memory
ERROR:  FreeSpaceMap hashtable out of memory


temporary solution:
  restart the DB and all things went back to normal. trying the same SQL would 
result to an update..

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Multiple RULES on Views

[=?ISO-8859-1?Q?Sebastian_B=F6ck?=]

David Wheeler wrote:
[...]
Well, I didn't have an unconditional update rule, so I added one without 
removing the other two:

CREATE RULE nothing_one AS
ON INSERT TO one DO INSTEAD NOTHING;
And it worked! Now I can have an insert do an INSERT or UPDATE on 
another table magically.

But my question is this: Is this a known and supported behavior? If not, 
is it likely to change? If so, how is the order or rules evaluated when 
a query is sent to the database? Order of definition? Alphabetically?
Yes, this is the correct way to do updateable views.
Multiple rules on the same table and same event type are
applied in alphabetical name order.
See:
http://www.postgresql.org/docs/current/static/sql-createrule.html
for more details and the above quote.
HTH
Sebastian
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

[GENERAL] artificial keys or not?

[Hannes Dorbath]

we have lots of small relations that only consist of a few attributes. 
like in an ecommerce app we have relations like:

payment_methods
payment_methods_lang
delivery_types
delivery_types_lang
basket_states
basket_states_lang
payment_methods, delivery_types, basket_states mostly just consist of
foo_id  serial  [PK]
active  boolean
the *_lang relations look like
foo_idinteger [PK]
language_id   integer [PK]
label varchar
description   text
The problem is, our queries got a bit unreadable..
SELECT
  b.basket_id
FROM
  baskets b
WHERE
  b.basket_state_id IN (1,3,6,7);
..dosen't tell much, so we came up with an additional attribute called 
handle, which is just an alternate key for foo_id.

SELECT
  b.basket_id
FROM
  baskets b
  INNER JOIN basket_states bs USING (basket_state_id)
WHERE
  bs.handle IN (
'open',
'sign_pending',
'freight_cost_calc_pending',
'expired'
  );
looks more readable, but there is need for one more join as well..
..so the only question is:
drop the the serials as PKs and just use short text handles? our 
database size is arround 290 mb. there shouldn't be speed issues as long 
as proper indexes exit, right? some of the conditions are dynamic 
though, so there can't be an proper index in any case. any other concerns?

thanks in advance
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] Get timestamp as UTC

[Michael Glaesemann]

On Apr 22, 2005, at 2:51, Steve - DND wrote:
I was under the impression that using timezone('UTC', 
now())::timestamptz
would give me the current UTC time, with timezone offset. Instead I am
getting the UTC time, but with an offset of -07(my local time). How do 
I get
UTC time, with the 0 offset that it should be?

If you want the server to return time zone information respective of 
another time zone, I believe you'll need to use SET TIME ZONE. 
Otherwise, the server automatically (converts the timestamp) and 
returns the time zone of the server.

For example,
test=# select (current_timestamp at time zone 'UTC') at time zone 'UTC';
   timezone
---
 2005-04-22 16:26:57.209082+09
(1 row)
test=# set time zone 'UTC';
SET
test=# select (current_timestamp at time zone 'UTC') at time zone 'UTC';
   timezone
---
 2005-04-22 07:27:55.841596+00
(1 row)
test=# select (current_timestamp at time zone 'UTC');
  timezone

 2005-04-22 07:28:48.888154
(1 row)
test=# select (current_timestamp at time zone 'UTC')::timestamptz;
   timezone
---
 2005-04-22 07:38:19.979511+00
(1 row)
I'm using at alternative form of the timezone() function. It should 
work the same. I'm also using current_timestamp which follows the SQL 
spec, now() is PostgreSQL-specific, though it works the same.

As you'll notice, timezone() applied to a timestamptz returns a 
timestamp; applied to a timestamp, timezone() returns timestamptz.

In the first example, I've applied the function twice to return 
timestamptz. The server returns it in the server time zone (in my case, 
JST). I then set the server time zone to UTC. (I believe this is only 
for my session. Other sessions are unaffected.) In the second example, 
you can see it returns the timestamp at UTC. In the third example, I've 
only applied AT TIME ZONE once, so it returns a timestamp, and you can 
see that it's relative to UTC.

In you example, as there is no specified timezone  (as the timezone() 
call returns a timestamp without time zone), it applies the time zone 
of the server.

In the example I've given, timezone the outermost timezone() is 
returning timestamptz at UTC, so the ::timestamptz cast is not needed. 
In your case, the timestamptz basically does the same thing: it returns 
a timestamptz at the server time zone.

Internally, timestamptz is represented the same regardless of time 
zone. The returned representation of that timestamptz is dependent on 
the server settings. To avoid this kind of hassle, I usually return the 
Unix epoch using EXTRACT(epoch from current_timestamp) and format the 
timestamptz in my application.

Hope this helps.
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

[GENERAL] FW: How to install Postgres that supports 64-bit integer/date-time.

[Dinesh Pandey]

Continued…..

 

I solved this
problem by myself.

 

A.
For 64 bit development you need these packages installed on
Solaris server:

SUNWarcx, SUNWbtoox, SUNWdplx,
SUNWscpux, SUNWsprox,
SUNWtoox, SUNWlmsx, SUNWlmx, SUNWlibCx

 

Pls confirm these using the
following command.

pkginfo SUNWarcx

pkginfo SUNWbtoox

pkginfo SUNWdplx

pkginfo SUNWscpux

pkginfo SUNWsprox,

pkginfo SUNWtoox

pkginfo SUNWlmsx

pkginfo SUNWlmx

pkginfo SUNWlibCx

 

B.
Install missing Solaris packages.

 
Insert Solaris software CD 1/2.

 

Log in as root.

  

pkgadd -d /cdrom/Solaris_9/Product
<>

 

eject cdrom

 

 

C.
Check Solaris OS mode and supported bits

isainfo -v   

   64-bit sparcv9 applications   32-bit sparc applications 

D.
To compile a 64-bit application on an UltraSparc with a
recent Sun Compiler.

   getconf -a | grep v9    XBS5_LP64_OFF64_CFLAGS:    -xarch=v9   XBS5_LP64_OFF64_LDFLAGS:   -xarch=v9   ….   …. 

E.
The transitional compilation environment is obtained with the
following compiler and linker flags:

   getconf LFS64_CFLAGS     -D_LARGEFILE64_SOURCE 

F.
The large file compilation environment is obtained with (A
64-bit application automatically has large file support built in by default.)

   getconf LFS_CFLAGS  -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 

G.
Re-Configure the Postgres with 64-bit integer date/time
support  from its source:

   bash-2.03# cd postgres 8.0.1/   bash-2.03# ./configure --enable-integer-datetimes --prefix=/usr/local/pgsql --with-perl --with-tclconfig=/usr/local/lib --with-tcl      checking build system type... sparc-sun-solaris2.8   checking host system type... sparc-sun-solaris2.8   checking which template to use... solaris checking whether to build with 64-bit integer date/time support... yes   checking whether NLS is wanted... no   checking for default port number... 5432   .…   ….   bash-2.03# make install

 

Kindly let me know if you need any further clarification
or any problem occurs.

 

 



Thanks

Dinesh Pandey











From: Dinesh Pandey
[mailto:[EMAIL PROTECTED]] 
Sent: Friday, April 22, 2005 11:00
AM
To: 'PostgreSQL';
'pgsql-general@postgresql.org'
Subject: How to install Postgres
that supports 64-bit integer/date-time.



 

How to install Postgres
8.0.1 that supports 64-bit integer/date-time.

 

#
./configure --prefix=/usr/local/pgsql --with-tclconfig=/usr/local/lib
--with-tcl 

checking build
system type... sparc-sun-solaris2.8

checking host system
type... sparc-sun-solaris2.8

checking which
template to use... solaris

checking
whether to build with 64-bit integer date/time support... no

checking whether NLS
is wanted... no

checking for default
port number... 5432

checking for gcc...
gcc

 

while installation I am getting this message:” checking whether
to build with 64-bit integer date/time support... no”

 

Thanks

 

Re: [GENERAL] Use of temporary tables in functions

[Patrick . FICHE]

My 
Postgresql version was 7.2.3.
I 
guess that this was fixed in a more recent version but I could not find it in 
the version history...
Could 
anybody confirm that this was fixed.
 
Patrick
 

--- 
Patrick Fiche email : [EMAIL PROTECTED] tél : 01 69 29 36 
18 --- 


  -Original Message-From: 
  [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]On Behalf Of 
  [EMAIL PROTECTED]Sent: jeudi 21 avril 2005 
  15:33To: pgsql-general@postgresql.orgSubject: [GENERAL] 
  Use of temporary tables in functions
  Hi,
   
  I had some issues 
  with previous versions of Postgresql using temporary tables in 
  functions.
  The main issue was 
  that the both indexes pg_attribute_relid_attnam_index and 
  pg_attribute_relid_attnum_index were always growing and I had to restart 
  postgresql in single mode and run a REINDEX command to free some space on the 
  disk.
   
  Does the problem 
  still exist in version 8 ?
  Thanks for your 
  answers.
   
  Patrick
  
  --- 
  Patrick Fiche email : [EMAIL PROTECTED] tél : 01 69 29 36 
  18 ---