[GENERAL] Constraint Trigger's referenced_table

[Richard Broersma Jr]

How does a Constraint Trigger react to a referenced table when the constraint 
is created implementing the FROM clause?

Regards,
Richard Broersma Jr.

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

Re: [GENERAL] Setting Sequence Values

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Fri, 21 Dec 2007 23:56:55 -0500
Tom Lane <[EMAIL PROTECTED]> wrote:

> [pokes at it for a bit...]  Actually the deptype='a' bit is not so
> robust; we used to use 'i' for serial dependencies.  I'd leave that
> test out entirely, I think --- it doesn't seem essential, because
> there isn't any other reason for a sequence to depend on a table
> column. Otherwise the query seems correct.

8.2 is type 'a' (and I assume 8.3), 8.1 is 'i'. I don't know about
anything older.

Sincerely,

Joshua D. Drake
- -- 
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ 
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHbK0vATb/zqfZUUQRAknrAKChvcHWdSpJSvCIl4nXXuJLF1CPQgCcDLVn
0ZQAn4F5mSyUaWWcaztJJC4=
=jE0v
-END PGP SIGNATURE-

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

[GENERAL] installation on Mac OS X 10.5.1

[Chuck]

Hello,

What is the best way to install PostgreSQL on Mac OS X 10.5.1?
Or, perhaps I should ask is there a preferred way to PostgreSQL on Mac OS X?

Is a disk image installer fine? Or, is it better to compile all the components?

Is this installation perfectly fine?
http://www.kyngchaos.com/wiki/software:postgres
PostGIS recommends Kyngesburye: http://www.postgis.org/download/
They also refer to Fink.

Pat Maddox's blog recommends this approach:


Apple recommends Fink:
http://developer.apple.com/internet/opensource/postgres.html

The 'Robby on Rails' blog recommends MacPorts:


Here's another installation (this is out-of-date):
http://www.psyex.com/techdocs/pgonx.html

Is there a clear "answer" for installation? Is the answer, "it 
depends?"  Does all of this also apply to a Mac OS X 10.4.x machine?


Should I just use the single click DMG from Kyngesburye and 
optionally install PostGIS? That looks like the easiest approach. Or, 
Should I use MacPorts or Fink?


Thanks and happy holidays,
Chuck

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

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

Re: [GENERAL] Setting Sequence Values

[Tom Lane]

"Gregory Williamson" <[EMAIL PROTECTED]> writes:
> Joshua Drake shaped the aether to say:
>> Shout out to AndrewSN for this one (although I was almost there when he
>> pasted it ;)):
>> 
>> SELECT c1.relname AS sequencename, n.nspname AS schema, 
>> c2.relname AS tablename, a.attname AS columnname
>> FROM pg_class c1
>> JOIN pg_depend d ON (d.objid=c1.oid)
>> JOIN pg_class c2 ON (d.refobjid=c2.oid)
>> JOIN pg_attribute a ON (a.attrelid=c2.oid AND a.attnum=d.refobjsubid)
>> JOIN pg_namespace n ON (n.oid=c2.relnamespace)
>> WHERE c1.relkind='S'
>> AND d.classid='pg_class'::regclass
>> AND d.refclassid='pg_class'::regclass
>> AND d.refobjsubid > 0
>> AND d.deptype='a';

> A thing of beauty ! Is it portable or tied to certain versions ? (not 
> familiar enough with system tables and changes therein to have my own opinion)

Offhand I believe that this would work in every PG version since 7.3.
It would definitely not work before that (7.2 had neither pg_namespace
nor pg_depend).

[pokes at it for a bit...]  Actually the deptype='a' bit is not so
robust; we used to use 'i' for serial dependencies.  I'd leave that test
out entirely, I think --- it doesn't seem essential, because there
isn't any other reason for a sequence to depend on a table column.
Otherwise the query seems correct.

As for possible future breakage, who can say?  There's nothing else here
that I foresee problems for, but I don't have a crystal ball.

regards, tom lane

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

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

Re: [GENERAL] Setting Sequence Values

[Martin]

In article <[EMAIL PROTECTED]>,
Greg Smith <[EMAIL PROTECTED]> wrote:

>See if this helps you:
>http://archives.postgresql.org/pgsql-general/2007-10/msg00969.php

Yes! That's exactly what I needed. I new there had to be
an easy way ;)


---(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] Setting Sequence Values

[Gregory Williamson]

Joshua Drake shaped the aether to say:
> 
> Shout out to AndrewSN for this one (although I was almost there when he
> pasted it ;)):
> 
> SELECT c1.relname AS sequencename, n.nspname AS schema, 
>c2.relname AS tablename, a.attname AS columnname
>FROM pg_class c1
>JOIN pg_depend d ON (d.objid=c1.oid)
>JOIN pg_class c2 ON (d.refobjid=c2.oid)
>JOIN pg_attribute a ON (a.attrelid=c2.oid AND a.attnum=d.refobjsubid)
>JOIN pg_namespace n ON (n.oid=c2.relnamespace)
> WHERE c1.relkind='S'
> AND d.classid='pg_class'::regclass
> AND d.refclassid='pg_class'::regclass
> AND d.refobjsubid > 0
> AND d.deptype='a';
> 
> sequencename | schema | tablename | columnname 
> - --++---+
>  foo_id_seq   | public | foo   | id
> (1 row)
> 
> - From there, scripting should be easy.

A thing of beauty ! Is it portable or tied to certain versions ? (not familiar 
enough with system tables and changes therein to have my own opinion)

Greg W.

Re: [GENERAL] Setting Sequence Values

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Fri, 21 Dec 2007 18:14:43 -0700
D"Gregory Williamson" <[EMAIL PROTECTED]> wrote:

