[GENERAL] starting postgres on windows

[garry saddington]

How would I start Postgres on windows as an un-privileged user without
logging into an un-privileged account. I have tried the -U switch but it
still complains. I have version 8.
kind regards
Garry


---(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] Development of cross-platform GUI for Open Source DBs

[Merlin Moncure]

On 11/25/06, Ritesh Nadhani <[EMAIL PROTECTED]> wrote:

 Hello all

Let me introduce myself first. I am the ex-lead developer of SQLyog
(one  of the most popular GUI for MySQL which is Windows only and runs
on  Linux through WINE, more info at http://www.webyog.com).

===
New Project: wxWidgets based cross-platform GUI for Open Source databases
===

===
Motivation
===

I have recently shifted to US to study for MS (and hopefully PhD in Univ
of Iowa). I have also shifted to Mac OS X as my primary usage machine
(after lifetime of Windows devotion).

As part of my coursework, I work with MySQL and PostgreSQL
extensively. I searched but couldnt find any GUI which has similar
power like SQLyog by any means. I have tried (and have tried before
also) various GUIs for respective databases but somehow the features
in them are restrictive and are not powerful enough for developers
like me who writes lots of SQL queries and needs to get things done
fast.


I think you should look for inspiration in the EMS suite, which while
windows-only and closed source is a pretty feature packed and cross
(db) platform.  You are on very well traveled ground here.

IMO, I think the 'enterprise manager' style tool market is pretty well
saturated for most databases.  I also think dropping standard drivers
(jdbc) from your project is a mistake unless you think you can do
better.  Also now you are writing two projects.  I'm not trying to
discourage you...it's just a lot of work...ask Dave Page :-)  This is
coming from someone who implemented a driver of sorts from the ground
up for PostgreSQL, namely ISAM support.  Your efforts might be better
spent improving the standard driver you most feel comfortable with.

Another thing is that the current crop of gui admin tools focus on
wizards and dialogs and not so much on classic code management.  What
I personally would really want to see is a souped up text editor with
very smart code completion tools (auto completing tables, columns,
etc) sort of like ctags but optimized for sql.  I would avoid emphasis
of dialog based sql property editing which I consider orthogonal to
good sql development practices.

If these comments seem a bit generic, take a good look at Source
Insight (windows only, sigh) as a tool more geared towards code
management.  It's really quite amazing.

merlin

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

  http://archives.postgresql.org/

Re: [GENERAL] starting postgres on windows

[Harald Armin Massa]

Garry,

the standard recommendation is to install PostgreSQL as a service on
windows; logging in with an own low privilege user account, usually named
"postgres".

That usage of a service is recommended because it solves all the usual
problems of services :) (start, shut down, login as seperate user )

However: I guess you have an privileged user (=Member of Administrators),
and want to start PostgreSQL as an unprivileged user. So just create a
command shell as that user:

runas /user:postgres cmd

and start and stop PostgreSQL within that shell. (in the long run, you can
change that line to immediately start PostgreSQL instead of going via cmd)

For 8.2 there is scheduled a "drop privilege" method of startup on windows,
so that starting PostgreSQL as Administrator is no longer a problem, since
PostgreSQL will drop it privileges immediately after start.


hth

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

Re: [GENERAL] Development of cross-platform GUI for Open Source DBs

[Harald Armin Massa]

Ritesh,

what you are trying to do sound very similiar to dabo:

http://dabodev.com/

Maybe have a look at it first?

best wishes

Harald



--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

Re: [GENERAL] Development of cross-platform GUI for Open Source DBs

[Ritesh Nadhani]

Hello Herald

Somebody mailed me earlier also regarding dabodev but its not what I
am talking about.

I took a look at dabo and it definitely serves one part of application
that I have in mind but more specifically I was thinking of starting
with a admin/developer tool like TOAD, MS Query Analyzer, MySQL GUI
tools rather then those form building but yes, in the long run - it
will be part of the tool or in form of another specific tool. Maybe we
can just integrate it with our tool line up later on.

All others, I will reply to you in could of days. I have two
assignments due in next 2 days so things are little crazy. Will reply
to each and every point.

On 11/27/06, Harald Armin Massa <[EMAIL PROTECTED]> wrote:

Ritesh,

what you are trying to do sound very similiar to dabo:

http://dabodev.com/

Maybe have a look at it first?

best wishes

Harald



--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.


---(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] new server

[Sim Zacks]

I am buying a new gentoo server for postgresql and I was wondering if a 64 bit CPU would make any 
difference in performance. I am not planning on using 64 bit integers.


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

[Alban Hertroys]

Martijn van Oosterhout wrote:
> Well, it kinda sucks for joining because comparing strings may take
> tens to hundreds of times as long as compairng integers. It's not just
> byte-wise comparison but you have to be locale sensetive about it.
> 
> My main problem with using any kind of string as "natural key" is that
> once you start passing it around you have worry about the encoding of
> said string and when it goes a round-trip to a client, will what you
> get back still be the same? If you can guarentee ASCII you might be ok,
> but otherwise... Integers have none of these issues.

That sounds like there would be a purpose for a locale-less type of
string, as ugly as that is.
That's not a feature request, just something to digest ;)

>> Having two keys, a primary_key of int4 and a unique key of varchar(128) 
>> would be very ugly on INSERT/DELETE/UPDATE queries because of the index 
>> overhead.
> 
> In general my tables are queried several orders of magnitude more often
> than they are updated, so index update cost isn't all that relevent.

Indeed. Basically if expensive calculations can be pre-calculated I tend
to move them into triggers that store the pre-calculated result. Usually
there's only a handfull of people modifying data, while there are many
people requesting it.

Regards,
-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

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

Re: [GENERAL] Slow left outer join

[Alexander Staubo]

On Nov 27, 2006, at 02:45 , Tom Lane wrote:


Alexander Staubo <[EMAIL PROTECTED]> writes:

select comments.*, users.*
from comments
left outer join users on
   users.id = comments.creator_id
inner join events_comments on
   comments.id = events_comments.comment_id
   and events_comments.event_id = 9244



The outer join seems unnecessarily slow.


Try flipping the order of the two joins.  PG 8.2 will be smart  
enough to

do that for itself, but no existing release understands when it's safe
to rearrange outer-join order.


That fixes it. Thanks.

Alexander.


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

Re: [GENERAL] Extract between year *and* month

[Alban Hertroys]

Syl wrote:
>> Try
>>
>> select *
>>  from tablename
>> where title like ('%nith%')
>>and date_trunc('month',recall_date::timestamp) >= 
>> date_trunc('month','1995-01-01'::timestamp)
>>and date_trunc('month',recall_date::timestamp) <= 
>> date_trunc('month','2006-12-31'::timestamp)
>>   order by recall_date
>>

Actually, that looks a lot like BETWEEN, which is even shorter :)

 select *
   from tablename
  where title like ('%nith%')
and date_trunc('month',recall_date::timestamp)
between date_trunc('month','1995-01-01'::timestamp)
and date_trunc('month','2006-12-31'::timestamp)
  order by recall_date


> Fantastic.
> 
> Thank you to Matthias and Russell - everything is working perfectly!

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

   http://archives.postgresql.org/

Re: [GENERAL] Connecting via ssh tunnel

[Weerts, Jan]

Hi all!

[EMAIL PROTECTED] wrote:
> On Friday 24 November 2006 12:56, ben short wrote:
>> I have a postgresql server setup on a Solaris 10 box. I can
>> connect to the db via psql from the local machine. What I have
>> been trying to do it connect with pgAdmin from my workstation. I
>> have setup the tunnel correctly, local port 5432 and destination
>> localhost:5432. Everytime I try to connect I get the following
>> message. 
> 
> I guess the tunnel isn't correct. You connect on the same ports -
> seems wrong to me. I'm using a tunnel like this:
> 
> ssh -l  -L :localhost:5432 

actually 
  ssh -l USER -L 5432:localhost:5432 SOMEOTHERHOSTTHANLOCALHOST

is ok. the "localhost" part is evaluated on the SOMEOTHERHOST
and dns lookup is done there. I have been bitten by this before.
If SOMEOTHERHOST is the same host, this would obviously not
work, since the ssh tunnel and the postgres server cannot
listen on the same port.

I am not sure if the error message you are seeing is the same
one you would get when a connection is impossible. To me it
looks like a connection was initiated by the server closed
it.

Regards
  Jan

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

Re: [GENERAL] starting postgres on windows

