Re: [GENERAL] pl/sql resources for pl/pgsql?

[Gurjeet Singh]

On Sat, Jun 20, 2009 at 10:52 AM, Jasen Betts  wrote:

> On 2009-06-16, gvimrc  wrote:
> > I'm fairly new to PostgreSQL and completely new to using pl/pgsql
> > though I've used MySQL's procedural language a little.
> > I heard pl/pgsql is similar to Oracle's pl/sql so would it be possible,
> > given that pl/pgsql literature is a bit thin on the ground, to use books
> > on pl/sql for developing pl/pgsql code?
>
> For inspiration perhaps, not as a textbook.
> differences from oracle:
> http://www.postgresql.org/docs/8.3/interactive/plpgsql-porting.html
>
> the postgresql manual chapter on pl-pgsql (and the preceeding chapters)
> contain all that you really need.
>
> pl-pgsql chapter:
> http://www.postgresql.org/docs/8.3/interactive/plpgsql.html
> full manual:
> http://www.postgresql.org/docs/8.3/interactive/index.html
>
> plpgsql is much like any other procedural language
> only you can embed SQL queries very very easily
>
> some hints you may find useful:
>
> The syntax checker doesn't always give useful advice when it rejects
> your code so develop a habit of step-wise testing.
>
> if you say "IF" and forget to say "THEN" it will lead a confusing
> error message.
>
> "ELSIF" (and "ELSEIF") are different to "ELSE IF" in the number of
> "END IF"s youll need to use later.
>
> -- sql comments and
> /* c-style
> comments */ can both be used.
>
>
And a major one is, that it cannot detect semantic error's (like missing
table, or wrong column name, or wrong expression assignment) until you
execute the function. This implies that if you have branches in code, say IF
.. THEN .. ELSE .. END IF then you will not see errors from a branch until
that branch of code is executed.

Best regards,
-- 
Lets call it Postgres

EnterpriseDB  http://www.enterprisedb.com

gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
Mail sent from my BlackLaptop device

Re: [GENERAL] How to use PQfn() in libpq library?

[Bruce YUAN]

Oh. Many thanks!
Bruce
2009/6/20 Alvaro Herrera 

> Bruce YUAN escribió:
> > Hi Tom,
> >
> > Which interface will replace this? And where can get it's reference?
>
> Try reading beyond the comma in the quoted paragraph:
>
> > >This interface is somewhat obsolete, as one can achieve similar
> > >performance and greater functionality by setting up a prepared
> > >statement to define the function call.  Then, executing the
> statement
> > >with binary transmission of parameters and results substitutes for a
> > >fast-path function call.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/ 
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

Re: [GENERAL] pl/sql resources for pl/pgsql?

[Jasen Betts]

On 2009-06-16, gvimrc  wrote:
> I'm fairly new to PostgreSQL and completely new to using pl/pgsql
> though I've used MySQL's procedural language a little.
> I heard pl/pgsql is similar to Oracle's pl/sql so would it be possible, 
> given that pl/pgsql literature is a bit thin on the ground, to use books
> on pl/sql for developing pl/pgsql code?

For inspiration perhaps, not as a textbook. 
differences from oracle:
http://www.postgresql.org/docs/8.3/interactive/plpgsql-porting.html

the postgresql manual chapter on pl-pgsql (and the preceeding chapters)
contain all that you really need.

pl-pgsql chapter:
http://www.postgresql.org/docs/8.3/interactive/plpgsql.html
full manual:
http://www.postgresql.org/docs/8.3/interactive/index.html

plpgsql is much like any other procedural language 
only you can embed SQL queries very very easily 

some hints you may find useful:

The syntax checker doesn't always give useful advice when it rejects
your code so develop a habit of step-wise testing.

if you say "IF" and forget to say "THEN" it will lead a confusing
error message.

"ELSIF" (and "ELSEIF") are different to "ELSE IF" in the number of 
"END IF"s youll need to use later.

-- sql comments and 
/* c-style 
comments */ can both be used.

bye.

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

Re: [GENERAL] Disaster recovery (server died)

[Uwe C. Schroeder]

On Friday 19 June 2009, Scott Marlowe wrote:
> On Fri, Jun 19, 2009 at 8:43 PM, Miguel
>
> Miranda wrote:
> > Well, i just didnt explain in detail, what i have is just the 16897
> > directory where i was storing the database, i tried just copying the
> > files but it didnt work,
> > should it be posible to import this database is any way?
>
> Nope, you need the whole data directory.

