Re: [GENERAL] Ubuntu for servers (was TurnKey PostgreSQL)

[Greg Smith]

On Mon, 8 Dec 2008, Scott Marlowe wrote:


Are you familiar with this bug:
https://bugs.launchpad.net/debian/+source/linux/+bug/245779

It's the reason my latest db servers are running Centos 5.2, sadly.
By the time I'd found the suggested workaround of setting a boot
option of NO_HZ=y I was already migrated off ubuntu for db servers.


Don't want to drag Liraz's thread completely off-topic, thus the new 
subject.


The response to that bug demonstrates one reason why I get a bit worked up 
when people suggest using Ubuntu for any serious server work.  Even when 
bugs get fixed, it's far too often only via installing a newer kernel, 
which puts you back to square one as far as testing goes.  Ubuntu puts 
minimal resources into back-porting kernel fixes into any earlier version, 
LTS or not, because they're consumed with constantly churning out new 
versions.  The usual cut-and-paste response appears in your thread same as 
it does in all the similar ones:


"The Ubuntu Kernel Team is planning to move to the 2.6.27 kernel for the 
upcoming Intrepid Ibex 8.10 release. As a result, the kernel team would 
appreciate it if you could please test this newer 2.6.27 Ubuntu kernel."


A good eye-opener if you don't believe who I'm characterizing things is 
take a look at the location your bug ended up being parked at (and may 
very well die at):


https://bugs.launchpad.net/~ubuntu-kernel-team/+assignedbugs

There you can gauge for yourself how concerned they are with fixing bugs 
in older versions.  You can't support yearly long-term support releases 
and aggresively back-port fixes without way more resources dumped onto the 
kernel team than Ubuntu has to apply.  Even RedHat, who has a lot more 
kernel engineers, doesn't even try.  That's part of the reason why it took 
more than two years between RHEL4 and 5.  They were busy that whole time 
backporting kernel fixes into the stable kernel, with major update drops 
to it every six months, rather than just plowing ahead only worrying about 
the newer ones.


I love Ubuntu on the desktop, but you combine its aggresive releases and 
limited kernel fix backporting with how much general kernel testing 
quality keeps going down and you get a grim combination.  I've realized 
this is just an unavoidable consequence of how much change the Linux 
kernel is going under every single day.  Nobody seem to care anymore about 
focusing on any individual kernel version long enough to squash its bugs 
right anymore; those will all get fixed in the next version, right?


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

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

Re: [GENERAL] Problems With Bad PID and Missing Socket -- FIXED

[Rich Shepard]

On Tue, 9 Dec 2008, Klint Gore wrote:


How did you fix it?  (so its filed in the archive)


klint,

  I removed the 'stale' .pid file, then manually started the postmaster (su
postgres -c 'postgres -D /var/lib/pgsql/data &') rather than using the
Slackware startup script (/etc/rc.d/rc.postgresql). That command line is in
the 'start' section of the script so I have no idea why it didn't work that
way. Sigh.

  Still no access to localhost/sql-ledger/login.pl and that's critical for
me.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [GENERAL] Problems With Bad PID and Missing Socket -- FIXED

[Klint Gore]

Rich Shepard wrote:

On Mon, 8 Dec 2008, Rich Shepard wrote:

>  I had this happen several years ago, but do not recall what fixed the bad
> PID file and socket. The thread had been saved here, but I must have
> inadvertenly deleted it.

   Figured out how to fix the problem, but still cannot get SQL-Ledger to
load. Time to ask on that mail list.
  


How did you fix it?  (so its filed in the archive)

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  
Fax: 02 6773 3266

EMail: [EMAIL PROTECTED]


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

Re: [GENERAL] Problems With Bad PID and Missing Socket -- FIXED

[Rich Shepard]

On Mon, 8 Dec 2008, Rich Shepard wrote:


 I had this happen several years ago, but do not recall what fixed the bad
PID file and socket. The thread had been saved here, but I must have
inadvertenly deleted it.


  Figured out how to fix the problem, but still cannot get SQL-Ledger to
load. Time to ask on that mail list.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

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

[GENERAL] Please Help

[Robert Sherry]

I am trying to get the following program to compile the following program
with Embedded SQL Processor:

 

#include 

 

main()

{

printf( "I am alive\n" );

#if 0

EXEC SQL CONNECT TO DEFAULT;

#endif

printf( "status is %d\n", sqlca.sqlcode );

}

 

 

I am doing this as a test that Postgres is properly installed on my system.
Here is how I compile the file (prog1.pgc):

 

INCDIR="/cygdrive/c/Program Files/PostgreSQL/8.3/include"

LIBDIR="/cygdrive/c/Program Files/PostgreSQL/8.3/lib"

ecpg prog1.pgc

cc -g -I "/cygdrive/c/Program Files/PostgreSQL/8.3/include" prog1.c

cc -g -o prog1 prog1.o -L "/cygdrive/c/Program Files/PostgreSQL/8.3/lib"
-lecpg

 

The result is that a get an undeclared identifier of: _ECPGget_sqlca from
the linker. I am hoping that somebody can tell me what I am doing

wrong.

 

Thanks

 

Bob Sherry

 

Re: [GENERAL] Want quit milis

[Klint Gore]

Tonny Sapri wrote:
Iwant quit this milis. could you help me? I forget my milis password. 



  


go to this web site http://www.postgresql.org/mailpref/pgsql-general
click "Sign In" at the top left.
find the section that says "Lost Password".
put your email address in there and click the "Mail My Password" button.
check your email for your list password.

go back to the web site and login properly
tick the check box next to pgsql-general
click the unsubscribe button


HTH,

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  
Fax: 02 6773 3266

EMail: [EMAIL PROTECTED]


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

[GENERAL] Want quit milis

[Tonny Sapri]

Iwant quit this milis. could you help me? I forget my milis password. 





  Selalu bisa chat di profil jaringan, blog, atau situs web pribadi! Yahoo! 