> I think the OP needs a way to do _all_ of the sequences, which can be
> a little dauning if you have lots of tables. I'm sure there's a way
> but I haven't the time to puzzle it out -- off to SF for a(n)
> (im)moderate celebration. I might hack at this later tonight if I am
> capable.

Shout out to AndrewSN for this one (although I was almost there when he
pasted it ;)):

SELECT c1.relname AS sequencename, n.nspname AS schema, 
   c2.relname AS tablename, a.attname AS columnname
   FROM pg_class c1
   JOIN pg_depend d ON (d.objid=c1.oid)
   JOIN pg_class c2 ON (d.refobjid=c2.oid)
   JOIN pg_attribute a ON (a.attrelid=c2.oid AND a.attnum=d.refobjsubid)
   JOIN pg_namespace n ON (n.oid=c2.relnamespace)
WHERE c1.relkind='S'
AND d.classid='pg_class'::regclass
AND d.refclassid='pg_class'::regclass
AND d.refobjsubid > 0
AND d.deptype='a';

sequencename | schema | tablename | columnname 
- --++---+
 foo_id_seq   | public | foo   | id
(1 row)

- From there, scripting should be easy.

Sincerely,

Joshua D. Drake



- -- 
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ 
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHbH6DATb/zqfZUUQRAlY9AJ9UwlaveD91Hw5FXv5YsHyfzfKIVACgrNQH
jwBU/EglIibnw9Nz9mgzg1w=
=7pot
-END PGP SIGNATURE-

---(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] Killing a session in windows

[Bruce Momjian]

Howard Cole wrote:
> > Wow, yea, I see that now, but it is alone a paragraph above.  I updated
> > the text to:
> >
> >The  program provides a convenient
> >interface for sending these signals to shut down the server.
> >Alternatively, you can send the signal directly using
> >kill on non-Windows systems.
> >
> >   
> My documentation point is that in administering the service, I cannot 
> drop a database if there is still an active connection. To drop a 
> process in linux appears to be easy using "kill", but this does not seem 
> to be the case in windows using taskkill. I would rather see some 
> reference to killing rogue connections using pg_ctl in the "Managing 
> Databases" chapter, and reference to it in "Destroying a Database" and 
> "DROP DATABASE" documentation because this would be where I would first 
> search for a solution when I had the problem.

Uh, well we have this TODO:

* Allow administrators to safely terminate individual sessions either
  via an SQL function or SIGTERM

  Lock table corruption following SIGTERM of an individual backend
  has been reported in 8.0.  A possible cause was fixed in 8.1, but
  it is unknown whether other problems exist.  This item mostly
  requires additional testing rather than of writing any new code.

  http://archives.postgresql.org/pgsql-hackers/2006-08/msg00174.php

so I am unsure how we would give such a capability on Windows when we
don't support it on Unix either.

> In the pg_ctl documentation, I would recommend explicitly stating that 
> "pg_ctl kill -TERM pid" can be used to kill individual connections to a 
> database in windows, because "taskkill" and "select pg_cancel_backend()" 
> do not seem to always work (for me anyway) in windows. Also HUP and 

Where do we say that about Unix in the pg_ctl manual?

> other signals mean nothing to a windows user.

We actually simulate these signals on Windows, so the pg_ctl kill
actually works just like Unix.  We do have in the pg_ctl manual:

   kill mode allows you to send a signal to a specified
process.  This is particularly valuable for Microsoft 
Windows
which does not have a kill command.  Use
--help to see a list of supported signal names.

Is that unclear?

> In general the documentation, understandably, is geared toward *nix, I 
> do not know what proportion of installations are Windows, but I suspect 
> they are growing at a rapid rate since version 8. Postgres on Windows is 
> a fabulous product, and the migration to the windows platform has been 
> much cleaner than the migration of Mysql, so it would be a shame to lose 
> market share on the basis that the documentation still has sections 
> biased towards *nix. Removal of *nix-isms from the main strand of the 
> documentation and additions of clearly marked build dependant comments 
> where appropriate would make a big difference in uniting the world! So 
> for example, the documentation for pg_ctl would have a description and 
> common options, and then list any linux/bsd/unix/windows differences in 
> section similar to the User Comments sections of the documentation.

Can you give a specific example?  As I said we simulate Windows so it
should act just like Unix.

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

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

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

   http://archives.postgresql.org/

Re: [GENERAL] Setting Sequence Values

[Greg Smith]

On Sat, 22 Dec 2007, Martin wrote:

The problem is getting all the sequences set without doing each one by 
hand.


See if this helps you:
http://archives.postgresql.org/pgsql-general/2007-10/msg00969.php

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

---(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] Setting Sequence Values

[Martin]

In article <[EMAIL PROTECTED]>,
Joshua D. Drake <[EMAIL PROTECTED]> wrote:

>> After porting tables and data from FrontBase I now find that I
>> need to update all the values for the sequences that were created
>> to reflect the data in the tables. Is there an easy way to do
>> this?

>Take a look at setval.

I know all about setval. The problem is getting all the sequences 
set without doing each one by hand.



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

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

Re: [GENERAL] Setting Sequence Values

[Gregory Williamson]

Joshua Drake spake thusly:
> 
> On Fri, 21 Dec 2007 23:57:21 -
> Martin <[EMAIL PROTECTED]> wrote:
> 
> > After porting tables and data from FrontBase I now find that I
> > need to update all the values for the sequences that were created
> > to reflect the data in the tables. Is there an easy way to do
> > this?
> 
> Take a look at setval.
> 
> postgres=# select setval('foo_id_seq',(select max(id) from foo));
>  setval 
> - 
> 100
> (1 row)
<...>