What I don't get is this: you said your CPU died. For me that's the processor 
or maybe some interpret that as the main board.
So why don't you grab the harddisk from that server and plug it into the new 
one? Maybe something might be corrupt due to the failure, but most of the 
data should be on the disk (unless you use disks which lie about fsync).
Yep - another reason why one has at least a daily backup (in my case 2 
replicas for every production server I run. I never had a major failure in 
over 15 years - knock on wood - but if that happens I don't lose a heck of a 
lot due to the backups and slony replicas)


Uwe

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

Re: [GENERAL] Dynamic table

[Jasen Betts]

On 2009-06-17, A B  wrote:
>> Your problem is currently sounding very much like an exam question; you
>> seem to be arbitrarily making decisions without showing any real data.
>> When you deal with real problems in the real world you're normally
>> making compromises when you model things and hence the decisions
>> wouldn't be as forced as you're making it.  Design is about picking and
>> choosing between compromises and without knowing what the choices are
>> you can't design anything, this is one of the problems with tests.
>
> I wish it was just an exam question
>
> Show real data?
> Well here it is:
>
> customer | value1 | value2 | value3|
> 1 5   3  4
> 2 8   2  10
>
> I hope you can believe me when I say that the names
> value1,value2,value3 really are impossible to relate. I will not
> decide upon those. Someone else is going to do that in the future. I
> just have to make a structure that can handle all cases from "shoe
> size" to "number of atoms in persons body"   (well, perhaps I can say
> that the values will be in the 0...100 range, but that's about it.
> There is really nothing else I know about how the system will be used
> by other people. :-( I just know that I have to make them enter data
> like this since it is a third party that need this kind of data.
>
>yes I took some random values for the "real data" since I
> don't know anything else about the data. 

I like the table-per-column approach 

And also the array approach, arrays of numbers of the sizes you discuss
are much faster than hard disks

what sorts of querys will be most common




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

Re: [GENERAL] postgresql-8.3.7 unexpected connection closures

[Craig Ringer]

On Fri, 2009-06-19 at 10:49 -0600, Scott Marlowe wrote:
> On Thu, Jun 18, 2009 at 7:30 PM, Andrew
> Maclean wrote:
> > One is running McAfee and the other is running Symantec Endpoint. It
> > does not matter whether the firewalls are on or off.
> 
> Note that many packages for windows that do networking and virus
> scanning installed "wedge" dlls that are always part of the code path
> of the OS whether the firewall / antivirus are active or not.
> Uninstalling is the only way to KNOW they aren't affecting your
> computer.

... assuming the uninstaller was competently written.
*ahem*logitechwebcam*ahem* . Sysinternals Process Explorer and regedit
for actually letting me find and remove that stunning piece of garbage
and its scarily buggy and badly designed hook/wedge DLL. (My webcam
driver broke Pg, gcc, and several other things.)

These days most virus scanners and software firewalls seem to be OK, but
a few years ago I used to have to manually remove leftover bits of
ZoneAlarm in particular from users' machines then reset their IP stack
("net interface ipv4 reset c:\resetlog.txt") to get their networking
going again.

It's still beyond me why anyone installs a software firewall on modern
Windows. The built-in firewall is _perfectly_ reliable and eminently
sufficient. It just works, and stays out of the way. Why replace it with
something that's inevitably buggy, less than perfectly integrated, and
too busy proclaiming to the user "I'm doing something, I'm doing
something".

--
Craig Ringer


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

Re: [GENERAL] connecting to a remote pq always require a password

[Jasen Betts]

On 2009-06-18, David Shen  wrote:
> Hi,
>
> I am trying to use the libpq to connect to my postgresql 8.3 server.
> If I use "dbname = mydb", the connection made successfully because I
> am using a socket connection. But if I use "host = 127.0.0.1 dbname =
> mydb", the error message is "no password supplied".
>
> In the pg_hba.conf file, I even change the host access control to this:
> hostall all 127.0.0.1/32  trust
>
> but it still does not work. What I missed?

postgres doesn't know who you are! specify a user in the connection
string.

you can use "ident sameuser" over TCP connections but it is no more
secure than the originating host. (it works via RFC1413)


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

Re: [GENERAL] Disaster recovery (server died)

[Scott Marlowe]

On Fri, Jun 19, 2009 at 8:43 PM, Miguel
Miranda wrote:
> Well, i just didnt explain in detail, what i have is just the 16897
> directory where i was storing the database, i tried just copying the files
> but it didnt work,
> should it be posible to import this database is any way?

Nope, you need the whole data directory.

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

Re: [GENERAL] Disaster recovery (server died)

[Madison Kelly]

Miguel Miranda wrote:
Well, i just didnt explain in detail, what i have is just the 16897 
directory where i was storing the database, i tried just copying the 
files but it didnt work,

should it be posible to import this database is any way?

the Os is Freebsd 6.2 and PG version is 8.1.3
thank you.


I am not familiar with FreeBSD's directory structure, so if someone 
pipes up, take their word over mind. However;


Your backups, what is the root directory(ies)? Ie: Did you backup 
'/var/lib/postgresql', '/etc/postgres*', ?


You should be able to stop postgres, use a tool like 'rsync' to copy the 
data back into place, then restart postgres. Something like:


/etc/init.d/postgresql stop
rsync -av /backup/var/lib/postgresql /var/lib/
rsync -av /backup/etc/postgres* /etc/
/etc/init.d/postgresql start

At this point, you should be golden. Not that it matters now, but why 
had you not been using pg_dump to do periodic backups? How big is the 
database?


lastly, depending on the value of the database, you may want to look at 
hiring someone to help you. Also, make sure you are recovering to the 
same versions of the OS and PostgreSQL that you old server had. This is 
not the time to be doing an upgrade. :)