memungkinkan Anda selalu bisa chat melalui Pingbox. Coba! 
http://id.messenger.yahoo.com/pingbox/

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

Re: [GENERAL] is there any error for my postgresql installation?

[中和刘]

On Mon, Dec 8, 2008 at 8:16 PM, Richard Huxton <[EMAIL PROTECTED]> wrote:
> 中和刘 wrote:
>> I have just installed postgresql 8.3 on my debian sid, and have set
>> the password of both system user postgres and database user to the
>> same password, but when i connect to it using pgadmin3(from the local
>> machine), i got the error:
>
>> here is the log messages
>> ---
>> 2008-12-06 12:33:08 HKT LOG:  could not load root certificate file
>> "root.crt": no SSL error reported (1)
>> 2008-12-06 12:33:08 HKT DETAIL:  Will not verify client certificates. (2)
>> 2008-12-06 12:33:08 HKT LOG:  could not create IPv6 socket: Address
>> family not supported by protocol (3)
>> 2008-12-06 12:33:09 HKT LOG:  database system was shut down at
>> 2008-12-06 12:32:18 HKT
>> 2008-12-06 12:33:09 HKT LOG:  autovacuum launcher started
>> 2008-12-06 12:33:09 HKT LOG:  database system is ready to accept connections
>> 2008-12-06 12:33:09 HKT LOG:  incomplete startup packet (4)
>> --
>> is (1) a error? what should i do?
>> what does (2) mean? is it normal?
>> is (3) ok?
>
> 1,2,3 aren't anything too serious - just saying that you've not set up
> ssl correctly and IPv6 networking isn't going to work. You're not using
> them, so it doesn't matter.
>
>> is (4) a error? what should i do?
>
> I'm not sure how you're getting #4 - it seems to be happening as soon as
> the system starts up.
>
> Three things to do:
>
> 1. Make sure log_connections is turned on in your postgresql.conf
>
> 2. Make sure your connection settings are correct in pgadmin
>
> 3. Try connecting from the command-line client psql
thank you! i finally found that it's because i did not setup the
postgres role's password
> --
>  Richard Huxton
>  Archonet Ltd
>



-- 
I'm a web developer using
debian+mono(C#)+postgresql+xhtml+javascript+xsl+xml+css
my home is http://www.starliu.com

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

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

[Adam Rich]

> >
> > When we get windowing functions, a lot of this pain will go away :)
> >
> 
> Yes! Hope it won't be too long now. The patch seems to behave like it
> should
> now :)
> Hopefully we'll see it commited for 8.4.
> 
> Though this does not look too much cleaner at least it's standard SQL:
> 
> A preview for Madi:
> 
> SELECT foo,bar
> FROM (SELECT foo,bar,
>  ROW_NUMBER() OVER (PARTITION BY foo ORDER BY bar) AS pos
>   FROM table
> ) AS t
> WHERE pos = 1
> ORDER BY bar;
> 
> Probably easier to understand what's going on in this one.
> 
> David.
> 

Is Oracle's FIRST_VALUE function not a SQL standard?   The way I would 
do this in Oracle looks like:

SELECT foo, FIRST_VALUE(bar) OVER (PARTITION BY foo ORDER BY bar) as bar
FROM table

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions059.
htm








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

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

[David Rowley]

Madison Kelly Wrote:
> David Fetter wrote:
> > On Mon, Dec 08, 2008 at 11:16:29PM -, David Rowley wrote:
> >>> -Original Message-
> >>> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> >>> [EMAIL PROTECTED] On Behalf Of Madison Kelly
> >>> Sent: 08 December 2008 22:19
> >>> To: pgsql-general@postgresql.org
> >>> Subject: [GENERAL] SELECT DISTINCT ... ORDER BY problem
> >>>
> >>> Hi all,
> >>>
> >>>I've got a table that I am trying to SELECT DISTINCT on one column
> >>> and ORDER BY on a second column, but am getting the error:
> >>>
> >>> SELECT DISTINCT ON expressions must match initial ORDER BY expressions
> >>>
> >>>I can't add the second column to the DISTINCT clause because every
> >>> row is unique. Likewise, I can't add the first column to my ORDER BY
> as
> >>> it'd not sort the way I need it to.
> >>>
> >>>Here is a simplified version of my query:
> >>>
> >>> \d table
> >>>  Table "table"
> >>>   Column  |  Type   |   Modifiers
> >>>
> >>> -+-+--
> 
> >>> --
> >>>   tbl_id  | integer | not null default
> >>> nextval('tbl_seq'::regclass)
> >>>   foo | text|
> >>>   bar | text|
> >>>
> >>> SELECT DISTINCT ON (foo) foo, bar FROM table WHERE bar < '2008-12-07
> >>> 16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1;
> >>>
> >> To make the query valid you would have to ORDER BY foo,bar
> >> DISTINCT ON in this case is only going to show the first bar value for
> each
> >> foo.
> >>
> >> Is tbl_id not your PK and only giving 1 row anyway?
> >>
> >>>I understand from:
> >>>
> >>> http://archives.postgresql.org/pgsql-sql/2007-02/msg00169.php
> >>>
> >>>That this is not really possible because the any given 'foo' column
> >>> could match multiple 'bar' columns, so what do you search by? However,
> >>> it's made some sort of decision as a value is shown in 'bar' for each
> >>> 'foo'.
> >>>
> >>>So my question is two-fold:
> >>>
> >>> 1. Can I not say, somehow, "sort all results by 'bar', and return the
> >>> first/last 'bar' for each distinct 'foo'?
> >>>
> >>> 2. Can I somehow say "Order the results using the value of 'bar' you
> >>> return, regardless of where it came from"?
> >> You can nest queries:
> >>
> >> SELECT foo,bar
> >> FROM (SELECT DISTINCT ON (foo) foo,
> >>Bar
> >>   FROM table
> >>   WHERE bar < '2008-12-07 16:32:46'
> >> AND tbl_id=153 ORDER BY foo,bar
> >> ) AS t ORDER BY bar;
> >>
> >> Notice that I'm only applying the final order by in the outer query.
> >
> > When we get windowing functions, a lot of this pain will go away :)
> >
> > Cheers,
> > David.
> 
> Oh?
> 
>I can't say I've been keeping up with what is in the pipes. What is
> windowing?
> 