I think the OP needs a way to do _all_ of the sequences, which can be a little 
dauning if you have lots of tables. I'm sure there's a way but I haven't the 
time to puzzle it out -- off to SF for a(n) (im)moderate celebration. I might 
hack at this later tonight if I am capable.

Cheers!

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments, is for 
the sole use of the intended recipient(s) and may contain confidential and 
privileged information and must be protected in accordance with those 
provisions. Any unauthorized review, use, disclosure or distribution is 
prohibited. If you are not the intended recipient, please contact the sender by 
reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)

Re: [GENERAL] Setting Sequence Values

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Fri, 21 Dec 2007 23:57:21 -
Martin <[EMAIL PROTECTED]> wrote:

> After porting tables and data from FrontBase I now find that I
> need to update all the values for the sequences that were created
> to reflect the data in the tables. Is there an easy way to do
> this?

Take a look at setval.

postgres=# select setval('foo_id_seq',(select max(id) from foo));
 setval 
- 
100
(1 row)

postgres=# select currval('foo_id_seq');
 currval 
- -
 100
(1 row)



Sincerely,

Joshua D. Drake


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


- -- 
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ 
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHbGQhATb/zqfZUUQRAkGqAJ9jJMF/1aYY8q7L4C35ogVnSsowAACgnwH3
J0HpPengi7eo4n4b+YLaCZA=
=uQZ6
-END PGP SIGNATURE-

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

   http://archives.postgresql.org/

[GENERAL] Setting Sequence Values

[Martin]

After porting tables and data from FrontBase I now find that I
need to update all the values for the sequences that were created
to reflect the data in the tables. Is there an easy way to do
this?


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

Re: [GENERAL] Warm standby system - FATAL: the database system is starting up

[Gurjeet Singh]

I would recommend that whenever you are trying to recover a database (be it
WAL shipping or any other method), first change the port it is listening on,
to something that your apps wouldn't assume the DB to be running on, and
then start the recovery.

This way, even if there's a rogue application that you forgot to shut down,
your recovering database wouldn't be bothered about it.

Then, when you are finished recovery, just change back the port to your
default, and restart the server.

Best regards,
-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

17° 29' 34.37"N,   78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N,   73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *

http://gurjeet.frihost.net

Mail sent from my BlackLaptop device
On Dec 21, 2007 1:24 PM, Glyn Astill <[EMAIL PROTECTED]> wrote:

> Is there a way I can check what it is?
>
> I see no reason why anything would be trying to connect, any ideas?
>
> Could it be the autovacuum as I suggested?
>
> Also something I omitted to point out in my original post, the
> processes running on the machine (ps -ax) are as follows
>
>  3467 pts/0S  0:00 /usr/local/pgsql/bin/postgres -D
> /data/postgres/
>  3468 ?Ss 0:02 postgres: startup process
>  4858 ?S  0:00 /bin/bash /data/postgres/restore.sh
> /mnt/archive/0001001A0087 pg_xlog/RECOVERYXLOG
>  6371 ?S  0:00 sleep 1
>
> Notice the "postgres: startup process", does that just mean we're in
> WAL reading mode? Or does it mean it's not working.
>
> I know Tom, you said it will be working, but does this extra info
> change that?
>
> Thanks
> Glyn
>
> --- Tom Lane <[EMAIL PROTECTED]> wrote:
>
> > Glyn Astill <[EMAIL PROTECTED]> writes:
> > > I see in my log on the backup machine:
> > > FATAL:  the database system is starting up
> > > Does this mean the backup is not working?
> >
> > No, it means something's trying to connect to the backup
> > postmaster.
> >
> >   regards, tom lane
> >
> > ---(end of
> > broadcast)---
> > TIP 4: Have you searched our list archives?
> >
> >http://archives.postgresql.org/
> >
>
>
>
>  ___
> Support the World Aids Awareness campaign this month with Yahoo! For Good
> http://uk.promotions.yahoo.com/forgood/
>
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>   http://archives.postgresql.org/
>

Re: [GENERAL] Warm standby system - FATAL: the database system is starting up

[Glyn Astill]

Hi chaps,

Indeed you were correct!

I enabled the good stuff in the log and sure enough one of the old
testbed machines which is running postgres and slony was trying to
connect!

However I'd never even referenced the new machine on the old testbed
so how on earth could it have been trying to connect?