Madi

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

Re: [GENERAL] Disaster recovery (server died)

[Miguel Miranda]

Well, i just didnt explain in detail, what i have is just the 16897
directory where i was storing the database, i tried just copying the files
but it didnt work,
should it be posible to import this database is any way?

the Os is Freebsd 6.2 and PG version is 8.1.3
thank you.

On Fri, Jun 19, 2009 at 3:46 PM, Madison Kelly  wrote:

> Miguel Miranda wrote:
>
>> Hi, the worst have ocurred, my server died (cpu), so i reinstalled another
>> server with the same postgres version.
>> I have the old data directory from the old server, how can i restore my
>> databases from this directory to the new one?
>> I dont have a backup (pg_dump,etc), just the main previus live data
>> directory from the old server.
>> best regards
>>
>
> First, make a copy. DO NOT USE your backup until done. :)
>
> Now then, assumin *nix;
>
> stop the postgresql daemon, copy the data directories into place and
> restart the daemon. Be sure to restore your config files like pg_hba.conf
> and such while the daemon is stopped.
>
> If your backup is in a consistent (or recoverable) state, you should be
> golden. For more specific instructions, post your OS and PgSQL versions.
>
> Best of luck!
>
> Madi
>
>
>

Re: [GENERAL] Disaster recovery (server died)

[Madison Kelly]

Miguel Miranda wrote:
Hi, the worst have ocurred, my server died (cpu), so i reinstalled 
another server with the same postgres version.
I have the old data directory from the old server, how can i restore my 
databases from this directory to the new one?
I dont have a backup (pg_dump,etc), just the main previus live data 
directory from the old server.

best regards


First, make a copy. DO NOT USE your backup until done. :)

Now then, assumin *nix;

stop the postgresql daemon, copy the data directories into place and 
restart the daemon. Be sure to restore your config files like 
pg_hba.conf and such while the daemon is stopped.


If your backup is in a consistent (or recoverable) state, you should be 
golden. For more specific instructions, post your OS and PgSQL versions.


Best of luck!

Madi



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

Re: [GENERAL] Disaster recovery (server died)

[Alan Hodgson]

On Friday 19 June 2009, Miguel Miranda  wrote:
> Hi, the worst have ocurred, my server died (cpu), so i reinstalled
> another server with the same postgres version.
> I have the old data directory from the old server, how can i restore my
> databases from this directory to the new one?
> I dont have a backup (pg_dump,etc), just the main previus live data
> directory from the old server.
> best regards

Just copy it into place where the new postmaster expects to live.

-- 
WARNING:  Do not look into laser with remaining eye.

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

Re: [GENERAL] FYI: Load times for a largish DB in 8.2 vs. 8.3 vs. 8.4

[Todd A. Cook]

Tom Lane wrote:

"Todd A. Cook"  writes:

First, the numbers:



  PG VersionLoad time pg_database_size autovac
--
   8.2.13179 min   92,807,992,820on
   8.3.7 180 min   84,048,744,044on (defaults)
   8.4b2 206 min   84,028,995,344on (defaults)
   8.4b2 183 min   84,028,839,696off



The bulk of the data is in 16 tables, each having about 55 million rows of
the form (int, int, smallint, smallint, int, int, int).  Each table has a
single partial index on one of the integer columns.


Given that it's multiple tables, it'd be possible for autovacuum to
kick in and ANALYZE the data inserted into earlier tables while the
later ones were still being loaded.  If so, the discrepancy might be
explained by 8.4's more-aggressive statistics target, which means that
a background ANALYZE will take about 10x more work than before.

If you have time to repeat the experiments, it would be interesting to
see what happens with consistent default_statistics_target across 8.3
and 8.4.


That would seem to be it:

 8.4b2  183 min  84,028,897,040 on (defaults, 
default_statistics_target=10)

