Re: [GENERAL] GiST indeices on range types

[Magnus Hagander]

On Sat, Mar 28, 2015 at 7:52 AM, Rebecca Zahra 
wrote:

> Good morning,
>
> I am Rebecca Zahra and I am currently in my final year of Masters studies
> at the University of Malta. My thesis is about the usage of indexes for
> multi-dimensional data.
>
> I was going through the posts regarding GIST indexes and I came across the
> following
> http://dba.stackexchange.com/questions/39589/optimizing-queries-on-a-range-of-timestamps-two-columns
>
> I was wondering if maybe you can help me with a question.  I know that an
> R-Tree index implementation is used on top of GIST to index spatial data.
> Can you please tell me what type of index is used on top of GIST to index 
> *range
> types*?
>
>
PostgreSQL has had indexable range types for quite some time now:
http://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-GIST

Indexable with gist or spgist. I don't think the docs cover the actual
implementation internals though - you'll probably have to go to the source
if you need that.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Upgrading hot standbys

[Magnus Hagander]

On Wed, Apr 29, 2015 at 6:19 AM, Aaron Burnett <
aaron.burn...@us.dunnhumby.com> wrote:

>
>  Greetings,
>
>
>  I'm in the process of upgrading PG 9.1.3 to 9.4.1 in the near future.
>
>
>  I have several machines which each house unique databases. Each of those
> are replicated to a standby server with matching configurations. A total of
> 10 servers, 5 masters, 5 slaves. Everything runs on Ubuntu.
>
>
>  My question, as I can't seem to find any documentation on this part, is
> once I successfully upgrade the master I will need to upgrade the standby
> as well. Will I have to rebuild the standby from scratch, or will the
> standby pick up where it was before the upgrade if I do things correctly?
>
>
>
You upgrade the master, and then you rebuild the standbys from a new
basebackup (using pg_basebackup or manually with start/stop backups etc).
You can't upgrade the standbys and have them re-join the master, they have
to be redone from scratch.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] count distinct and group by

[Magnus Hagander]

On Thu, May 7, 2015 at 12:23 PM, Szymon Guz  wrote:

> Hi,
> I'm not sure why there is a reason for such behaviour.
>
> For this table:
>
> create table bg(id serial primary key, t text);
>
> This works:
>
> select count(id) from bg;
>
> This works:
>
> select count(distinct id) from bg;
>
> And this doesn't:
>
> select count(distinct id) from bg order by id;
> ERROR:  column "bg.id" must appear in the GROUP BY clause or be used in
> an aggregate function
> LINE 1: select count(distinct id) from bg order by id;
>
>
There is no "id" column in the returned dataset to order by. You are just
returning one value, how would it be ordered? (and that row has a column
named "count" - but you can alias it to SELECT count(distinct id) AS id
FROM bg ORDER BY id - it just makes no sense to order a single row..

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] 9.3.9 ?

[Magnus Hagander]

On Jun 12, 2015 1:33 PM, "Albe Laurenz"  wrote:
>
> Birta Levente wrote:
> > In the postgresql yum repo appeared the 9.3.9 and 9.4.4, but on the
> > postgresql.org nothing about the new version. Where I can see the
changelog?
>
> AFAIK, it is being packaged and will be announced soon.

The rpm packages were accidentally released a bit early. The announcement
will be later today. But they have the correct content, so they are safe to
use. Release notes will be available alongside the announcement.

/Magnus

Re: [GENERAL] Filters not supported for LDAP authentication

[Magnus Hagander]

On Wed, Aug 31, 2016 at 2:43 PM, Bastien Bodart 
wrote:

> Hi,
>
> Is there any reason filters are not supported for search+bind LDAP
> authentication?
> There is no option to pass a filter in search+bind mode and
> "ldapsearchattribute" parameter is even checked to prevent filter injection.
> "ldapurl" parameter is defined as an RFC 4516 LDAP URL except filters and
> extensions are not supported despite being parsed.
>

AFAIK there is no reason other than that nobody has gotten around to write
the code for it. I see no reason why we wouldn't accept a patch for that
functionality.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Unexpected trouble from pg_basebackup

[Magnus Hagander]

On Tue, Oct 4, 2016 at 10:42 PM, otheus uibk  wrote:

> After a 3 to 4 minute delay, pg_basebackup started doing it's thing and
> finished within a few minutes. So now the question is: why the startup
> delay?
>


Sounds to me like it's doing a CHECKPOINT with spreading, which make it
take time. Try with "-c fast" and see if the problem goes away.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Unexpected trouble from pg_basebackup

[Magnus Hagander]

On Wed, Oct 5, 2016 at 3:55 PM, otheus uibk  wrote:

>
>
> On Tue, Oct 4, 2016 at 10:49 PM, Magnus Hagander 
> wrote:
>
>>
>>
>> On Tue, Oct 4, 2016 at 10:42 PM, otheus uibk 
>> wrote:
>>
>>> After a 3 to 4 minute delay, pg_basebackup started doing it's thing and
>>> finished within a few minutes. So now the question is: why the startup
>>> delay?
>>>
>>
>>
>> Sounds to me like it's doing a CHECKPOINT with spreading, which make it
>> take time. Try with "-c fast" and see if the problem goes away.
>>
>
> Maybe not too far off.
>
>
> 2016-10-04 17:43:40.620 GMT 57eb90a0.6e07
> 402 0   LOG:  checkpoint complete: wrote 12799 buffers (1.0%); 0
> transaction log file(s) added, 0 removed, 5 recycled; write=1389.348 s,
> sync=0.033 s, total=1389.400 s; sync files=240, longest=0.003 s,
> average=0.000 s; distance=92915 kB, estimate=129373 kB
> 2016-10-04 18:20:31.714 GMT 57eb90a0.6e07
> 403 0   LOG:  checkpoint starting: time
> 2016-10-04 18:39:19.870 GMT 57eb90a0.6e07
> 404 0   LOG:  checkpoint complete: wrote 10265 buffers (0.8%); 0
> transaction log file(s) added, 0 removed, 4 recycled; write=1128.118 s,
> sync=0.023 s, total=1128.155 s; sync files=190, longest=0.002 s,
> average=0.000 s; distance=73419 kB, estimate=123778 kB
> 2016-10-04 19:07:12.647 GMT [unknown]   pgsync  57f3fde0.52e3   2
>   0   LOG:  replication connection authorized: user=pgsync
> 2016-10-04 19:07:12.703 GMT 57eb90a0.6e07
> 405 0   LOG:  checkpoint starting: force wait
> 2016-10-04 19:20:32.879 GMT [unknown]   pgsync  57f40100.5891   2
>   0   LOG:  replication connection authorized: user=pgsync
> 2016-10-04 19:23:05.249 GMT 57eb90a0.6e07
> 406 0   LOG:  checkpoint complete: wrote 8638 buffers (0.7%); 0
> transaction log file(s) added, 0 removed, 5 recycled; write=952.514 s,
> sync=0.016 s, total=952.546 s; sync files=238, longest=0.002 s,
> average=0.000 s; distance=68257 kB, estimate=118226 kB
> 2016-10-04 19:23:05.249 GMT 57eb90a0.6e07
> 407 0   LOG:  checkpoint starting: force wait
> 2016-10-04 19:28:52.232 GMT 57eb90a0.6e07
> 408 0   LOG:  checkpoint complete: wrote 3102 buffers (0.2%); 0
> transaction log file(s) added, 0 removed, 0 recycled; write=346.957 s,
> sync=0.018 s, total=346.982 s; sync files=149, longest=0.002 s,
> average=0.000 s; distance=36016 kB, estimate=110005 kB
> 2016-10-04 19:30:31.922 GMT [unknown]   pgsync  57f40357.5c70   2
>   0   LOG:  replication connection authorized: user=pgsync
> 2016-10-04 19:30:31.932 GMT 57eb90a0.6e07
> 409 0   LOG:  checkpoint starting: force wait
> 2016-10-04 19:33:40.857 GMT 57eb90a0.6e07
> 410 0   LOG:  checkpoint complete: wrote 1763 buffers (0.1%); 0
> transaction log file(s) added, 0 removed, 1 recycled; write=188.886 s,
> sync=0.030 s, total=188.924 s; sync files=125, longest=0.004 s,
> average=0.000 s; distance=13135 kB, estimate=100318 kB
>
>
>
> OK, so what was happening is that the checkpoints were taking 5 to 15
> minutes minutes, and aborted basebackups were triggering new checkpoints
> which waited on the previous ones.
>
> Is it possible the new environment is not high-performance enough??
>
> possibly relevant configuration change:
>checkpoint_timeout=1h
>
>
>
>
pg_basebackup will always send in a checkpoint. PostgreSQL will time that
to take approximately checkpoint_timeout * checkpoint_completion_target
time. So with checkpoint_timeout set to a very high value, it will by
default target something like 30 minutes before it even gets started. The
only reason it takes as *little* as it does is that your system is lightly
loaded.

You can do a fast checkpoint with the parameter I suggested before, or you
can tune your checkpoint_timeout to be something that gives you a more
reasonable time.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] pg_upgrade not able to cope with pg_largeobject being in a different tablespace

[Magnus Hagander]

On Thu, Oct 13, 2016 at 7:35 AM, Andreas Joseph Krogh 
wrote:

> På torsdag 13. oktober 2016 kl. 16:09:34, skrev Bruce Momjian <
> br...@momjian.us>:
>
> On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote:
> > I would assume that having pg_largeobject in a separate tablespace is
> more and
> > more common these days, having real-cheap SAN vs. fast-SSD for normal
> tables/
> > indexes/wal.
>
> So common that no one has ever asked for this feature before?
>
>
>
> Sometimes one gets the feeling that one is the only one in the universe
> doing something one considers "quite common":-)
>
>
> > So - I'm wondering if we can fund development of pg_upgrade to cope with
> this
> > configuration or somehow motivate to getting this issue fixed?
> >
> > Would any of the PG-companies (2ndQ, EDB, PgPro) take a stab at this?
> >
> > Any feedback welcome, thanks.
>
> You would need to get buy-in that that community wants the relocation of
> pg_largeobject to be supported via an SQL command, and at that point
> pg_upgrade would be modified to support that.  It is unlikely pg_upgrade
> is going to be modified to support something that isn't supported at the
> SQL level.  Of course, you can create a custom version of pg_upgrade to
> do that.
>
>
> Doesn't "ALTER TABLE pg_largeobject SET TABLESPACE myspace_lo" count as
> being "at the SQL-level"?
>

Well, it requires that you set allow_system_table_mods on, which is
documented as a developer option. It's documented with things like "The
following parameters are intended for work on the PostgreSQL source code,
and in some cases to assist with recovery of severely damaged databases. There
should be no reason to use them on a production database.".

Perhaps we should add another disclaimer there saying that if you make
changes in this mode, tools like pg_upgrade (or for that matter, pg_dump or
pretty much anything at all) may not work anymore?



> The whole problem seems to come from the fact that BLOBs are stored in
> pg_largeobject which for some reason is implemented as a system-catalogue
> in PG, which imposes all kinds of weird problems, from a DBA-perspective.
>

Yes, there are several issues related to how lo style large objects work.
I've often gone to similar implementations but in userspace on top of
custom tables to work around those.



> Can we pay you at EDB for making such a custom version of pg_upgrade for
> 9.6?
>
>
You're assuming pg_upgrade is the only potential problem. If you are
willing to spend towards it, it would probably be better to spend towards
the "upper layer" problem which would be to make it possible to move
pg_largeobject to a different tablespace *without* turning on
system_table_mods.

That said, I cannot comment to the complexity of either doing that *or*
doing a custom pg_upgrade that would support it. But solving a long-term
problem seems better than solving a one-off one.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

[Magnus Hagander]

On Nov 29, 2015 18:34, "George Neuner"  wrote:
>
> On Sun, 29 Nov 2015 05:04:42 -0500, "Steve Petrie, P.Eng."
>  wrote:
>
> >"George Neuner"  wrote in message
> >news:kaed5btl92qr4v8ndevlgtv0f28qaae...@4ax.com...
> >
> >> My vote for an email client would be Thunderbird.  It runs on XP or
> >> higher and you can import Outlook's PST files so as to keep your mail
> >> archives.  Importing PST files directly requires Outlook be available
> >> on the same system [there is also a less friendly way to do it via EML
> >> files exported from Outlook where Outlook is not on the same system].
> >>
> >
> >It's a common misconception that MS Outlook Express is compatible with MS
> >Outlook. But in fact the two products are architecturally unrelated.
>
> My understanding was that OE was based on the old (Win9x) Outlook.  I
> know it isn't the same as the "enterprise" version.

This is fantastically of topic but no, it was not. OE was based on the old
"Internet mail and news".  The actual outlook product has always been
separate.

/Magnus

Re: [GENERAL] Support for hardware tokens for server/replication private key

[Magnus Hagander]

On Thu, Dec 3, 2015 at 5:31 AM, mdaswani  wrote:

> Hi,
>
> Postgres allows client-side SSL requests to use secret keys on hardware
> tokens via OpenSSL engine support. Is there an equivalent way to store the
> server key on a hardware token.
>
> Similarly, is it possible to specify private keys on a hardware token for
> replication connections? Does the sslkey parameter of the primary_conninfo
> string in the recovery.conf file accept an OpenSSL Engine token key?
>

While I haven't tested it and haven't heard of anybody else who has, it
should work. From a libpq perspective ,the replication standby is "just
another client", so any parameters that work for libpq should work there.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] dblink connect per socket / specify cluster name

[Magnus Hagander]