[Raymond O'Donnell]

On 27 Nov 2006 at 8:03, garry saddington wrote:

> How would I start Postgres on windows as an un-privileged user without
> logging into an un-privileged account. I have tried the -U switch but
> it still complains. I have version 8. 

If you installed PostgreSQL using the installer - which I'd 
recommend, as it makes life *very* easy - then along the way it 
should have created an unprivileged user. Have a look at the server 
service in Administrative Tools -> Services, and on the "Log on" tab 
you should see the user under which it's running.

--Ray.


--

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
[EMAIL PROTECTED]
--



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

Re: [GENERAL] new server

[Merlin Moncure]

On 11/27/06, Sim Zacks <[EMAIL PROTECTED]> wrote:

I am buying a new gentoo server for postgresql and I was wondering if a 64 bit 
CPU would make any
difference in performance. I am not planning on using 64 bit integers.


a 64 bit server will allow the computer to use more memory.  This can
be a pretty big deal if the database is going to get large.

merlin

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

  http://archives.postgresql.org/

[GENERAL] pg_restore error

[Peter Wilson]

I've just got the following message while trying to restore a database :

pg_restore : [custom archiver] Dumping a specific TOC data block out of order is
not supported without ID on this input stream (fseek required).

The command was :
pg_restore -L /tmp/toc --dbname=whitebeam --disable-triggers --username=postgres
/var/backups/restore-db.psql

/tmp/toc is a re-orderd output from :

pg_restore -l -a /var/backups/restore-db.psql > /tmp/toc

Using Postgres 8.1.4 on
Linux version 2.6.15-1.2054_FC5

The dump itself was made on another machine running 8.1.4 on Fedora core 4.

I've run similar commands on this machine before with no problems. Not sure why
I should start to have these problems now?

Anyone else seen anything similar? Thoughts much appreciated!

Pete

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

[GENERAL] Solaris 10 problem

[Doron Baranes]

Hi,

I installed postgres 814 on solaris 10 using the following packages
SUNWpostgr-libs-8.1.4-x86.tar.gz
SUNWpostgr-8.1.4-x86.tar.gz
SUNWpostgr-server-8.1.4-x86.tar.gz
SUNWpostgr-server-data-8.1.4-x86.tar.gz
SUNWpostgr-contrib-8.1.4-x86.tar.gz
SUNWpostgr-devel-8.1.4-x86.tar.gz
SUNWpostgr-docs-8.1.4-x86.tar.gz
SUNWpostgr-jdbc-8.1.4-x86.tar.gz
SUNWpostgr-tcl-8.1.4-x86.tar.gz
SUNWpostgr-pl-8.1.4-x86.tar.gz

and no error occured.

when i try to run initdb i get Invalid Argument.

any ideas please,

10x ahead
DoronB

---(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] kerberos authentication error with Windows 2003 SP1 AD

[koppelp]

 I am able to use kerberos authentication with Windows 20003 SP1 Active
Directory.   I couldn't get Postgres 7.414 to work, but as soon as I
upgraded to 8.15, added my username to postgres (also set in Active
Directory), used POSTGRES as the service principal, I could login using
psql successfully.

Thanks for all who helped.

Paul Koppel




   
 "Magnus Hagander" 
 <[EMAIL PROTECTED] 
 et>To
   <[EMAIL PROTECTED]> 
 11/20/2006 04:16   cc
 AM  
   Subject
   RE: [GENERAL] kerberos  
   authentication error with Windows
   2003 SP1 AD 
   
   
   
   
   
   




Hi!

Wherever your pg_ctl command sets the logfiles, or syslog if you use
syslog etc.

(Note that you still need to define the user in PostgreSQL as well, but
that shoudl give a different error message)

//Magnus

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: den 17 november 2006 23:18
> To: Magnus Hagander
> Subject: RE: [GENERAL] kerberos authentication error with
> Windows 2003 SP1 AD
>
> HI Magnus-
>
> Thanks for your reply. Which error log in postgres should I
> look at? Do I need to configure postgres to add more detailed
> logging? Thanks again for your help.
>
> Please include my email address in your reply.
>
> -- pk
>
> Inactive hide details for "Magnus Hagander"
> <[EMAIL PROTECTED]>"Magnus Hagander" <[EMAIL PROTECTED]>
>
>
>
>
>"Magnus Hagander"
<[EMAIL PROTECTED]>
>
>11/14/2006 10:22 AM
>
>
>
> To
>
> <[EMAIL PROTECTED]>, 
>
>
> cc
>
>
>
>
> Subject
>
> RE: [GENERAL] kerberos authentication error with Windows 2003 SP1 AD

>
>
> > My operating system is Red Hat Linux AS 4, Kerberos 5, with
> > postgresql-7.4.14 that I compiled. I can authenticate using
> ssh, su,
> > console login, and also have gotten apache mod_auth_kerb to
> work with
> > AD - but I am missing something with postgresql. When I try:
> >
> > [EMAIL PROTECTED] ~]$ /usr/local/pgsql/bin/psql -d test -h ipswich
> > psql: Kerberos 5 authentication failed
> >
> > For the configure step, I did (needed the include statement
> to prevent
> > an error about comm_err.h):
> >
> > [EMAIL PROTECTED] postgresql-7.4.14]$ ./configure --with-java
> > --with-krb5 --with-includes=/usr/include/et
> >
> > The make proceeded normally.
> >
> > My pg_hba.conf looks like this (with pkoppe01 defined in Active
> > Directory but not defined in postgres using "createuser")
> >
> > local all all trust
> > host test pkoppe01 192.168.1.0 255.255.255.0 krb5
> >
> > Also have "tcpip_socket = true" and the postgres keytab
> referenced in
> > postgresql.conf and the keytab file itself owned by postgres.
> >
> > When I try the psql command above (as pkoppe01) I do get
> the service
> > ticket for postgres:
> >
> > [EMAIL PROTECTED] ~]$ klist
> > Ticket cache: FILE:/tmp/krb5cc_501_LCzZ1P Default principal:
> > [EMAIL PROTECTED]
> >
> > Valid starting Expires Service principal
> > 11/13/06 11:17:25 11/13/06 21:17:28
> > krbtgt/[EMAIL PROTECTED] renew until 11/14/06 11:17:25
> > 11/13/06 11:19:02 11/13/06 21:17:28
> > postgres/[EMAIL PROTECTED]
> > renew until 11/14/06 11:17:25
> >
> > Any ideas would be greatly appreciated. Thanks in advance.
> > Please feel free to email me directly as I just joined the list and
> > don't know my way around yet.
>
> The server log from postgresql should give some more information.
>
> //Magnus
>
>
>

Re: [GENERAL] Buffer overflow in psql

[John D. Burger]

Tom Lane wrote:


Um, is that really considered a fix???  We all know that there's no
guarantee at all, even in ANSI C, that unsigned int isn't bigger than
32 bits, right?


OID is 32 bits.  Full stop.


I should know better than to argue about this, but:

In that case, casting it as in the OP's code sample seems problematic  
in the other direction:


  sprintf(buf, "%u", (unsigned int)PQoidValue(results));

since unsigned int could be as small as 16 bits, thus truncating the  
OID value.


Ok, I'll stop now, I promise.

- John D. Burger
  MITRE

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

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

Re: [GENERAL] PGDATA

[Jeffrey Webster]

On 24 Nov 2006 04:43:02 -0800, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:




I just can't understand the use of this PGDATA variable!

-I am on FC3. (pgl 7.4)
-I am installing rpms and then running /etc/init.d/postgresql start
(which is done by default)
-The resulting "data" directory is in /var/lib/pgsql/data

I now want the "data" directory to be on
/home3/myreallylargepartition/pgsql/data
so:

-I login as postgres
-I change .bash_profile so that PGDATA points at the new directory
-I restart the server by  doing a "/etc/init.d/postgresql restart"

Shouldn't any new tables I create be in the new area?!

Thanks.




After you changed PGDATA, and thus, the location of the data files, did you
remember to run "initdb" first?

This is the usual sequence of events for changing where the data files exist
(as root, or via sudo):

/etc/init.d/postgresql stop
mkdir -p /pgsql/data
chown -R pgsql:pgsql /pgsql/data
su -l pgsql
export PGDATA=/pgsql/data  # Make sure /etc/init.d/postgresql is aware of
this change.
initdb



Starting the database being the last thing you do.

Re: [GENERAL] Development of cross-platform GUI for Open Source DBs

[Tony Caduto]

Merlin Moncure wrote:


I think you should look for inspiration in the EMS suite, which while
windows-only and closed source is a pretty feature packed and cross
(db) platform.  You are on very well traveled ground here.




Not to mention Lightning Admin for Postgresql  and MySQL:-) 


(http://www.amsoftwaredesign.com)

Which does run great with WINE, so while not native cross platform it 
does run well any where newer versions of

WINE do.

The EMS stuff is nice, but I found them to be to a little bloated and 
cumbersome to work with during my research of competing products.


One could also use Mono 1.2 and any .net 1.1 IDE such as Borland Turbo 
C# or Delphi.net (with npgsql .net data provider)  both of which you can 
get for free here:

http://www.turboexplorer.com
or SharpDevelop which you can get here:  http://www.icsharpcode.com

You can take the exes these IDEs create and simply run them as is on 
Linux or where ever Mono 1.2 does. 
It really does work well. Much nicer than Java.  You do have to stick to 
fully managed code when you develop your apps on win32 as any win32 API 
pinvoke commands will not work in Mono.


Currently there is no IDE available on Linux or other Unix systems that 
has the productivity of the tools mentioned above. (except for the 
unfortunate Kylix IDE)


Sure you could use Wxwidgets with Python or whatever, but you 
development time is going to be more than twice as long as compared to a 
application built
with Delphi.  The closest to Delphi in a cross platform system is 
NetBeans and even with their form designer it's still tedious working 
with databases compared to

Delphi.  If you want to speak to the Oracle, you have to go to Delphi :-)

I have also tried MonoDevelop, but it has a way to go before being 
really useful.


--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



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

[GENERAL] -b command line parameter in pg_dump

[Andrus]

I noticed that that pgAdmin invokes pg_dump with -b command line parameter.

I havent found any documentation about this parameter in Psotgres Docs.

What is the purpose of this parameter ?



Andrus.



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

   http://archives.postgresql.org/

Re: [GENERAL] -b command line parameter in pg_dump

[Scott Marlowe]

On Mon, 2006-11-27 at 10:36, Andrus wrote:
> I noticed that that pgAdmin invokes pg_dump with -b command line parameter.
> 
> I havent found any documentation about this parameter in Psotgres Docs.
> 
> What is the purpose of this parameter ?

According to pg_dump --help:

  -b, --blobs  include large objects in dump


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

Re: [GENERAL] -b command line parameter in pg_dump

[Alvaro Herrera]

Andrus wrote:
> I noticed that that pgAdmin invokes pg_dump with -b command line parameter.
> 
> I havent found any documentation about this parameter in Psotgres Docs.

You haven't looked closely enough

pg_dump --help:

  -b, --blobs  include large objects in dump

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

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

[GENERAL] Returning multiple rows from a function?

[Bret Schuhmacher]

Hi all,

I'm trying to return multiple rows from a function, but all I can get 
with the code below is the first row.  I got most of the function below 
off the net and I think the problem is the first "RETURN" statement, 
which stops the loop.


CREATE OR replace function getOnCallVol() RETURNS RECORD AS $$
DECLARE
   r RECORD;  
BEGIN

   FOR r IN
   select fname,lname,phone1,phone2,phone3,phone4,phone5
   from events e,volunteer v
   where (now() >= starttime and now()<=endtime and e.v_id = 
v.v_id)

   OR (fname='Backup') limit 2

   LOOP
   return r;
   END LOOP;
  
   RETURN null;


END;
$$ Language plpgsql;

When I run the SQL alone, I get two rows, as I should:
MarySmith112233   
BackupCellphone3319993   

However, if I run it via the function (i.e. select getOnCallVol()), I 
get this:

(Mary,Smith,11,22,33,"","")

Is there another way to get each row returned?  I played around with 
making the function return a "SETOF RECORD" and using "RETURN NEXT", but 
had no luck. 


Thanks,

Bret


--
Bret Schuhmacher
[EMAIL PROTECTED]



---(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] How to increace nightly backup speed

[Andrus]

I need to create nightly backups to separate computer over internet from 8.1 
server

I tried to run pg_dump to make backup using command

"..\pg_dump\pg_dump.exe" -i -Z9 -b -v -f "C:\061127 mybackup.backup" -F c -h
81.50.12.18 -U myuser -p 5431 mydb

but this takes 4.5 hours to complete.

How to increace backup speed ?

Andrus.




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

Re: [GENERAL] Returning multiple rows from a function?

[brian]

Bret Schuhmacher wrote:

Hi all,

I'm trying to return multiple rows from a function, but all I can get 
with the code below is the first row.  I got most of the function below 
off the net and I think the problem is the first "RETURN" statement, 
which stops the loop.


CREATE OR replace function getOnCallVol() RETURNS RECORD AS $$
DECLARE
   r RECORD;  BEGIN
   FOR r IN
   select fname,lname,phone1,phone2,phone3,phone4,phone5
   from events e,volunteer v
   where (now() >= starttime and now()<=endtime and e.v_id = 
v.v_id)

   OR (fname='Backup') limit 2

   LOOP
   return r;
   END LOOP;
 RETURN null;

END;
$$ Language plpgsql;

When I run the SQL alone, I get two rows, as I should:
MarySmith112233   Backup
Cellphone3319993  
However, if I run it via the function (i.e. select getOnCallVol()), I 
get this:

(Mary,Smith,11,22,33,"","")

Is there another way to get each row returned?  I played around with 
making the function return a "SETOF RECORD" and using "RETURN NEXT", but 
had no luck.

Thanks,



Use "RETURNS SETOF record" and "FOR r IN ... LOOP RETURN NEXT; END LOOP; 
RETURN;"


brian

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

  http://archives.postgresql.org/

Re: [GENERAL] How to increace nightly backup speed

[Alexander Staubo]

On Nov 27, 2006, at 18:13 , Andrus wrote:

I need to create nightly backups to separate computer over internet  
from 8.1

server

I tried to run pg_dump to make backup using command

"..\pg_dump\pg_dump.exe" -i -Z9 -b -v -f "C:\061127  
mybackup.backup" -F c -h

81.50.12.18 -U myuser -p 5431 mydb