I'll run the test on 8.3.7 with default_statistics_target=100 over the weekend.

-- todd




regards, tom lane
.




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

[GENERAL] Disaster recovery (server died)

[Miguel Miranda]

Hi, the worst have ocurred, my server died (cpu), so i reinstalled another
server with the same postgres version.
I have the old data directory from the old server, how can i restore my
databases from this directory to the new one?
I dont have a backup (pg_dump,etc), just the main previus live data
directory from the old server.
best regards

Re: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ?

[leif]

   Hi again,

   Will do. Thanks again,

 Leif


- "Albe Laurenz"  wrote:

> leif wrote:
> >Thank you for precise answer. And yes, I have (at least) 2 
> > connections, all named. So I am even not using the 'default' 
> > connection prepared statement as you point out.
> > 
> >I have looked a little further into the output of ecpg as 
> > well as adding the "AT " to my statements. Adding 
> > the "AT" to the prepare statement seems to have fixed the 
> > named error :-). However, after that I'm not able to compile 
> > my stuff on the 8.2 installation. Is there a way to 'detect' 
> > (using #if  ) whether I am using 8.2 or 8.3+ ?
> 
> You could #include  and check PG_VERSION_NUM.

Ah, yes.
> 
> >I also tried to put the "AT " on the the 
> > ALLOCATE/DEALLOCATE DESCRIPTOR statements, which was accepted 
> > for the ALLOCATE, but gave an error for the DEALLOCATE. I can 
> > see in the ecpg output that the "AT" is not (yet?) used for 
> > these statements, so I guess that it is ok to leave them as is?
> 
> I tend to agree; I'd say that descriptors are not associated with
> connections, they are something that "lives" on the client side.
> But I don't know for certain.
> 
> I would say that you should leave them without AT, but it is
> weird that ALLOCATE lets you use AT without complaining.
> 
> >Are all this documented somewhere ?
> 
> The documentation seems to be a bit vague on these things...
> Looking at the C output and the source helps...

The "real" documentation ;-)

> 
> Yours,
> Laurenz Albe

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

[GENERAL] looping over a small record set over and over in a function

[Ivan Sergio Borgonovo]

I've a record set on which I have to loop several times.
The function should return the same record set + one more computed
field.
Something that in could look like:

foreach(row) {
  // compute stuff
  if(...) {
  }
  // place stuff in field[N+1] of the row
}
if(some condition) {
  //
}
foreach(row) {
  // compute stuff
  if(...) {
  }
  // place stuff in a field[N+1] of the row
}
if(some condition) {
  //
}
...

actually return row + computed field.

in pgplsql

where each loop depends on the result of the previous.
The dataset is very small.


If I could easily load all the dataset into an array, loop through
it and then just update the computed field it would be nice... but
how?
Is it really worth to load the whole record set in an array, loop
over etc... in spite of eg. building a temp table with the same
structure of the input record set + 1 field, loop over the table
etc... what about all the UPDATEs involved to change field N+1 of
the temp table? Will be they expensive?

thanks

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


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

Re: [GENERAL] FYI: Load times for a largish DB in 8.2 vs. 8.3 vs. 8.4

[Tom Lane]

Stefan Kaltenbrunner  writes:
> Tom Lane wrote:
>> If you have time to repeat the experiments, it would be interesting to
>> see what happens with consistent default_statistics_target across 8.3
>> and 8.4.

> given that this was likely a single-thread restore and therefor wal 
> logged I wonder if the 206min one might be affected by the issue 
> discussed here 
> http://archives.postgresql.org/pgsql-hackers/2009-06/msg01133.php

It might be, but the current theory about that (ie that it's the
bulkwrite patch doing it) doesn't explain the interaction with
autovacuum on/off.  I'd still like to hear whether changing the stats
target changes Todd's results.

regards, tom lane

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

Re: [GENERAL] FYI: Load times for a largish DB in 8.2 vs. 8.3 vs. 8.4

[Stefan Kaltenbrunner]

Tom Lane wrote:

"Todd A. Cook"  writes:

First, the numbers:



  PG VersionLoad time pg_database_size autovac
--
   8.2.13179 min   92,807,992,820on
   8.3.7 180 min   84,048,744,044on (defaults)
   8.4b2 206 min   84,028,995,344on (defaults)
   8.4b2 183 min   84,028,839,696off



The bulk of the data is in 16 tables, each having about 55 million rows of
the form (int, int, smallint, smallint, int, int, int).  Each table has a
single partial index on one of the integer columns.


Given that it's multiple tables, it'd be possible for autovacuum to
kick in and ANALYZE the data inserted into earlier tables while the
later ones were still being loaded.  If so, the discrepancy might be
explained by 8.4's more-aggressive statistics target, which means that
a background ANALYZE will take about 10x more work than before.