On Dec 21, 2015 08:58, "james garner"  wrote:
>
> Dear list,
>
> i'm running PgSQL 9.3 on Debian installed from the Debian packets.
> I'm using dblink to access a different database.
> When im trying to connect to the database by socket via
> dblink_exec('dbname=test host=/var/run/postgresql' ..
> everything works nicely if there's just one db-cluster named 'main'
> I didn't find a way however to select a different cluser name.
> If a connection is made by TCP, the cluster ist determined by the port,
but how is it done when connecting by socket?

It's still determined by the port. Set the host name to the directory where
the sockets are, and the port number will control the actual socket name.

/Magnus

Re: [GENERAL] Let's Do the CoC Right

[Magnus Hagander]

On Fri, Jan 22, 2016 at 7:41 PM, David E. Wheeler 
wrote:

>
>
> They are in fact both unreconstructed bigots.
>
>

Regardless whether it's true or not (to which I cannot speak), surely
statements like that would violate *both* the contributor covenant *and*
the CoC suggested by others.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Let's Do the CoC Right

[Magnus Hagander]

On Fri, Jan 22, 2016 at 8:37 PM, David E. Wheeler 
wrote:

> On Jan 22, 2016, at 11:28 AM, Magnus Hagander  wrote:
>
> > Regardless whether it's true or not (to which I cannot speak), surely
> statements like that would violate *both* the contributor covenant *and*
> the CoC suggested by others.
>
> It may well violate the Contributor Covenant (my apologies, I was out of
> line), but not the current draft of the CoC, IME. Why? Because that’s just
> my opinion, and the CoC draft formally recognizes my right to have an
> “opposing view”.
>

Are you really saying this does not violate "* Participants must ensure
that their language and actions are free
of personal attacks and disparaging personal remarks."?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] PostgreSQL flavors

[Magnus Hagander]

On Tue, Feb 23, 2016 at 1:04 PM, Josh berkus  wrote:

> On 02/23/2016 07:51 AM, Sherrie Kubis wrote:
>
>> Hello, my first post to the list, thank you for this place to ask
>> questions and get help.
>>
>> Our management has tasked me with devising a plan to migrate our
>> existing databases from Oracle to PostgreSQL.  I’m researching and
>> getting familiar with PostgreSQL before getting a Linux box to start
>> learning and staging.  I have a long way to go, but it will be fun.
>>
>> Out of the gate, I can see different PostgreSQL products – PostgreSQL,
>> PostgreSQLPlus, EnterpriseDB Advanced Server.
>>
>
> So here's a quick rundown.  I'm sure I'm forgetting some, but here's a lot
> of them.  I've deliberately omitted PostgreSQL forks/versions which are no
> longer maintained or not commercially available.
>
> Open Source
> ---
>
> PostgreSQL Plus: EnterpriseDB's distribution of PostgreSQL with extra open
> source tools included in the installer.
>

It's Postgres Plus, not PostgreSQL plus.

And AFAIK, it was also retired some time ago and doesn't actually exist
anymore.

(The advanced servers version does of course, but that's not open source).

I think the only "Plus" product now is their cloud offering?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] could not migrate 8.0.13 database with large object data to 9.5.1

[Magnus Hagander]

On Mar 2, 2016 06:01, "John R Pierce"  wrote:
>
> (thread moved from pg_bugs)
> (upgrading a 8.0.13 database on Windows XP 32bit to 9.5.1 on Windows 8 64
bit.)
>
>
> On 3/1/2016 8:05 PM, Premsun Choltanwanich wrote:
>>
>> Modified command by remove -Ft flag as per you suggestion:
>> pg_dump -v -h 192.168.200.75 -U clubadmin -d clubprogram | psql -U
clubadmin -d clubprogram
>>
>> Result (got same message even with parameter -b or not):
>> pg_dump: reading rewrite rules
>> pg_dump: reading policies
>> pg_dump: reading large objects
>> ...
>>
>> pg_dump: creating FUNCTION "public.plpgsql_call_handler()"
>> pg_dump: creating FUNCTION "public.plpgsql_validator(oid)"
>> ...
>>
>> pg_dump: creating FUNCTION "public.pg_file_write(text, text, boolean)"
>
>
> those all sound like standard postgres functions, its not clear to me why
pg_dump is generating the CREATE FUNCTION code for them.
>
>
>> pg_dump: [archiver] could not write to output file: Invalid argument
>>
>
> ok, presumably your new box has plenty of disk space?  try this on the
new 9.5 system...
>

How large is the total database? The earliest versions of pg on Windows had
bugs in pg_dump for files larger than 2GB. I don't recall exactly when they
were fixed, but this was a long time ago.. Through if my memory is correct
the actual bugs were in pg_dump itself, so using a new pg_dump against the
old server should be safe.

/Magnus

Re: Email address VERP problems (was RE: [GENERAL] Does a call to a language handler provide a context/session, and somewhere to keep session data?

[Magnus Hagander]

On Wed, Mar 9, 2016 at 7:03 PM, Alvaro Herrera 
wrote:

> David Bennett wrote:
> > > ow...@postgresql.org] On Behalf Of Alvaro Herrera
> >
> > > On that subject.  I noticed that Outlook seems to add the "return
> > > path"
> > > addresses (sometimes called bounce address or envelope sender) to the
> > > CC header, which sets a new record in the stupidity scale.  Since we
> > > use VERP, each message gets a different return path address, so with
> > > each reply you make, Outlook adds a new address to the CC.
> >
> > Interesting. I use a lot of mailing lists and I've not run across one
> > actually using VERP before. Is it becoming more frequent?
>
> Not sure if it's becoming more frequent -- I only manage *this* list
> server and we enabled VERP several years ago.  I thought it was common
> practice ... the idea of manually managing addresses that bounce seems
> completely outdated now.
>


It's been frequent for quite some time.



> > I checked the headers. It seems this list is using a VERP address for
> both
> > the Return-path and the Reply-To, and only the Sender identifies the list
> > directly.
>
> I'm pretty sure our list server is not setting the VERP address in
> Reply-To.  That would be insane, wouldn't it.  We don't touch the
> Reply-To header at all.  Maybe some other program along the way modifies
> the email before Outlook gets it?
>

Yeah, same here.

However, if you look at the thread, it seems the VERP address was added to
the *original email*. In the To field. Perhaps that's what confused the MUA
into adding *another* VERP address on the reply?

Then AFAICT in
http://www.postgresql.org/message-id/raw/001601d17852$7bea9e80$73bfdb80$@pfxcorp.com
a second VERP address was added to the mail (very the 485 one).

To me it look slike this was definitely done by the MTA or MUA at
pfxcorp.com. The archived copy (which is delivered the exact same way as a
general email, it doesn't have any shortcut) does not contain this address
naywhere, it was only used as an envelope sender. Possibly it got confused
by the other VERP address in the initial email, which AFAICT is a manual
mistake.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] PostgreSQL crashed, whole PC not responding

[Magnus Hagander]

On Thu, Mar 10, 2016 at 2:37 AM, Kiswono Prayogo  wrote:

> Hi, I'm upgrading to PostgreSQL 9.5.1 in 64-bit ArchLinux (Linux
> 4.4.1-2-ARCH) for around 4 days (previously we use 9.4.x), and the
> PostgreSQL crashed and make the whole PC down, with this last log from
> journalctl:
>
> Mar 09 23:31:17 asd kernel: BUG: Bad page state in process postgres
>  pfn:d18515
> Mar 09 23:31:17 asd kernel: page:ea0034614540 count:0 mapcount:0
> mapping:  (null) index:0x1
> Mar 09 23:31:17 asd kernel: flags: 0x2fffe00()
> Mar 09 23:31:17 asd kernel: page dumped because: page still charged to
> cgroup
> Mar 09 23:31:17 asd kernel: page->mem_cgroup:0002
>
> Is there any possible cause of this? or how to search the causation of
> this? since the only log i have is journalctl
> and log_min_duration_statement 2000 (and it shows nothing).
>

That looks like a kernel issue/bug. Both because the log comes from the
kernel, and because PostgeSQL can't actually take down the whole PC as long
as the kernel is working properly.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Equivalent of Oracle's per-session statistics (v$client_stats)?

[Magnus Hagander]

On Mar 22, 2016 6:14 PM, "Frits Jalvingh"  wrote:
>
> Hello list,
>
> Oracle has a way to get per-session statistics. You identify a session
using a call to dbms_session.set_identifier('xxx'), then you enable
statistics using dbms_monitor.client_id_stat_enable('xxx').
> After this you do normal database statements.
> Before you close the connection you can read a view, v$client_stats,
which now contains all kinds of metrics specifically to your connection's
use. Metrics that can be read are things like the number of logical blocks
read, physical blocks read etc.
> Using this mechanism you can show exactly how "bad" for instance a screen
from an application behaves, by finding out how much database I/O it does.
>
> I was wondering whether Postgresql has something like this? I looked at
the pg_stats tables but I do not see anything that can be related to the
"current session" or "current connection".
>

There aren't really any on a session basis but there are per transaction.
Take a look at pg_stat_xact_*.

/Magnus

Re: [GENERAL] Site down

[Magnus Hagander]

On Thu, Apr 14, 2016 at 4:14 PM, Adrian Klaver 
wrote:

> Multiple attempts from multiple locations show that the Web site is down
> for me at least. I can ping the address, just not pull up the Web content.
>


Sorry about that. Botched upgrade and the alerts got lost along with the
big issues we've had with ipv6 routing recently.

It should be back up now.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Winsock error 10035 while trying to upgrade from 8.0 to 8.2

[Magnus Hagander]

Cyril VELTER wrote:
> 
>>> Cyril VELTER wrote:
Searching the source files, it seems the error message is generated in 
 port/win32/socket.c line 594.
>>> Right, but the important thing is which path down to that function is it
>>> generated in. Which is why a backtrace would help.
>>  Yes, I understand that.
>>
>>> Looking at the code, the problem is probably somewhere in
>>> pgwin32_recv(). Now, it really shouldn't end up doing what you're
>>> seeing, but obviously it is.
>>
>>  After looking at the code of pgwin32_recv(), I don't understand why 
>> pgwin32_waitforsinglesocket() is called with the FD_ACCEPT argument. 
>>
>>> Perhaps we just need to have it retry if it gets the WSAEWOULDBLOCK?
>>> Thoughts?
>>  I've modified pgwin32_recv() to do that (repeat the 
>> pgwin32_waitforsinglesocket() / WSARecv while the error is WSAEWOULDBLOCK 
>> and 
> 
> 
>> not raising this error. I've an upgrade running right now (I will have the 
>> result in the next hours).
> 
> 
>   Replying to myself, the upgrade is not finished yet, but I can confirm 
> that 
> there is cases where pgwin32_waitforsinglesocket() return and the WSARecv 
> immediatly fail. I-ve modified the end of pgwin32_recv() :
> 
> 
>   /* No error, zero bytes (win2000+) or error+WSAEWOULDBLOCK (<=nt4) */
> 
>   for(;;) {
>   if (pgwin32_waitforsinglesocket(s, FD_READ | FD_CLOSE | 
> FD_ACCEPT,
>   
> INFINITE) == 0)
>   return -1;
> 
>   r = WSARecv(s, &wbuf, 1, &b, &flags, NULL, NULL);
>   if (r == SOCKET_ERROR)
>   {
>   printf("SOCKERROR");
>   if (WSAGetLastError() != WSAEWOULDBLOCK)
>   {
>   TranslateSocketError();
>   return -1;
>   }
>   }
>   else
>   {
>   return b;
>   }
>   }
> 
> 
>   The printf("SOCKERROR") line have been hit two times.
> 
>   Any though ?
> 
>   Once this upgrade is finished, I will make another try removing 
> FD_ACCEPT from 
> 
> the pgwin32_waitforsinglesocket() call.

Hmm. That really isn't supposed to happen, but seems it is. Does it work
when you add that loop, though? Spits out the message and works, or does
it spit out the message and still not work?

I'm also a bit worried about it getting caught in a tight loop if the
error codes are wrong, but probably it just goes back into waitfor.. and
blocks the second time. Otherwise, you'd see screenfuls of that message.

Can you determine if it was hit two times right after each other, or if
there was time between them?

//Magnus

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

   http://archives.postgresql.org/

Re: [Re] Re: [GENERAL] Winsock error 10035 while trying to upgrade from 8.0 to 8.2

[Magnus Hagander]

Cyril VELTER wrote:
>   OK, I've the results of my tests :
> 
>   With the previous code, then message "SOCKERROR" is printed 5 times 
> during the 
> whole process (100 Gb dump import with psql). There one group of three and 
> one 
> group of two, but I don't have timestamps and am not sure if they are 
> printing 
> in the same loop or not. The import is finally successful.

Ok.


>   The second test I have done is to remove FD_ACCEPT I still have the 
> message 
> one times, but it still happen. The import is also sucessfull.

Ok. So FD_ACCEPT is not the fix. Good, I didn't think it would be.


>> I'm also a bit worried about it getting caught in a tight loop if the
>> error codes are wrong, but probably it just goes back into waitfor.. and
>> blocks the second time. Otherwise, you'd see screenfuls of that message.
>>
>> Can you determine if it was hit two times right after each other, or if
>> there was time between them?
> 
>   For the first test I don't known the amount of time between them (I 
> have two 
> groups separeted in the logs with other messages).

Ok. I'm thinking of just sticking a minimal wait in there to protect
against absolute runaway, but that should be enough I think.


>   What do you think ? may be a bug in the windows server installation I 
> have 
> (this machines have not been updated for some times, perhaps I should try to 
> do 
> that and see if the problem is still there. In the long run, I plan to 
> upgrade 
> to windows 2003).

I don't *think* it should be a bug with your version, it doesn't look
like it. but if you're not on the latest service pack, that's certainly
possible. Please update to latest servicepack + updates from Windows
Update / WSUS, and let me know if the problem persists.

Meanwhile, I'll try to cook up a patch.

//Magnus

---(end of broadcast)---
TIP 1: 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: [Re] Re: [GENERAL] Winsock error 10035 while trying to upgrade from 8.0 to 8.2

[Magnus Hagander]

On Tue, May 29, 2007 at 11:25:30PM +0200, Magnus Hagander wrote:
> > What do you think ? may be a bug in the windows server installation I 
> > have 
> > (this machines have not been updated for some times, perhaps I should try 
> > to do 
> > that and see if the problem is still there. In the long run, I plan to 
> > upgrade 
> > to windows 2003).
> 
> I don't *think* it should be a bug with your version, it doesn't look
> like it. but if you're not on the latest service pack, that's certainly
> possible. Please update to latest servicepack + updates from Windows
> Update / WSUS, and let me know if the problem persists.
> 
> Meanwhile, I'll try to cook up a patch.

I have applied a patch for this to HEAD and 8.2. It includes a small wait
so we don't hit it too hard, and a limit on 5 retries before we simply give
up - so we don't end up in an infinite loop.

//Magnus


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

Re: [GENERAL] Generate random password

[Magnus Hagander]

Robert Fitzpatrick wrote:
> Can anyone suggest how one might be able to do this? I want to be able
> to generate an 8 character random password for users in their password
> field. Perhaps through the default setting of the field or a trigger
> function. I found the following, but is there anything that can be used
> on both Windows and *nix or can this be used on Windows somehow?
> 
> http://pgfoundry.org/forum/forum.php?forum_id=994

If you don't need something that's actually secure, you ca ndo it
trivially in PL/pgsql.Here's what I use, for example:

CREATE FUNCTION generate_random_password() RETURNS text
AS $$
DECLARE
   j int4;
   result text;
   allowed text;
   allowed_len int4;
BEGIN
   allowed := '23456789abcdefghjkmnpqrstuvwxyzABCDEFGHJKMNPQRSTUVWXYZ&#%@';
   allowed_len := length(allowed);
   result := '';
   WHILE length(result) < 16 LOOP
  j := int4(random() * allowed_len);
  result := result || substr(allowed, j+1, 1);
   END LOOP;
   RETURN result;
END;
$$
LANGUAGE plpgsql;




It's not fast (but how many thousands are you generating per second
anyway), it's not "really secure", but it works :)

(Note that the function explicitly excludes characters like I, 1 and l
because they look too similar)

//Magnus

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

Re: [GENERAL] PostGreSQL for a small Desktop Application

[Magnus Hagander]

On Mon, Jun 11, 2007 at 10:44:38AM -0700, Gabriele wrote:
> I'm going to develop a medium sized business desktop client server
> application which will be deployed mostly on small sized networks and
> later eventually, hopefully, on medium sized networks.
> It will probably be developed using C#.
> 
> I do need a solid DBMS wich can work with .Net framework. I do know
> PostGreSQL is a good DBMS in general (it sports most of the advanced
> DBMS features, transactions and stored procedure included) but i
> wonder if it is suited for my application.
> 
> Knowledge base of my users is very low and "servers" will be standard
> class desktop computers most probably ran on Windows XP (and Vista
> later on, i suspect).
> The service should be enough lightweight to be ran on such "server"
> and I need silent installation and configuration because i can't
> expect my user to be able to configure a DBMS.