Reducing compression (change -Z9 to something like -Z0) might help a  
little, depending on the processing power of the server and available  
bandwidth. If you really want compression, you could do the dump over  
a SSH connection and gzip on the client end:


  $ ssh 81.50.12.18 "pg_dump -Z0 -Fc -ibv -U myuser mydb" | gzip -9  
>mybackup.backup


Alexander.


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

  http://archives.postgresql.org/

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Scott Ribe]

> Bottom line: check digits are in SSNs

Uhm, no they're not. And this is of course one of the huge problems with
SSNs. (Although not quite as bad as the fact that they're not strictly
unique. Yes, really, duplicates have been issued in the past.)

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



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

Re: [GENERAL] OID Perfomance - Object-Relational databases

[Scott Ribe]

> There are basically two ways to attack the problem of a database-wide
> unique ID:
> 
> * Use a single int8 sequence for the whole database;
> 
> * Use the combination of table OID and row OID (or, perhaps, an int8
> sequence for the row identifier, if you need more than a billion or
> so rows in the table).

There's a third way: int8 serial, lower 48 bits for row id, upper 16 bits
appended with your own table id.

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



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

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

Re: [GENERAL] Returning multiple rows from a function?

[Volkan YAZICI]

On Nov 27 11:59, Bret Schuhmacher wrote:
> I'm trying to return multiple rows from a function, but all I can get 
> ...
>
> CREATE OR replace function getOnCallVol() RETURNS RECORD AS $$

You should return "SETOF record". See related section of the manual
about SRFs (Set Returning Fucntions).


Regards.

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

   http://archives.postgresql.org/

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Scott Marlowe]

On Thu, 2006-11-23 at 10:23, Ron Johnson wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 11/22/06 20:23, carter ck wrote:
> > Hi all,
> > 
> > I am wonderring if it is a good practice to use SERIAL index as primary
> > key, as it is only available up to 999?
> > 
> > Currently i am dealing with storing LDAP users into Postgres and i am
> > looking for a better way to make use of the DN as primary key instead of
> > SERIAL index.
> > 
> > Any advice or suggestion is appreciated.
> 
> I'm one of those who thinks that a (possibly multisegment) natural
> key *does* exist, and that if you think it doesn't, your design is
> wrong.

Spend some time in the travel industry...  The tax category ZO means
Passenger Service Chareg in Denmark.  Or Greenland, or Faroe Islands. 
And can be entered more than once.  And the travel agent has to look at
the context of the travel itinerary to know which one(s) it is.

Sadly, the real world has many data problems created by idiots in suits
30 years ago that aren't going to go away any time soon.


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

Re: [GENERAL] Linux hard drive/device nodes for a Postgres RAID

[Scott Marlowe]

On Thu, 2006-11-16 at 14:56, Martijn van Oosterhout wrote:
> On Thu, Nov 16, 2006 at 12:40:41PM -0800, Glen Parker wrote:
> > But now, pull the drive from port 2 and boot the system.  You will now 
> > have SDA,SDB,SDC.  The kernel will now fail BOTH of the last two drives 
> > from the RAID array.  The one that was SDC is gone, and obviously fails. 
> >  The one that was SDD is now SDC, so its ID doesn't match what the 
> > kernel thought it should be, so it fails it too.  If you kill the FIRST 
> > drive in the array, I believe the entire array becomes inoperable 
> > because of the resulting shift and ID mismatch.
> 
> Is that really so? AIUI the position of the disk in the array is stored
> on the disk itself, so it should be able to handle disks moving around
> no problem, have you tried it?

Just FYI, I've tried this before.  yes, linux software RAID, knowing
that the linux scsi numbering system is non-deterministic, is designed
to handle this. 

In fact, you can build a RAID5 or RAID0 array of as many disks as you
like, shut down the machine, change every single drive ID, and the
machine will still find the RAID arrays.

Last I tested this was on something like RH 7.2 by the way.  Times may
have changed, but I can't imagine someone being stupid enough to break
the RAID array handling that worked so well back then.

> 
> > So the question is, is there some way to "pin" a drive to a device 
> > mapping?  In other words, is there a way to force the drive on port 0 to 
> > always be SDA, and the drive on port 2 to always be SDC, even if the 
> > drive on port 1 fails or is pulled?
> 
> I thought you could do this with options on the command-line, or using
> udev. But I don't think it's actually necessary.

You can, it's generally not.


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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Joshua D. Drake]

On Mon, 2006-11-27 at 11:47 -0600, Scott Marlowe wrote:
> On Thu, 2006-11-23 at 10:23, Ron Johnson wrote:
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> > 
> > On 11/22/06 20:23, carter ck wrote:
> > > Hi all,
> > > 
> > > I am wonderring if it is a good practice to use SERIAL index as primary
> > > key, as it is only available up to 999?
> > > 
> > > Currently i am dealing with storing LDAP users into Postgres and i am
> > > looking for a better way to make use of the DN as primary key instead of
> > > SERIAL index.'


Bigserial?

Joshua D. Drake


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




---(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] How to increace nightly backup speed

[Jeff Davis]

On Mon, 2006-11-27 at 19:13 +0200, Andrus wrote:
> I need to create nightly backups to separate computer over internet from 8.1 
> server
> 
> I tried to run pg_dump to make backup using command
> 
> "..\pg_dump\pg_dump.exe" -i -Z9 -b -v -f "C:\061127 mybackup.backup" -F c -h
> 81.50.12.18 -U myuser -p 5431 mydb
> 
> but this takes 4.5 hours to complete.
> 
> How to increace backup speed ?
> 

You might try using online backups. By following the steps in this
document:

http://www.postgresql.org/docs/8.1/static/backup-online.html

you can back up the data at the speed of your filesystem. There's no way
to make it faster than that.

Hope this helps,
 Jeff Davis


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

Re: [GENERAL] vacuum: out of memory error

[Andrew Sullivan]

On Fri, Nov 24, 2006 at 11:59:16AM +0100, Jakub Ouhrabka wrote:
> I've done little research in mailing list archives and I found possible 
> cause: table corruption caused by flaky hardware. Does it sound about 
> right? Are there any other possible causes?

It sounds about right, yes; but the other possible cause is a
software bug.  In the absence of data proving you have no hardware
problems, though, I think you'll find that people are singularly
unwilling to investigate software bugs in this case.

> What can be corrupted? 

Anything.

> How can I check it? 

You can try stepping through the table in question and seeing if you
run into problems anywhere.  By binary search, you should be able to
narrow it pretty quickly.

> How can I correct it?

Well, the corrupt rows are lost.  The usual method is "restore from
backup".

> What 
> are possible consequences of this corruption? 

You can't read the data.  But you already knew that: it's why your
vacuum is blowing up.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The plural of anecdote is not data.
--Roger Brinner

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

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

[GENERAL] SSL error: decryption failed or bad record mac

[Claudio Rossi]

Hello, I just installed postgresql 8.1.5 and the only things I ported from 
8.0.3 (last version I used) are server, user and CA X509 certificates (fresh 
install for everything else). I'm using OpenSSL 0.9.8d, Fedora Core 4, I have 
enabled SSL as described in manual (at every step where it's needed) and I had 
no problems with previous 8.0.3. This is the problem: when I set up a SSL 
connection I get this log output:

DEBUG: SSL connection from "common_name"
DEBUG: SSL: write alert (0x0214)
LOG: SSL error: decryption failed or bad record mac

and backend returns a signal 15, terminating connection. Any idea? Does anybody 
know what kind of error is "decryption failed or bad record mac" (I mean, 
client certificate error? server certificate error?)? Thanks.

Regards,
Claudio Rossi


--
Mutui a tassi scontati da 40 banche. Richiedi online e risparmia...Servizio 
gratuito. www.mutuionline.it
http://click.libero.it/mutuionline27nov



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

Re: [GENERAL] How to increace nightly backup speed

[Joshua D. Drake]

> > 
> 
> You might try using online backups. By following the steps in this
> document:
> 
> http://www.postgresql.org/docs/8.1/static/backup-online.html
> 
> you can back up the data at the speed of your filesystem. There's no way
> to make it faster than that.

Note however that you will want to apply the backups to the copied
version of the postgresql every night. Otherwise you are likely looking
at a very long restore process should you need to bring it up.

Joshua D. Drake


> 
> Hope this helps,
>  Jeff Davis
> 
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 
-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

[GENERAL] PostgreSQL doesn't accept connections when Windows Server is rebooted

[George Weaver]

Hi everyone,

I recently installed PostgreSQL (Version 8.1.5.1) using the MSI installer on a 
Windows Server 2000 system.

I ran the installer logged in under an Administrator account, and set the 
service account to postgres.

During the installation process, when it came time to run initdb, the installer 
would stop with the error message:

 "Failed to run initdb: 128.  Please see the logfile in 'C:\Program 
Files\PostgreSQL\8.1\tmp\initdb.log. 
  Note!  You must read/copy this logfile before you click OK, or it will be 
automatically removed."

I surmized that the problem was that I was attempting to run the install as an 
Administrator, so I addressed the problem by running the install, but not 
having the installer run initdb step to intialize the database cluster.  When 
the install finished without error, I then logged onto the server under the 
postgres account and manually ran initdb.  I was able to initialize the cluster.

I then updated the postgresql.conf file with "listen_addresses = '*' and 
changed the port from 5432 (currently being used for a 7.3 server) to 5435.

Everything runs as expected.

Until the Windows Server is rebooted.  When this occurs, the PostgreSQL service 
starts as expected, but won't accept connections.

If the PostgreSQL service is then restarted manually (via Administrative Tools 
> Component Services > Restart), the service runs as expected and everything is 
normal.

How can I correct this?

Thanks in advance,
George

Re: [GENERAL] PostgreSQL doesn't accept connections when Windows

[Joshua D. Drake]

> 
> Everything runs as expected.
>  
> Until the Windows Server is rebooted.  When this occurs, the
> PostgreSQL service starts as expected, but won't accept connections.
>  
> If the PostgreSQL service is then restarted manually (via
> Administrative Tools > Component Services > Restart), the service runs
> as expected and everything is normal.
>  
> How can I correct this?

By providing us with some logging output that tells us what PostgreSQL
is doing. :)

Sincerely,

Joshua D. Drake


>  
> Thanks in advance,
> George
-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

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

AIX and getaddrinfo (was Re: [GENERAL] Upgrade problem)

[Tom Lane]

Bill Kurland <[EMAIL PROTECTED]> writes:
> I did a google search on AIX + getaddrinfo and found
> http://lists.samba.org/archive/rsync/2002-April/002063.html
> In that context the author says that adding the port number in 
> etc/services solved his problem with getaddrinfo.

Interesting.  I wonder whether IBM thinks that there is some
security-related reason for only allowing programs to bind to port
numbers that are listed in /etc/services?

> So I tried that and, 
> lo, it has some effect, though I'm not sure it's 100% desirable. The log 
> entry is:

> LOG:  could not bind IPv6 socket: The type of socket is not supported in 
> this protocol family.
> HINT:  Is another postmaster already running on port 5432? If not, wait 
> a few seconds and retry.

I think this is OK.  There are many machines on which the userspace
code supports IPv6 while the kernel doesn't, or vice versa.  It looks
to me like getaddrinfo returned both IPv4 and IPv6 translations of
"localhost", but the kernel rejected the IPv6 version when PG tried it.
Since you evidently have a working IPv4 port, there's nothing to worry
about.  If it really bugs you, the /etc/netsvc.conf change suggested in
our FAQ_AIX would probably suppress the log message.