If you have time to repeat the experiments, it would be interesting to
see what happens with consistent default_statistics_target across 8.3
and 8.4.


given that this was likely a single-thread restore and therefor wal 
logged I wonder if the 206min one might be affected by the issue 
discussed here 
http://archives.postgresql.org/pgsql-hackers/2009-06/msg01133.php



Stefan

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

Re: [GENERAL] postgresql-8.3.7 unexpected connection closures

[justin]

Pavel Stehule wrote:

  2009/6/19 Leif B. Kristensen :
  
  
nobody needs Windows. But Postgres has only one reputation. Problems
on windows are PostgreSQL's problem too.

And there are some native windows firms that starts develop with
Postgres. And this people expecting stability. So if we support some,
we have to do it well.

regards
Pavel Stehul

i've been running Postgresql on windows for a few years now and did not
suffer these problems . I have dealt with these problems running MsSQL
7 and 2000.   

If any software is having a specific problem with windows I guarantee
the MS offering is having the same problem.  Just replace X application
with the  MS offering you will have possible list of solutions.  

Re: [GENERAL] How to use PQfn() in libpq library?

[Alvaro Herrera]

Bruce YUAN escribió:
> Hi Tom,
> 
> Which interface will replace this? And where can get it's reference?

Try reading beyond the comma in the quoted paragraph:

> >This interface is somewhat obsolete, as one can achieve similar
> >performance and greater functionality by setting up a prepared
> >statement to define the function call.  Then, executing the statement
> >with binary transmission of parameters and results substitutes for a
> >fast-path function call.

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

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

Re: [GENERAL] postgresql-8.3.7 unexpected connection closures

[justin]

Craig Ringer wrote:

  On Fri, 2009-06-19 at 01:03 -0400, Tom Lane wrote:
  
  I see lots of questions here that seem to be related to (a) virus
scanner interference and (b) installation/reinstallation. Lots of the
reinstall issues seem to be with people who don't really understand NT
users, ACLs, etc and aren't really competent to admin a machine, but
they do make me wonder if the Pg installer can do more to help them out,
eg:
  

I'm hesitant to agree  with this  the PG installer doing more 
automatically.  If the user does not understand security and proper
configuration to get it to work on windows client, the server setup
will be poorly configured with security problems that a MAC truck can
drive through. 

Example is MS itself and CAD developers.  I have yet to see a CAD
program that does not require to run with Administrator security
credentials.   MS with many of its Server Programs does similar stuff
now that PG does automatically creates users sets up the
directory/registry security.   Its really easy to trash those setting
keeping the app from working.   

You hear about the same problems many people talk about hear as they do
with the other databases running on windows see 
http://support.microsoft.com/kb/309422
http://support.microsoft.com/kb/287932
http://msdn.microsoft.com/en-us/library/ms175043.aspx

The only thing i think that would make sense, is to have the
installer add exceptions to the windows firewall for the
Postgresql ports.  


  
"The data directory you have specified (C:\PgData) already exists, but
cannot be accessed by the user you want to run PostgreSQL as. Would you
like to: [a] use a new data directory, [b] grant the postgresql user the
rights to access the data directory you have specified, or [c] change
the user you start PostgreSQL as to the user owning the data directory ?
[clean install to new directory][grant access to old directory][change
postgresql user][cancel installation]"
  

This is the same problem MSSQL has on reinstall except it just creates
new directories and Security Credentials automatically.  I have seen
computers with 5 SQLServer user accounts.
along with lots of other crap laying around

PG install does lots of the stuff for the user but does not do
everything as many installers do which can leave the OS install a
complete and utter mess requiring reformat and reinstall to get it to
work at all. Or leaves that applications install such tangle mess its
impossible to figure out what the user did.  

I can read it know "Postgresql gurus i go to the PGDATA directory and
find it has PGDATA though PGDATA_8  how do i tell which is the current
data directory."    I have suffered this question with MsSQL.  All that
is accomplish is changing the question asked.  
     


  
"The data directory you have specified (C:\PgData) contains a database
from an older version of PostgreSQL (8.2) that this version (8.3.6)
cannot access. Would you like to use a new data directory C:\PgData-8.3,
leaving the old one untouched? Note that PostgreSQL will not
automatically convert your data. You REALLY should read the upgrading
documentation before continuing. [clean install to new data
directory][cancel installation]"

"PostgreSQL has detected that another program, probably an older version
of PostgreSQL, is listening on port 5432. If you want to use this
version on the default port 5432, you will need to stop or uninstall the
other program first. [Change PostgreSQL port][Cancel installation]"
  


I agree more verbose install messages on errors and warnings would be
nice.  


  