Also (maybe I'm a bit slow or something" should the WAL backup
machine be showing "postgres: startup process"?

Thanks
Glyn



--- Tom Lane <[EMAIL PROTECTED]> wrote:

> Glyn Astill <[EMAIL PROTECTED]> writes:
> > Is there a way I can check what it is?
> 
> log_connections would help...
> 
>   regards, tom lane
> 
> ---(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
> 



  __
Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com



---(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] Warm standby system - FATAL: the database system is starting up

[Tom Lane]

Glyn Astill <[EMAIL PROTECTED]> writes:
> Is there a way I can check what it is?

log_connections would help...

regards, tom lane

---(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] Warm standby system - FATAL: the database system is starting up

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Fri, 21 Dec 2007 13:24:01 -0800 (PST)
Glyn Astill <[EMAIL PROTECTED]> wrote:

> Is there a way I can check what it is?

Change your log line prefix to show connections and ip addresses.

Joshua D. Drake


- -- 
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ 
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHbDCcATb/zqfZUUQRAqabAKCCzgwW/JeKfEj6q6jkcSpp4JYV+ACfX8J4
BYwcImPTtVwyrsbw678q/9Q=
=aHBj
-END PGP SIGNATURE-

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

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

Re: [GENERAL] Warm standby system - FATAL: the database system is starting up

[Glyn Astill]

Is there a way I can check what it is?

I see no reason why anything would be trying to connect, any ideas?

Could it be the autovacuum as I suggested?

Also something I omitted to point out in my original post, the
processes running on the machine (ps -ax) are as follows

 3467 pts/0S  0:00 /usr/local/pgsql/bin/postgres -D
/data/postgres/
 3468 ?Ss 0:02 postgres: startup process
 4858 ?S  0:00 /bin/bash /data/postgres/restore.sh
/mnt/archive/0001001A0087 pg_xlog/RECOVERYXLOG
 6371 ?S  0:00 sleep 1

Notice the "postgres: startup process", does that just mean we're in
WAL reading mode? Or does it mean it's not working.

I know Tom, you said it will be working, but does this extra info
change that?

Thanks
Glyn

--- Tom Lane <[EMAIL PROTECTED]> wrote:

> Glyn Astill <[EMAIL PROTECTED]> writes:
> > I see in my log on the backup machine:
> > FATAL:  the database system is starting up
> > Does this mean the backup is not working?
> 
> No, it means something's trying to connect to the backup
> postmaster.
> 
>   regards, tom lane
> 
> ---(end of
> broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org/
> 



  ___
Support the World Aids Awareness campaign this month with Yahoo! For Good 
http://uk.promotions.yahoo.com/forgood/


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

   http://archives.postgresql.org/

Re: [GENERAL] Warm standby system - FATAL: the database system is starting up

[Tom Lane]

Glyn Astill <[EMAIL PROTECTED]> writes:
> I see in my log on the backup machine:
> FATAL:  the database system is starting up
> Does this mean the backup is not working?

No, it means something's trying to connect to the backup postmaster.

regards, tom lane

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

   http://archives.postgresql.org/

[GENERAL] Warm standby system - FATAL: the database system is starting up

[Glyn Astill]

Hi people,

I've set up a warm standby system using WAL shipping, partially
following the guide here (My setup ios on two physically separate
servers)

http://archives.postgresql.org/sydpug/2006-10/msg1.php

I'm using an NFS share on the main server that is then mounted in
fstab on the backup.

I see in my log on the backup machine:

FATAL:  the database system is starting up

Does this mean the backup is not working?

I've left autovacuum on in postgresql.conf - could anythign like this
 be causing it?

Or could I be missing part of the backup in some way?

If I create lots of records on the main server, then the log on the
backup looks like this:

FATAL:  the database system is starting up
FATAL:  the database system is starting up
FATAL:  the database system is starting up
`/mnt/archive/0001001A0080' -> `pg_xlog/RECOVERYXLOG'
LOG:  restored log file "0001001A0080" from archive
`/mnt/archive/0001001A0081' -> `pg_xlog/RECOVERYXLOG'
LOG:  restored log file "0001001A0081" from archive
`/mnt/archive/0001001A0082' -> `pg_xlog/RECOVERYXLOG'
LOG:  restored log file "0001001A0082" from archive
`/mnt/archive/0001001A0083' -> `pg_xlog/RECOVERYXLOG'
LOG:  restored log file "0001001A0083" from archive
`/mnt/archive/0001001A0084' -> `pg_xlog/RECOVERYXLOG'
LOG:  restored log file "0001001A0084" from archive
`/mnt/archive/0001001A0085' -> `pg_xlog/RECOVERYXLOG'
LOG:  restored log file "0001001A0085" from archive
FATAL:  the database system is starting up
`/mnt/archive/0001001A0086' -> `pg_xlog/RECOVERYXLOG'
LOG:  restored log file "0001001A0086" from archive
FATAL:  the database system is starting up
FATAL:  the database system is starting up
FATAL:  the database system is starting up


As far as I know theres nothing else trying to connect.

Thanks 
Glyn




  __
Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com



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

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

Re: [GENERAL] Restoring 8.0 db to 8.1

[Vivek Khera]

On Dec 21, 2007, at 11:09 AM, Martijn van Oosterhout wrote:


The usual answer is use slony. You can use it to replicate the 8.0
server onto an 8.1 server. This may take weeks/months/years/whatever  
to
synchronise. When the slave is up to date, you pull the plug on the  
8.0

server and get everyone to use the 8.1 server... No downtime...



except he has large objects, which slony can't replicate.


---(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] FK creation -- "ON DELETE NO ACTION" seems to be a no-op

[Webb Sprague]

> >
> >   ... "currentsessions_username_fkey" FOREIGN KEY (username)
> > REFERENCES authorizedusers(username) ON UPDATE CASCADE
>
> Hmm, NO ACTION is the default.

Oh, how embarrassing.  Never mind...

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

---(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] self ordering list

[brian]

Bryan Wilkerson wrote:


My first question concerns self ordering lists.  Basically, a numeric
column that automatically maintains an order but allows arbitrary
reordering by the user.  For example, a media playlist or the
priority of workitems within a parent container ;) This seems
like a common pattern.

priority |  workitem
-+---
1| task 1
2| task 2
3| task 3
4| task 4
5| task 5

Insert a new task with priority==2 and...

 update tablename set priority=priority+1 where priority >= 2

delete task with priority==2 and...

 update tablename set priority=priority-1 where priority > 2

reorder task with priority==2, set its priority=4

 update tablename set priority=priority+1 where priority >= 4

>  update tablename set priority=priority-1 where priority > 2
>   and priority < 4


etc

I've implemented in my model code but it has some deadlock issues and
I really strongly have believed all along that this belongs in the db
anyway.  Implementing the above with triggers is a tricky problem
because the trigger would key off the priority change and the
successive updates would recusively trigger.



Why not update everything into a temp table first, then update the 
original with the new values from that? Or maybe a view is better suited 
to this.


brian

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

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

Re: [GENERAL] FK creation -- "ON DELETE NO ACTION" seems to be a no-op

[Alvaro Herrera]

Webb Sprague escribió:

> It is not updating the table correctly to reflect the "no action".
> After \d currentsessions (and some editing) I get the following,
> which, as I understand it, should say something about the "ON DELETE
> NO ACTION":
> 
>   ... "currentsessions_username_fkey" FOREIGN KEY (username)
> REFERENCES authorizedusers(username) ON UPDATE CASCADE

Hmm, NO ACTION is the default.

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

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

[GENERAL] self ordering list

[Bryan Wilkerson]

Hello to all.  I'm new to the list and have only been engineering on postgresql 
for about a year.  I have solved some neat problems in that time and will 
gladly give  back to the community.  I'll try to contribute as much as startup 
time permits. 

My first question concerns self ordering lists.  Basically, a numeric column 
that automatically maintains an order but allows arbitrary reordering by the 
user.  For example, a media playlist or the priority of workitems within a 
parent container ;) This seems like a common pattern.   

priority |  workitem
-+---
1| task 1
2| task 2
3| task 3
4| task 4
5| task 5

Insert a new task with priority==2 and...

   update tablename set priority=priority+1 where priority >= 2

delete task with priority==2 and...
   
   update tablename set priority=priority-1 where priority > 2

reorder task with priority==2, set its priority=4

   update tablename set priority=priority+1 where priority >= 4
   update tablename set priority=priority-1 where priority > 2 and priority < 4

etc

I've implemented in my model code but it has some deadlock issues and I really 
strongly have believed all along that this belongs in the db anyway.  
Implementing the above with triggers is a tricky problem because the trigger 
would key off the priority change and the successive updates would recusively 
trigger.  

Instead of trying to update all the rows affected you could instead just update 
the successive row and let a trigger chain reaction take care of updating all 
the rows.  I've implemented this it also has issues that I'm not sure are 
entirely mine.  

I'll break out and upload a sample case of the issues but before I do can any 
point me to any publication on implementing this basic pattern with postgres 
and plpgsql.   

Thanks,

-bryan

[GENERAL] FK creation -- "ON DELETE NO ACTION" seems to be a no-op

[Webb Sprague]

Hi list,

First, my select version() gives:

PostgreSQL 8.2.4 on x86_64-pc-linux-gnu, compiled by GCC
x86_64-pc-linux-gnu-gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.2)