Silent installation is available and works fine. There will be a bit of
overhead, especially in disk usage, compared to an embedded database. But
it's much better than most commercial ones, like MSDE.
See http://pginstaller.projects.postgresql.org/silent.html.


> Additionally i need a passable to good data provider to interface
> PostGreSQL with .Net which possibly provide better performance than
> ODBC (don't know if it exists and i hope it is free).

npgsql (http://pgfoundry.org/projects/npgsql) works very well for .net.


> Anyway performance shoudn't be a big issue, i expect low concurrency
> level (less than 10 users) and low to medium volume of rows and
> queries. If more users and more data are needed for especially big
> customer i can simply suggest bigger and dedicated server. (different
> problems will arise for the aggregated data which will feed the web
> application, but for these we will have a real server).
> 
> Is PostGreSQL suited for such use? If not which alternatives are there
> to be used? When using PostGreSQL in such a way is there any
> suggestion to be followed? Links to sources which i may find
> interesting (how to make a silent install, basic hardware
> requirements, so on).

Your other option would be to use an embedded database like SQLite. It has
a much smaller footprint, but is of course also a lot less capable. But it
could be worthwhile to check it out.

As for suggestions, well, don't forget to run autovacuum and to schedule
your backups properly. You can certainly not expect your users to do that
:) Also, plan and test in advance a method for upgrading the installations
- you'll always want to be at the latest release in a branch as soon as
possible after the release.

//Magnus

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

Re: [GENERAL] PostgreSQL Installer for Windows x64

[Magnus Hagander]

On Wed, Jun 20, 2007 at 10:17:10AM +0900, EBIHARA, Yuichiro wrote:
> Hi,
> 
> Can I get a PostgreSQL Installer for Windows x64(EM64T)?
> That for 32bit Windows is available at http://www.postgresql.org/ftp/win32/ 
> but I need x64 native
> version.

There is no such thing. PostgreSQL 64-bit is currently only supported on
Unix based platforms.

The 32-bit version for Windows works just fine on 64-bit windows, though.

//Magnus

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

   http://archives.postgresql.org/

Re: [GENERAL] PostgreSQL Installer for Windows x64

[Magnus Hagander]

On Wed, Jun 20, 2007 at 04:31:40PM +0900, EBIHARA, Yuichiro wrote:
> Magnus,
> 
> Thank you for your quick reply.
> 
> > > Can I get a PostgreSQL Installer for Windows x64(EM64T)?
> > > That for 32bit Windows is available at 
> > > http://www.postgresql.org/ftp/win32/ but I need x64 native version.
> > 
> > There is no such thing. PostgreSQL 64-bit is currently only 
> > supported on Unix based platforms.
> 
> I thought I can run 64bit PG on Windows if I compile it myself...

Not yet. We hope to do this in the future, but we're not there yet.
Basically, 8.2 and earlier relied on MingW for building which cannot do
64-bit. 8.3 will also fully support MSVC, which will be the default
environment for the binary build. And since MSVC supports 64-bit, that's a
good start on our path to support native 64-bit.

> > The 32-bit version for Windows works just fine on 64-bit 
> > windows, though.
> 
> I'll go this way.
> 
> BTW, nobody wants to utilize much larger cache?

You can do that with the 32-bit version! PostgreSQL mainly relies on the
filesystem cache for this, and 32-bit pg on 64-bit windows will utilize
that one just fine. The advantages of a native 64-bit version is mainly
that you can use more *sort memory* (not very common requirement), or that
you can use more registers and new instructions.

//Magnus

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

Re: [GENERAL] Proposed Feature

[Magnus Hagander]

Naz Gassiep wrote:
> Hey,
> I'm sure that'd be greatly appreciated, most other major servers and
> DBs have a similar feature, and that's what the systray is for, i.e.,
> viewing major user-installed services.

Don't forget that the tray (or taskbar notification area as it's
supposed to be called) is one of the most abused areas of the windows
GUI. I've seen installs where it takes up half the screen. So it's not
*necessarily* a good thing - making it mandatory to have an easy way to
turn it off. And if we don't add any actual *functionality*, it should
absolutely not be enabled by default. Providing it as an option is never
wrong, though.

//Magnus

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

Re: [GENERAL] Proposed Feature

[Magnus Hagander]

Tony Caduto wrote:
> Bruce Momjian wrote:
>> Naz Gassiep wrote:
>>  
>>> I'm using PG on windows for the first time (as of about 6 minutes ago).
>>> I was thinking that it would be great to have a system tray icon with a
>>> running indicator, kind of like the way Apache2.x for windows has, or
>>> even MSSQL. Perhaps the PG logo with a small white circle with a red
>>> square or a green triangle in the same fashion.
>>> Just a thought.
>>> 
>>
>> And what does the icon show or do?
>>
>>   
> That would be pretty easy to do with Delphi.
> I could whip something up and donate it to the project with a BSD license.

I think it'd be reasonably easy in any language really, but that's
beside the point - if you're volunteering to do it, you get to pick
language :)

Does Delphi bring in any additional runtime requirements, though? I
don't think we'd want to add a big extra runtime for such a small thing.


> It could be a green "Play" arrow if the service is running and a red one
> if it is not, then have a few right click options
> to start/stop/restart the service.

How would it deal with multiple servers (same or different version)
running on the same box? I did think about this for 10 minutes back when
we first did 8.0 for win32, but I got stuck somewhere between that
question and the fact that I didn't need/want it myself :-)

//Magnus


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

Re: [GENERAL] Proposed Feature

[Magnus Hagander]

Joris Dobbelsteen wrote:
>> -Original Message-
>> From: [EMAIL PROTECTED] 
>> [mailto:[EMAIL PROTECTED] On Behalf Of 
>> Magnus Hagander
>> Sent: zaterdag 23 juni 2007 11:39
>> To: Naz Gassiep
>> Cc: Tony Caduto; pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Proposed Feature
>>
>> Naz Gassiep wrote:
>>> Hey,
>>> I'm sure that'd be greatly appreciated, most other major servers 
>>> and DBs have a similar feature, and that's what the systray is for, 
>>> i.e., viewing major user-installed services.
>> Don't forget that the tray (or taskbar notification area as 
>> it's supposed to be called) is one of the most abused areas of 
>> the windows GUI. I've seen installs where it takes up half the 
>> screen. So it's not
>> *necessarily* a good thing - making it mandatory to have an 
>> easy way to turn it off. And if we don't add any actual 
>> *functionality*, it should absolutely not be enabled by 
>> default. Providing it as an option is never wrong, though.
> 
> I fully agree with the not part of the default installation. And make it
> easy to turn the thing off.
> 
> In that respect it sound like a good feature for developer systems (not
> servers per se). Just ensure for a small memory footprint, preferably
> within a few 100 KB of memory. If you are going over 1 MB you are
> seriously doing something wrong. (Obviously that might be acceptable
> when I'm actively using it, but otherwise it should keep memory usage as
> low as resonably possible).

Good point. I could hack it up in C# in 15 minutes, and it'd be a 32Kb
EXE file. But it'd use 15-20Mb RAM at least, making it completely
unacceptable of course. So that's absolutely an important figure.


//Magnus


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

Re: [GENERAL] Automatic install on windows

[Magnus Hagander]

Jason Long wrote:
> I have a client that wants a disaster recovery plan put into place. 
> What is the easiest way to do a hands free install of postgresql on a
> window box?

http://pginstaller.projects.postgresql.org/silent.html


//Magnus

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

Re: [GENERAL] Looking for help regarding getting the latest inserted sequence value.

[Magnus Hagander]

Mavinakuli, Prasanna (STSD) wrote:
> Hello All,
> 
> We are looking for your help.The scenarion which we need to address
> is,There are 2 threads and both of them are in separate transction and
> insert the value to a same table and also sequence number field gets
> incremented automotically for each of them.The problem we are facing
> is,We will need to get back the appropriate id inserted for that
> particualr record as it is used in some other places.
> 
> Right now we are doing it in 2 steps.inserting the record to table.And
> getting the max(id) from the table.Now the problem is assume there is
> another thread also does the insertion and commits that transction both
> of the thread return the same id which is not desirable in our case.
> 
> It would be really very much helpful to know the form of a query which
> inserts record and also returns the latest inserted ID for that record
> in a single query.

If you're on 8.2 the easiest way is to use INSERT RETURNING. For example:
INSERT INTO t (somefield) VALUES ('somevalue') RETURNING pkey

with pkey being the SERIAL field.

You can also do it with currval() on the sequence, but that requires two
queries.

//Magnus

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

Re: [GENERAL] installing pljava on windows xp

[Magnus Hagander]

Pouria wrote:
> Hi,
> 
> When I try to install pljava 1.3 on postgresql 8.2 (or 8.1) I get an
> error stating that it cannot load pljava.dll from a location
> specificed in the config file (while the dll is clearly there). I have
> followed the postgredql and pljava manual installation instructions
> exactly with no luck.
> 
> This error is listed as fixed on the pljava wiki, yet i'm still
> experiencing it.
> 
> Any ideas?

Most likely it's a missing dependency. Try using the depends tool (from
the windows support tools) to find out why it's failing.

//Magnus


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

Re: [GENERAL] postgressqlnot support inwindows 2000

[Magnus Hagander]

siva prakash wrote:
> if i run the setup choose language then go to next button it shows error
> *"Failed to create process: 2!*

Please don't remove the mailinglist from the CC list, so others can
learn from the answers.

The error you get indicates that your windows installation is broken. At
that point it tries to execute "msiexec" which is a part of windows
installer and a core piece of windows that's not working. You need to
make sure that it works properly before you can install PostgreSQL.

//Magnus

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

   http://archives.postgresql.org/

Re: [GENERAL] postgressqlnot support inwindows 2000

[Magnus Hagander]

Dave Page wrote:
> 
>> siva prakash wrote:
>>> if i run the setup choose language then go to next button it shows error
>>> *"Failed to create process: 2!*
>> Please don't remove the mailinglist from the CC list, so others can
>> learn from the answers.
>>
>> The error you get indicates that your windows installation is broken. At
>> that point it tries to execute "msiexec" which is a part of windows
>> installer and a core piece of windows that's not working. You need to
>> make sure that it works properly before you can install PostgreSQL.
> 
> Unless I'm misreading it errors when the Next Button is clicked on the 
> language dialog, which means msiexec has already run once.

Yes, but it was not necessarily launched as "msiexec". If the file was
just double-clicked on, the path to msiexec will be fetched from the
registry and not the system PATH. That's the only explanation I can find.


> Siva; did you extract both msi files from the zip file before running the 
> installer?

That gives a different error message - it starts msiexec and then
msiexec is the one that complains. This error indicates that it can't
even find msiexec.exe to run.

//Magnus


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

Re: [GENERAL] Can't change working directory to C:/Documents and Settings in Windows

[Magnus Hagander]