Of course, if Windows development is un-fun, windows program
installation and installer building is more so.
  


That is very true


  
--
Craig Ringer


  

Re: [GENERAL] postgresql-8.3.7 unexpected connection closures

[Scott Marlowe]

On Thu, Jun 18, 2009 at 7:30 PM, Andrew
Maclean wrote:
> One is running McAfee and the other is running Symantec Endpoint. It
> does not matter whether the firewalls are on or off.

Note that many packages for windows that do networking and virus
scanning installed "wedge" dlls that are always part of the code path
of the OS whether the firewall / antivirus are active or not.
Uninstalling is the only way to KNOW they aren't affecting your
computer.

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

Re: [GENERAL] postgresql-8.3.7 unexpected connection closures

[Pavel Stehule]

2009/6/19 Leif B. Kristensen :
> On Friday 19. June 2009, Andrew Maclean wrote:
>>I would NEVER run a production server in windows!
>>
>>These are just laptops/workstations that are used for development e.g,
>> when network connections are not available or when travelling.
>
> Both my workstation and laptop have run Linux since 2003, and work
> great. Who needs Windows?

nobody needs Windows. But Postgres has only one reputation. Problems
on windows are PostgreSQL's problem too.

And there are some native windows firms that starts develop with
Postgres. And this people expecting stability. So if we support some,
we have to do it well.

regards
Pavel Stehule

> --
> Leif Biberg Kristensen | Registered Linux User #338009
> Me And My Database: http://solumslekt.org/blog/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

Re: [GENERAL] postgresql-8.3.7 unexpected connection closures

[Leif B. Kristensen]

On Friday 19. June 2009, Andrew Maclean wrote:
>I would NEVER run a production server in windows!
>
>These are just laptops/workstations that are used for development e.g,
> when network connections are not available or when travelling.

Both my workstation and laptop have run Linux since 2003, and work 
great. Who needs Windows?
-- 
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/

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

Re: [GENERAL] How to use PQfn() in libpq library?

[Bruce YUAN]

Hi Tom,

Which interface will replace this? And where can get it's reference?
Best regards,
Bruce
2009/6/19 Tom Lane 

> Bruce YUAN  writes:
> > How to use PQfn() in libpq library?
>
> Look into src/interfaces/libpq/fe-lobj.c for several examples.
> However, please note the comment in the documentation:
>
>This interface is somewhat obsolete, as one can achieve similar
>performance and greater functionality by setting up a prepared
>statement to define the function call.  Then, executing the statement
>with binary transmission of parameters and results substitutes for a
>fast-path function call.
>
> > Which server functions can be
> > executed from client?
>
> Anything you could call normally can be called this way too.
>
>regards, tom lane
>

Re: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ?

[Albe Laurenz]

leif wrote:
>Thank you for precise answer. And yes, I have (at least) 2 
> connections, all named. So I am even not using the 'default' 
> connection prepared statement as you point out.
> 
>I have looked a little further into the output of ecpg as 
> well as adding the "AT " to my statements. Adding 
> the "AT" to the prepare statement seems to have fixed the 
> named error :-). However, after that I'm not able to compile 
> my stuff on the 8.2 installation. Is there a way to 'detect' 
> (using #if  ) whether I am using 8.2 or 8.3+ ?

You could #include  and check PG_VERSION_NUM.

>I also tried to put the "AT " on the the 
> ALLOCATE/DEALLOCATE DESCRIPTOR statements, which was accepted 
> for the ALLOCATE, but gave an error for the DEALLOCATE. I can 
> see in the ecpg output that the "AT" is not (yet?) used for 
> these statements, so I guess that it is ok to leave them as is?

I tend to agree; I'd say that descriptors are not associated with
connections, they are something that "lives" on the client side.
But I don't know for certain.

I would say that you should leave them without AT, but it is
weird that ALLOCATE lets you use AT without complaining.

>Are all this documented somewhere ?

The documentation seems to be a bit vague on these things...
Looking at the C output and the source helps...

Yours,
Laurenz Albe

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

Re: [GENERAL] How to use PQfn() in libpq library?

[Tom Lane]

Bruce YUAN  writes:
> How to use PQfn() in libpq library?

Look into src/interfaces/libpq/fe-lobj.c for several examples.
However, please note the comment in the documentation:

This interface is somewhat obsolete, as one can achieve similar
performance and greater functionality by setting up a prepared
statement to define the function call.  Then, executing the statement
with binary transmission of parameters and results substitutes for a
fast-path function call.

> Which server functions can be
> executed from client?

Anything you could call normally can be called this way too.

regards, tom lane

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

[GENERAL] How to use PQfn() in libpq library?

[Bruce YUAN]

Hi All,

How to use PQfn() in libpq library?

#include 
PGresult *PQfn(PGconn* conn,
   int fnid,
   int *result_buf,
   int *result_len,
   int result_is_int,
   const PQArgBlock *args,
   int nargs);
Who can share a detailed example source code? Which server functions can be
executed from client?
Many thanks!

Bruce

Re: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ?

[leif]

   Hi Albe,

   Thank you for precise answer. And yes, I have (at least) 2 connections, all 
named. So I am even not using the 'default' connection prepared statement as 
you point out.

   I have looked a little further into the output of ecpg as well as adding the 
"AT " to my statements. Adding the "AT" to the prepare statement 
seems to have fixed the named error :-). However, after that I'm not able to 
compile my stuff on the 8.2 installation. Is there a way to 'detect' (using #if 
 ) whether I am using 8.2 or 8.3+ ?

   I also tried to put the "AT " on the the ALLOCATE/DEALLOCATE 