The Problem:  when I run

ALTER TABLE currentsessions ADD CONSTRAINT
currentsessions_username_fkey FOREIGN KEY (username) REFERENCES
authorizedusers on delete no action on update cascade;

It is not updating the table correctly to reflect the "no action".
After \d currentsessions (and some editing) I get the following,
which, as I understand it, should say something about the "ON DELETE
NO ACTION":

  ... "currentsessions_username_fkey" FOREIGN KEY (username)
REFERENCES authorizedusers(username) ON UPDATE CASCADE

However, if I drop the constraint and rebuild it with

ALTER TABLE currentsessions ADD CONSTRAINT
currentsessions_username_fkey FOREIGN KEY (username) REFERENCES
authorizedusers on delete cascade on update cascade;

I get:

  ... "currentsessions_username_fkey" FOREIGN KEY (username)
REFERENCES authorizedusers(username) ON UPDATE CASCADE ON DELETE
CASCADE

Is this a bug or a strange thing I had thought I understood?  Or has
it been fixed in 8.2.5 ("Fix incorrect handling of some foreign-key
corner cases (Tom)" -- from the release notes)

If anyone wants more data, just holler.

Thanks
W

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

   http://archives.postgresql.org/

[GENERAL] slow query

[Marc]

Hey Folks,

This query is running really slowly.  Sometimes much slower then others.  I
have a feeling that there may be contention on one of the indices it is
using.

Query and explain plan are below.  Seems like it spend the most time doing
Index Scan using i_tablea_atextfield on tablea ru
(cost=0.00..2265.28rows=2 width=12) (actual time=
0.624..881.313 rows=228 loops=1)

Any suggestions?

SELECT z.atextfield,
   z.btextfield,
   z.abigintfield,
   p.achar255field, p.ptextfield,
   z.achar1field, u.aboolfield,
   z.textfield1,
   z.achar8field,
   z.achar16field
FROM tablea ru
INNER JOIN tableb u ON ( u.atextfield = ru.anothertextfield )
INNER JOIN tablec z ON u.atextfield = z.atextfield
INNER JOIN tabled p ON p.id = z.pid
LEFT JOIN tablee m ON u.atextfield = m.atextfield  AND m.boolcol5
WHERE ru.atextfield = 'thelookupval'
  AND u.boolcol1 IS TRUE
  AND u.boolcol2 IS FALSE
  AND ru.achar1field <> 'N'
  AND ru.boolcol3 IS FALSE
  AND NOT EXISTS ( SELECT 'x' FROM tablea fru WHERE fru.atextfield =
ru.anothertextfield AND fru.boolcol3 IS TRUE )
  AND EXISTS ( SELECT 'x' FROM tablef s WHERE s.atextfield = ru.atextfieldAND
s.boolcol4 IS TRUE )
  ORDER by ru.anothertextfield asc



Sort  (cost=2341.96..2341.97 rows=2 width=146) (actual time=
1118.810..1119.098 rows=228 loops=1)
  Sort Key: ru.anothertextfield
  ->  Nested Loop Left Join  (cost=0.00..2341.95 rows=2 width=146) (actual
time=0.930..1117.258 rows=228 loops=1)
->  Nested Loop  (cost=0.00..2313.36 rows=2 width=131) (actual time=
0.842..914.554 rows=228 loops=1)
  ->  Nested Loop  (cost=0.00..2296.65 rows=2 width=93) (actual
time=0.765..901.916 rows=228 loops=1)
->  Nested Loop  (cost=0.00..2281.98 rows=2 width=72)
(actual time=0.690..893.648 rows=228 loops=1)
  ->  Index Scan using i_tablea_atextfield on tablea
ru  (cost=0.00..2265.28 rows=2 width=12) (actual
time=0.624..881.313rows=228 loops=1)
Index Cond: (atextfield =
'thelookupval'::text)
Filter: ((achar1field <> 'N'::bpchar) AND
(boolcol3 IS FALSE) AND (NOT (subplan)) AND (subplan))
SubPlan
  ->  Index Scan using tablef_pkey on tablef
s  (cost=0.00..8.34 rows=1 width=0) (actual time=0.016..0.016 rows=1
loops=228)
Index Cond: (atextfield = $1)
Filter: (boolcol4 IS TRUE)
  ->  Bitmap Heap Scan on tablea fru  (cost=
4.61..90.41 rows=1 width=0) (actual time=3.590..3.590 rows=0 loops=243)
Recheck Cond: (atextfield = $0)
Filter: (boolcol3 IS TRUE)
->  Bitmap Index Scan on
i_tablea_atextfield  (cost=0.00..4.61 rows=22 width=0) (actual time=
0.044..0.044 rows=17 loops=243)
  Index Cond: (atextfield = $0)
  ->  Index Scan using tablec_pkey on tablec z
(cost=0.00..8.34 rows=1 width=60) (actual time=0.047..0.049 rows=1
loops=228)
Index Cond: (z.atextfield =
ru.anothertextfield)
->  Index Scan using tabled_pkey on tabled p  (cost=
0.00..7.32 rows=1 width=29) (actual time=0.030..0.031 rows=1 loops=228)
  Index Cond: (p.id = z.pid)
  ->  Index Scan using tableb_pkey on tableb u
(cost=0.00..8.34rows=1 width=38) (actual time=
0.049..0.051 rows=1 loops=228)
Index Cond: (u.atextfield = ru.anothertextfield)
Filter: ((boolcol1 IS TRUE) AND (boolcol2" IS FALSE))"
->  Index Scan using tablee_atextfield_idx on tablee m  (cost=
0.00..14.28 rows=1 width=39) (actual time=0.883..0.883 rows=0 loops=228)
  Index Cond: (u.atextfield = m.atextfield)

Re: [GENERAL] Restoring 8.0 db to 8.1

[Richard Huxton]

[EMAIL PROTECTED] wrote:

Hi

I am trying to upgrade my database from 8.0 to 8.1 and am looking for a
little info/advice.

This is a production database that we are migrating and it is in CONSTANT
use, so the maintenance window must be small and hopefully mostly off-hours.

We use a PITR/LVM snapshot solution for our backups and were hoping to
simply restore the filesystem and startup under 8.1.  Obviously this didnt
work, and I know the doc says a conversion is necessary, however I havent
found anything with enough detail to sway me from a better solution than
pg_restore.


Slony will let you replicate between different versions.

Given that you can't afford any downtime, and presumably don't want to 
repeat this process in a hurry, I'd suggest going to 8.2, or 8.3 if you 
can afford to wait a few weeks.


--
  Richard Huxton
  Archonet Ltd

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

[GENERAL] Requirements for Constraint Trigger's Function

[Richard Broersma Jr]

What is the proper way for the function of a constraint trigger to signal where 
or not referential integrity was compromised?

Should it return some sort of value?  Should it raise an exception?

Regards,
Richard Broersma Jr.

---(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] Exactly what is DEFERRABLE

[Richard Broersma Jr]

--- On Fri, 12/21/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
> Does this imply that custom CONSTRAINT TRIGGERs are not
> DEFERRABLE? Or are they?

Sorry dumb question.  They must be deferrable since their create statement 
allows for differable.

Regard,s
Richard Broersma Jr.


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

Re: [GENERAL] settings for 8.2.5 on Mac OS X 10.4.11

[Erik Jones]

On Dec 21, 2007, at 2:12 AM, Wolfgang Keller wrote:


The best thing is to fix the OS' ridiculously small shmem limits.
I've got this in /etc/sysctl.conf:

kern.sysv.shmmax=33554432
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.sysv.shmall=8192


Dependency on the available RAM?


and on a production DB you'd probably want shmmax and shmall
a good bit higher.

Don't forget to reboot after changing settings.  Also, a
"sysctl -a | grep shm" is a good idea to make sure it "took";
OS X is infamously picky about these settings.

regards, tom lane


A Macnerd as a core developer of Postgres? :-)


Not surprising.  Macs are an absolute joy for developing.

Erik Jones

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

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



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

[GENERAL] Hotel for FOSDEM 2008

[Andreas 'ads' Scherbaum]

Hello all,

i got a reservation for a hotel in Brussels/Belgium for FOSDEM.
The hotel is:

Hotel Agenda Louise
rue de Florence 6
B-1000 Brussels
Tel:  + 32.2.539.00.31
Fax: + 32.2.539.00.63
www.hotel-agenda.com

It has free wireless and i reserved 20 rooms in advance, the price is
80 Euros per night from friday to sunday. Breakfast, taxes and services
included. Additional days cost 100E per night.

So, who wants to stay with us in this hotel (~10 rooms left), please
let ME know. I will come back to everyone who sends me a mail. If i get
more requests, i will try to get more rooms.


Thanks & kind regards

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group

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

[GENERAL] Exactly what is DEFERRABLE

[Richard Broersma Jr]

I noticed from the 8.3 manual (CREATE TABLE section):

NOT DEFERRABLE
... Only foreign key constraints currently accept this clause. All other 
constraint types are not deferrable.

Does this imply that custom CONSTRAINT TRIGGERs are not DEFERRABLE? Or are they?

Regards,
Richard Broersma Jr.

---(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] Machine spec 64-bit/32 GB RAM

[Joshua D. Drake]

David Cotter wrote:

Hello,

I am looking at a linux machine for a PG install with 32G of RAM. I have 
never installed on anyting with more than 2 or 3 Gigs before. In order 
to access that 32 Gigs do I have to use 64 bit OS and PG? Is there any 
reason not to use a 64 bit? With a 32-bit version of PG can the 32 G of 
RAM be used for caching?


There is zero reason not to run PG on 64bit with that configuration.

Sincerely,

Joshua D. Drake



Many thanks,
David



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

Re: [GENERAL] Restoring 8.0 db to 8.1

[Tom Lane]

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> The problem with pg_restore is that our database takes 3+ weeks to restore
> from a dump file.  This is not an acceptable window for us.  ( Approximately
> 3 days of this is data + lobs, and the rest indexes and constraints. If we
> are doing something wrong here, I am all ears as well )

Uh, how big is that database exactly?

One question is, if migration is so painful, why are you updating to a
branch that is already obsolete?  At this point you should be skipping
8.1.x and going directly to 8.2.x; or maybe planning to wait a few more
weeks and go to 8.3.x.  I say this not just on general principles, but
mindful of the fact that there were some pretty significant improvements
in sorting speed in 8.2, which would translate directly to shorter btree
index build times.  (Are all your indexes btrees?)

Also, are you following the guidelines at
http://developer.postgresql.org/pgdocs/postgres/populate.html#POPULATE-PG-DUMP
?

regards, tom lane

---(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] Restoring 8.0 db to 8.1

[Martijn van Oosterhout]

On Fri, Dec 21, 2007 at 10:53:49AM -0500, [EMAIL PROTECTED] wrote:
> I am trying to upgrade my database from 8.0 to 8.1 and am looking for a
> little info/advice.
> 
> This is a production database that we are migrating and it is in CONSTANT
> use, so the maintenance window must be small and hopefully mostly off-hours.

The usual answer is use slony. You can use it to replicate the 8.0
server onto an 8.1 server. This may take weeks/months/years/whatever to
synchronise. When the slave is up to date, you pull the plug on the 8.0
server and get everyone to use the 8.1 server... No downtime...

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution 
> inevitable.
>  -- John F Kennedy


signature.asc
Description: Digital signature

[GENERAL] Restoring 8.0 db to 8.1

[[EMAIL PROTECTED]]

Hi

I am trying to upgrade my database from 8.0 to 8.1 and am looking for a
little info/advice.

This is a production database that we are migrating and it is in CONSTANT
use, so the maintenance window must be small and hopefully mostly off-hours.

We use a PITR/LVM snapshot solution for our backups and were hoping to
simply restore the filesystem and startup under 8.1.  Obviously this didnt
work, and I know the doc says a conversion is necessary, however I havent
found anything with enough detail to sway me from a better solution than
pg_restore.

The problem with pg_restore is that our database takes 3+ weeks to restore
from a dump file.  This is not an acceptable window for us.  ( Approximately
3 days of this is data + lobs, and the rest indexes and constraints. If we
are doing something wrong here, I am all ears as well )

Could anyone point me to the information I am missing or offer some middle
ground solutions?

TIA

Bill

Re: [GENERAL] Machine spec 64-bit/32 GB RAM

[Greg Smith]

On Fri, 21 Dec 2007, David Cotter wrote:


In order to access that 32 Gigs do I have to use 64 bit OS and PG?


Yes.  You'll be tormented by various things being limited at 2-4 GB on a 
32 bit one.



Is there any reason not to use a 64 bit?


The only systems I have running 32 bit are because they rely on some 
closed-source component where I only have that version available.  There 
are some wrapper libraries available to work around some of these 
situations.  Generally, if you're only dealing with open source software, 
you should be able to get everything running in 64 bits.



With a 32-bit version of PG can the 32 G of RAM be used
for caching?


Yes, but you will have to tune things yourself and ignore a lot of the PG 
folklore aimed at earlier versions+smaller machines.  I've collected some 
guidelines for your sort of situation at 
http://www.westnet.com/~gsmith/content/postgresql/ ; the first article 
there suggests how to get the database server to use all your RAM 
usefully, while the second points out some potential issues with the fact 
that by default, your Linux installation may be caching >1GB worth of 
writes, which can cause some performance issues when you have a 
checkpoint.


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

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

Re: [GENERAL] Conditional updateable view

[Merlin Moncure]

On Dec 15, 2007 8:57 PM, snacktime <[EMAIL PROTECTED]> wrote:
> I can't seem to find an example of how to add restrictions to the
> where clause of an updateable view created via the rule system.  For
> example I don't want the update to complete if a where clause is
> missing entirely, and in some cases I want to only allow the update if
> the where clause specifies a particular column.
>
> Is there a way to do this?

That's an interesting question.  There are some sneaky ways to slip
something into a rule but in your case that doesn't pass the smell
test.  ISTM you are trying to enforce integrity in the wrong place.
If you want strict checking on input parameters you can make a
function and have that update the view...inelegant but effective.
Ideally, you don't need this because your constraints are keeping
everything together.

Maybe with some more background I could give you some specific advice.

merlin

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

Re: [GENERAL] change owner to all object in a schema

[Greg Smith]

On Fri, 21 Dec 2007, Andreas Kretschmer wrote:


(answer from the nice doc-bot on our irc-channel)


Which for those of us who hate IRC is also available at 
http://search.postgresql.org ; the docbot hits show up at the top before 
the main search results.


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

---(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] Conditional updateable view

[Peter Childs]

On 16/12/2007, snacktime <[EMAIL PROTECTED]> wrote:
>
> I can't seem to find an example of how to add restrictions to the
> where clause of an updateable view created via the rule system.  For
> example I don't want the update to complete if a where clause is
> missing entirely, and in some cases I want to only allow the update if
> the where clause specifies a particular column.
>
> Is there a way to do this?



Quick answer no.

Long answer. You can limit which fields can be updated and indeed rename
fields or update completely different tables using rules and/or triggers.
But you not can say that the where clause must include something.

Peter

Re: [GENERAL] change owner to all object in a schema

[Andreas Kretschmer]

Nicola Benaglia <[EMAIL PROTECTED]> schrieb:

> Hi!
> I searched a lot on the net, but I haven't found a quick way to change
> owner to all  objects in a schema.
> Could you please suggest me a tool or an existing script to do that ?

There are any scripts, for instance:


13:38 < akretschmer> ??grantall
13:38 < rtfm_please> For information about grantall
13:38 < rtfm_please> see http://pgedit.com/public/sql/acl_admin/index.html
13:38 < rtfm_please> or http://unf.be/postgresql/postgres_grant_all.perl
13:38 < rtfm_please> or http://www.archonet.com/pgdocs/grant-all.html

(answer from the nice doc-bot on our irc-channel)


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

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

Fwd: Re: [GENERAL] SuSE gcc segfault compiling pgsql

[Marcin]

> > Marcin wrote:
> > > Did anybody face the same problem during the compilation of
> > > postgresql 8.2.5 on suse 10.2? I'm using gmake 3.81
> > 
> > In the future, please include subjects in your posts.
> > 
> 
> I'm very sorry for that. It seems that I pressed the send button to early.
> 
> 
> > 
> > Your compiler is segfaulting.  This suggests a problem with the 
> > compiler, rather than the code it's compiling.
> > 
> > SuSE has a bug [0] for segfaulting compilers, but it may not be related 
> > to the problem you're experiencing.
> > 
> > Also, the current PostgreSQL source at doxygen.postgresql.org [1] 
> > doesn't show a line 55 for dynalloc2.pgc.  Could you show us what's 
> > around that line?
> > 
> > Would you also show the full output of "gcc -v"?
> > 
> > Colin
> > 
> > [0] https://bugzilla.novell.com/show_bug.cgi?id=334054
> > [1] http://doxygen.postgresql.org/sql-dynalloc2_8c-source.html
> > 
> 
I downloaded a bunch of patches for the whole distribution and it has helped.
 
You can find the current output from gcc -v below:

Using built-in specs.
 Target: i586-suse-linux
 Configured with: ../configure --enable-threads=posix --prefix=/usr 
--with-local-prefix=/usr/local --infodir=/usr/share/info 
--mandir=/usr/share/man --libdir=/usr/lib --libexecdir=/usr/lib 
--enable-languages=c,c++,objc,fortran,obj-c++,java,ada 
--enable-checking=release --with-gxx-include-dir=/usr/include/c++/4.1.2 
--enable-ssp --disable-libssp --disable-libgcj --with-slibdir=/lib 
--with-system-zlib --enable-shared --enable-__cxa_atexit 
--enable-libstdcxx-allocator=new --program-suffix=-4.1 
--enable-version-specific-runtime-libs --without-system-libunwind 
--with-cpu=generic --host=i586-suse-linux
 Thread model: posix
 gcc version 4.1.2 20061115 (prerelease) (SUSE Linux)
 
 

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

[GENERAL] change owner to all object in a schema

[Nicola Benaglia]

Hi!
I searched a lot on the net, but I haven't found a quick way to change
owner to all  objects in a schema.
Could you please suggest me a tool or an existing script to do that ?

Thank you,
Nicola

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

[GENERAL] Machine spec 64-bit/32 GB RAM

[David Cotter]

Hello,

I am looking at a linux machine for a PG install with 32G of RAM. I have
never installed on anyting with more than 2 or 3 Gigs before. In order to
access that 32 Gigs do I have to use 64 bit OS and PG? Is there any reason
not to use a 64 bit? With a 32-bit version of PG can the 32 G of RAM be used
for caching?

Many thanks,
David

Re: [GENERAL] referential integrity and defaults, DB design or trick

[Richard Huxton]

Ivan Sergio Borgonovo wrote:

The default property (that is actually made by several fields) in my
case is not completely homogeneous with the others, because it has a
double meaning.
It is cleaner to split the meanings and the data. 


It usually is. A lot of the design decisions you can regret a year or 
two down the line are from trying to use one value for two purposes.


--
  Richard Huxton
  Archonet Ltd

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

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

Re: [GENERAL] settings for 8.2.5 on Mac OS X 10.4.11

[Wolfgang Keller]

The best thing is to fix the OS' ridiculously small shmem limits.
I've got this in /etc/sysctl.conf:

kern.sysv.shmmax=33554432
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.sysv.shmall=8192


Dependency on the available RAM?


and on a production DB you'd probably want shmmax and shmall
a good bit higher.

Don't forget to reboot after changing settings.  Also, a
"sysctl -a | grep shm" is a good idea to make sure it "took";
OS X is infamously picky about these settings.

regards, tom lane


A Macnerd as a core developer of Postgres? :-)

Sincerely,

Wolfgang Keller

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