These are also known as analytical functions in some other database systems,
though by the standard they are known as window functions.

http://en.wikipedia.org/wiki/Select_(SQL)

It's worth a read. Hopefully we'll see this in 8.4.

David.


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

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

[David Rowley]

> -Original Message-
> From: David Fetter [mailto:[EMAIL PROTECTED]
> Sent: 09 December 2008 00:55
> To: David Rowley
> Cc: 'Madison Kelly'; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem
> 
> On Mon, Dec 08, 2008 at 11:16:29PM -, David Rowley wrote:
> > > -Original Message-
> > > From: [EMAIL PROTECTED] [mailto:pgsql-general-
> > > [EMAIL PROTECTED] On Behalf Of Madison Kelly
> > > Sent: 08 December 2008 22:19
> > > To: pgsql-general@postgresql.org
> > > Subject: [GENERAL] SELECT DISTINCT ... ORDER BY problem
> > >
> > > Hi all,
> > >
> > >I've got a table that I am trying to SELECT DISTINCT on one column
> > > and ORDER BY on a second column, but am getting the error:
> > >
> > > SELECT DISTINCT ON expressions must match initial ORDER BY expressions
> > >
> > >I can't add the second column to the DISTINCT clause because every
> > > row is unique. Likewise, I can't add the first column to my ORDER BY
> as
> > > it'd not sort the way I need it to.
> > >
> > >Here is a simplified version of my query:
> > >
> > > \d table
> > >  Table "table"
> > >   Column  |  Type   |   Modifiers
> > >
> > > -+-+--
> 
> > > --
> > >   tbl_id  | integer | not null default
> > > nextval('tbl_seq'::regclass)
> > >   foo | text|
> > >   bar | text|
> > >
> > > SELECT DISTINCT ON (foo) foo, bar FROM table WHERE bar < '2008-12-07
> > > 16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1;
> > >
> >
> > To make the query valid you would have to ORDER BY foo,bar
> > DISTINCT ON in this case is only going to show the first bar value for
> each
> > foo.
> >
> > Is tbl_id not your PK and only giving 1 row anyway?
> >
> > >
> > >I understand from:
> > >
> > > http://archives.postgresql.org/pgsql-sql/2007-02/msg00169.php
> > >
> > >That this is not really possible because the any given 'foo' column
> > > could match multiple 'bar' columns, so what do you search by? However,
> > > it's made some sort of decision as a value is shown in 'bar' for each
> > > 'foo'.
> > >
> > >So my question is two-fold:
> > >
> > > 1. Can I not say, somehow, "sort all results by 'bar', and return the
> > > first/last 'bar' for each distinct 'foo'?
> > >
> > > 2. Can I somehow say "Order the results using the value of 'bar' you
> > > return, regardless of where it came from"?
> >
> > You can nest queries:
> >
> > SELECT foo,bar
> > FROM (SELECT DISTINCT ON (foo) foo,
> >Bar
> >   FROM table
> >   WHERE bar < '2008-12-07 16:32:46'
> > AND tbl_id=153 ORDER BY foo,bar
> > ) AS t ORDER BY bar;
> >
> > Notice that I'm only applying the final order by in the outer query.
> 
> When we get windowing functions, a lot of this pain will go away :)
> 

Yes! Hope it won't be too long now. The patch seems to behave like it should
now :)
Hopefully we'll see it commited for 8.4.

Though this does not look too much cleaner at least it's standard SQL:
 
A preview for Madi:

SELECT foo,bar
FROM (SELECT foo,bar,
 ROW_NUMBER() OVER (PARTITION BY foo ORDER BY bar) AS pos
  FROM table
) AS t
WHERE pos = 1
ORDER BY bar;

Probably easier to understand what's going on in this one.

David.


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

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

[Madison Kelly]

David Fetter wrote:

On Mon, Dec 08, 2008 at 11:16:29PM -, David Rowley wrote:

-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Madison Kelly
Sent: 08 December 2008 22:19
To: pgsql-general@postgresql.org
Subject: [GENERAL] SELECT DISTINCT ... ORDER BY problem

Hi all,

   I've got a table that I am trying to SELECT DISTINCT on one column
and ORDER BY on a second column, but am getting the error:

SELECT DISTINCT ON expressions must match initial ORDER BY expressions

   I can't add the second column to the DISTINCT clause because every
row is unique. Likewise, I can't add the first column to my ORDER BY as
it'd not sort the way I need it to.

   Here is a simplified version of my query:

\d table
 Table "table"
  Column  |  Type   |   Modifiers

-+-+--
--
  tbl_id  | integer | not null default
nextval('tbl_seq'::regclass)
  foo | text|
  bar | text|

SELECT DISTINCT ON (foo) foo, bar FROM table WHERE bar < '2008-12-07
16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1;


To make the query valid you would have to ORDER BY foo,bar
DISTINCT ON in this case is only going to show the first bar value for each
foo.

Is tbl_id not your PK and only giving 1 row anyway?


   I understand from:

http://archives.postgresql.org/pgsql-sql/2007-02/msg00169.php

   That this is not really possible because the any given 'foo' column
could match multiple 'bar' columns, so what do you search by? However,
it's made some sort of decision as a value is shown in 'bar' for each
'foo'.

   So my question is two-fold:

1. Can I not say, somehow, "sort all results by 'bar', and return the
first/last 'bar' for each distinct 'foo'?

2. Can I somehow say "Order the results using the value of 'bar' you
return, regardless of where it came from"?

You can nest queries:

SELECT foo,bar
FROM (SELECT DISTINCT ON (foo) foo,
   Bar
  FROM table
  WHERE bar < '2008-12-07 16:32:46'
AND tbl_id=153 ORDER BY foo,bar
) AS t ORDER BY bar;