Can anyone else confirm the behavior of getaddrinfo wanting port 5432
to be listed in /etc/services?  If this is real, we ought to have
something about it in FAQ_AIX.

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/27/06 11:26, Scott Ribe wrote:
>> Bottom line: check digits are in SSNs
> 
> Uhm, no they're not. And this is of course one of the huge problems with
> SSNs. (Although not quite as bad as the fact that they're not strictly
> unique. Yes, really, duplicates have been issued in the past.)

Hmm, you're right.  Other kinds of important numbers have check
digits, though.

http://www.cs.nmsu.edu/~cssem/DickOct18.pdf

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFay20S9HxQb37XmcRAvh/AJ9q2mgWBGCgR7/IY2lB2TJVheq/DwCgiJkP
MLhLb6Au0HOL3Iruk0ZrCSk=
=vGcr
-END PGP SIGNATURE-

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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/27/06 11:47, Scott Marlowe wrote:
> On Thu, 2006-11-23 at 10:23, Ron Johnson wrote:
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 11/22/06 20:23, carter ck wrote:
>>> Hi all,
>>>
>>> I am wonderring if it is a good practice to use SERIAL index as primary
>>> key, as it is only available up to 999?
>>>
>>> Currently i am dealing with storing LDAP users into Postgres and i am
>>> looking for a better way to make use of the DN as primary key instead of
>>> SERIAL index.
>>>
>>> Any advice or suggestion is appreciated.
>> I'm one of those who thinks that a (possibly multisegment) natural
>> key *does* exist, and that if you think it doesn't, your design is
>> wrong.
> 
> Spend some time in the travel industry...  The tax category ZO means
> Passenger Service Chareg in Denmark.  Or Greenland, or Faroe Islands. 
> And can be entered more than once.  And the travel agent has to look at
> the context of the travel itinerary to know which one(s) it is.
> 
> Sadly, the real world has many data problems created by idiots in suits
> 30 years ago that aren't going to go away any time soon.

Yes, that's the point.  They are legacy designs, and that portion of
the design is wrong.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFay6ES9HxQb37XmcRAly9AKC5qEpO9Z9Oscf5Bp3nbuSgIswPJwCg0dh3
FoDu81i4pndNwIQ88Bl2SsU=
=WCzo
-END PGP SIGNATURE-

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

Re: [GENERAL] PostgreSQL doesn't accept connections when Windows

[Tony Caduto]

George Weaver wrote:


I then updated the postgresql.conf file with "listen_addresses = '*' 
and changed the port from 5432 (currently being used for a 7.3 server) 
to 5435.
 

Have you tried setting the port back to default and see what happens?
Is it possible for you to stop the 7.3 server for a moment to test this 
theory?


The only thing you have different is the port, so I am thinking that it 
has something to do with that.


Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[John McCawley]

Yes, that's the point.  They are legacy designs, and that portion of
the design is wrong.

 

I'll weigh in my my .02 on this subject.  After much pain and agony in 
the real world, I have taken the stance that every table in my database 
must have an arbitrary, numeric primary key (generally autogenerated).  
I have found that this gets me into a lot of arguments with other 
database guys, but never seems to cause any problems for me.  
Conversely, I have seen innumerable problems in the real world caused by 
the usage of actual data as primary keys.


Perhaps I am amazingly ignorant, but I have yet to find a case where my 
approach causes any real problems.  What does using "real" data as a 
primary key buy you?  The only real advantages I can see are that an 
individual record's data will be somewhat more human-readable without 
joining to other tables, and that your search queries can be simpler 
because they don't have to join against other tables.


On the (many) occasions that I have worked on databases with "real" data 
as primary keys, I just saw so many problems arise.  In the real world, 
data changes, even supposedly unchangeable data.  When using arbitrary 
primary keys, all you have to do is change the data in the one table 
where it lives.  If you are using real data as your keys, you have write 
complex queries or code to "fix" your data when the supposedly 
unchangeable data changes.


Anyway, I'm sure this is a huge argument, but that's my 0.2



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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Brandon Aiken]

Simply put, it doesn't scale as well.

If a table already has candidate keys, then you've presumably got unique
indices on them.  A surrogate primary key adds another segment of data
to pass through I/O and another index to maintain.  Under high loads,
those extra cycles will cost you transactions per minute.

If you're able to throw hardware at the problem to compensate for
performance and data size issues, it's not a problem.  Most databases
are run on systems that are overkill already.  If, OTOH, you're running
a system that needs to be able to process billions of transactions with
exabytes data (say, for example, a comprehensive multi-national health
record database) then you're going to be as interested in SQL tuning as
it's possible to be because no amount of hardware will be enough.

The other argument is that it's redundant data with no real meaning to
the domain, meaning using surrogate keys technically violates low-order
normal forms.

As far as data changing, if you're using foreign key constraints
properly you should never need to issue more than one UPDATE command.
ON UPDATE CASCADE is your friend.

It is always possible to design a domain model which perfectly captures
business logic.  However, it is *not* always possible to actually
implement that domain in a computerized RDBMS, nor is it always
practical.  Just as the domain model represents an estimated
implementation of the real world information, an RDBMS is just an
estimated implementation of the relational model.

--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of John McCawley
Sent: Monday, November 27, 2006 1:53 PM
To: Ron Johnson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] IS it a good practice to use SERIAL as Primary
Key?


>Yes, that's the point.  They are legacy designs, and that portion of
>the design is wrong.
>
>  
>
I'll weigh in my my .02 on this subject.  After much pain and agony in 
the real world, I have taken the stance that every table in my database 
must have an arbitrary, numeric primary key (generally autogenerated).  
I have found that this gets me into a lot of arguments with other 
database guys, but never seems to cause any problems for me.  
Conversely, I have seen innumerable problems in the real world caused by

the usage of actual data as primary keys.

Perhaps I am amazingly ignorant, but I have yet to find a case where my 
approach causes any real problems.  What does using "real" data as a 
primary key buy you?  The only real advantages I can see are that an 
individual record's data will be somewhat more human-readable without 
joining to other tables, and that your search queries can be simpler 
because they don't have to join against other tables.

On the (many) occasions that I have worked on databases with "real" data

as primary keys, I just saw so many problems arise.  In the real world, 
data changes, even supposedly unchangeable data.  When using arbitrary 
primary keys, all you have to do is change the data in the one table 
where it lives.  If you are using real data as your keys, you have write

complex queries or code to "fix" your data when the supposedly 
unchangeable data changes.

Anyway, I'm sure this is a huge argument, but that's my 0.2



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

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

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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Harald Armin Massa]

John,

I'll weigh in my my .02 on this subject.  After much pain and agony in

the real world, I have taken the stance that every table in my database
must have an arbitrary, numeric primary key (generally autogenerated).



I feel the same.

In the "real world" there is no such thing as a primary key. At least not
over time. Not enough people understand the concept of a primary key to make
those things existent in the real world.

So we take an artificially primary key - and most reliable way is to create
it yourself.

Harald



--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

[GENERAL] fatal error on 8.1 server

[Tony Caduto]

I don't know what the exact version is but I am getting this:

FATAL:  database is not accepting commands to avoid wraparound data loss 
in database "postgres"


How can I recover from this and why woud this happen to the postgres 
database?  Has my server been hacked?  there should be no inserts or 
anything going on.


Thanks,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



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

Re: [GENERAL] fatal error on 8.1 server

[Alvaro Herrera]

Tony Caduto wrote:
> I don't know what the exact version is but I am getting this:
> 
> FATAL:  database is not accepting commands to avoid wraparound data loss 
> in database "postgres"
> 
> How can I recover from this and why woud this happen to the postgres 
> database?  Has my server been hacked?  there should be no inserts or 
> anything going on.

Stop the postmaster, start a standalone backend, and issue a
database-wide VACUUM on database postgres.

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

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

Re: [GENERAL] PostgreSQL doesn't accept connections when Windows Server is rebooted

[Harald Armin Massa]

George,

I guess your service is not running after rebooting? You can check this via
services.msc

Most propable cause: the postgres user does not have logon_as_service
privilege.

I ran the installer logged in under an Administrator account, and set the

service account to postgres.



That is the recommended way. The installer will create an low-priv postgres
service account and run initdb as that user.


During the installation process, when it came time to run initdb,

the installer would stop with the error message:

 "Failed to run initdb: 128.  Please see the logfile in 'C:\Program
Files\PostgreSQL\8.1\tmp\initdb.log.
  Note!  You must read/copy this logfile before you click OK, or it
will be automatically removed."



Did you read that log? Read and copy it befor clicking OK, just as the
message stated?

BECAUSE: running THE INSTALLER as Administrator is recommended AND necessary
to create the postgres user and to give correct rights to this user.

SO: in running initdb.exe something else went wrong, it is NOT because you
ran the installer as Administrator.

(guessing  you installed with port 5432, and initdb tried to contact
5432, failing, because it is an 7.3 ?)


If the PostgreSQL service is then restarted manually (via Administrative

Tools > Component Services > Restart), the service runs as expected and
everything is normal.
How can I correct this?



One try to repair this: login as Administrator, open services.msc,  and
from  the postgreSQL service refill the login information. That does reset
the LOGON_AS_SERVICE privilege.

Harald


--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

Re: [GENERAL] fatal error on 8.1 server

[Tony Caduto]

Alvaro Herrera wrote:

Tony Caduto wrote:
  

I don't know what the exact version is but I am getting this:

FATAL:  database is not accepting commands to avoid wraparound data loss 
in database "postgres"


How can I recover from this and why woud this happen to the postgres 
database?  Has my server been hacked?  there should be no inserts or 
anything going on.



Stop the postmaster, start a standalone backend, and issue a
database-wide VACUUM on database postgres.

  
Yep, I saw that in the docs, now I am just having problems finding a 
standalone backend, not sure what that means.


--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Scott Ribe]

> In the real world,
> data changes, even supposedly unchangeable data.

And that's the crux of it. All of the "that design is wrong" arguments in
the world won't stop data that has been constant & unique for decades from
changing when some manager, rightly or wrongly, sees a "business need" for
it. User-provided data can never, ever be counted on to forever and always
have the attributes (uniqueness, stability, whatever) described by the
users, no matter how many times you are reassured on the subject. The data
will very often behave exactly as described, but to base a database design
on that assumption is to create a brittle design--as you described.


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