On Sun, Jul 01, 2007 at 10:46:22PM -0300, Jorge Godoy wrote:
> On Sunday 01 July 2007 22:25:24 Casey Crosbie wrote:
> > Jorge,
> >
> > Thanks for the suggestion. But unfortunately, I tried both
> >   \cd "C:/Document~1" and just \cd C:/"Document~1" and neither worked.
> 
> Sorry.  It should be up to 8 chars: "Docume~1" or some variation like that 
> (I've seen ~2  due to some unknown reason).  This looks like a Windows 
> problem on finding directories with spaces in its name.  The same happens 
> with diacriticals...

To help others in the future, the way to find out what the directory short
name actually is, use "dir /x c:\". 

Not that it was the problem this time, but I'm sure someone will need it at
some point.

//Magnus

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

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

Re: [GENERAL] pgpass.conf

[Magnus Hagander]

On Tue, Jul 10, 2007 at 04:34:56PM +0530, Ashish Karalkar wrote:
> >>Hello All,
> >>
> >>I am trying tu run a script to create database from a batch programme
> >>and dont want to supply password everytime.
> >>So i tried to setup pgpass.conf file.
> >>File is kept in user profile/application data
> >>i.e
> >>C:\Documents and Settings\postgres\Application 
> >>Data\postgresql\pgpass.conf
> >>
> >>file contains:
> >>
> >>localhost:5432 :*:postgres:mypass
> >>localhost:5432:qsweb:qsweb:mypass1
> >>
> >>Still the batch asks for the password.!!!
> >> I am just not getting why its not reading password from pgpass file.
> >>
> >>can anyone please figure out what is going wrong.
> >
> >Under what user account is the batch file being run? The pgpass.conf
> >file needs to be under *that* user account, which is not necessarily the
> >one that the postgresql server runs under.
> >
> >Regards, Dave
> 
> The batch file is run under postgres user, also owner of the pgpass.conf 
> file is postgres.
> As far as my knowledge the permission checking is not done on windows 
> anyways the owner is same so i dont think there is any problem of permission
> 
> from pg documents:
> "The permissions on .pgpass must disallow any access to world or group; 
> achieve this by the command chmod 0600 ~/.pgpass. If the permissions are 
> less strict than this, the file will be ignored. (The file permissions are 
> not currently checked on Microsoft Windows, however.)"

That part is correct. But it's of course necessary that the user can *read*
the file, in order to get it's contents. But there is no check if others
can (on Windows).

So just to be sure of that,  I suggest you try logging in as the user in
question and making sure you can read the file from that account.

//Magnus

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

Re: [GENERAL] query optimizer

[Magnus Hagander]

On Thu, Jul 19, 2007 at 10:41:03AM -0400, Tom Lane wrote:
> Luca Ferrari <[EMAIL PROTECTED]> writes:
> > I'd like to better understand how the optimizer works and is implemented. 
> > Is 
> > there any available documentation (before start reading the source!) to 
> > understand concepts about geqo and system r? Any chance about any demo or 
> > presentation with detailed examples (about how the optimizer makes and 
> > discards choices, not about how to read the planner output)?
> 
> http://developer.postgresql.org/pgdocs/postgres/overview.html
> (particularly 42.5)
> 
> src/backend/optimizer/README
> 
> The developers section of the website used to have slides from a couple
> of talks I gave at OSCON, but I don't see them there anymore :-(

How long ago was this (that they were on the website)? I don't recall that
ever being removed, and I can't find it in the cvs either.

If you still have the presentations, we can always add them back in... (I
recall reading them, but don't have a local copy)

//Magnus

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

Re: [GENERAL] several postgres installations on the same machine?

[Magnus Hagander]

On Fri, Jul 20, 2007 at 09:24:05AM +0200, Zlatko Matić wrote:
> Hi.
> If I understood correctly, this blog describes how to create second instance 
> that is linked to first (the same service acount user)?
> But, I want to know whether it is possible to have second instance completely 
> independent, not influencing each other?
> Regards,

It is, but you need to install it manually. The installer will only permit
one comlpete installation per version. (you can have multiple servers
running of course, but they share the binaries if installed by the
installer)

//Magnus

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

Re: [GENERAL] query optimizer

[Magnus Hagander]

Tom Lane wrote:
> Magnus Hagander <[EMAIL PROTECTED]> writes:
>> On Thu, Jul 19, 2007 at 10:41:03AM -0400, Tom Lane wrote:
>>> The developers section of the website used to have slides from a couple
>>> of talks I gave at OSCON, but I don't see them there anymore :-(
> 
>> How long ago was this (that they were on the website)? I don't recall that
>> ever being removed, and I can't find it in the cvs either.
> 
> Hmm, I thought they were once on the same page as Bruce's papers, now
> http://www.postgresql.org/developer/coding
> but possibly I'm mistaken; it was well before the website reorg anyway.
> 
>> If you still have the presentations, we can always add them back in... (I
>> recall reading them, but don't have a local copy)
> 
> Yeah, the PDFs are still in my home directory on cvs.postgresql.org.

Added to the website, will appear on next build.

//Magnus


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

Re: [GENERAL] Silent Uninstall of Postgres

[Magnus Hagander]

Joe L wrote:
> Hi,
> 
> I saw the page on silent installation of postgres, but I want to be able
> to uninstall postgres when my product is uninstalled, silently.  I
> couldn't find the product ID anywhere, which is the method I'm using for
> uninstalling the JRE.
> 
> Any help on how to uninstall silently would be great.

If you have the MSi around, just
msiexec /qn /x postgresql-8.2-int.msi

If not, you can find the GUID in the registyr. IIRC, it's somewhere in
HKLM\Software\PostgreSQL\Installations.

//Magnus

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

   http://archives.postgresql.org/

Re: [GENERAL] Replicating db structure changes

[Magnus Hagander]

On Wed, Jul 25, 2007 at 09:08:56PM -0700, Robby Russell wrote:
> Hey all,
> 
> I'm catching up the replication options for PostgreSQL and was looking 
> at slony, but was wondering if I could get some pointers.
> 
> If I understand the slony documentation correctly, it doesn't allow me 
> to send CREATE/ALTER TABLE/INDEX statements to the master and have those 
> replicate to the slaves. Am I mistaken? If this is the case, are there 
> any alternatives to slony that would allow this to happen? We're using 
> Rails migrations, which generate the corresponding SQL statements and 
> then runs those against the production database. This works great, but 
> we're not sure how to go about having those migrations properly affect 
> slave databases, without running execute statements through slonik.
> 
> Are there any other replication options that might work for what we're 
> trying to do?

Are you looking for failover or loadsharing? If it's just failover, PITR
warm standby should have no problem with DDL. But you can't do queries
against the slave until after a failover...

//Magnus


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

Re: [GENERAL] The leanest, meanest Windows installer possible

[Magnus Hagander]

Mitchell Vincent wrote:
> Now that I think of it, shouldn't everything that is needed to build
> the .MSI installer be in one of the source packages? As a user of
> InnoSetup I'm afraid I'm clueless about MSI, so if someone could shove
> me in the right direction I'd sure appreciate it!

All that's needed is certainly in the pginstaller package. Or rather,
it's either there or in one of the packages referenced by the
documentation for that one (we don't actually ship a "source package").

You can find the source for the installer on
http://pgfoundry.org/projects/pginstaller.

//Magnus

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

Re: [GENERAL] The leanest, meanest Windows installer possible

[Magnus Hagander]

Mitchell Vincent wrote:
> Ah, I see what you mean.
> 
> I was hoping to be able to rebuild the MSI to not include the things I
> didn't need but that doesn't look like it's going to be the case
> Humbug.. Is there any way at all to modify what's contained in the MSI
> file so I can shrink the distribution size of the windows installer
> package?

Yes, you need to modify the wxs/pginst.wxs file - it lists what's
included in the package.

//Magnus

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

Re: [GENERAL] pgTray - win32 tray tool for monitoring PostgreSQL service

[Magnus Hagander]

Tony Caduto wrote:
> Andrei Kovalevski wrote:
>>Hi all!
>>
>>Everyone who use PostgreSQL server on Windows knows - it would be
>> nice to have some tray management and  monitoring tool for PostgreSQL
>> server which is running as NT Service (for example - MS SQL already
>> have such tool). I have created a new project on pgfoundry -
>> http://pgfoundry.org/projects/pgtray. I'm opened for any ideas how can
>> we improve this tool and what features whould be helpful.
>>
>>
>> Thanks, Andrei.
>>
> Have you done any development yet?   I can do something in Delphi in a
> matter of hours and would be able to donate the code as a BSD license.
> I have done a similar tray application for Firebird and it would be just
> a matter of changing the service it monitors.

yeah, this is good stuff. (not having tested it..)


> We could also do a control panel applet.

I don't really see the point. Given that "the modern way of doing
things" is sticking the icon in administrative tools and not control
panel. But I won't prevent you of course :-)

//Magnus

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

Re: [GENERAL] pgTray - win32 tray tool for monitoring PostgreSQL service

[Magnus Hagander]

Andrei Kovalevski wrote:
>> Have you done any development yet? 
> Yes, you can download and try it. Now it's a single pgtray.exe
> application. I'm going to make an msi installer and add "Autostart"
> option to the menu.

When you do the installer, please make sure it's compatible with
stackbuilder (http://pgfoundry.org/projects/stackbuilder) so it can use
the new easy-to-install method on windows.

//Magnus

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

   http://archives.postgresql.org/

Re: [GENERAL] v8.2 ... command line interface on Windows

[Magnus Hagander]

nac1967 wrote:
> I am a PostgreSQL user, using the native windows version 8.2. I am new
> to PostgreSQL and am wondering if other users have suggestions
> regarding command line interfaces. I cannot for the life of me get
> readline to work on the Windows version. If you use IPython, for
> example, readline works fantastically with smart ctrl-P search of
> previous commands and tab completion. Does anyone know of a way either
> to get readline to work or another good command line interface?

Readline only works with US keyboard layouts under native windows,
that's why it's been turned off in the binary builds. You are left with
the very limited commandline editing in the default windows command shell.

If you are using only US keyboard layout, you can rebuild psql from
source (using the MingW build system) with readline. You only need to
rebuild psql - not libpq or the backend itself.

//Magnus


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

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

Re: [GENERAL] v8.2 ... command line interface on Windows

[Magnus Hagander]

nac1967 wrote:
> On Aug 4, 5:23 pm, [EMAIL PROTECTED] ("Bill Bartlett")
> wrote:
>> Couldn't this be offered pre-built as an alternative in the Windows
>> installer or as a "psql2.exe" program?  Seems like there might be more
>> than a few PostgreSQL users running with US keyboard layouts under
>> native Windows, and providing the prebuilt version along with the
>> "normal" one might eliminate many of the postings like this that keep
>> reappearing on these lists.
>>
>> - Bill
>>
>>
>>
>>> -Original Message-
>>> From: [EMAIL PROTECTED]
>>> [mailto:[EMAIL PROTECTED] On Behalf Of
>>> Magnus Hagander
>>> Sent: Saturday, August 04, 2007 3:09 PM
>>> To: nac1967
>>> Cc: [EMAIL PROTECTED]
>>> Subject: Re: [GENERAL] v8.2 ... command line interface on Windows
>>> nac1967 wrote:
>>>> I am a PostgreSQL user, using the native windows version
>>> 8.2. I am new
>>>> to PostgreSQL and am wondering if other users have suggestions
>>>> regarding command line interfaces. I cannot for the life of me get
>>>> readline to work on the Windows version. If you use IPython, for
>>>> example, readline works fantastically with smart ctrl-P search of
>>>> previous commands and tab completion. Does anyone know of a
>>> way either
>>>> to get readline to work or another good command line interface?
>>> Readline only works with US keyboard layouts under native
>>> windows, that's why it's been turned off in the binary
>>> builds. You are left with the very limited commandline
>>> editing in the default windows command shell.
>>> If you are using only US keyboard layout, you can rebuild
>>> psql from source (using the MingW build system) with
>>> readline. You only need to rebuild psql - not libpq or the
>>> backend itself.
>>> //Magnus
>>> ---(end of
>>> broadcast)---
>>> TIP 3: Have you checked our extensive FAQ?
>>http://www.postgresql.org/docs/faq
>>
>> ---(end of broadcast)---
>> TIP 4: Have you searched our list archives?
>>
>>http://archives.postgresql.org/
> 
> Your suggestion makes a lot of sense to me. In fact, given the number
> of compiler options that exist for the Unix/Linux build, the Windows
> build is surprisingly devoid of options.

Not at all. All the options are there if you build from source, just
like on Unix. The binary build has picked one set of options - just like
the .deb build or the .rpm build has picked their sets of options. They
pick the options that makes the *most* sense on their platform in *most*
cases. If you need other options, you have to rebuild from source - just
like on Windows.

//Magnus

---(end of broadcast)---
TIP 1: 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] Checkpoint segments too small

[Magnus Hagander]

On Mon, Aug 06, 2007 at 11:26:18AM +0200, Henrik Zagerholm wrote:
> Hi list,
> 
> I'm running 8.2.4 and I've started to get these messages and even  
> though I googled for some answers I couldn't find any good info  
> against the 8.2 code base.
> 
> 2007-08-05 04:00:58.815 CEST  LOG:  checkpoints are occurring too  
> frequently (17 seconds apart)
> 2007-08-05 04:00:58.815 CEST  HINT:  Consider increasing the  
> configuration parameter "checkpoint_segments".
> 
> Right now I use the default setting.
> 
> Is there any idea of raising these at all if I can't move them to a  
> separate disk?

Yes. Just make sure you have enough diskspace for them before you do it.

//Magnus

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

   http://archives.postgresql.org/

Re: [GENERAL] PostgreSQL and cluster

[Magnus Hagander]

On Wed, Aug 08, 2007 at 01:03:54PM +0200, [EMAIL PROTECTED] wrote:
> Hi.
> Anybody know if is possible to install PostgreSQL in cluster mode on a
> cluster formed by two windows server 2003 cluster?

Yes. Set it up as a generic service, and make sure you store the data
directory (and any other tablespaces) on disks that the service depends on.

//Magnus

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

Re: [GENERAL] PostgreSQL and cluster

[Magnus Hagander]

Probably the easiest way is to unregister the server using pg_ctl
unregister, and then re-register it with the other data directory.

The other option is to edit the registry under Services and change the
commandline used to start the PostgreSQL service. Note that you will
have to reboot your server if you go with this method.

Normally, you'd point out the cluster directory when you originally
install PostgreSQL and the installer will take care of it.

//Magnus

[EMAIL PROTECTED] wrote:
> Thanks, setting it up as generic service make the clustering procedure to
> work fine, but I'm still unable to relocate the data directory. We have
> copied the DATA directory in the right path, but which variables I must
> change? I've changed the variable about the path (now I'm not in the office
> and don't remember the name) in the file PostgreSQL.conf, but nothing is
> changed.
> 
> Luca
>   
> - Original Message 
>   Da: Magnus Hagander <[EMAIL PROTECTED]>
>   To: 
>   Cc: pgsql-general@postgresql.org
>   Oggetto: Re: [GENERAL] PostgreSQL and cluster
>   Data: 08/08/07 15:41
>   
>   > 
>>
>> On Wed, Aug 08, 2007 at 01:03:54PM +0200, [EMAIL PROTECTED] wrote:
>>> Hi.
>>> Anybody know if is possible to install PostgreSQL in cluster mode on a
>>> cluster formed by two windows server 2003 cluster?
>> Yes. Set it up as a generic service, and make sure you store the data
>> directory (and any other tablespaces) on disks that the service depends
> on.
>> //Magnus
>>
>> ---(end of broadcast)---
>> TIP 2: Don't 'kill -9' the postmaster
>>
>>
>>  
>  --
>  Email.it, the professional e-mail, gratis per te: http://www.email.it/f
>  
>  Sponsor:
>  Non perderti nella giungla di facili promesse, Logos ti da credito sempre!
> 
>  Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6559&d=20070808


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

Re: [GENERAL] language interface in postgresql

[Magnus Hagander]

Trevor Talbot wrote:
> On 8/14/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote:
> 
>> Let me fine tune my question here. What I mean to say is the way we can
>> write stored procedures in C, perl etc in Postgres specifying the language
>> parameter at the end of stored procedure, compared to that, in SQL Server
>> 2000 I've seen SP writing in pure SQL only.
>> Can you write Stored Procedures in SQL Server 2000 or Oracle in different
>> programing languages as well ?
> 
> AFAIK SQL Server 2000 only has a C interface as the other option; CLR
> hosting was added in SQL Server 2005.  Because the CLR is a virtual
> machine that runs compiled bytecode, and compilers for all of the
> available languages are not necessarily available at runtime, it
> doesn't make sense to specify such code in source form.  The process
> is more like creating a function in C in PostgreSQL (compile and load
> a shared library).  Details here, if you're curious:
> http://msdn2.microsoft.com/en-us/library/ms345136.aspx
> 
> I don't know what Oracle supports.

I believe Oracle support Java in the same way MSSQL supports .net, give
or take.

And IIRC the method is you build a DLL on your client and upload it to
the server so no, source not specified int he CREATE PROCEDURE call.

//Magnus

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

   http://archives.postgresql.org/

Re: [GENERAL] Transactional DDL

[Magnus Hagander]

Harpreet Dhaliwal wrote:
> So you mean to say something like this as far as oracle is concerned:
> 
> BEGIN
>   DDL 1 (commits right after its execution)
>   DDL 2 (commits right after its execution)
> END
> 
> That means there's no concept of putting DDL statements in a transaction
> in oracle basically, right?

Yes.

//Magnus

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

Re: [GENERAL] Accessing pg_timezone_names system view

[Magnus Hagander]

On Fri, Aug 17, 2007 at 11:51:52PM +1000, Naz Gassiep wrote:
> I was wondering if there is any reason that accessing the system view 
> pg_timezone_names is extremely slow relative to other queries. The 
> following query:
> 
>SELECT * FROM pg_timezone_names;
> 
> Executes in between 29ms and 32ms on my server. It takes about the same 
> when I put a
> 
>WHERE name = 'some/timezone'
> 
> clause in it. To put this into perspective, on the pages that execute 
> this, it accounts for something like 3/4 of my DB execution time.

This view is backed by a set returning function that will enumerate all the
files in the timezone directory. The WHERE clause doesn't apply until after
the function has already traversed all files.

> As you can see, the execution of that single fetch dwarfs all other 
> processing loads. I've run this a few times, and the timings are always 
> roughly the same. Is there a way for me to speed this up? Would I be 
> better off loading these into a static table and executing from there? 

Yes, much better if it's something you're querying regularly. 

//Magnus

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

Re: [GENERAL] PostgreSQL clustering (shared disk)

[Magnus Hagander]

On Fri, Aug 17, 2007 at 04:19:57PM +0200, Hannes Dorbath wrote:
> On 17.08.2007 15:59, Tom Lane wrote:
> >On the other side of the coin, I have little confidence in DRBD
> >providing the storage semantics we need (in particular guaranteeing
> >write ordering).  So that path doesn't sound exactly risk-free either.
> 
> To my understanding DRBD provides this. I think a discussion about that 
> with the DRBD developers would be very useful for many users searching 
> for a solution to replicate PostgreSQL, so I'm cross posting this to 
> DRBD list. Maybe you can make clear in detail what requirements 
> PostgreSQL has.

It does, AFAIK, if yuo configure it properly. I think it's the "protocol"
parameter you need to set to C which is the slowest, but it's the only one
that waits for the block to hit *both* disks.

//Magnus


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

Re: [GENERAL] Automating logins for mundane chores

[Magnus Hagander]

Phoenix Kiula wrote:
> I am writing some simple batch scripts to login to the DB and do a
> pg_dump. Also, when I login to do my own SQL tinkering, I'd like not
> to be asked for a password every time (which, for silly corporate
> reasons, is quite a convoluted one).
> 
> So I read up on .pgpass. Where should this file be located. "User's
> home directory" says the manual, but there's no "home directory" for
> database users (or is there? if so, where?), only for the postgres
> user. So I promptly did "su - postgres" and added the requisite info
> in the .pgpass file therein. But that doesn't seem to automate
> anything for actual DB users.

.pgpass is read by the client. It may not even be located on the same
machine as your server, depending on where you run psql.


> Next, the manual refers to some PGPASSFILE env variable (
> http://www.postgresql.org/docs/8.2/interactive/libpq-envars.html ) but
> it is unclear where this environment is set up. I don't see any such
> setting in the postgresql.conf file, which wouldn't make much sense
> anyway. Where else can I tweak the environment variables? No pointer
> in the manual.  Or was this page only some arcane C libpg stuff?

It's in the environment on the client machine. If it's for your scripts,
you can set it inside the script before you launch psql for example. If
you need it.


> So, my questions:
> 
> 1. Where do I set up the automated password for (a) psql stuff and (b)
> for bash scripts or cron jobs -- I suppose both could have the same
> solution.

a) In the home directory of the user running psql.
b) In the home directory of the user running the cronjob.

> 2. While we're on psql, I quite like the "\timing" stuff inside psql.
> I find it very useful to have that on every time I login to psql
> console, but I don't see any command line option to automate this
> every time. The psql man page (
> http://www.postgresql.org/docs/8.2/interactive/app-psql.html ) talks
> about a "psqlrc", which on my system is found at
> "/usr/share/pgsql/psqlrc" so I opened it up, entered the only line:
> 
>\timing
> 
> And saved it. Then I started the psql console again, but there's no
> timing on by default. How can I setup default options for psql?

That should be /usr/share/pgsql/psqlrc.sample, which is just a sample
file and isn't parsed (unless your distribution did something really
strange). On a source install, you need to put the file in
/usr/local/pgsql/etc/psqlrc - since you're obviously not using a source
install, you'll need to put it wherever your package is configured to
have it (should be documented alongside the package, I hope).

The easier way is to put it in the file .psqlrc in your home directory
(same home directory as you put .pgpass in), assuming you only want this
for one user.

//Magnus



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

Re: [GENERAL] Automating logins for mundane chores

[Magnus Hagander]

Phoenix Kiula wrote:
> On 18/08/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
>> Phoenix Kiula wrote:
>>> I am writing some simple batch scripts to login to the DB and do a
>>> pg_dump. Also, when I login to do my own SQL tinkering, I'd like not
>>> to be asked for a password every time (which, for silly corporate
>>> reasons, is quite a convoluted one).
>>>
>> It's in the environment on the client machine. If it's for your scripts,
>> you can set it inside the script before you launch psql for example. If
>> you need it.
> 
> 
> Let's say my script was in Perl or PHP. What would the variable name
> be to set this password? My script is unlikely to call psql, I'm
> thinking of using only pg_dump and pg_restore.

As long as the interfaced is based off libpq, .pgpass will work. AFAIK,
this includes both Perl and PHP, and it certainly includes pg_dump and
pg_restore.



>>> 1. Where do I set up the automated password for (a) psql stuff and (b)
>>> for bash scripts or cron jobs -- I suppose both could have the same
>>> solution.
>> a) In the home directory of the user running psql.
>> b) In the home directory of the user running the cronjob.
> 
> 
> Thanks for this. I am logged in as root. Put it there and it works. I
> also put a ".psqlrc" in the home directory and that works too! Thanks!
> 
> I'd love to contribute back to the community and mention this in the
> manual for 8.2/interactive. But the community login and commenting on
> the site seems to be broken! Even after I am logged in, it does not
> show it on each page of the site, and when I submit my comment (and
> login all over again for it) it shows me a "numeric error". Where
> should I post that error?

It will appear on the site once it's been approved. But if you get an
actual error, than that needs to be fixed - please email the complete
error you get to the [EMAIL PROTECTED] mailinglist. Thanks!

//Magnus

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

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

Re: [GENERAL] Postgresql performance in production environment

[Magnus Hagander]

Phoenix Kiula wrote:

> 
> 
> Well based on some past posts, I looked into my pg_log stuff and found
> a number of these lines:
> 
> 
> [
> LOG:  could not fork new process for connection: Resource temporarily
> unavailable
> LOG:  could not fork new process for connection: Resource temporarily
> unavailable
> LOG:  could not fork new process for connection: Resource temporarily
> unavailable
> LOG:  could not fork new process for connection: Resource temporarily
> unavailable
> LOG:  could not fork new process for connection: Resource temporarily
> unavailable
> LOG:  could not fork new process for connection: Resource temporarily
> unavailable
> ]
> 
> 
> Which suggests that our guess of running out of connections is the right one.

No, you're not running out of "connections". You are, however, running
over some kernel limit.

> So, we have three options (to begin with) --
> 
> 1. Increase the number of max_connections. This seems to be a voodoo
> art and a complex calculation of database size (which in our case is
> difficult to predict; it grows very fast), hardware, and such. I
> cannot risk other apps running on this same machine.

No. You are not yet reaching max_connections, that would give you an
error message that actually says so. This message indicates that you
have an ulimit for the account that postgresql runs under that limits
some resources - in this case most likely the number of processes. And
this limit is not "compatible" with your settings for max_connections.
You need to find this ulimit, and at least change it, or even remove it.


> 2. Use connection pooling. I've found pgpool2 and pgbouncer from the
> Skype group. Does anyone have experience using either? The latter
> looks good, although we're usually skeptical about connection pooling
> in general (or is that just the mysqli_pconnect() hangover?)

Connection pooling "in general", is something that pretty much *every*
larger app will always use. It may be implemented in the app (something
which has often been troublesome in PHP solutions, but it's certainly
the norm for Java or .Net apps) or in middleware like pgpool or
pgbouncer. There should be no need to be sceptical about it in general ;-)

Can't speak for either of those apps specifically, as I haven't used
them in production.


> 3. Use caching of queries. Memcache comes recommended, but there's a
> discussion as recently as Jan 2007 on this list about race conditions
> and such (most of which I don't quite understand) which cautions
> against its use. We do expect plenty of transactions and if something
> that has been updated is not very correctly and promptly invalidated
> in the cache, it has huge business repercussions for us.

There are ways to do this, but if you can't just use timeouts to expire
from the cache, things can become pretty complicated pretty fast. But
perhaps you can isolate some kinds of queries that can be cached for 
minutes, and keep the rest without caching?


//Magnus


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

Re: [GENERAL] Postgresql performance in production environment

[Magnus Hagander]

Phoenix Kiula wrote:
> [Sorry for the length of this post. It stretched as I provided as much
> info as possible..]
> 
> So the rubber meets the road. We've put postgresql in a production
> environment with some heavy simultaneous usage. It works well in
> general, but often PG doesn't respond. How should I test what is going
> wrong? All tables are vacuumed, analyzed and such. INSERTS and UPDATEs
> are happening but not much, it's mostly SELECTs. Is PGSQL running out
> of connections? We can temporarily fix this by restarting pgsql but
> I'd like a more tenable solution.
> 
> Speculating that it could be some conf variable somewhere
> (max_fsm_pages in particular) I am including three things at the
> bottom of this post:
> 
> 1. Our PS output (for "postgres")
> 2. *Verbose* vacuum info for a table that shows max_fsm warning
> 3. Our postgresql.conf settings
> 
> My question 1 -- how should we test and tweak our production
> installation? Where should we look. In MySQL we could do a "show
> status" at the console and it would give a mountain of information.
> Then there was that handy little "tuning-primer" script that made it
> all come alive. I suppose this stuff is also available in pg_catalog
> but is there any website that goes in depth into HOW to tune, what
> different values mean, and such?
> 
> My question 2 -- in production, we're constantly seeing this message
> while vacuuming one table with less than 3 million rows, but one that
> we expect to keep growing:
> 
> [-
> WARNING:  relation "public.links" contains more than "max_fsm_pages"
> pages with useful free space
> HINT:  Consider compacting this relation or increasing the
> configuration parameter "max_fsm_pages".
> VACUUM
> -]
> 
> I can merrily increase the "max_fsm_pages" directive, but the manual
> also caveats that with "this can use more system V memory than
> available on your system". My full verbose vacuum info below includes
> the line:

Do you actually run VACUUM FULL, or do you just mean you run VACUUM over
the full database? If you run VACUUM FULL, you need to stop doing that
:-) However, you will need to run it at least once over the whole
database once you've fixed your max_fsm_pages setting.


> [-
> INFO:  "traders": scanned 3000 of 199396 pages, containing 40775 live
> rows and 0 dead rows; 3000 rows in sample, 2710124
> estimated total rows
> -]

There should be a line like this at the end of a "VACUUM VERBOSE" command:
INFO:  free space map contains 33 pages in 74 relations
DETAIL:  A total of 1184 page slots are in use (including overhead).
1184 page slots are required to track all free space.
Current limits are:  153600 page slots, 1000 relations, using 965 kB.
VACUUM


(note that my numbers are for a more or less empty database. Yours will
be much higher)

If your database size is reasonably stable, pick a good value a bit
above the numbers suggested. If you expect it to grow a lot, add some
more overhead, but monitor this value.


> Does this mean my table needs nearly 200,000 pages, and that should be
> the setting of max_fsm_pages? This server is on a fairly common setup

No. It means that the table "traders" is using 199396 pages - most of
them aren't free, so they are not tracked in the FSM.


//Magnus


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

Re: [GENERAL] server closed the connection unexpectedly

[Magnus Hagander]

Muhyiddin A.M Hayat wrote:
> Dear all,
>  
> i'm unable to connect postgres server with error :
>  
> C:\Program Files\PostgreSQL\8.2\bin>psql -U postgres siakad
> Password for user postgres:
> psql: server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
>  
>  
> pg_log :
>  
> 2007-08-19 03:00:50 LOG:  database system was shut down at 2007-08-19
> 02:58:26 Malay Peninsula Standard Time
> 2007-08-19 03:00:50 LOG:  checkpoint record is at 0/75A808
> 2007-08-19 03:00:50 LOG:  redo record is at 0/75A808; undo record is at
> 0/0; shutdown TRUE
> 2007-08-19 03:00:50 LOG:  next transaction ID: 0/1931; next OID: 16737
> 2007-08-19 03:00:50 LOG:  next MultiXactId: 1; next MultiXactOffset: 0
> 2007-08-19 03:00:51 LOG:  database system is ready
> 2007-08-19 03:01:43 LOG:  could not receive data from client: An
> operation was attempted on something that is not a socket.
>  
>  
> 2007-08-19 03:01:43 LOG:  incomplete startup packet
>  
> somebody help me please

This error is most likely some antivirus or firewall software that's
misbehaving. Try uninstalling any such software from the server (you can
try disabling it first, but often a complete uninstall is needed) and
try again.

//Magnus

//Magnus

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

Re: [GENERAL] Postgresql performance in production environment

[Magnus Hagander]

Phoenix Kiula wrote:
> On 19/08/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
>> Phoenix Kiula wrote:
> 
> .snipped
> 
>>> I can merrily increase the "max_fsm_pages" directive, but the manual
>>> also caveats that with "this can use more system V memory than
>>> available on your system". My full verbose vacuum info below includes
>>> the line:
>> Do you actually run VACUUM FULL, or do you just mean you run VACUUM over
>> the full database? If you run VACUUM FULL, you need to stop doing that
>> :-) However, you will need to run it at least once over the whole
>> database once you've fixed your max_fsm_pages setting.
> 
> 
> 
> No we only do a "vacuum analyze" when we do something manually.
> Otherwise, it's all "autovacuum". Never done  a "vacuum full" --