Notice that I'm only applying the final order by in the outer query.


When we get windowing functions, a lot of this pain will go away :)

Cheers,
David.


Oh?

  I can't say I've been keeping up with what is in the pipes. What is 
windowing?


Madi

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

Re: [GENERAL] Problems With Bad PID and Missing Socket -- UPDATE

[Rich Shepard]

On Mon, 8 Dec 2008, Alvaro Herrera wrote:


Wow.  Is this Gentoo by chance?


Alvaro,

  Nope. It's Slackware-12.1, and not being at all gentle.

  I had this happen several years ago, but do not recall what fixed the bad
PID file and socket. The thread had been saved here, but I must have
inadvertenly deleted it.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

[David Fetter]

On Mon, Dec 08, 2008 at 11:16:29PM -, David Rowley wrote:
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:pgsql-general-
> > [EMAIL PROTECTED] On Behalf Of Madison Kelly
> > Sent: 08 December 2008 22:19
> > To: pgsql-general@postgresql.org
> > Subject: [GENERAL] SELECT DISTINCT ... ORDER BY problem
> > 
> > Hi all,
> > 
> >I've got a table that I am trying to SELECT DISTINCT on one column
> > and ORDER BY on a second column, but am getting the error:
> > 
> > SELECT DISTINCT ON expressions must match initial ORDER BY expressions
> > 
> >I can't add the second column to the DISTINCT clause because every
> > row is unique. Likewise, I can't add the first column to my ORDER BY as
> > it'd not sort the way I need it to.
> > 
> >Here is a simplified version of my query:
> > 
> > \d table
> >  Table "table"
> >   Column  |  Type   |   Modifiers
> > 
> > -+-+--
> > --
> >   tbl_id  | integer | not null default
> > nextval('tbl_seq'::regclass)
> >   foo | text|
> >   bar | text|
> > 
> > SELECT DISTINCT ON (foo) foo, bar FROM table WHERE bar < '2008-12-07
> > 16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1;
> > 
> 
> To make the query valid you would have to ORDER BY foo,bar
> DISTINCT ON in this case is only going to show the first bar value for each
> foo.
> 
> Is tbl_id not your PK and only giving 1 row anyway?
> 
> > 
> >I understand from:
> > 
> > http://archives.postgresql.org/pgsql-sql/2007-02/msg00169.php
> > 
> >That this is not really possible because the any given 'foo' column
> > could match multiple 'bar' columns, so what do you search by? However,
> > it's made some sort of decision as a value is shown in 'bar' for each
> > 'foo'.
> > 
> >So my question is two-fold:
> > 
> > 1. Can I not say, somehow, "sort all results by 'bar', and return the
> > first/last 'bar' for each distinct 'foo'?
> > 
> > 2. Can I somehow say "Order the results using the value of 'bar' you
> > return, regardless of where it came from"?
> 
> You can nest queries:
> 
> SELECT foo,bar
> FROM (SELECT DISTINCT ON (foo) foo,
>Bar
>   FROM table
>   WHERE bar < '2008-12-07 16:32:46'
> AND tbl_id=153 ORDER BY foo,bar
> ) AS t ORDER BY bar;
> 
> Notice that I'm only applying the final order by in the outer query.

When we get windowing functions, a lot of this pain will go away :)

Cheers,
David.
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

[Madison Kelly]

David Rowley wrote:

-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Madison Kelly
Sent: 08 December 2008 22:19
To: pgsql-general@postgresql.org
Subject: [GENERAL] SELECT DISTINCT ... ORDER BY problem

Hi all,

   I've got a table that I am trying to SELECT DISTINCT on one column
and ORDER BY on a second column, but am getting the error:

SELECT DISTINCT ON expressions must match initial ORDER BY expressions

   I can't add the second column to the DISTINCT clause because every
row is unique. Likewise, I can't add the first column to my ORDER BY as
it'd not sort the way I need it to.

   Here is a simplified version of my query:

\d table
 Table "table"
  Column  |  Type   |   Modifiers

-+-+--
--
  tbl_id  | integer | not null default
nextval('tbl_seq'::regclass)
  foo | text|
  bar | text|

SELECT DISTINCT ON (foo) foo, bar FROM table WHERE bar < '2008-12-07
16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1;



To make the query valid you would have to ORDER BY foo,bar
DISTINCT ON in this case is only going to show the first bar value for each
foo.

Is tbl_id not your PK and only giving 1 row anyway?


   I understand from:

http://archives.postgresql.org/pgsql-sql/2007-02/msg00169.php

   That this is not really possible because the any given 'foo' column
could match multiple 'bar' columns, so what do you search by? However,
it's made some sort of decision as a value is shown in 'bar' for each
'foo'.

   So my question is two-fold:

1. Can I not say, somehow, "sort all results by 'bar', and return the
first/last 'bar' for each distinct 'foo'?

2. Can I somehow say "Order the results using the value of 'bar' you
return, regardless of where it came from"?


You can nest queries:

SELECT foo,bar
FROM (SELECT DISTINCT ON (foo) foo,
   Bar
  FROM table
  WHERE bar < '2008-12-07 16:32:46'
AND tbl_id=153 ORDER BY foo,bar
) AS t ORDER BY bar;

Notice that I'm only applying the final order by in the outer query.

David.


haha, darn...

  I've even done embedded SELECTs before, I should have thought of 
that!  Thanks!


Madi

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

Re: [GENERAL] Problems With Bad PID and Missing Socket -- UPDATE

[Alvaro Herrera]

Rich Shepard wrote:
> On Mon, 8 Dec 2008, Rich Shepard wrote:
>
>>  I upgraded the distribution on my system and am now having problems
>> opening a local application. /var/log/apache/error.log shows:
>
>   More insight. When I stop the postmaster I see this:
>
> [EMAIL PROTECTED] ~]# /etc/rc.d/rc.postgresql stop Shutting down PostgreSQL...
> pg_ctl: invalid data in PID file "/var/lib/pgsql/data/postmaster.pid"