---(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 error on 8.1 server

[Joshua D. Drake]

On Mon, 2006-11-27 at 13:36 -0600, Tony Caduto wrote:
> Alvaro Herrera wrote:
> > Tony Caduto wrote:
> >   
> >> I don't know what the exact version is but I am getting this:
> >>
> >> FATAL:  database is not accepting commands to avoid wraparound data loss 
> >> in database "postgres"
> >>
> >> How can I recover from this and why woud this happen to the postgres 
> >> database?  Has my server been hacked?  there should be no inserts or 
> >> anything going on.
> >> 
> >
> > Stop the postmaster, start a standalone backend, and issue a
> > database-wide VACUUM on database postgres.
> >
> >   
> Yep, I saw that in the docs, now I am just having problems finding a 
> standalone backend, not sure what that means.

postgres.exe or possibly postmaster.exe I don't know how the windows
side does it.

Sincerely,

Joshua D. Drake


> 
-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

   http://archives.postgresql.org/

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/27/06 13:23, Harald Armin Massa wrote:
> John,
> 
> I'll weigh in my my .02 on this subject.  After much pain and agony in
>> the real world, I have taken the stance that every table in my database
>> must have an arbitrary, numeric primary key (generally autogenerated).
> 
> 
> I feel the same.
> 
> In the "real world" there is no such thing as a primary key. At least not
> over time. Not enough people understand the concept of a primary key to
> make those things existent in the real world.
> 
> So we take an artificially primary key - and most reliable way is to create
> it yourself.

There are lots of numbers.  Credit card numbers, account numbers
sales ticket numbers, etc, etc ad nauseum.  Julian day and Julian
date, even.  You can't have lived in "the west" in the past 30 years
 without being surrounded by them.

It's their blind use in *every* table which I take issue with, *most
especially* when they are the only unique key.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFaz8HS9HxQb37XmcRAjbWAKCT2yN84OiemgPnCH2uc0JNMsykxACeOrfI
vNKts9gWRqq7U3hIyEVNKY8=
=mGfl
-END PGP SIGNATURE-

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

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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Joshua D. Drake]

> There are lots of numbers.  Credit card numbers, account numbers
> sales ticket numbers, etc, etc ad nauseum.  Julian day and Julian
> date, even.  You can't have lived in "the west" in the past 30 years
>  without being surrounded by them.
> 
> It's their blind use in *every* table which I take issue with, *most
> especially* when they are the only unique key.

There is also *almost* always a way to have a natural and artificial
key.

Joshua D. Drake


> 
> - --
> Ron Johnson, Jr.
> Jefferson LA  USA
> 
> Is "common sense" really valid?
> For example, it is "common sense" to white-power racists that
> whites are superior to blacks, and that those with brown skins
> are mud people.
> However, that "common sense" is obviously wrong.
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.5 (GNU/Linux)
> 
> iD8DBQFFaz8HS9HxQb37XmcRAjbWAKCT2yN84OiemgPnCH2uc0JNMsykxACeOrfI
> vNKts9gWRqq7U3hIyEVNKY8=
> =mGfl
> -END PGP SIGNATURE-
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
> 
-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/27/06 13:37, Scott Ribe wrote:
>> In the real world,
>> data changes, even supposedly unchangeable data.
> 
> And that's the crux of it. All of the "that design is wrong" arguments in
> the world won't stop data that has been constant & unique for decades from
> changing when some manager, rightly or wrongly, sees a "business need" for
> it. User-provided data can never, ever be counted on to forever and always
> have the attributes (uniqueness, stability, whatever) described by the
> users, no matter how many times you are reassured on the subject. The data
> will very often behave exactly as described, but to base a database design
> on that assumption is to create a brittle design--as you described.

If I base a master sales table on account_number and date/time, then
every CPA in the country will descend on me with calculators
sharpened if I decide to update the SALE_DATE column.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFa0BiS9HxQb37XmcRAmQ2AJ9hYFVgXmW9ls5uvhrQkvxqvV7KWwCgoaOi
fa9fMXbjOK4ZDzsd3JH67xs=
=j0Rb
-END PGP SIGNATURE-

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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/27/06 12:53, John McCawley wrote:
> 
>> Yes, that's the point.  They are legacy designs, and that portion of
>> the design is wrong.
>>
>>  
>>
> I'll weigh in my my .02 on this subject.  After much pain and agony in
> the real world, I have taken the stance that every table in my database
> must have an arbitrary, numeric primary key (generally autogenerated). 
> I have found that this gets me into a lot of arguments with other
> database guys, but never seems to cause any problems for me. 
> Conversely, I have seen innumerable problems in the real world caused by
> the usage of actual data as primary keys.
> 
> Perhaps I am amazingly ignorant, but I have yet to find a case where my
> approach causes any real problems.  What does using "real" data as a
> primary key buy you?  The only real advantages I can see are that an
> individual record's data will be somewhat more human-readable without
> joining to other tables, and that your search queries can be simpler
> because they don't have to join against other tables.

Joshua Drake wrote an interesting blog article earlier this year
about what happened when a firm he was contracting for dropped the
natural key on an important table and retained only the synthetic
key.  Unfortunately I can't find it... :(


> On the (many) occasions that I have worked on databases with "real" data
> as primary keys, I just saw so many problems arise.  In the real world,
> data changes, even supposedly unchangeable data.  When using arbitrary
> primary keys, all you have to do is change the data in the one table
> where it lives.  If you are using real data as your keys, you have write
> complex queries or code to "fix" your data when the supposedly
> unchangeable data changes.
> 
> Anyway, I'm sure this is a huge argument, but that's my 0.2

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFa0FoS9HxQb37XmcRAt8ZAJ40c0KtB8zHifTymcsa/PBLwWkEuACfQ+dD
K+4NNU7m0RNTQLgeP9pNaqQ=
=SH10
-END PGP SIGNATURE-

---(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] IS it a good practice to use SERIAL as Primary Key?

[Brandon Aiken]

It's an arbitrary identifier that only has meaning within the context of
the database.  The domain model isn't supposed to model data in a
database.  It's supposed to model data which coincidentally is going to
be stored in a database.

As far as your bank's poor software design, I can't help you there.
That's simply poor planning. 

Look, I'm not denying the benefits of surrogate keys.  There are many
cases where it makes the most sense to use them.  My only point is that
it *does* violate the relational model.  The fact is that's nothing
special or new for a DBA.  The SQL standard itself violates the
relational model by allowing you to create tables without primary keys.

--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: David Morton [mailto:[EMAIL PROTECTED] 
Sent: Monday, November 27, 2006 2:30 PM
To: Brandon Aiken
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] IS it a good practice to use SERIAL as Primary
Key?

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


On Nov 27, 2006, at 1:21 PM, Brandon Aiken wrote:
> The other argument is that it's redundant data with no real meaning to
> the domain, meaning using surrogate keys technically violates low- 
> order
> normal forms.

It has real meaning in the sense that it is an internal identifier  
that doesn't change.   My bank set my online login to a stupid 5  
letters of my name plus last four digits of SSN, and they "can not  
change" it.   Most likely, it is the primary key used for as a  
foreign key to all the financial data.   Dumb, dumb, dumb.

If, OTOH, they would go with an internal id, it would be trivial to  
change the login id.

David Morton
Maia Mailguard http://www.maiamailguard.com
[EMAIL PROTECTED]



-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (Darwin)

iD8DBQFFazzQUy30ODPkzl0RAs/sAJ9rBTbXPNN/T4eQ9zjJFMAKFpfrPACdHcLj
pVtAZhjxk24vgRm/ScNfuyw=
=mLTC
-END PGP SIGNATURE-

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

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

[GENERAL] DB crashed

[rbaisak]

I have been using postgres since a long time. Recently number of users
in my portal has been increased drastically and because of that load on
Database server has been increased. Suddenly postgres Database crashed
and I need to restart the DB.  I am not able to find out root cause of
this problem. I need help to get any clue regarding what query caused DB
to crash. Any utility/program that can help me to debug the DB will be
well appreciated. 

 

- R

 

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[psql]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


On Nov 27, 2006, at 1:21 PM, Brandon Aiken wrote:

The other argument is that it's redundant data with no real meaning to
the domain, meaning using surrogate keys technically violates low- 
order

normal forms.


It has real meaning in the sense that it is an internal identifier  
that doesn't change.   My bank set my online login to a stupid 5  
letters of my name plus last four digits of SSN, and they "can not  
change" it.   Most likely, it is the primary key used for as a  
foreign key to all the financial data.   Dumb, dumb, dumb.


If, OTOH, they would go with an internal id, it would be trivial to  
change the login id.


David Morton
Maia Mailguard http://www.maiamailguard.com
[EMAIL PROTECTED]



-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (Darwin)

iD8DBQFFazzQUy30ODPkzl0RAs/sAJ9rBTbXPNN/T4eQ9zjJFMAKFpfrPACdHcLj
pVtAZhjxk24vgRm/ScNfuyw=
=mLTC
-END PGP SIGNATURE-

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

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

Re: [GENERAL] Solaris 10 problem

[Ray Stell]

One suggestion would be to not cross post to admin and general and answer
the question sent back to you on the admin list, but that's just me.



On Mon, Nov 27, 2006 at 04:59:22PM +0200, Doron Baranes wrote:
> 
> Hi,
> 
> I installed postgres 814 on solaris 10 using the following packages
> SUNWpostgr-libs-8.1.4-x86.tar.gz
> SUNWpostgr-8.1.4-x86.tar.gz
> SUNWpostgr-server-8.1.4-x86.tar.gz
> SUNWpostgr-server-data-8.1.4-x86.tar.gz
> SUNWpostgr-contrib-8.1.4-x86.tar.gz
> SUNWpostgr-devel-8.1.4-x86.tar.gz
> SUNWpostgr-docs-8.1.4-x86.tar.gz
> SUNWpostgr-jdbc-8.1.4-x86.tar.gz
> SUNWpostgr-tcl-8.1.4-x86.tar.gz
> SUNWpostgr-pl-8.1.4-x86.tar.gz
> 
> and no error occured.
> 
> when i try to run initdb i get Invalid Argument.
> 
> any ideas please,
> 
> 10x ahead
> DoronB
> 
> ---(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

-- 

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

Re: [GENERAL] DB crashed

[Thomas H.]

post some more informations, i.e. version, latest entries in logfile before 
crash, etc.

- thomas
  - Original Message - 
  From: rbaisak 
  To: pgsql-general@postgresql.org 
  Sent: Monday, November 27, 2006 8:26 PM
  Subject: [GENERAL] DB crashed


  I have been using postgres since a long time. Recently number of users in my 
portal has been increased drastically and because of that load on Database 
server has been increased. Suddenly postgres Database crashed and I need to 
restart the DB.  I am not able to find out root cause of this problem. I need 
help to get any clue regarding what query caused DB to crash. Any 
utility/program that can help me to debug the DB will be well appreciated. 



  - R

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Joshua D. Drake]

> > Perhaps I am amazingly ignorant, but I have yet to find a case where my
> > approach causes any real problems.  What does using "real" data as a
> > primary key buy you?  The only real advantages I can see are that an
> > individual record's data will be somewhat more human-readable without
> > joining to other tables, and that your search queries can be simpler
> > because they don't have to join against other tables.
> 
> Joshua Drake wrote an interesting blog article earlier this year
> about what happened when a firm he was contracting for dropped the
> natural key on an important table and retained only the synthetic
> key.  Unfortunately I can't find it... :(

I did? hmmm I just reviewed my blog entries and don't see one but I
can certainly point out billions of reasons why that is a bad idea.
Actually now that I think about it.. you are probably thinking of the
other illustrious Josh.. Berkus, he does have a blog discussing the
topic:

http://blogs.ittoolbox.com/database/soup/archives/primary-keyvil-part-i-7327


Here is a simple example:

id, first_name, last_name
=
1 | Joshua | Drake
2 | Joshua | Drake

Which one is the Joshua Drake you want?

But:

id, first_name, last_name, address_id
=
1 | Joshua | Drake | 1
2 | Joshua | Drake | 2


primary key (first_name,last_name,address_id)

One will enable you to have a distinct Joshua Drake, one in portland
oregon and one in portland maine.

The other, you will have more than one Joshua Drake and not know if you
are applying your sales to the correct one (without a whole bunch of
work).

Besides, I am sure that many in this community will agree that one
Joshua Drake is certainly enough.

Sincerely,