Ok. That's good.

> should we do one now given that we've overrun our max_fsm_pages?

Yes, but not until you've fixed it. And only once.

>>> [-
>>> INFO:  "traders": scanned 3000 of 199396 pages, containing 40775 live
>>> rows and 0 dead rows; 3000 rows in sample, 2710124
>>> estimated total rows
>>> -]
>> There should be a line like this at the end of a "VACUUM VERBOSE" command:
>> INFO:  free space map contains 33 pages in 74 relations
>> DETAIL:  A total of 1184 page slots are in use (including overhead).
>> 1184 page slots are required to track all free space.
>> Current limits are:  153600 page slots, 1000 relations, using 965 kB.
>> VACUUM
>>
> 
> 
> Nope, there's no line that includes such useful info. The entire
> verbose output was included in my note. I did not see the words
> "Current limits are". Do I need to enable something in the conf file
> to get more verbose output? "debug2", "debug3" -- kind of stuff?

Strange. It comes out at level INFO, and you do see other stuff at INFO
level. Any chance this just got mixed up with an autovacuum run and that
input it somewhere in the middle of your output? (that this "traders"
info is from autovac)

//Magnus

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

Re: [GENERAL] Postgresql performance in production environment

[Magnus Hagander]

Phoenix Kiula wrote:
> On 19/08/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
>> Phoenix Kiula wrote:
> 
>> There are ways to do this, but if you can't just use timeouts to expire
>> from the cache, things can become pretty complicated pretty fast. But
>> perhaps you can isolate some kinds of queries that can be cached for 
>> minutes, and keep the rest without caching?
> 
> 
> Thanks. In fact we need caching on a very specific part of our
> application, for only three queries which hit the DB hard with
> thousands of simultaneous SELECTs.
> 
> Do pgmemcache or pgbouncer allow for very specific usage? Both look
> way too complex. I don't mind the initial headachy setup and config,
> but then I would like the system to hum on its own, and the querying
> should be simple and intuitive.
> 
> I need a simple mechanism to query the cache, and invalidate a
> specific query in the cache when the underlying table is UPDATED so
> that the query gets cached afresh when issued later. (And a way to use
> this mechanism through PHP or Perl would be splendid).
> 
> TIA for any tips!

You can use LISTEN and NOTIFY to clear the cache, if you have many
clients that can cause cache invalidations. If you only have a single
app that can update the database, you can invalidate the cache from that
applications code directly (such as using asp.net output caching if you
were doing it in .net).
I've implemented the prior a couple of times, but it does get a bit
complex. The second part would be easier, but I don't have any direct
pointers on that since it depends on the app development framework
you're using.

//Magnus

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

Re: [GENERAL] Postgresql performance in production environment

[Magnus Hagander]

Phoenix Kiula wrote:
> On 19/08/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
>> Phoenix Kiula wrote:
>>> On 19/08/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
> 
> 
>>> should we do one (VACUUM FULL) now given that we've overrun our 
>>> max_fsm_pages?
>> Yes, but not until you've fixed it. And only once.
>>
> 
> 
> 
> FIxed what - the max_fsm_pages? That was my question: how to know what
> value to set for this. If the "vacuum verbose" won't give me the info
> you suggested because it is likely overlapping with autovacuum, should
> I temporarily turn autovacuum off and then run vacuum verbose? Also,
> while running vacuum full, any precautions to take?

Yeah, you can do that - or you can just trawl back through the logs to
find that information - it's there somewhere. grep would be helpful to
find it.

vacuum full will take out blocking locks on your database, so run it
during a maintenance window or at least during a low-traffic time.

//Magnus

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

Re: [GENERAL] Postgresql performance in production environment

[Magnus Hagander]