Wow.  Is this Gentoo by chance?

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

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

Re: [GENERAL] SELECT DISTINCT ... ORDER BY problem

[David Rowley]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Madison Kelly
> Sent: 08 December 2008 22:19
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] SELECT DISTINCT ... ORDER BY problem
> 
> Hi all,
> 
>I've got a table that I am trying to SELECT DISTINCT on one column
> and ORDER BY on a second column, but am getting the error:
> 
> SELECT DISTINCT ON expressions must match initial ORDER BY expressions
> 
>I can't add the second column to the DISTINCT clause because every
> row is unique. Likewise, I can't add the first column to my ORDER BY as
> it'd not sort the way I need it to.
> 
>Here is a simplified version of my query:
> 
> \d table
>  Table "table"
>   Column  |  Type   |   Modifiers
> 
> -+-+--
> --
>   tbl_id  | integer | not null default
> nextval('tbl_seq'::regclass)
>   foo | text|
>   bar | text|
> 
> SELECT DISTINCT ON (foo) foo, bar FROM table WHERE bar < '2008-12-07
> 16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1;
> 

To make the query valid you would have to ORDER BY foo,bar
DISTINCT ON in this case is only going to show the first bar value for each
foo.

Is tbl_id not your PK and only giving 1 row anyway?

> 
>I understand from:
> 
> http://archives.postgresql.org/pgsql-sql/2007-02/msg00169.php
> 
>That this is not really possible because the any given 'foo' column
> could match multiple 'bar' columns, so what do you search by? However,
> it's made some sort of decision as a value is shown in 'bar' for each
> 'foo'.
> 
>So my question is two-fold:
> 
> 1. Can I not say, somehow, "sort all results by 'bar', and return the
> first/last 'bar' for each distinct 'foo'?
> 
> 2. Can I somehow say "Order the results using the value of 'bar' you
> return, regardless of where it came from"?

You can nest queries:

SELECT foo,bar
FROM (SELECT DISTINCT ON (foo) foo,
   Bar
  FROM table
  WHERE bar < '2008-12-07 16:32:46'
AND tbl_id=153 ORDER BY foo,bar
) AS t ORDER BY bar;

Notice that I'm only applying the final order by in the outer query.

David.



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

[GENERAL] SELECT DISTINCT ... ORDER BY problem

[Madison Kelly]

Hi all,

  I've got a table that I am trying to SELECT DISTINCT on one column 
and ORDER BY on a second column, but am getting the error:


SELECT DISTINCT ON expressions must match initial ORDER BY expressions

  I can't add the second column to the DISTINCT clause because every 
row is unique. Likewise, I can't add the first column to my ORDER BY as 
it'd not sort the way I need it to.


  Here is a simplified version of my query:

\d table
Table "table"
 Column  |  Type   |   Modifiers 


-+-+
 tbl_id  | integer | not null default nextval('tbl_seq'::regclass)
 foo | text|
 bar | text|

SELECT DISTINCT ON (foo) foo, bar FROM table WHERE bar < '2008-12-07 
16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1;



  I understand from:

http://archives.postgresql.org/pgsql-sql/2007-02/msg00169.php

  That this is not really possible because the any given 'foo' column 
could match multiple 'bar' columns, so what do you search by? However, 
it's made some sort of decision as a value is shown in 'bar' for each 'foo'.


  So my question is two-fold:

1. Can I not say, somehow, "sort all results by 'bar', and return the 
first/last 'bar' for each distinct 'foo'?


2. Can I somehow say "Order the results using the value of 'bar' you 
return, regardless of where it came from"?


Thanks all!

Madi

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

[GENERAL] Problem Related to storing the field value in a String

[aravind chandu]

Hello Guys,

The following is my sample program

 result R(T.exec("select * from emp"));
string L;
 stringstream S;
 vector v;
 int z,i;
 z = R.size();

for (i = 0;i> L;
 v.push_back(L);
 }

 There is a table emp with single column,while I am working on this module I 
encountered a problem,I can able to get the first
 row of the table only,I am not able to get the other row values.

 Lets say column values are abc,def,ghi.jkl etc There is no problem with 
R[i][0] its getting the correct values,
 but the problem is while reading field values into 'S' I am able to get only 
the table's field value of first row only and the rest as zero's.
 I don't know what's the problem.Can you guys please help me to solve this 
problem.

 If there is another way to store field value into a string please let me know.

 Thanks,
 Avin.



  

Re: [GENERAL] Problems With Bad PID and Missing Socket -- UPDATE

[Rich Shepard]

On Mon, 8 Dec 2008, Rich Shepard wrote:


 I upgraded the distribution on my system and am now having problems
opening a local application. /var/log/apache/error.log shows:


  More insight. When I stop the postmaster I see this:

[EMAIL PROTECTED] ~]# /etc/rc.d/rc.postgresql stop 
Shutting down PostgreSQL...

pg_ctl: invalid data in PID file "/var/lib/pgsql/data/postmaster.pid"

  It appears that I have several things to correct. This time, I promise to
hold on to the messages so I can fix things up by myself the next time they
occur.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

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

[GENERAL] Problems With Bad PID and Missing Socket

[Rich Shepard]

  I upgraded the distribution on my system and am now having problems
opening a local application. /var/log/apache/error.log shows:

Error: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
Compilation failed in require at /usr/local/sql-ledger/login.pl line 85.
[Sat Nov 29 11:50:17 2008] [notice] caught SIGTERM, shutting down

  While I thought I had saved the solution message from the last time this
happened, I did not do so. After stopping and restarting postgresql, there
is no domain socket .s.PGSQL.5432 in /tmp. How do I get it back?

  Perhaps related to this, when I restart postgres I see:

Starting PostgreSQL
19895
PostgreSQL daemon already running

and I don't understand why the daemon is already running if I shut down the
application.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [GENERAL] Possible bug with ALTER LANGUAGE ... OWNER TO ...

[Tom Lane]