Joshua D. Drake



-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




---(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 error on 8.1 server

[Tony Caduto]

Alvaro Herrera wrote:

Stop the postmaster, start a standalone backend, and issue a
database-wide VACUUM on database postgres.

  
Just in case anyone is interested I did get it up and running with no 
damage to the system.
It took well over a year for it to reach the 1 million threshold mark.  
This is on a very very busy Postfix email server that does lookups 
against at table for things like relay hosts etc.
It took awhile for me to piece together everything I needed from the 
docs, I found the info about starting a standalone backend in the 
reindex docs.


--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



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

  http://archives.postgresql.org/

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Scott Ribe]

> ...*most especially* when they are the only unique key.

There are usually other keys which should be unique, and this should
certainly be reflected in the db design. On the other hand, designers should
not strive to find and enforce combinations that won't actually necessarily
be unique, such as the above-cited example of first 5 letters of last name +
last 4 of SSN. (There are certainly more than 10,000 Smiths in the US. In
fact: there will be more than 10,000 Smiths in each of most of the 50
states!)

> If I base a master sales table on account_number and date/time, then
> every CPA in the country will descend on me with calculators
> sharpened if I decide to update the SALE_DATE column.

But if the company is sold/merged, it is likely that accounts will get new
account numbers, and even possible that account numbers will not be unique
across the union of the (formerly) two companies' accounts thus absolutely
requiring account number changes. This is exactly the kind of thing I'm
talking about, and why I think account # + date/time would be a lousy
primary key. It's fine to treat it as a key, but certainly not the primary.

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



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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Scott Ribe]

> primary key (first_name,last_name,address_id)
> 
> One will enable you to have a distinct Joshua Drake, one in portland
> oregon and one in portland maine.

What happens when you move? Do we treat you as a different person? Or do we
pretend that you've always lived in the same place?

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



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

[GENERAL] Unexpected sort order.

[Ron Mayer]

Shouldn't the results of this query shown here been sorted by "b" rather than 
by "a"?

I would have thought since "order by b" is in the outer sql statement it would 
have
been the one the final result gets ordered by.

li=# select * from (select (random()*10)::int as a, (random()*10)::int as b 
from generate_series(1,10) order by a) as x order by b;
 a | b
---+
 0 |  8
 1 | 10
 3 |  4
 4 |  8
 5 |  1
 5 |  9
 6 |  4
 6 |  5
 8 |  4
 9 |  0
(10 rows)


Changing the constant from 10 to 11 in either but not both of the
places produces results I would have expected; as do many other ways of
rewriting the query.

Unless I'm missing something, it seems the way I wrote the query creates
some confusion of which of the two similar expressions with random()
it's sorting by.

---(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] IS it a good practice to use SERIAL as Primary Key?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/27/06 14:34, Scott Ribe wrote:
>> ...*most especially* when they are the only unique key.
> 
> There are usually other keys which should be unique, and this should
> certainly be reflected in the db design. On the other hand, designers should
> not strive to find and enforce combinations that won't actually necessarily
> be unique, such as the above-cited example of first 5 letters of last name +
> last 4 of SSN. (There are certainly more than 10,000 Smiths in the US. In
> fact: there will be more than 10,000 Smiths in each of most of the 50
> states!)

Just because your (bank?) creates a lame username, doesn't mean that
there shouldn't be one.

>> If I base a master sales table on account_number and date/time, then
>> every CPA in the country will descend on me with calculators
>> sharpened if I decide to update the SALE_DATE column.
> 
> But if the company is sold/merged, it is likely that accounts will get new
> account numbers, and even possible that account numbers will not be unique
> across the union of the (formerly) two companies' accounts thus absolutely
> requiring account number changes. This is exactly the kind of thing I'm
> talking about, and why I think account # + date/time would be a lousy
> primary key. It's fine to treat it as a key, but certainly not the primary.

OK, let's use a synthetic key on the sales master table.  In fact,
*both* companies have a synthetic key on their sales master tables.

OMG, conflicting/overlapping synthetic keys!

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFa1AUS9HxQb37XmcRAt0fAJsFPJfjMUEv+2E2XELq6Av6ZFZ98gCfXnkf
sJeeyjr3Bq2T9N5Sd0ca7SY=
=vedL
-END PGP SIGNATURE-

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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Joshua D. Drake]

On Mon, 2006-11-27 at 13:38 -0700, Scott Ribe wrote:
> > primary key (first_name,last_name,address_id)
> > 
> > One will enable you to have a distinct Joshua Drake, one in portland
> > oregon and one in portland maine.
> 
> What happens when you move? Do we treat you as a different person? Or do we
> pretend that you've always lived in the same place?

You would update the address, the address id wouldn't change. If you
want to keep track of old addresses you would keep an archive table
associated with the user.id.


Sincerely,

Joshua D. Drake

> 
-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Scott Ribe]

> You would update the address, the address id wouldn't change. If you
> want to keep track of old addresses you would keep an archive table
> associated with the user.id.

But what about historical data that referenced the address? If you move
today, I still want to know where I shipped last week's orders.


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



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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Scott Ribe]

> OK, let's use a synthetic key on the sales master table.  In fact,
> *both* companies have a synthetic key on their sales master tables.
> 
> OMG, conflicting/overlapping synthetic keys!

Which are not exposed in any UI and therefore easier to change?

Heck, I'd use UUIDs everywhere if it weren't such a pain in the neck to type
them into queries.

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



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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[John McCawley]

Right, but when you write your script (or whatever) that cleans these 
keys up and does the merge, you're where you started -- arbitrary 
integer keys with no meaning.  If you merge databases where the keys are 
*supposed* to have meaning, you then have to mangle *real* data to make 
them merge.



Ron Johnson wrote:


OK, let's use a synthetic key on the sales master table.  In fact,
*both* companies have a synthetic key on their sales master tables.

OMG, conflicting/overlapping synthetic keys!


 



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

  http://archives.postgresql.org/

[GENERAL] backend crash following load command

[Merlin Moncure]

We are getting a backend crash after issueing a load command.  It's
pretty easy to recreate -- so easy that I'm not sure that there is
something being overlooked.  This is on pg 8.2 roughly two weeks old.

Basic m.o. is:
1. create pic .so
2. load .so and call a function in it (from psql).
3. recompile .so with no changes to source
4. load again and crash.  note that merely touching the file and not
recompiling does not cause a crash...example stack trace:

Process 4808 attached - interrupt to quit
recv(8, "Q\0\0\0\35load \'/pgtest/pgfuncs\'\n;\0", 8192, 0) = 30
gettimeofday({1164652467, 63440}, NULL) = 0
write(2, "LOG:  statement: load \'/pgtest/p"..., 43) = 43
_llseek(3, 0, [16384], SEEK_CUR)= 0
close(3)= 0
[snip]
_llseek(32, 0, [122880], SEEK_CUR)  = 0
close(32)   = 0
_llseek(33, 0, [8192], SEEK_CUR)= 0
close(33)   = 0
_llseek(34, 0, [32768], SEEK_CUR)   = 0
close(34)   = 0
stat64("/pgtest/pgfuncs", 0xbfec3820)   = -1 ENOENT (No such file or
directory)
stat64("/pgtest/pgfuncs.so", {st_mode=S_IFREG|0755, st_size=4490, ...}) = 0
stat64("/pgtest/pgfuncs.so", {st_mode=S_IFREG|0755, st_size=4490, ...}) = 0
--- SIGSEGV (Segmentation fault) @ 0 (0) ---


detailed steps to create the problem follow:

1. Create the C function
// pgfuncs.c
#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(addone);

Datum addone(PG_FUNCTION_ARGS)
{
 PG_RETURN_INT32(PG_GETARG_INT32(0) + 1);
}
// end pgfuncs.c


2. compile it
PG_SERVER_INC=/usr/local/pgsql/include/server
gcc -fpic -shared -I $PG_SERVER_INC -o /pgtest/pgfuncs.so pgfuncs.c


3. create the addone func (using a fresh psql session)
CREATE OR REPLACE FUNCTION addone(INTEGER) RETURNS INTEGER
 AS '/pgtest/pgfuncs', 'addone' LANGUAGE C STRICT;


4. Execute addone, which will load pgfuncs.so
funcy=# select addone(1);
addone

 2
(1 row)


5. Try to reload the library (this works)
funcy=# LOAD '/pgtest/pgfuncs';
LOAD


6. Recomplie pgfuncs.so
Follow the same steps that are outlined in Step 2.


7. Issue a LOAD 'library' command
funcy=# LOAD '/pgtest/pgfuncs';
server closed the connection unexpectedly
   This probably means the server terminated abnormally
   before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

merlin

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

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

Re: [GENERAL] DB crashed

[Ranjan Kumar Baisak]

Thomas H. wrote:
post some more informations, i.e. version, latest entries in logfile 
before crash, etc.
 
- thomas


- Original Message -
*From:* rbaisak 
*To:* pgsql-general@postgresql.org

*Sent:* Monday, November 27, 2006 8:26 PM
*Subject:* [GENERAL] DB crashed

I have been using postgres since a long time. Recently number of
users in my portal has been increased drastically and because of
that load on Database server has been increased. Suddenly postgres
Database crashed and I need to restart the DB.  I am not able to
find out root cause of this problem. I need help to get any clue
regarding what query caused DB to crash. Any utility/program that
can help me to debug the DB will be well appreciated.

 


- R

 

I am using Postgres 7.4.3. Unfortunately I could not able to track log 
file as this was happened with live system.

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Scott Ribe]

> Right, but when you write your script (or whatever) that cleans these
> keys up and does the merge, you're where you started -- arbitrary
> integer keys with no meaning.  If you merge databases where the keys are
> *supposed* to have meaning, you then have to mangle *real* data to make
> them merge.

Also, there are plenty of circumstances besides the sale/merge one I
mentioned which would require account # changes but not require changes to
the synthetic keys.

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



---(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] IS it a good practice to use SERIAL as Primary Key?

[Joshua D. Drake]

On Mon, 2006-11-27 at 14:00 -0700, Scott Ribe wrote:
> > You would update the address, the address id wouldn't change. If you
> > want to keep track of old addresses you would keep an archive table
> > associated with the user.id.
> 
> But what about historical data that referenced the address? If you move
> today, I still want to know where I shipped last week's orders.

As I mentioned, you can use an archive table, or you could have a column
in the order table that references the sale_to or ship_to address id.

There are actually many different ways to do this. For example:

insert a new address, and update the users table to the new address_id.
The new address has a column called active(boolean). Thus all your sales
orders would reference an inactive, but valid address for that sale.

Sincerely,

Joshua D. Drake


> 
> 
-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




---(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] IS it a good practice to use SERIAL as Primary Key?

[Scott Ribe]

> insert a new address, and update the users table to the new address_id

Which changes the user's "primary key". My point was that having the address
id be part of the primary key is wrong. Having it be a part of a key may be
fine for many uses. But it's contrary to the notion of primary key that
something that not only can, but will, change for many records should be
part of the primary key. "Unique" and "primary" are *not* synonyms.

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



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

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

Re: [GENERAL] which version? old user coming back....

[Ron Mayer]

[EMAIL PROTECTED] wrote:
>>> ...Should I expect
>>> any problems with this even on the old 2.4 kernel?
>> 
>> I'd advise you to be using a 2.6 kernel at this point, too.
>>
> ... I assume 8 will still work on 2.4 though.