Phoenix Kiula wrote:
> On 19/08/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
>> Phoenix Kiula wrote:
> 
> ...snip
> 
>> There should be a line like this at the end of a "VACUUM VERBOSE" command:
>> INFO:  free space map contains 33 pages in 74 relations
>> DETAIL:  A total of 1184 page slots are in use (including overhead).
>> 1184 page slots are required to track all free space.
>> Current limits are:  153600 page slots, 1000 relations, using 965 kB.
>> VACUUM
>>
> 
> 
> I ran the vacuum analyze verbose again, and did not see anything like
> that. Should I run a vacuum alone?

It shows up in both variants for me.
Oh, hang on. I think it only shows up if you're logged in with a
superuser - table owner is not enough. Check that.


> In any case, in your example, which number would I take note of, and
> derive the max_fsm_pages from?

The 1184 number (the one for "page slots are required to track")


> I do notice this in my own output:
> 
>  There were 2959498 unused item pointers.
>  133616 pages contain useful free space.
>  0 pages are entirely empty.
> 
> Does this mean I should have over 133,616 in my max_fsm_pages. Should
> I set it up at 150,000 for example?

Probably not enough - that's for a single table, no?

> Secondly, the max_fsm_relations -- if I have about 150 "relations" in
> my database (relations as per PGSQL lingo) then can this figure be,
> say, 200? Or does this have to match max_fsm_pages?

No need to match. If you have 150 relations, 200 is a reasonable value.
But once you get the proper output from the vacuum command, it tells you
that as well (74 in my example above)

As for your other question,how to view connections. Use "SELECT * FROM
pg_stat_activity". See
http://www.postgresql.org/docs/8.2/static/monitoring-stats.html.

//Magnus



---(end of broadcast)---
TIP 1: 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] FATAL: could not reattach to shared memory (Win32)

[Magnus Hagander]

Alvaro Herrera wrote:
> Terry Yapt wrote:
> 
>> This is the main error:
>> * FATAL:  could not reattach to shared memory (key=5432001, addr=01D8): 
>> Invalid argument
>>
>> It is always followed by this another system-app error:
>> * LOG:  unrecognized win32 error code: 487
> 
> FWIW,
> http://help.netop.com/support/errorcodes/win32_error_codes.htm
> 
> says
> 487   Attempt to access invalid address.  ERROR_INVALID_ADDRESS
> 
> This problem has been reported before, for example in
> 
> http://bbs.chinaunix.net/thread-973003-1-1.html
> (not that I can read it very well)
> 
> and
> 
> http://lists.pgfoundry.org/pipermail/brasil-usuarios/20061127/003150.html
> 
> No resolution seems to have been found.

8.3 will have a new way to deal with shared mem on win32. It's the same
underlying tech, but we're no longer trying to squeeze it into an
emulation of sysv. With a bit of luck, that'll help :-)

//Magnus


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

Re: [GENERAL] FATAL: could not reattach to shared memory (Win32)

[Magnus Hagander]

Shelby Cain wrote:
>> - Original Message  From: Magnus Hagander
>> <[EMAIL PROTECTED]> To: Alvaro Herrera
>> <[EMAIL PROTECTED]> Cc: Terry Yapt <[EMAIL PROTECTED]>;
>> pgsql-general@postgresql.org Sent: Thursday, August 23, 2007
>> 3:43:32 PM Subject: Re: [GENERAL] FATAL: could not reattach to
>> shared memory (Win32)
>> 
>> 
>> 8.3 will have a new way to deal with shared mem on win32. It's the
>> same underlying tech, but we're no longer trying to squeeze it into
>> an emulation of sysv. With a bit of luck, that'll help :-)
>> 
>> //Magnus
>> 
> 
> Wild guess on my part... could that error be the result of an attempt
> to map shared memory into a process at a fixed location that just
> happens to already be occupied by a dll that Windows had decided to
> relocate?

Not that wild a guess, really :-) I'd say it's a very good possibility -
but I have no idea why it'd do that, since all backends load the same
DLLs at that stage.

//Magnus


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

Re: [GENERAL] Turning off atime on PostgreSQL DB volumes

[Magnus Hagander]

Keaton Adams wrote:
> After reading several articles on the performance drag that Linux atime
> has on file systems we would like to mount our DB volumes with the
> noatime parameter to see just what type of a performance gain we will
> achieve.  Does PostgreSQL use atime in any way when reading/writing
> data?  If we turn off/disable atime on the DB volumes will that cause
> any type of issue at all with PostgreSQL 8.1 on Red Hat Enterprise Linux?

No, it shouldn't cause any issues. Just turn it off.

//Magnus


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

Re: [GENERAL] PostgreSQL with Kerberos and Active Directory

[Magnus Hagander]

On Thu, Aug 30, 2007 at 02:07:13PM +0300, Idan Miller wrote:
> Hi everyone,
> 
> I'm trying to configure PostgreSQL version 8.2.4 with Kerberos and Active
> Directory.
> The AD is run on a windows 2003 server, and the postgre on gentoo.
> The gentoo computer name is postgre and it's added to the windows 2003
> server AD domain.
> 
> I did the following:
> - I compiled postgre with kerberos support and installed it on the gentoo
> machine.
> - I created a keytab for the user postgres/postgre on the windows 2003
> server machine and copied it to the gentoo machine.
> - I configured the postgresql.conf to point to the keytab.
> - I configured pg_hba.conf to authenticate remote users by kerberos.
> - I followed additional configurations from the howto in the mailing list
> archives.
> 
> Now, when trying to log in with an AD user to postgre I get:
> psq: krb5_sendauth: Bad application version was sent (via sendauth)
> 
> Any help will be appreciated.

Are you sure you have postgresql 8.2 on both ends of the connection? Are
yuor clients on windos or unix?

//Magnus

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

Re: [GENERAL] PostgreSQL with Kerberos and Active Directory

[Magnus Hagander]

Ok. I'd try locally from the machine first, so you know the krb
configurations are absolutely identical all the way. Just change your
pg_hba so it uses krb5 (and don't forget to use -h - krb5 only works over
TCP/IP sockets)

THat said, I think your problem is in that you use "postgres" as your SPN.
It has to be uppercase POSTGRES to work with Active Directory.

//Magnus


On Thu, Aug 30, 2007 at 03:34:18PM +0300, Idan Miller wrote:
> We tried to connect from a different gentoo machine.
> both client and server are running version 8.2.4 of postgresql.
> right now, we are trying to connect from gentoo, but we want to connect from
> windows as well
> 
> Idan
> 
> 
> On 8/30/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
> >
> > On Thu, Aug 30, 2007 at 02:07:13PM +0300, Idan Miller wrote:
> > > Hi everyone,
> > >
> > > I'm trying to configure PostgreSQL version 8.2.4 with Kerberos and
> > Active
> > > Directory.
> > > The AD is run on a windows 2003 server, and the postgre on gentoo.
> > > The gentoo computer name is postgre and it's added to the windows 2003
> > > server AD domain.
> > >
> > > I did the following:
> > > - I compiled postgre with kerberos support and installed it on the
> > gentoo
> > > machine.
> > > - I created a keytab for the user postgres/postgre on the windows 2003
> > > server machine and copied it to the gentoo machine.
> > > - I configured the postgresql.conf to point to the keytab.
> > > - I configured pg_hba.conf to authenticate remote users by kerberos.
> > > - I followed additional configurations from the howto in the mailing
> > list
> > > archives.
> > >
> > > Now, when trying to log in with an AD user to postgre I get:
> > > psq: krb5_sendauth: Bad application version was sent (via sendauth)
> > >
> > > Any help will be appreciated.
> >
> > Are you sure you have postgresql 8.2 on both ends of the connection? Are
> > yuor clients on windos or unix?
> >
> > //Magnus
> >

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

Re: [GENERAL] PostgreSQL with Kerberos and Active Directory

[Magnus Hagander]

Not really - it's always worked that way for me :-(

Have you managed to make any other kerberised applications work on this
machine? There are sample programs in the kerberos package - try those to
see if the problem is in postgresql or int he kerberos libs/setup.

//Magnus

On Sun, Sep 02, 2007 at 12:05:54PM +0300, Idan Miller wrote:
> Hi Magnus,
> 
> I tried changing the SPN to uppercase POSTGRES, but still the same error
> occurs.
> Any other ideas? (this didn't work both locally and remotely).
> 
> Idan.
> 
> 
> On 8/31/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
> >
> > Ok. I'd try locally from the machine first, so you know the krb
> > configurations are absolutely identical all the way. Just change your
> > pg_hba so it uses krb5 (and don't forget to use -h - krb5 only works over
> > TCP/IP sockets)
> >
> > THat said, I think your problem is in that you use "postgres" as your SPN.
> > It has to be uppercase POSTGRES to work with Active Directory.
> >
> > //Magnus
> >
> >
> > On Thu, Aug 30, 2007 at 03:34:18PM +0300, Idan Miller wrote:
> > > We tried to connect from a different gentoo machine.
> > > both client and server are running version 8.2.4 of postgresql.
> > > right now, we are trying to connect from gentoo, but we want to connect
> > from
> > > windows as well
> > >
> > > Idan
> > >
> > >
> > > On 8/30/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
> > > >
> > > > On Thu, Aug 30, 2007 at 02:07:13PM +0300, Idan Miller wrote:
> > > > > Hi everyone,
> > > > >
> > > > > I'm trying to configure PostgreSQL version 8.2.4 with Kerberos and
> > > > Active
> > > > > Directory.
> > > > > The AD is run on a windows 2003 server, and the postgre on gentoo.
> > > > > The gentoo computer name is postgre and it's added to the windows
> > 2003
> > > > > server AD domain.
> > > > >
> > > > > I did the following:
> > > > > - I compiled postgre with kerberos support and installed it on the
> > > > gentoo
> > > > > machine.
> > > > > - I created a keytab for the user postgres/postgre on the windows
> > 2003
> > > > > server machine and copied it to the gentoo machine.
> > > > > - I configured the postgresql.conf to point to the keytab.
> > > > > - I configured pg_hba.conf to authenticate remote users by kerberos.
> > > > > - I followed additional configurations from the howto in the mailing
> > > > list
> > > > > archives.
> > > > >
> > > > > Now, when trying to log in with an AD user to postgre I get:
> > > > > psq: krb5_sendauth: Bad application version was sent (via sendauth)
> > > > >
> > > > > Any help will be appreciated.
> > > >
> > > > Are you sure you have postgresql 8.2 on both ends of the connection?
> > Are
> > > > yuor clients on windos or unix?
> > > >
> > > > //Magnus
> > > >
> >

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

Re: [GENERAL] Version 8.2.5 for Windows doesn't startup normallyafter upgrading from 8.2.4

[Magnus Hagander]

On Wed, Sep 19, 2007 at 09:10:43AM -0500, Walter Roeland wrote:
> Dave,
> 
> For the reasons you mention for using pg_ctl -w, it seemed to me the wisest 
> option to recreate the postgres database. After the normal startup messages, 
> the following error appears once in the log:
> 
> 127.0.0.1 postgres postgres FATAL:  the database system is starting up
> 
> I assume that this is due to the -w option. The service is now working 
> normally.

Yeah, this is normal. pg_ctl polls the server before it has finished the
startup sequence, causing this message to be logged.

It is kind of "scary" that it's logged as FATAL, but it's actually not a
problem at all.

//Magnus

---(end of broadcast)---
TIP 1: 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] autovacuum

[Magnus Hagander]

On Thu, Sep 20, 2007 at 04:33:25PM -0500, Scott Marlowe wrote:
> On 9/20/07, Robert Fitzpatrick <[EMAIL PROTECTED]> wrote:
> > On Thu, 2007-09-20 at 16:38 -0400, Bill Moran wrote:
> > > In response to Robert Fitzpatrick <[EMAIL PROTECTED]>:
> > > Why does everyone leave of the IO subsystem?  It's almost as if many
> > > people don't realize that disks exist ...
> > >
> > > With 2G of RAM, and a DB that's about 3G, then there's at least a G of
> > > database data _not_ in memory at any time.  As a result, disk speed is
> > > important, and _could_ be part of your problem.  You're not using RAID
> > > 5 are you?
> >
> > Yes, using RAID 5, not good? RAID 5 with hot fix total of 4 drives. All
> > SATA 80GB drives giving me little under 300GB to work with.
> 
> RAID5 optimizes for space, not performance or reliability.  It gets
> faster but less reliable as it gets bigger.  If you can afford the
> space RAID-10 is generally preferred.
> 
> Note however that it is far more important for most general purpose
> servers to have a RAID controller that is both fast by nature (i.e.
> not $50.00) and has battery backed cache with write thru turned on.

Surely you mean with write thru turned *off*... Or write-back turned on.
But write thru turned on will make your battery unnecessary...

//Magnus

---(end of broadcast)---
TIP 1: 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] Please change default characterset for database cluster

[Magnus Hagander]

Ron Johnson wrote:
> On 09/28/07 21:12, Tom Lane wrote:
>> Michael Fuhr <[EMAIL PROTECTED]> writes:
>>> No, it isn't.  If you get UTF8 (formerly UNICODE) as a default then
>>> it's because initdb is picking it up from your environment.
>> Which initdb has done since 8.0.  If the OP is such a rabid UTF8 fan,
>> one wonders why his default locale setting isn't using UTF8 ...
> 
> He uses Windows?

Just FYI: The next version of the Windows installer will attempt to pick
up the locale from the environment. If that succeeds, it will use that
locale and UNICODE encoding. Only if that fails will it pick SQL_ASCII.

//Magnus

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

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

Re: [GENERAL] windows and pg 8.2 (change database to another server)

[Magnus Hagander]

On Sun, Sep 30, 2007 at 11:36:23PM +0200, Terry Yapt wrote:
> Hello all,
> 
> I would like to change a pg database to another server.
> 
> The source environment is: postgresql Windows v.8.2.4 (windows xp 
> workstation).
> The target environment is: postgresql Windows v.8.2.5 (windows 2003 Server).
> 
> I would like to do migration without pg_dumpall and I think I can do the 
> migration process _only_ copying this (after stop Postgresql service, of 
> course):
> 1.- Data directory (c:\program files\postgresql\8.2\data).
> 2.- Another TableSpace folders/directories.
> 
> But after copy that folders to my new server, service PostgreSQL doesn't 
> start with a 'cannot create postmaster.pid'.
> 
> First of all.  I think this method is admisible. Isn't it ?

It is.


> And second question: I think my problem is that some rights are wrong 
> after copying data folder.  What are the right rights to apply to data 
> folder ?

Yes, most likely. You need to grant the postgres service account "Change"
permissions (or Full Control, but Change is recommended) on the data
directory. If you didn't do anything speicifically, it will just have
inherited from further up in the tree, which means that the service account
only has "Read" access.

//Magnus

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

Re: [GENERAL] windows and pg 8.2 (change database to another server)

[Magnus Hagander]

On Mon, Oct 01, 2007 at 11:38:53PM +0200, Terry Yapt wrote:
> Magnus Hagander escribió:
> >On Sun, Sep 30, 2007 at 11:36:23PM +0200, Terry Yapt wrote:
> >>First of all.  I think this method is admisible. Isn't it ?
> >>
> >
> >It is.
> >  
> 
> Glad to read it  :-)
> 
> >>And second question: I think my problem is that some rights are wrong 
> >>after copying data folder.  What are the right rights to apply to data 
> >>folder ?
> >>
> >
> >Yes, most likely. You need to grant the postgres service account "Change"
> >permissions (or Full Control, but Change is recommended) on the data
> >directory. If you didn't do anything speicifically, it will just have
> >inherited from further up in the tree, which means that the service account
> >only has "Read" access.
> >
> >//Magnus
> >  
> 
> I have tried a couple of combinations none of them was successful.
> 
> I have tried to assign 'Full Control' to data folder and sub-folders and 
> files.  Varying this 'Full Control' preserving inheritance, deleting 
> inheritance.  I have tried to assign 'Full Control' to Administrators 
> and SYSTEM accounts/groups too.  I have tried to do the same thing over 
> sub-folders, files and so on.