Erik Jones <[EMAIL PROTECTED]> writes:
> I've just run up against a problem with ALTER LANGUAGE ... OWNER  
> TO ... wherein the change of ownership does not propagate to a  
> language's handler and validator functions preventing you from  
> dropping the role if it created a language.  I'm assuming a valid  
> workaround is manually change the owner of the handler and validator  
> functions but I'd think that changing a languages owning role should  
> propagate to any other objects created when the language was created.

Why?  The underlying functions are independent objects, in the general
case.

What you really want for this case is REASSIGN OWNED, I think.

regards, tom lane

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

[GENERAL] Possible bug with ALTER LANGUAGE ... OWNER TO ...

[Erik Jones]

Greetings,

I've just run up against a problem with ALTER LANGUAGE ... OWNER  
TO ... wherein the change of ownership does not propagate to a  
language's handler and validator functions preventing you from  
dropping the role if it created a language.  I'm assuming a valid  
workaround is manually change the owner of the handler and validator  
functions but I'd think that changing a languages owning role should  
propagate to any other objects created when the language was created.


Here's my test case.  I haven't posted this as an official bug report  
as I'm not sure if this should be called a bug or simply not  
implemented convenience behavior (i.e. a feature request).


$ psql -U postgres
Password for user postgres:
Null display is "\N".
Timing is on.
Welcome to psql 8.2.7 (server 8.3.1), the PostgreSQL interactive  
terminal.


Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

WARNING:  You are connected to a server with major version 8.3,
but your psql client is major version 8.2.  Some backslash commands,
such as \d, might not work properly.

postgres=# create user foouser superuser;
CREATE ROLE
postgres=# create database foo;
CREATE DATABASE
postgres=# \c foo
You are now connected to database "foo".
foo=# set role foouser;
SET
foo=# create language plpgsql;
CREATE LANGUAGE
foo=# reset role;
RESET
foo=# drop user foouser;
ERROR:  role "foouser" cannot be dropped because some objects depend  
on it

DETAIL:  owner of language plpgsql
owner of function plpgsql_validator(oid)
owner of function plpgsql_call_handler()
foo=# alter language plpgsql owner to postgres;
ALTER LANGUAGE
foo=# drop user foouser;
ERROR:  role "foouser" cannot be dropped because some objects depend  
on it

DETAIL:  owner of function plpgsql_validator(oid)
owner of function plpgsql_call_handler()

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






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

Re: [GENERAL] TurnKey PostgreSQL: new installable live CD optimized for easy of use

[Scott Marlowe]

On Mon, Dec 8, 2008 at 10:47 AM, Liraz Siri <[EMAIL PROTECTED]> wrote:
>> This sounds great.  If I wind up with a big machine to test it on I'll
>> tell you how it goes.
>
> Thanks, though you don't necessarily have to wait for a "big machine" if
> you just want to give the appliance a go. A little machine or even a
> virtual machine should do.

Oh, I'll definitely try it on smaller machines. but...

>> Are you familiar with this bug:
>>
>> https://bugs.launchpad.net/debian/+source/linux/+bug/245779
>>
>> It's the reason my latest db servers are running Centos 5.2, sadly.
>> By the time I'd found the suggested workaround of setting a boot
>> option of NO_HZ=y I was already migrated off ubuntu for db servers.
>
> No, I wasn't familiar with the bug. Very strange. I've been using Hardy
> with that kernel for a few months and have yet to come across it...

Well, it only seems to show up on certain high performance hardware
setups, i.e. many cores, heavy access, etc.  At least for me.  We run
pgsql on a number of different boxes on ubuntu 8.04 and 7.10 with no
real problems.  but on that one machine with 8 opterons and 16 disks
on a fast raid controller, it shows up within a few days of heavy use,
stealing one CPU at time until the machine locks up.

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

Re: [GENERAL] ALTER TABLE .....Error: Must be owner of the table

[Josh Harrison]

Thanks all

On 12/7/08, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Josh Harrison" <[EMAIL PROTECTED]> writes:
>> How can I give the ALTER permission
>
> You can't grant ALTER permission --- that's only allowed to the table
> owner.  However, you could make thw table be owned by a group role and
> grant membership in that role.
>
>   regards, tom lane
>

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

Re: [GENERAL] TurnKey PostgreSQL: new installable live CD optimized for easy of use

[Liraz Siri]

> This sounds great.  If I wind up with a big machine to test it on I'll
> tell you how it goes.

Thanks, though you don't necessarily have to wait for a "big machine" if
you just want to give the appliance a go. A little machine or even a
virtual machine should do.

> Are you familiar with this bug:
> 
> https://bugs.launchpad.net/debian/+source/linux/+bug/245779
> 
> It's the reason my latest db servers are running Centos 5.2, sadly.
> By the time I'd found the suggested workaround of setting a boot
> option of NO_HZ=y I was already migrated off ubuntu for db servers.

No, I wasn't familiar with the bug. Very strange. I've been using Hardy
with that kernel for a few months and have yet to come across it...

Cheers,
Liraz

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

Re: [GENERAL] Unique constaint violated without being violated

[Tom Lane]

"Richard Broersma" <[EMAIL PROTECTED]> writes:
> Does this mean the PostgreSQL supports row-wise updates?  When did this 
> happen?

Only sort of ...
http://archives.postgresql.org/pgsql-committers/2006-09/msg00021.php

I didn't want this applied
http://archives.postgresql.org/pgsql-patches/2006-07/msg00246.php
but I got outvoted.

regards, tom lane

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

Re: [GENERAL] Unique constaint violated without being violated

[Richard Broersma]

On Sat, Dec 6, 2008 at 7:36 AM, Merlin Moncure <[EMAIL PROTECTED]> wrote:

>>  itidb=> update joblist set (full_name, email_address, recruiter,
>>  itidb(> subscribed, verified, created_at, updated_at) =
>>  itidb-> ('[name hidden]', '[email address hidden]', false, true
>>  itidb(> true, current_timestamp(0), current_timestamp(0));

> It looks to me like you are setting the whole table to the same
> address in the update statement (no where clause)...so of course you'd
> get the error.  Maybe you want to do an insert statement?

Does this mean the PostgreSQL supports row-wise updates?  When did this happen?


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

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

Re: [GENERAL] TurnKey PostgreSQL: new installable live CD optimized for easy of use

[Scott Marlowe]

On Mon, Dec 8, 2008 at 7:06 AM, Liraz Siri <[EMAIL PROTECTED]> wrote:
> Hi everyone,
>
> I am one of the developers for TurnKey Linux, a new opensource project
> that develops a family of lightweight installable live CDs optimized for
> various server-type tasks including LAMP, Ruby on Rails, Django, Joomla,
> Drupal, MediaWiki, and others:
>
> http://www.turnkeylinux.org/appliances
>
> This type of pre-integrated, ready-to-use system is typically called a
> software appliance:
>
> http://en.wikipedia.org/wiki/Software_appliance
>
> Our project's goal is to build software appliances that are easy to use,
> easy to deploy and free. In a nutshell, we believe everything that can
> be easy, should be easy!
>
> We just released TurnKey PostgreSQL, an easy-to-use, lightweight,
> installable live CD of the PostgreSQL relational database engine that
> can run on real hardware in addition to most types of virtual machines.
> It features a Mac OS X-themed Web management interface and a Python
> configuration and installation console. It is based on Ubuntu 8.04.1
> Hardy LTS, and is designed to provide users with a pre-integrated,
> automatically updated, turn-key operating system environment that is
> carefully built from the ground up with the minimum components needed to
> run PostgreSQL with maximum usability, efficiency, and security.

This sounds great.  If I wind up with a big machine to test it on I'll
tell you how it goes.

Are you familiar with this bug:

https://bugs.launchpad.net/debian/+source/linux/+bug/245779

It's the reason my latest db servers are running Centos 5.2, sadly.
By the time I'd found the suggested workaround of setting a boot
option of NO_HZ=y I was already migrated off ubuntu for db servers.

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

Re: [GENERAL] tune postgres for UPDATE

[Scott Marlowe]

On Mon, Dec 8, 2008 at 3:28 AM, Sebastian Böhm <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have a table with a lot of columns (text and integer).
>
> It currently has 3Mio Rows.
>
> Updating a column in all rows (integer) takes endless (days).

I'm afraid you may not understand how postgresql's MVCC implementation
works here.  Updating a row creates a new copy of the row and leaves
the old copy in place.  Running such an update several times in a row
can result in a table that is mostly dead space and very slow to
access, both for reads and writes.

What does vacuum verbose tablename say about your table?

Is there a valid reason you're updating every row?  Do they all really
need to change?

> How can I tune postgres to do this much more quickly?

Get a faster hard drive.

> VMstat looks like this:
>  r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id
> wa
>  0  1188  14160  16080 86706400   880   888  168  479  1  2  0
> 97
>  1  1188  15288  16080 86598000   832   512  152  474  7  2  0
> 91
>  0  1188  15464  16080 86534800   872   592  144  461  2  1  0
> 97

Wow, that's a REALLY REALLY slow drive subsystem.  Here's the numbers
from my laptop while updating a similar table, with 1.2 million rows
(update table xxx set y=y+1 kinda query):

procs ---memory-- ---swap-- -io -system-- cpu
 2  0  43124  30344  79804 27097080016 21156  913 2456 22  4 38 36
 0  3  43124  26472  79808 27133840080 20232  725 2163 22  2 44 32
 0  2  43124  25656  79508 271408400   148 24200  706 2187 31  4 36 29
 0  2  43124  29336  79400 271070000 0 23616  788 2577 36  5 33 26

Note that I'm writing out at 20+megs a second, you're not even hitting
1Meg.  I've got pretty slow USB memory sticks that hit 8 to 10 megs a
second.

> so mostly iowait.
>
> iostat shows about  1 block writes per second.

Then either iostat or vmstat are lying to you.  1 1k blocks per
second is about 10 times as fast as we're seeing in vmstat.

>
> My systems is debian-lenny (postgresql 8.3.5)
>
> I already increased checkpoint_segments to 32, shared_buffers to 200MB
>
> I also tried do disable autovacuum

Probably not your best move.  it's there for a good reason.  You can
tune it to make it more or less aggresive, but this kind of update is
likely causing plenty of bloating and turning off autovacuum is likely
counterproductive.

>
>
> here is a sample statement:
>
> update users set price = (select price from prices where type =
> 'normal_price' and currency = users.currency)

Any way to make that selective so it only updates the prices that need
to be updated?

> (the table price only has 30 rows)

Then why don't you just FK to point to it instead of this?

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

Re: [GENERAL] tune postgres for UPDATE

[Filip Rembiałkowski]

2008/12/8 Grzegorz Jaśkiewicz <[EMAIL PROTECTED]>

> try rewriting it to something like:
>
> update users set price = p.price from prices p where p.type =
> 'normal_price' and p.currency = users.currency;


also avoid "fake" updates:

update users set price = p.price from prices p where p.type =
'normal_price' and p.currency = users.currency
and users.price is distinct from p.price;



If price change is a frequent operation, rethink the design
- maybe you could keep a pointer to "pricing group" instead of keeping
separate price for every user.




-- 
Filip Rembiałkowski

[GENERAL] TurnKey PostgreSQL: new installable live CD optimized for easy of use

[Liraz Siri]

Hi everyone,

I am one of the developers for TurnKey Linux, a new opensource project
that develops a family of lightweight installable live CDs optimized for
various server-type tasks including LAMP, Ruby on Rails, Django, Joomla,
Drupal, MediaWiki, and others:

http://www.turnkeylinux.org/appliances

This type of pre-integrated, ready-to-use system is typically called a
software appliance:

http://en.wikipedia.org/wiki/Software_appliance

Our project's goal is to build software appliances that are easy to use,
easy to deploy and free. In a nutshell, we believe everything that can
be easy, should be easy!

We just released TurnKey PostgreSQL, an easy-to-use, lightweight,
installable live CD of the PostgreSQL relational database engine that
can run on real hardware in addition to most types of virtual machines.
It features a Mac OS X-themed Web management interface and a Python
configuration and installation console. It is based on Ubuntu 8.04.1
Hardy LTS, and is designed to provide users with a pre-integrated,
automatically updated, turn-key operating system environment that is
carefully built from the ground up with the minimum components needed to
run PostgreSQL with maximum usability, efficiency, and security.

Key features:

* auto-updated daily with latest security patches
* MacOS X themed web management interface
* easy to use configuration console (written from scratch in Python)
* packaged as an installable Live CD that runs on real machines and VMs
* minimal footprint (152MB) - includes only minimum required components
* based on Ubuntu 8.04.1 Hardy LTS

http://www.turnkeylinux.org/appliances/postgresql

Check it out and tell us what you think!

Cheers,
Liraz

-- 

Website: http://www.turnkeylinux.org/
Launchpad: https://launchpad.net/~liraz-siri


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

Re: [GENERAL] tune postgres for UPDATE

[Grzegorz Jaśkiewicz]

try rewriting it to something like:

update users set price = p.price from prices p where p.type =
'normal_price' and p.currency = users.currency;

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

Re: [GENERAL] shared disk failover

[Andrew Sullivan]

On Sun, Dec 07, 2008 at 02:36:56PM -0500, Jaime Casanova wrote:
> Hi,
> 
> any one has doing this... is there a good tutorial o directions for it?

The answer to this is highly dependent on the system you're using.
What is it?

A


-- 
Andrew Sullivan
[EMAIL PROTECTED]

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

[GENERAL] tune postgres for UPDATE

[Sebastian Böhm]

Hi,

I have a table with a lot of columns (text and integer).

It currently has 3Mio Rows.

Updating a column in all rows (integer) takes endless (days).

The column I update is not indexed.

How can I tune postgres to do this much more quickly?

VMstat looks like this:
 r  b   swpd   free   buff  cache   si   sobibo   in   cs us  
sy id wa
 0  1188  14160  16080 86706400   880   888  168  479  1   
2  0 97
 1  1188  15288  16080 86598000   832   512  152  474  7   
2  0 91
 0  1188  15464  16080 86534800   872   592  144  461  2   
1  0 97


so mostly iowait.

iostat shows about  1 block writes per second.

My systems is debian-lenny (postgresql 8.3.5)

I already increased checkpoint_segments to 32, shared_buffers to 200MB

I also tried do disable autovacuum


here is a sample statement:

update users set price = (select price from prices where type =  
'normal_price' and currency = users.currency)


(the table price only has 30 rows)

thank you very much!
sebastian


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

Re: [GENERAL] Multi Lingual problem

[Stephane Bortzmeyer]

On Tue, Dec 02, 2008 at 05:35:35PM +0530,
 ravi kiran <[EMAIL PROTECTED]> wrote 
 a message of 19 lines which said:

> I am using the psqlodbc driver to connect to postgresql... But this
> is not supporting all the languages that UTF should support..

An important thing: the Unicode character set, encoded as
UTF-something, does not support *languages* but *scripts*.

> can you please give a solution so that i can psqlodbc supports
> multiple languages.

You provide almost no details so I do not think that anyone could
help. Please give more information (code, error messages, etc).

> Right now there is support for russian characters from my
> application..

You mean the cyrillic characters? They are not specific to
russian. Cyrillic is a script, russian a language.


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

Re: [GENERAL] Planner picking topsey turvey plan?

[Glyn Astill]

> From: Tom Lane <[EMAIL PROTECTED]>
> 
> You've provided no evidence that this is a bad plan.
> 

Looks like I didn't take the time to understand properly what the explains were 
showing.

> In particular, the plan you seem to think would be better
> would involve
> an estimated 153 iterations of the cost-15071 hash
> aggregation, which
> simple arithmetic shows is more expensive than the plan it
> did choose.
> 

I'd totally missed that all the cost was in the view that I'd created.

Thanks tom




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

Re: [GENERAL] Prepared statement already exists

[Richard Huxton]

WireSpot wrote:
> This mechanism is still not perfect. Technically it is still possible
> for race conditions to appear. Apparently (in PHP at least) pg_connect
> does persistent connections by default.

Nope - pg_pconnect() does that. Multiple calls to pg_connect() within
the same script will give the same connection though.

-- 
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] is there any error for my postgresql installation?

[Richard Huxton]

中和刘 wrote:
> I have just installed postgresql 8.3 on my debian sid, and have set
> the password of both system user postgres and database user to the
> same password, but when i connect to it using pgadmin3(from the local
> machine), i got the error:

> here is the log messages
> ---
> 2008-12-06 12:33:08 HKT LOG:  could not load root certificate file
> "root.crt": no SSL error reported (1)
> 2008-12-06 12:33:08 HKT DETAIL:  Will not verify client certificates. (2)
> 2008-12-06 12:33:08 HKT LOG:  could not create IPv6 socket: Address
> family not supported by protocol (3)
> 2008-12-06 12:33:09 HKT LOG:  database system was shut down at
> 2008-12-06 12:32:18 HKT
> 2008-12-06 12:33:09 HKT LOG:  autovacuum launcher started
> 2008-12-06 12:33:09 HKT LOG:  database system is ready to accept connections
> 2008-12-06 12:33:09 HKT LOG:  incomplete startup packet (4)
> --
> is (1) a error? what should i do?
> what does (2) mean? is it normal?
> is (3) ok?

1,2,3 aren't anything too serious - just saying that you've not set up
ssl correctly and IPv6 networking isn't going to work. You're not using
them, so it doesn't matter.

> is (4) a error? what should i do?

I'm not sure how you're getting #4 - it seems to be happening as soon as
the system starts up.

Three things to do:

1. Make sure log_connections is turned on in your postgresql.conf

2. Make sure your connection settings are correct in pgadmin

3. Try connecting from the command-line client psql

-- 
  Richard Huxton
  Archonet Ltd

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