IIRC, you need a reasonably modern 2.6 kernel (early 2005)
if you want fsync() to flush the write caches on hard
disks (IDE and SATA and SCSI included).  The Linux SATA
driver author explains here:
 http://hardware.slashdot.org/comments.pl?sid=149349&cid=12519114

Anyone know if those write barrier patches were ever
backported into the 2.4 kernels?

If not, I think you need to either use 2.6 or disable
the disk's write caches and suffer the performance hits.

---(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 doesn't accept connections when Windows

[George Weaver]

- Original Message - 
From: Harald Armin Massa 

Hi Harald,

>I guess your service is not running after rebooting? You can check this via 
>services.msc

This is what is confusing me - the service IS running after rebooting, but does 
not appear to be listening on port 5435.  If I simply restart the service in 
the Administrative Tools > Component Services section everything is fine.

>>During the installation process, when it came time to run initdb, the 
>>installer would stop with the error message:

>> "Failed to run initdb: 128.  Please see the logfile in 'C:\Program 
>> Files\PostgreSQL\8.1\tmp\initdb.log. 
> > Note!  You must read/copy this logfile before you click OK, or it will 
> > be automatically removed."

> Did you read that log? Read and copy it befor clicking OK, just as the 
> message stated? 

When I tried to (before clicking OK), there was no initdb.log file - or any 
other file - in the 'C:\Program Files\PostgreSQL\8.1\tmp\ directory.

>One try to repair this: login as Administrator, open services.msc,  and from  
>the postgreSQL service refill the login information. That does reset the 
>LOGON_AS_SERVICE privilege. 

I'll give this a try and see what happens.

George

Re: [GENERAL] backend crash following load command

[Tom Lane]

"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> We are getting a backend crash after issueing a load command.

No crash from your example here (on Fedora Core 5).  What platform and
gcc are you using exactly?  Can you provide a stack trace from the crash?

regards, tom lane

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

Re: [GENERAL] Unexpected sort order.

[Jeff Davis]

On Mon, 2006-11-27 at 12:44 -0800, Ron Mayer wrote:
> Shouldn't the results of this query shown here been sorted by "b" rather than 
> by "a"?
> 
> I would have thought since "order by b" is in the outer sql statement it 
> would have
> been the one the final result gets ordered by.
> 
> li=# select * from (select (random()*10)::int as a, (random()*10)::int as b 
> from generate_series(1,10) order by a) as x order by b;
>  a | b
> ---+
>  0 |  8
>  1 | 10
>  3 |  4
>  4 |  8
>  5 |  1
>  5 |  9
>  6 |  4
>  6 |  5
>  8 |  4
>  9 |  0
> (10 rows)
> 
> 
> Changing the constant from 10 to 11 in either but not both of the
> places produces results I would have expected; as do many other ways of
> rewriting the query.
> 
> Unless I'm missing something, it seems the way I wrote the query creates
> some confusion of which of the two similar expressions with random()
> it's sorting by.

It looks like a planner bug.

Below are two plans; the first fails and the second succeeds. That leads
me to believe it's a planner bug, but what seems strangest to me is that
it does order by a, and not by some new evaluation of (random()*10).

=> explain select * from (select (random()*10)::int as a, (random
()*10)::int as b from generate_series(1,10) order by a) as x order by b;
  QUERY PLAN
--
 Sort  (cost=77.33..79.83 rows=1000 width=0)
   Sort Key: ((random() * 10::double precision))::integer
   ->  Function Scan on generate_series  (cost=0.00..27.50 rows=1000
width=0)
(3 rows)

Time: 0.584 ms

=> explain select * from (select (random()*10)::int as a, (random
()*11)::int as b from generate_series(1,10) order by a) as x order by b;
 QUERY PLAN

 Sort  (cost=139.66..142.16 rows=1000 width=8)
   Sort Key: x.b
   ->  Sort  (cost=77.33..79.83 rows=1000 width=0)
 Sort Key: ((random() * 10::double precision))::integer
 ->  Function Scan on generate_series  (cost=0.00..27.50
rows=1000 width=0)
(5 rows)

You can apparently get the correct behavior on almost any kind of
rewriting of the query, including the mere addition of a "DESC" onto the
end.

However, the query also fails if you nest it as another subselect, like
so:

=> select a,b from (select a,b from (select (random()*10)::int as a,
(random()*10)::int as b from generate_series(1,10) order by a) as x) as
y order by b;

Regards,
Jeff Davis


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

Re: [GENERAL] fatal error on 8.1 server

[Martijn van Oosterhout]

On Mon, Nov 27, 2006 at 02:22:19PM -0600, Tony Caduto wrote:
> Just in case anyone is interested I did get it up and running with no 
> damage to the system.
> It took well over a year for it to reach the 1 million threshold mark.  

You mean one *billion*, right?

That's one busy server!

Hopeefully you've updated your maintainence setup to avoid this in the
future?

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Unexpected sort order.

[Tom Lane]

Jeff Davis <[EMAIL PROTECTED]> writes:
> On Mon, 2006-11-27 at 12:44 -0800, Ron Mayer wrote:
>> Shouldn't the results of this query shown here been sorted by "b" rather 
>> than by "a"?

>> li=# select * from (select (random()*10)::int as a, (random()*10)::int as b 
>> from generate_series(1,10) order by a) as x order by b;

> It looks like a planner bug.

It looks to me like the planner thinks that order by a and order by b
are equivalent because the expressions are equal(); hence it discards
what it thinks is a redundant second sort step.

I suppose we could add a check for whether the sort expression contains
volatile functions before believing this, but I'm having a hard time
believing that there are any real-world cases where the check wouldn't
be a waste of cycles.  What's the use-case for sorting by a volatile
expression in the first place?

regards, tom lane

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

Re: [GENERAL] Unexpected sort order (suspected bug)

[Ron Mayer]

Jeff Davis wrote:
> On Mon, 2006-11-27 at 12:44 -0800, Ron Mayer wrote:
>> Shouldn't the results of this query shown here been sorted by "b" rather 
>> than by "a"?
>>
>> I would have thought since "order by b" is in the outer sql statement it 
>> would have
>> been the one the final result gets ordered by.
>>
>> li=# select * from (select (random()*10)::int as a, (random()*10)::int as b 
>> from generate_series(1,10) order by a) as x order by b;
>>  a | b
>> ---+
>>  0 |  8
>>  1 | 10
>>  3 |  4
>>  4 |  8
>>  5 |  1
>>  5 |  9
>>  6 |  4
>>  6 |  5
>>  8 |  4
>>  9 |  0
>> (10 rows)
>>...
> 
> It looks like a planner bug.
> 
> Below are two plans; the first fails and the second succeeds. That leads
> me to believe it's a planner bug, but what seems strangest to me is that
> it does order by a, and not by some new evaluation of (random()*10).
> 

Yeah, looks that way to me too.

So how would I report it.  Ccing the bugs list?  Guess it can't hurt.

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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Brandon Aiken]

Shenanigans!

That problem occurs regardless of whether or not you use surrogate keys.
You have exceeded the scope of the example.

--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Scott Ribe
Sent: Monday, November 27, 2006 4:01 PM
To: Joshua D. Drake; pgsql-general@postgresql.org
Subject: Re: [GENERAL] IS it a good practice to use SERIAL as Primary
Key?

> You would update the address, the address id wouldn't change. If you
> want to keep track of old addresses you would keep an archive table
> associated with the user.id.

But what about historical data that referenced the address? If you move
today, I still want to know where I shipped last week's orders.


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



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

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

Re: [GENERAL] fatal error on 8.1 server

[Tony Caduto]

Martijn van Oosterhout wrote:

On Mon, Nov 27, 2006 at 02:22:19PM -0600, Tony Caduto wrote:
  
Just in case anyone is interested I did get it up and running with no 
damage to the system.
It took well over a year for it to reach the 1 million threshold mark.  



You mean one *billion*, right?

That's one busy server!

Hopeefully you've updated your maintainence setup to avoid this in the
future?

Have a nice day,
  
The server stops accepting requests when it is 1 million transactions 
away from hitting 1 billion.

That's what I meant by the 1 million threshold :-)

I did not have autovacuum turned on and I usually do a vacuumdb -z -a -f 
-q  each night but this one slipped through the cracks :-(


Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



---(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] Unexpected sort order.

[Ron Mayer]

Tom Lane wrote:
> Jeff Davis <[EMAIL PROTECTED]> writes:
>> On Mon, 2006-11-27 at 12:44 -0800, Ron Mayer wrote:
>>> li=# select * from (select (random()*10)::int as a, (random()*10)::int as b 
>>> from generate_series(1,10) order by a) as x order by b;
> 
>> It looks like a planner bug.
> 
> It looks to me like the planner thinks that order by a and order by b
> are equivalent because the expressions are equal(); hence it discards
> what it thinks is a redundant second sort step.
> 
> I suppose we could add a check for whether the sort expression contains
> volatile functions before believing this, but I'm having a hard time
> believing that there are any real-world cases where the check wouldn't
> be a waste of cycles.

Would it be a smaller waste of cycles and still avoid the problem
if the planner blindly kept only the second sort step rather than
the first one when it sees these redundant steps?  Or would that
get other cases wrong?

> What's the use-case for sorting by a volatile
> expression in the first place?
> 

There was no use-case I had in mind when I reported it.
The order just surprised me so I thought I'd post it here.


If I wanted to make up a possible use case - hmm, perhaps random
sampling - but surely there would be better ways of doing that.
So nope, no real-world use cases I can make up - just a odd
result on a rather weirdly written query.   None of my real
applications would care if it's not fixed.

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

Re: [GENERAL] Unexpected sort order.

[Tom Lane]

I wrote:
> It looks to me like the planner thinks that order by a and order by b
> are equivalent because the expressions are equal(); hence it discards
> what it thinks is a redundant second sort step.
> ... What's the use-case for sorting by a volatile
> expression in the first place?

It may be worth pointing out that there are related gotchas without
bothering with anything as complicated as a sub-select.  Consider

select random() from foo order by 1;
select random() from foo order by random();

Are these the same, or not?  If you experiment you'll find out that
Postgres treats them the same --- random() is evaluated only once per
row of foo, and you get output that is sorted.  Arguably for the
second case there should be two evaluations of random() per row, and you
should get output that appears randomly ordered (because the sort key
and the output value will be uncorrelated).  If you do

select random() from foo order by random()+1;

then you do get two evaluations and random-looking output.

I'd be the first to admit that these various behaviors "just grew"
rather than being intentionally designed; no one has been thinking
about volatility in sort keys.  The question remains whether it is
worth expending development effort and planning cycles to have a more
consistent definition.  What's the use-case?

regards, tom lane

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

Re: [GENERAL] fatal error on 8.1 server

[Alvaro Herrera]

Tony Caduto wrote:
> Martijn van Oosterhout wrote:
> >On Mon, Nov 27, 2006 at 02:22:19PM -0600, Tony Caduto wrote:
> >  
> >>Just in case anyone is interested I did get it up and running with no 
> >>damage to the system.
> >>It took well over a year for it to reach the 1 million threshold mark.  
> >>
> >
> >You mean one *billion*, right?
> >
> >That's one busy server!
> >
> >Hopeefully you've updated your maintainence setup to avoid this in the
> >future?
> >
> >Have a nice day,
> >  
> The server stops accepting requests when it is 1 million transactions 
> away from hitting 1 billion.
> That's what I meant by the 1 million threshold :-)
> 
> I did not have autovacuum turned on and I usually do a vacuumdb -z -a -f 
> -q  each night but this one slipped through the cracks :-(

Strange -- autovacuum should have started an automatic database-wide
vacuum on that database, even if disabled.

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

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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Scott Ribe]