Your errors certainly indicate it's a permissions issue. You should also
veryfi that the read-only flag is not set on any of the files. I don't see
how it could become that, but if it is that'll give the same error.

Also, check the permissions on c:\, C:/Archivos de programa/ and all teh
way down the tree. The postgres service account needs read access there,
and write to data and below.

Permissions set for SYSTEM and/or administrators make no difference at all
to the server.

And yes, it shoul dbe set on the file and all subdirs. Use the checkbox to
overwrite all permissions on subdirs, that's the fastest way.

//Magnus

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

Re: [GENERAL] pg_cancel_backend() does not work with buzz queries

[Magnus Hagander]

On Wed, Oct 03, 2007 at 11:18:32AM +0100, Richard Huxton wrote:
> Don't forget to cc: the list.
> Try not to top-post replies, it's easier to read if you reply below the 
> text you're replying to.
> 
> Sergey Konoplev wrote:
> >>1. Is it always the same query?
> >>2. Does the client still think it's connected?
> >>3. Is that query using up CPU, or just idling?
> >>4. Anything odd in pg_locks for the problem pid?
> 
> >1. No it isn't. I have few functions (plpgsql, plpython) that cause
> >such situations more often than another but they are called more often
> >also.
> 
> OK, so there's no real pattern. That would suggest it's not a particular 
> query-plan that's got something wrong.
> 
> Do you always get this problem inside a function?

Does pl/python listen to SIGINT during execution of functions? If not,
that'd be an explanation - if it's stuck inside a pl/python function...

AFAIK, pl/pgsql does listen for SIGINT during execution, but I don't nkow
abuot plpython.

> 4. You have to cancel the query from the command-line using "kill -9 
> "

That's not cancel, that's taking a sledgehammer to your server :(

//Magnus

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

   http://archives.postgresql.org/

Re: [GENERAL] ERROR: could not open file "pg_subtrans...

[Magnus Hagander]

> > I have Postgres 8.1.3 running on Windows XP. I keep seeing this error in 
> > the log files:
> 
> Update to 8.1.3.

I'm sure Alvaro means 8.1.10..

> Better yet, update to 8.2.5, as 8.1 is unsupported on Windows.
> 
> http://www.postgresql.org/about/news.865

Since we haven't released 8.3 yet, it's still suported. That said, it's still 
good advice since there's a reason we're dropping that support soon.

The first thing to do with an error like this, though, is to look for and 
remove any antivirus, antispyware or such programs.

/Magnus  
> 
> -- 
> Alvaro Herrera   Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
> "La gente vulgar solo piensa en pasar el tiempo;
> el que tiene talento, en aprovecharlo"
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
> 

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

> Hi,
> 
> Sorry for top-posting but since I am answering questions that don't all
> appear in this message:
> 
> - I installed the default download of Postgres. I didn't compile myself,
> so it's probably the mingw version

It is.
 
> - Max_connections is set to 500. I did that originally because I kept
> seeing a message about no connection available and I thought it was
> because I was not allocating enough connections. My machine has 2GB of RAM.

There's your problem. 500 is way above what the windows version can handle. 
IIRC the hard max is somewhere around 200  depending on some OS factors that we 
don't entirely know. I'd never recommend going above 100-150. With no more than 
2Gb ram, not above 100. 

You'll ned to figure out what's eating all your connections - it sounds like 
it's not entirely expected. Perhaps conections are leaked somewhere?

> - How do I determine what DLL is failing and what is causing it to fail in
> its initialization routine?

You really can't in this case, but if you could it wouldn't help you. It's 
windows running out of global resources.

/Magnus 

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

On Wed, Oct 17, 2007 at 02:40:14AM -0400, Tom Lane wrote:
> "Magnus Hagander" <[EMAIL PROTECTED]> writes:
> >> - Max_connections is set to 500.
> 
> > There's your problem. 500 is way above what the windows version can
> > handle. IIRC the hard max is somewhere around 200 depending on some OS
> > factors that we don't entirely know.
> 
> Maybe we should put an #ifdef WIN32 into guc.c to limit max_connections
> to something we know the platform can stand?  It'd be more comfortable
> if we understood exactly where the limit was, but I think I'd rather
> have an "I'm sorry Dave, I can't do that" than random-seeming crashes.

Yeayh, that's probably a good idea - except we never managed to figure out
where the limit is. It appears to vary pretty wildly between different
machines, for reasons we don't really know why (total RAM has some effect
on it, but that's not the only one, for example)

//Magnus

---(end of broadcast)---
TIP 1: 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] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

> > > Maybe we should put an #ifdef WIN32 into guc.c to limit
> > > max_connections to something we know the platform can stand?  It'd
> > > be more comfortable if we understood exactly where the limit was,
> > > but I think I'd rather have an "I'm sorry Dave, I can't do that"
> > > than random-seeming crashes.
> > 
> > Yeayh, that's probably a good idea - except we never managed to
> > figure out where the limit is. It appears to vary pretty wildly
> > between different machines, for reasons we don't really know why
> > (total RAM has some effect on it, but that's not the only one, for
> > example)
> 
> How about we just emit a warning..
> 
> WARNING: Connections above 250 on Windows platforms may have
> unpredictable results. 
> 

That's probably a better idea. I'll go look at that unless people feel we 
should just stick it in docd/faq?

/Magnus

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

Tom Lane wrote:
> "Magnus Hagander" <[EMAIL PROTECTED]> writes:
>>> How about we just emit a warning..
>>>
>>> WARNING: Connections above 250 on Windows platforms may have
>>> unpredictable results. 
> 
>> That's probably a better idea. I'll go look at that unless people feel we 
>> should just stick it in docd/faq?
> 
> Unless we've got some credible basis for citing a particular number,
> I don't think this will help much.

ok. Maybe a note in the docs or FAQ at least?


> Rainer Bauer <[EMAIL PROTECTED]> writes:
>> My guess is that Windows is running out of handles. Each backend uses about
>> 150 handles. 100 Backends means 15000 handles. Depending how many other
>> programs are currently running the no. of startable backends will vary
>> depending on the total handle limit Windows imposes.
> 
> I find this theory very interesting; for one thing it explains the
> reported variability of results, since the non-Postgres demand for
> handles could be anything.  Is there any way we could check it?
> If it's accurate, what we ought to be whining about is some
> combination of max_connections and max_files_per_process, rather
> than only considering the former.

It's not that simple. Merlin ran some checks, and drastically reducing
max_files_per_process made no measurable difference.

My best guess is it's due to the non-paged pool. Handles are a part of
what goes in there, but only a part.

//Magnus

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

Trevor Talbot wrote:
> On 10/20/07, Rainer Bauer <[EMAIL PROTECTED]> wrote:
> 
>> Anyway, the problem are the no. of semaphores created by Postgres:
>> Every backend creates at least 4* semaphores. Just
>> increase  to an unusual high value (say 1) and
>> start creating new connections while monitoring the handle count.
> 
> Hmm, they're actually the same semaphores, so the only cost is for
> slots in each process's handle table, which comes from kernel paged
> pool.  Testing shows I can easily create about 30 million handles to a
> given object on this machine.  This is under win2003 with 1.25GB RAM,
> which gives it a paged pool limit of 352MB.
> 
> I tried going up to 2 max_connections, and still blew postmaster's
> VM space long before paged pool was exhausted.  I couldn't test any
> higher values, as there's some interaction between max_connections and
> shared_buffers that prevents it from mapping the buffer contiguously.
> 
> Something's missing though, since I'm not hitting the same issue you
> are.  How are you generating the connections?  I just have an app
> calling PQconnectdb() in a loop, but I guess that's not good enough.

Yeah, something is obviously missing.. Are you guys on the exactly the
same Windows versions? WRT both version and servivepack. Anybody on x64
windows?

Another thing worth testing - check if the amount of shared memory used
makes a noticable difference. Try both very small and very large values.

I don't think the paged pool is the problem - I think it's the nonpaged
pool. Would be interesting to track that one in the failing case (using
performance monitor, up to the point where it fails). And the nonpaged
one is smaller... If that looks like it's the problem, it could be
helpful to do a pooltag trace on it (see for example
http://blogs.msdn.com/ntdebugging/archive/2006/12/18/Understanding-Pool-Consumption-and-Event-ID_3A00_--2020-or-2019.aspx)

//Magnus

---(end of broadcast)---
TIP 1: 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] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

Trevor Talbot wrote:
> On 10/17/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
>> On Wed, Oct 17, 2007 at 02:40:14AM -0400, Tom Lane wrote:
> 
>>> Maybe we should put an #ifdef WIN32 into guc.c to limit max_connections
>>> to something we know the platform can stand?  It'd be more comfortable
>>> if we understood exactly where the limit was, but I think I'd rather
>>> have an "I'm sorry Dave, I can't do that" than random-seeming crashes.
>> Yeayh, that's probably a good idea - except we never managed to figure out
>> where the limit is. It appears to vary pretty wildly between different
>> machines, for reasons we don't really know why (total RAM has some effect
>> on it, but that's not the only one, for example)
> 
> I tried generating idle connections in an effort to reproduce
> Laurent's problem, but I ran into a local limit instead: for each
> backend, postmaster creates a thread and burns 4MB of its 2GB address
> space.  It fails around 490.

Oh, that's interesting. That's actually a sideeffect of us increasing
the stack size for the postgres.exe executable in order to work on other
things. By default, it burns 1MB/thread, but ours will do 4MB. Never
really thought of the problem that it'll run out of address space.
Unfortunately, that size can't be changed in the CreateThread() call -
only the initially committed size can be changed there.

There are two ways to get around it - one is not using a thread for each
backend, but a single thread that handles them all and then some sync
objects around it. We originally considered this but said we won't
bother changing it because the current way is simpler, and the overhead
of a thread is tiny compared to a process. I don't think anybody even
thought about the fact that it'd run you out of address space...

The other way is to finish off win64 support :-) Which I plan to look
at, but I don't think that alone should be considered a solution.

The question is if it's worth fixing that part, if it will just fall
down for other reasons before we reach these 500 connections anyway. Can
you try having your program actually run some queries and so, and not
just do a PQconnect? To see if it falls over then, because it's been
doing more?


> Laurent's issue must depend on other load characteristics.  It's
> possible to get a trace of DLL loads, but I haven't found a
> noninvasive way of doing that.  It seems to require a debugger be
> attached.

AFAIK, it does require that, yes.

//Magnus

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

On Sun, Oct 21, 2007 at 09:43:27PM +0200, Rainer Bauer wrote:
> Magnus Hagander wrote:
> 
> >Trevor Talbot wrote:
> >> On 10/20/07, Rainer Bauer <[EMAIL PROTECTED]> wrote:
> >> 
> >>> Anyway, the problem are the no. of semaphores created by Postgres:
> >>> Every backend creates at least 4* semaphores. Just
> >>> increase  to an unusual high value (say 1) and
> >>> start creating new connections while monitoring the handle count.
> >> 
> >> Hmm, they're actually the same semaphores, so the only cost is for
> >> slots in each process's handle table, which comes from kernel paged
> >> pool.  Testing shows I can easily create about 30 million handles to a
> >> given object on this machine.  This is under win2003 with 1.25GB RAM,
> >> which gives it a paged pool limit of 352MB.
> 
> On my system I can only create about 4 millions semaphores.

Is that 4 million semaphores, or 4 million handles to a smaller number of
semaphores?

> >> I tried going up to 2 max_connections, and still blew postmaster's
> >> VM space long before paged pool was exhausted.  I couldn't test any
> >> higher values, as there's some interaction between max_connections and
> >> shared_buffers that prevents it from mapping the buffer contiguously.
> >> 
> >> Something's missing though, since I'm not hitting the same issue you
> >> are.  How are you generating the connections?  I just have an app
> >> calling PQconnectdb() in a loop, but I guess that's not good enough.
> 
> I am using the ASCII version of the psqlODBC driver version 8.2.4.2 to
> establish the test connections.

Could you try the same tests with the client runnint on a different system?
Since the client eats up a bunch of handles and such as well, and that
would eliminate the difference due to different clients.


> >Yeah, something is obviously missing.. Are you guys on the exactly the
> >same Windows versions? WRT both version and servivepack. Anybody on x64
> >windows?
> 
> No, I am using WinXP SP2 32 bit with 2GB RAM.

Ok. So one is on XP and one is on 2003. That' interesting - given that 2003
is tuned towards servers, it doesn't surprise me that it allows more
clients before breaking.


> These are my altered settings from the default 8.2.5 Postgres installation:
> ssl = on

Does it make a difference if you turn this off?

> shared_buffers = 512MB

As a general note, thsi is *way* too high. All evidence I've seen points to
that you should have shared_buffers as *small* as possible on win32,
because memory access there is slow. And leave more of the caching up to
the OS.

> work_mem = 16MB
> maintenance_work_mem = 256MB
> wal_sync_method = fsync_writethrough
> checkpoint_segments = 15
> checkpoint_timeout = 30min
> random_page_cost = 3.0
> effective_cache_size = 1GB
> autovacuum_vacuum_scale_factor = 0.10
> autovacuum_analyze_scale_factor = 0.05

None of those should make a difference on this. 

> 
> >Another thing worth testing - check if the amount of shared memory used
> >makes a noticable difference. Try both very small and very large values.
> 
> Well I tried different shared_buffers settings, but the result was consisting:
> with max_connections set to 1, I can create 150 database connections.

Ok. But if you decrease max_connections, you can have more connections? Or
the other way around?


> However, I checked the handle count at the moment the last connection fails
> and it is only at 1,5 million. So it seems the handles are not the primary
> problem.

Good, it shouldn't be, but it's good to have that confirmed.

/Magnus

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

On Mon, Oct 22, 2007 at 10:23:16AM +0200, Magnus Hagander wrote:
> > >> I tried going up to 2 max_connections, and still blew postmaster's
> > >> VM space long before paged pool was exhausted.  I couldn't test any
> > >> higher values, as there's some interaction between max_connections and
> > >> shared_buffers that prevents it from mapping the buffer contiguously.
> > >> 
> > >> Something's missing though, since I'm not hitting the same issue you
> > >> are.  How are you generating the connections?  I just have an app
> > >> calling PQconnectdb() in a loop, but I guess that's not good enough.
> > 
> > I am using the ASCII version of the psqlODBC driver version 8.2.4.2 to
> > establish the test connections.
> 
> Could you try the same tests with the client runnint on a different system?
> Since the client eats up a bunch of handles and such as well, and that
> would eliminate the difference due to different clients.

Followup, when running these tests, could you check using Process Explorer
if you're hitting close to the limit of either of the two pools? See
http://blogs.technet.com/askperf/archive/2007/03/07/memory-management-understanding-pool-resources.aspx

//Magnus

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