DESCRIPTOR statements, which was accepted for the ALLOCATE, but gave an error 
for the DEALLOCATE. I can see in the ecpg output that the "AT" is not (yet?) 
used for these statements, so I guess that it is ok to leave them as is?

   Are all this documented somewhere ?

   Once again, thank you for pointing out the problem.

 Leif


- "Albe Laurenz"  wrote:

> l...@crysberg.dk wrote:
> >I have a problem when upgrading from 8.2.4 to 8.3/8.4 using ecpg
> with a prepare statement:
> > 
> > EXEC SQL PREPARE execquery FROM :stmt; line = __LINE__;
> > EXEC SQL AT :_thisDbConn DECLARE execcurs CURSOR FOR execquery; line
> = __LINE__;
> > EXEC SQL AT :_thisDbConn OPEN execcurs; line = __LINE__;
> > EXEC SQL ALLOCATE DESCRIPTOR execdesc; line = __LINE__;
> > 
> >I get an "SQL error: invalid statement name "(null)" on line ##"
> (3rd line above) on the OPEN cursor statement.
> 
> You have two different connections, right?
> And you PREPARE the statement on one connection and then use it on the
> other, right?
> 
> This used to work because prepared statements were treated as global
> in 8.2, while from
> 8.3 on a prepared statement belongs to a specific connection.
> 
> The change in the source code was made here:
> http://archives.postgresql.org/pgsql-committers/2007-09/msg00408.php
> 
> This makes prepared statements thread-safe, which is more sane
> anyway.
> 
> If you look at the C file output by the preprocessor, you'll find
> in 8.2 something like:
> 
>   { ECPGdo(__LINE__, 0, 1, _thisDbConn, "declare execcurs  cursor 
> for ?", 
>  
> ECPGt_char_variable,(ECPGprepared_statement("execquery")),(long)1,(long)1,(1)*sizeof(char),
> 
>   ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
> 
> and in 8.4 something like:
> 
>   { ECPGdo(__LINE__, 0, 1, _thisDbConn, 0, ECPGst_normal, "declare
> execcurs cursor for $1", 
>   ECPGt_char_variable,(ECPGprepared_statement(_thisDbConn,
> "execquery", __LINE__)),(long)1,(long)1,(1)*sizeof(char), 
>   ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
> 
> So you see, the ECPGprepared_statement function used to treate a
> prepared statement
> as something global rather than belonging to a certain connection.
> 
> 
> The solution is to fix your program so that it uses a prepared
> statement
> only on the connection where you prepared it.
> 
> Yours,
> Laurenz Albe

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

Re: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ?

[Albe Laurenz]

l...@crysberg.dk wrote:
>I have a problem when upgrading from 8.2.4 to 8.3/8.4 using ecpg with a 
> prepare statement:
> 
> EXEC SQL PREPARE execquery FROM :stmt; line = __LINE__;
> EXEC SQL AT :_thisDbConn DECLARE execcurs CURSOR FOR execquery; line = 
> __LINE__;
> EXEC SQL AT :_thisDbConn OPEN execcurs; line = __LINE__;
> EXEC SQL ALLOCATE DESCRIPTOR execdesc; line = __LINE__;
> 
>I get an "SQL error: invalid statement name "(null)" on line ##" (3rd line 
> above) on the OPEN cursor statement.

You have two different connections, right?
And you PREPARE the statement on one connection and then use it on the other, 
right?

This used to work because prepared statements were treated as global in 8.2, 
while from
8.3 on a prepared statement belongs to a specific connection.

The change in the source code was made here:
http://archives.postgresql.org/pgsql-committers/2007-09/msg00408.php

This makes prepared statements thread-safe, which is more sane anyway.

If you look at the C file output by the preprocessor, you'll find
in 8.2 something like:

  { ECPGdo(__LINE__, 0, 1, _thisDbConn, "declare execcurs  cursor  for ?", 
  
ECPGt_char_variable,(ECPGprepared_statement("execquery")),(long)1,(long)1,(1)*sizeof(char),
 
  ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);

and in 8.4 something like:

  { ECPGdo(__LINE__, 0, 1, _thisDbConn, 0, ECPGst_normal, "declare execcurs 
cursor for $1", 
  ECPGt_char_variable,(ECPGprepared_statement(_thisDbConn, "execquery", 
__LINE__)),(long)1,(long)1,(1)*sizeof(char), 
  ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);

So you see, the ECPGprepared_statement function used to treate a prepared 
statement
as something global rather than belonging to a certain connection.


The solution is to fix your program so that it uses a prepared statement
only on the connection where you prepared it.

Yours,
Laurenz Albe

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

Re: [GENERAL] postgres -- monitor and suggestions

[Gerd König]

Hello Sim,

as Grzegorz already mentioned, you have to gather the info from the logs.
Therefore I would suggest the tool pgfouine. There's an option to "visualize"
the log of a VACUUM run. You should give it a try...

regardsGERD...

Sim Zacks schrieb:
> Is there a tool that can monitor my postgresql and recommend
> configuration changes based on actual use?
> 
> For example, when I run VACUUM ANALYZE it tells me that I should
> consider increasing the max_fsm_relations.
> 
> After monitoring for a period of time, I would like to know if my shared
> buffers are set optimally (for example). Or if I need more RAM in the
> machine to run better etc..
> 
> Thank you
> Sim
> 

-- 
/===\
| Gerd König
| - Infrastruktur -
|
| TRANSPOREON GmbH
| Pfarrer-Weiss-Weg 12
| DE - 89077 Ulm
|
|
| Tel: +49 [0]731 16906 16
| Fax: +49 [0]731 16906 99
| Web: www.transporeon.com
|
\===/



Bleiben Sie auf dem Laufenden.
Jetzt den Transporeon Newsletter abonnieren!
http://www.transporeon.com/unternehmen_newsletter.shtml


TRANSPOREON GmbH, Amtsgericht Ulm, HRB 722056
Geschäftsf.: Axel Busch, Peter Förster, Roland Hötzl, Marc-Oliver Simon

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

Re: [GENERAL] Maintaining user roles and permissions in Postgres - general question

[Richard Huxton]

Scott Marlowe wrote:

On Thu, Jun 18, 2009 at 8:03 PM, Postgres
User wrote:

Does anyone have a recommendation for maintaining user permissions on
a changing database?  The lack of an option to grant specific rights
to all objects of a given type within a Postgres db obviously


I think we're answering a different question to the one being asked.

>> places

the burden on the administrator to keep roles updated as objects are
added and dropped from a given database.


"Postgres User" is talking about adding new functions/tables etc. to an 
existing database.



What works better is to assign roles the proper permissions.  So,
hr_admin role can change records in hr tables, hr_user can read
records in hr tables and only change one or two, and so on.  Then when
someone comes on as an HR user, you just grant them the role.  They
leave the HR group, you revoke the role.  ding, job done.  


We're talking about changing the users who fill various roles. Correct 
(and useful) info but not quite on-target for the original question.


This is in fact the main benefit of the various "grant all" scripts 
(easy enough to google for). Combined with Scott's advice you keep your 
life simple by:

1. Script revokes all permissions
2. Script applies minimum permissions required (e.g. read only on 
Scott's hr tables)
3. Script applies privileged permissions to specific roles/objects as 
required


Re-run the script after every update to the database schema on your test 
DB. If the developers need to update the script but forget, they'll 
notice straight away when they can't access their new table/function. 
Keep the script under version-control and you can quickly identify the 
permission changes to make to the live system when you apply the schema 
changes there.


--
  Richard Huxton
  Archonet Ltd


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

[GENERAL] Issue with WAL recovery in windows

[Nishkarsh]

Hello every one,

I am running Postgres 8.2 on Windows. I was trying to use WAL for archive
and recovery. I followed the instructions giving in following link.

http://www.mkyong.com/database/postgresql-point-in-time-recovery-incremental-backup/
http://www.mkyong.com/database/postgresql-point-in-time-recovery-incremental-backup/
 

I am able to archive WAL. When i reverted back to the older copy of data
directory, i was able to start the service but on opening pgAdmin III it is
telling that the "Server doesn't listen"... any idea what has gone wrong? I
had checked that the service is running

regards
Nishkarsh

-- 
View this message in context: 
http://www.nabble.com/Issue-with-WAL-recovery-in-windows-tp24106493p24106493.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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