> Shenanigans!
> 
> That problem occurs regardless of whether or not you use surrogate keys.
> You have exceeded the scope of the example.

Yes the "problem occurs" in that this is something that needs to be tracked,
but the suggested schema presents peculiar problems for what otherwise is a
pretty simple thing.

Reasonable solution: every address is kept, so each address is a row in an
address table, with its own id. Shipment rows simply record the id of the
address current at the time of shipment. The user row simply records the id
of the most-recent address as the now-current one. That's rational.

But if you use the address id as part of the user's primary key, well now
you have a primary key that keeps changing as users move. On the other hand,
if you don't change the address key but the contents of the address row
itself, in order to preserve this bizarre notion of primary key, you have to
keep a copy somewhere of the same address with a different id in order to
use that for shipments.

>> You would update the address, the address id wouldn't change. If you
>> want to keep track of old addresses you would keep an archive table
>> associated with the user.id.
> 
> But what about historical data that referenced the address? If you move
> today, I still want to know where I shipped last week's orders.

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



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

   http://archives.postgresql.org/

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Joshua D. Drake]

On Mon, 2006-11-27 at 14:36 -0700, Scott Ribe wrote:
> > insert a new address, and update the users table to the new address_id
> 
> Which changes the user's "primary key". My point was that having the address
> id be part of the primary key is wrong.

As I said, you don't *have* to do it that way. I was just giving an
example. You could just as easily grab the address id, insert that into
an archive table with a date stamp and then just update the address
itself. Thus *not* changing the "Primary Key".

Joshua D. Drake


>  Having it be a part of a key may be
> fine for many uses. But it's contrary to the notion of primary key that
> something that not only can, but will, change for many records should be
> part of the primary key. "Unique" and "primary" are *not* synonyms.
> 
-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




---(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] Unexpected sort order.

[Tom Lane]

Ron Mayer <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> It looks to me like the planner thinks that order by a and order by b
>> are equivalent because the expressions are equal(); hence it discards
>> what it thinks is a redundant second sort step.

> Would it be a smaller waste of cycles and still avoid the problem
> if the planner blindly kept only the second sort step rather than
> the first one when it sees these redundant steps?  Or would that
> get other cases wrong?

I was fuzzing the explanation a bit --- there really isn't any place
that we could simply reverse the logic and get the other behavior.
The real issue is that the planner's "PathKey" representation of sort
ordering is actually incapable of distinguishing whether the sub-query
is sorted by a or by b: in either case the PathKeyItem will contain the
expression "(random()*10)::int".  So when the upper query tries to
decide whether the lower query is already sorted the way it wants,
it'll come out with a match.  We surely don't want to discard the
optimization of avoiding redundant sorts of subquery outputs, so the
only way to "fix" this would be a fundamental redesign of the PathKey
mechanism to special-case volatile expressions somehow.  I'm resistant
to doing that without a fairly solid use-case for sorting by volatile
expressions ...

regards, tom lane

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

Re: [GENERAL] fatal error on 8.1 server

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Tony Caduto wrote:
>> I did not have autovacuum turned on and I usually do a vacuumdb -z -a -f 
>> -q  each night but this one slipped through the cracks :-(

> Strange -- autovacuum should have started an automatic database-wide
> vacuum on that database, even if disabled.

We only added that in 8.2, no?  8.1 autovacuum would have forced the
vacuum to occur, but only if it was enabled in postgresql.conf.

regards, tom lane

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

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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Scott Ribe]

>>> insert a new address, and update the users table to the new address_id
>> 
>> Which changes the user's "primary key". My point was that having the address
>> id be part of the primary key is wrong.
> 
> As I said, you don't *have* to do it that way. I was just giving an
> example. You could just as easily grab the address id, insert that into
> an archive table with a date stamp and then just update the address
> itself. Thus *not* changing the "Primary Key".

Thus making it more difficult to deal with historical data, and also
reducing the "address id" in the "user" row to nothing more than an
additional auto-generated number referencing address data that might as well
just be put into the user row, because that would be no less normalized
anyway than this single address row whose contents keep changing to
represent different addresses over time.

Using the address id as part of the user primary key forces this choice
between a user primary key which changes, or funkiness in tracking
addresses. Whether you change the id stored in the user row, or whether you
update the address row, you are still constructing a primary key from data
that is expected to change.

Even the justification for it is thin. The address is certainly one good way
to figure out which of two users with the same name is being referred to,
but that doesn't make it part of a reasonable primary key. Phone numbers,
credit card used, order history--all of these could help discriminate
between users and not one of them has any place in the users' primary key.

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



---(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] IS it a good practice to use SERIAL as Primary Key?

[Joshua D. Drake]

On Mon, 2006-11-27 at 15:47 -0700, Scott Ribe wrote:
> >>> insert a new address, and update the users table to the new address_id
> >> 
> >> Which changes the user's "primary key". My point was that having the 
> >> address
> >> id be part of the primary key is wrong.
> > 
> > As I said, you don't *have* to do it that way. I was just giving an
> > example. You could just as easily grab the address id, insert that into
> > an archive table with a date stamp and then just update the address
> > itself. Thus *not* changing the "Primary Key".
> 
> Thus making it more difficult to deal with historical data, and also
> reducing the "address id" in the "user" row to nothing more than an
> additional auto-generated number referencing address data that might as well
> just be put into the user row, because that would be no less normalized
> anyway than this single address row whose contents keep changing to
> represent different addresses over time.

O.k., do you make it a point of over analyzing everything? I gave a very
simple example of how to not use an artificial key and why it could be
bad. I wasn't meant to be a golden parachute.

Of course there are problems with the example. It was a 10 second
example with zero business or data requirements qualified around it. 

Please... find something more productive to do.

Sincerely,

Joshua D. Drake


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

[GENERAL] CertFirst Legit?

[rnshah]

I've been tasked with administering one of our PostgreSQL databases and
know little or nothing about the product (though I do have DB
experience).  I've been looking for training and came across an
administration class at 'http://www.postgresql.org/about/event.425' on
the main web site.  When I called the certfirst people offering the
classes I got the impression that they weren't on the up and up.  When I
asked if the class in Newport Beach was sure to run they tried to talk
me into taking a class in their Illinois location, and when I asked
about a class in January in Vegas they told me it was already full but I
could take the December class in Illinois -- but they wouldn't tell me
where their classes in Vegas are held.

Can anyone provide me with a reference for these guys?  Also, they claim
to offer a certification but it is done online using some web site I've
never heard of.

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

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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[John McCawley]

This list represents the most informed database admins I know, and while 
the conversation can easily devolve into minutae, I am genuinely 
interested in everyone's opinions on this subject.  Primary keys are a 
pretty central aspect of database design, and most everyone on this list 
has unique experiences with the pros and cons of each approach...I'm 
pretty interested in the discussion.


Joshua D. Drake wrote:


Please... find something more productive to do.

Sincerely,

Joshua D. Drake


 



---(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] IS it a good practice to use SERIAL as Primary Key?

[Joshua D. Drake]

On Mon, 2006-11-27 at 17:09 -0600, John McCawley wrote:
> This list represents the most informed database admins I know, and while 
> the conversation can easily devolve into minutae, I am genuinely 
> interested in everyone's opinions on this subject.  Primary keys are a 
> pretty central aspect of database design, and most everyone on this list 
> has unique experiences with the pros and cons of each approach...I'm 
> pretty interested in the discussion.

Well sure, but the conversation was no longer productive. People were
getting stuck on what amounted to a very trivial example. There were
huge problems with that example and it in no way could represent an
legitimate use in production without a whole lot of caveats.

If we want to have a discussion about artificial versus natural keys,
rock on.. but the answer is simple:

A artificial does not protect against duplication.

That's it, in a nut shell. There is no argument there. That is why you
don't use artificial keys. That said... pretty much every table I create
will have an artificial key... because it makes managing data easy. An
example (to reuse the simple example):

users
=
id serial unique,
first_name text,
last_name text,
primary key (first_name,last_name)

Yes there are problems with the above, namely you will likely have more
than one joshua drake.

Sincerely,

Joshua D. Drake


> 
> Joshua D. Drake wrote:
> 
> >Please... find something more productive to do.
> >
> >Sincerely,
> >
> >Joshua D. Drake
> >
> >
> >  
> >
> 
> ---(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
> 
-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[John McCawley]

I promise I'm not trying to be a pain in the butt ;)  Do you then use 
your serial id as your foreign key in other tables, or the 
firstname/lastname primary key? 


Joshua D. Drake wrote:


users
=
id serial unique,
first_name text,
last_name text,
primary key (first_name,last_name)

Yes there are problems with the above, namely you will likely have more
than one joshua drake.

Sincerely,

Joshua D. Drake
 

   



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

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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Scott Ribe]

> A artificial does not protect against duplication.
> 
> That's it, in a nut shell. There is no argument there. That is why you
> don't use artificial keys.

Sure, but in many cases natural primary keys simply do not exist. (People
being the prime example.) Many examples of what are proposed as natural
primary keys are actually not--they are very often non-unique or prone to
change, or both. 

Relational theory is quite powerful, but its dogmatic application often
conflicts with the mess that is the real world. Given a choice between a
synthetic primary key and a sloppy approximation of a natural one (or even
an accurate-looking one handed to me by a clean-shaven guy in an expensive
suit), I'll take the synthetic one because my experience has been that over
the long term it will cause fewer problems by far.


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



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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Joshua D. Drake]

On Mon, 2006-11-27 at 17:31 -0600, John McCawley wrote:
> I promise I'm not trying to be a pain in the butt ;)  Do you then use 
> your serial id as your foreign key in other tables, or the 
> firstname/lastname primary key? 

Now that is a good question. I would use the id, but that is not
technically proper :). 

Joshua D. Drake


> 
> Joshua D. Drake wrote:
> 
> >users
> >=
> >id serial unique,
> >first_name text,
> >last_name text,
> >primary key (first_name,last_name)
> >
> >Yes there are problems with the above, namely you will likely have more
> >than one joshua drake.
> >
> >Sincerely,
> >
> >Joshua D. Drake
> >  
> >
> >>
> >>
> 
-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/27/06 17:25, Joshua D. Drake wrote:
> On Mon, 2006-11-27 at 17:09 -0600, John McCawley wrote:
[snip]
> users
> =
> id serial unique,
> first_name text,
> last_name text,
> primary key (first_name,last_name)
> 
> Yes there are problems with the above, namely you will likely have more
> than one joshua drake.

Right, and then the question gets to: how do you create a "good"
user id?  Many prefer serial types; I prefer something that is not a
monotonically incrementing scalar.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFa39KS9HxQb37XmcRAm+ZAKCAcpky09a2eTYMZX6HZiPIMECfcgCgrkXf
9oj3C45XcamX8avi5lYVn98=
=f0Ct
-END PGP SIGNATURE-

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

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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Scott Ribe]

>> I promise I'm not trying to be a pain in the butt ;)  Do you then use
>> your serial id as your foreign key in other tables, or the
>> firstname/lastname primary key?
> 
> Now that is a good question. I would use the id, but that is not
> technically proper :).

But firstname/lastname is *NOT* a primary key. Merely calling it one in the
db schema does not make it so.

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



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