On Mon, Oct 22, 2007 at 10:41:14AM +0200, Magnus Hagander wrote:
> On Mon, Oct 22, 2007 at 10:23:16AM +0200, Magnus Hagander wrote:
> > > >> I tried going up to 2 max_connections, and still blew postmaster's
> > > >> VM space long before paged pool was exhausted.  I couldn't test any
> > > >> higher values, as there's some interaction between max_connections and
> > > >> shared_buffers that prevents it from mapping the buffer contiguously.
> > > >> 
> > > >> Something's missing though, since I'm not hitting the same issue you
> > > >> are.  How are you generating the connections?  I just have an app
> > > >> calling PQconnectdb() in a loop, but I guess that's not good enough.
> > > 
> > > I am using the ASCII version of the psqlODBC driver version 8.2.4.2 to
> > > establish the test connections.
> > 
> > Could you try the same tests with the client runnint on a different system?
> > Since the client eats up a bunch of handles and such as well, and that
> > would eliminate the difference due to different clients.
> 
> Followup, when running these tests, could you check using Process Explorer
> if you're hitting close to the limit of either of the two pools? See
> http://blogs.technet.com/askperf/archive/2007/03/07/memory-management-understanding-pool-resources.aspx

Another followup. Been working with Dave on and off today (well, him mostly
on to be honest, me a bit more on and off), and it seems that both our
repros clearly blame the desktop heap, and nothing else. Please use the
desktop heap tool and see if it breaks when the desktop heap usage
approaches 100%:

http://www.microsoft.com/downloads/details.aspx?familyid=5cfc9b74-97aa-4510-b4b9-b2dc98c8ed8b&displaylang=en


It'd still be good to know why the difference is so big between your two
systems.


//Magnus

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

   http://archives.postgresql.org/

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

On Mon, Oct 22, 2007 at 04:03:35PM +0200, Rainer Bauer wrote:
> >> shared_buffers = 512MB
> >
> >As a general note, thsi is *way* too high. All evidence I've seen points to
> >that you should have shared_buffers as *small* as possible on win32,
> >because memory access there is slow. And leave more of the caching up to
> >the OS.
> 
> I followed Josh's advice here:
> 
> 
> What value would you recommend then? The default 32MB?

That advice is good - for Unix platforms. For windows, yes, try with 32Mb.


//Magnus

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

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

On Mon, Oct 22, 2007 at 08:04:03AM -0700, Trevor Talbot wrote:
> On 10/22/07, Dave Page <[EMAIL PROTECTED]> wrote:
> > Dave Page wrote:
> > > So, we seem to be hitting two limits here - the desktop heap, and
> > > something else which is cluster-specific. Investigation continues...
> >
> > In further info, I've been testing this with the 8.3b1 release build
> > that we put out with pgInstaller, and a build with all optional
> > dependencies (OpenSSL, Kerberos, gettext, ldap etc) disabled. I'm seeing
> > pretty much the same results with each - roughtly 9.6KB of desktop heap
> > used per connection.
> 
> The question is where that's coming from.  I wondered if it was
> desktop heap originally, but there's no reason it should be using it,
> and that seems to be precisely the difference between my system and
> the others.  Connections here are barely making a dent; at 490 there's
> an entire 45KB committed in the service desktop.

Yes, that would be very interesting to know. Because obviouslyi it's
something.

I read somewhere that Vista makes the size of the desktop heap dynamic, but
you were on 2003, right?

Are you running the server as a service or from the commandprompt?


> > Magnus and I did observe that we're using 1 user object and 4 GDI
> > objects per connection. If anyone happens to know how we might identify
> > those, please shout as so far we've drawn a blank :-(
> 
> Those appear to belong to the console window.

Makes sense - a Windows, a system menu, etc. There's probably a "hidden
console window" when running as a service...


> I've yet to do anything that generates real load (lightweight system),
> but a simple "select version()" doesn't make any difference here
> either, and raising shared buffers just makes postmaster run out of VM
> space faster.  (I don't think I mentioned that error before, but it
> shows up as "FATAL:  could not create sigchld waiter thread: error
> code 8".)

Yeah, that makes sense. We need to fix that, but I think that's too big of
a change to push during beta, given how few reports we've had of people
running into it.

//Magnus

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

Dave Page wrote:
> Trevor Talbot wrote:
>> The question is where that's coming from.  I wondered if it was
>> desktop heap originally, but there's no reason it should be using it,
>> and that seems to be precisely the difference between my system and
>> the others.  Connections here are barely making a dent; at 490 there's
>> an entire 45KB committed in the service desktop.
> 
> Hmm, Greg mentioned to me earlier that he was suspicious of SSPI which
> seems to drag in dependencies on gdi32.dll and user32.dll via
> secur32.dll. Sure enough, testing with 8.2.5 on XP Pro, I get to 150
> connections running as a service having used 97.2 of desktop heap (vs.
> 45 connections max with 8.3).
> 
> So we have a pretty serious regression in 8.3.
> 
> Of course, that still doesn't tally up with what you're seeing on
> Win2k3. I'll test on there tomorrow.

Could you try a build without SSPI? It should be as simple as removing
the #define ENABLE_SSPI 1 from port/win32.h. I don't think you need to
touch the linker lines at all, actually, so try without first.

//Magnus

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

Re: [HACKERS] [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

Florian Weimer wrote:
> * Magnus Hagander:
> 
>> Oh, that's interesting. That's actually a sideeffect of us increasing
>> the stack size for the postgres.exe executable in order to work on other
>> things. By default, it burns 1MB/thread, but ours will do 4MB. Never
>> really thought of the problem that it'll run out of address space.
>> Unfortunately, that size can't be changed in the CreateThread() call -
>> only the initially committed size can be changed there.
> 
> Windows XP supports the STACK_SIZE_PARAM_IS_A_RESERVATION flag, which
> apparently allows to reduce the reserved size.  It might be better to do
> this the other way round, though (leave the reservation at its 1 MB
> default, and increase it only when necessary).

It does, but we still support windows 2000 as well. I think it's better
to use a different method altogether - one not using one thread per child.

//Magnus

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

Trevor Talbot wrote:
> On 10/21/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
> 
>>> I tried generating idle connections in an effort to reproduce
>>> Laurent's problem, but I ran into a local limit instead: for each
>>> backend, postmaster creates a thread and burns 4MB of its 2GB address
>>> space.  It fails around 490.
>> Oh, that's interesting. That's actually a sideeffect of us increasing
>> the stack size for the postgres.exe executable in order to work on other
>> things. By default, it burns 1MB/thread, but ours will do 4MB. Never
>> really thought of the problem that it'll run out of address space.
>> Unfortunately, that size can't be changed in the CreateThread() call -
>> only the initially committed size can be changed there.
>>
>> There are two ways to get around it - one is not using a thread for each
>> backend, but a single thread that handles them all and then some sync
>> objects around it. We originally considered this but said we won't
>> bother changing it because the current way is simpler, and the overhead
>> of a thread is tiny compared to a process. I don't think anybody even
>> thought about the fact that it'd run you out of address space...
> 
> I'd probably take the approach of combining win32_waitpid() and
> threads.  You'd end up with 1 thread per 64 backends; when something
> interesting happens the thread could push the info onto a queue, which
> the new win32_waitpid() would check.  Use APCs to add new backends to
> threads with free slots.

I was planning to make it even easier and let Windows do the job for us,
just using RegisterWaitForSingleObject(). Does the same - one thread per
64 backends, but we don't have to deal with the queueing ourselves.
Should be rather trivial to do.

Keeps win32_waitpid() unchanged.

That said, refactoring win32_waitpid() to be based on a queue might be a
good idea *anyway*. Have the callback from above put something in the
queue, and go with your idea for the rest.

//Magnus

---(end of broadcast)---
TIP 1: 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] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

Dave Page wrote:
> Magnus Hagander wrote:
>> Could you try a build without SSPI? It should be as simple as removing
>> the #define ENABLE_SSPI 1 from port/win32.h. I don't think you need to
>> touch the linker lines at all, actually, so try without first.
> 
> Nope, doesn't help - still using around 9.7KB per connection. Just to be
> sure I did remove the link option, and checking with depends see that
> there are now only delay load references to secur32.dll, nothing direct
> - as is the case with 8.2

ok. That makes sense, actually...


> So the only other changes I can think of that might affect things are
> the VC++ build or the shared memory changes, though I can't see why they
> would cause problems offhand. I'll go try a mingw build...

Yeah, it could be that the newer MSVCRT files do something we don't
like.. Other than that, did we upgrade to a different version of some of
our dependents?

Also, is this the DEBUG or RELEASE build of 8.3?

//Magnus

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

Re: [HACKERS] [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

Tom Lane wrote:
> Magnus Hagander <[EMAIL PROTECTED]> writes:
>> I was planning to make it even easier and let Windows do the job for us,
>> just using RegisterWaitForSingleObject(). Does the same - one thread per
>> 64 backends, but we don't have to deal with the queueing ourselves.
>> Should be rather trivial to do.
> 
> How can that possibly work?  Backends have to be able to run
> concurrently, and I don't see how they'll do that if they share a stack.

We're not talking about the backends, we're talking about the backend
waiter threads whose sole purpose is to wait for a backend to die and
then raise a signal when it does. We can easily have the kernel wait for
a whole bunch of them at once, and have it call our callback function
whenever anyone of them dies.

//Magnus

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

Rainer Bauer wrote:
> After increasing the session heap size in the registry from 512KB to 1024KB
> the no. of connections was roughly doubled. So this might be a solution for
> people running out of Desktop heap.
> 
> Alter the value of the following key
> 
> 
> The numeric values following "SharedSection=" control the heap management:
> On WinXP these are the default values: "SharedSection=1024,3072,512"
> Altering this to "SharedSection=1024,3072,1024" will increase the heap for all
> non-interactive window stations to 1024KB.

This part should go in the FAQ, I think. It's valid for 8.2 as well,
from what I can tell, and it's valid for 8.3 both before and after the
patch I just applied.

Dave, you're listed as maintainer :-P

//Magnus

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

   http://archives.postgresql.org/

Re: [GENERAL] using libpq.lib in Microsoft C++ (managed)

[Magnus Hagander]

On Wed, Oct 24, 2007 at 03:06:03PM +0200, [EMAIL PROTECTED] wrote:
> Hi All.
> I've the necessity to call some functions of libpq.lib from my code. The
> problem is that my code is Managed C++ (as defined by Microsoft VisualStudio
> 2005). Managed C++ has a very special memory management and I don't know if
> using libpq.lib (an old C library) is a safe procedure. I've Already had
> problems using libpq.lib in a multi-threaded standard C++ and so I'm quite
> doubtful that all will work fine in Managed C++.
> 
> Has some one had experience concerning my doubts?

If you are using Managed C++, you should probably be using npgsql instead
of libpq.

//Magnus

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

Re: [GENERAL] subversion support?

[Magnus Hagander]

Roberts, Jon wrote:
> So the long story short I'm getting is, "no it is not on the radar".  This
> is terribly ironic given the fact that pgAdmin is developed using source
> control but the code you write with the tool doesn't have any hooks into
> source control.

Actually, it is on my personal radar. But beware that I have a very very
longrange radar, and I have a lot of things that sit much higher up on
my priority list.

//Magnus

---(end of broadcast)---
TIP 1: 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] If Postgresql DLL files causing any kind of conflict for two different versions of Postgresql installed on one machine

[Magnus Hagander]

dhiraj bhosale wrote:
> Hi,
> 
> I would like to know if there is any kind of conflict or limitation
> occur between postgresql dll files for two different setups(different
> versions) in different data directories and running on two serepate
> ports. And if it has any conflict can anyone put some more details
> regarding the same.

Shouldn't be as long as you are using 8.1 or newer. 8.0 installed shared
DLLs in system32, in which case you may have a conflict. 8.1 and newer
store the DLLs in the version-local directory.

//Magnus

---(end of broadcast)---
TIP 1: 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] How to create primary key

[Magnus Hagander]

On Tue, Nov 06, 2007 at 01:38:31AM -0800, Samantha Atkins wrote:
> 
> On Nov 6, 2007, at 1:15 AM, Raymond O'Donnell wrote:
> 
> >On 06/11/2007 08:54, Reg Me Please wrote:
> >
> >>What'd be the syntax to create a primary key on an already build  
> >>table?
> >
> >ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (f1);
> >
> 
> So, ALTER TABLE test ADD PRMARY KEY(f1 [, ... ] )
> isn't enough?  It appears in the examples.  What more does the  
> explicit test_pk do that wouldn't happen anyway?

It's not needed. It gives a name to the primary key. If you don't specify
one, the system will pick one for you automatically.

//Magnus

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

   http://archives.postgresql.org/

Re: [GENERAL] System V IPC on Windows

[Magnus Hagander]

 > > Does anyone know how to adjust the IPC settings in Windows?
> 
> There aren't any such settings in Windows, AFAIK. 

Correct. The only real adjustable limit is the size of the Windows pagefile, 
but that one is normally dynamic. But there must be room for all the shared 
memory in it. It's not going to be there, but the space is reserved.

That said, if you need to increase the pagefile size to accomodate your shared 
buffers, you likely have way too large value for shared buffers.

/Magnus


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

Re: [GENERAL] System V IPC on Windows

[Magnus Hagander]

Kevin Neufeld wrote:
> That makes sense, thanx.
> Another individual was having problems adjusting the shared_memory
> settings higher than 1.2GB on a 8GB 64bit machine running Vista. 

Pg on win32 is 32-bit. It has a total address space of 2Gb, minus the OS
overhead, minus the code, minus local memory etc. You're just not going
to fit that much in the address space.

There are fixes to reduce the memory usage of the postmaster (which is
likely what runs out first, unless you have a large work_mem) in 8.3,
but you're still right up against the wall with that large
shared_buffers. Plus, as I said in my other email, you're likely not
seeing any performance gain from such a large shared_buffers anyway. If
you are, you're seeing something new, and we definitely need to find out
why.


//Magnus


---(end of broadcast)---
TIP 1: 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] System V IPC on Windows

[Magnus Hagander]

Lee Keel wrote:
>> -Original Message-
>> From: [EMAIL PROTECTED] [mailto:pgsql-general-
>> [EMAIL PROTECTED] On Behalf Of Magnus Hagander
>> Sent: Thursday, November 08, 2007 12:47 AM
>> To: Tom Lane
>> Cc: Kevin Neufeld; pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] System V IPC on Windows
>>
>>  > > Does anyone know how to adjust the IPC settings in Windows?
>>> There aren't any such settings in Windows, AFAIK.
>> Correct. The only real adjustable limit is the size of the Windows
>> pagefile, but that one is normally dynamic. But there must be room for all
>> the shared memory in it. It's not going to be there, but the space is
>> reserved.
>>
>> That said, if you need to increase the pagefile size to accomodate your
>> shared buffers, you likely have way too large value for shared buffers.
>>
>> /Magnus
>>
>>
>> ---(end of broadcast)---
>> TIP 5: don't forget to increase your free space map settings
> [Lee Keel] 
> 
> What if the page file exceeds the shared_buffers, but you can't increase the
> shared buffers to a larger amount?  For example, page file is set to be
> between 500MB and 10GB, but you can't set the shared_buffers to more than
> 1200MB.  If set to 1300MB or higher then service will not start.

Have you measured any performance at all on this? The general
recommendation is to have a *small* shared_buffers on Widnows. >1Gb is
likely way too large - try something much smaller unless you have
benchmarks showing that this is helping you.

//Magnus

---(end of broadcast)---
TIP 1: 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