Re: [GENERAL] PGSQL backup options

[Shoaib Mir]

You can get the details at -->
http://www.postgresql.org/docs/8.2/static/backup.html

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/13/07, RPK <[EMAIL PROTECTED]> wrote:



We are planning a mission-critical client/server application and want to
use
PGSQL as backend to VB.NET. Before designing I want to test all the backup
and recovery options available in PGSQL and also options with which we can
recover from PGSQL service failure.

Where I can find all the back/recover details of PGSQL?
--
View this message in context:
http://www.nabble.com/PGSQL-backup-options-tf3219125.html#a8939844
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

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

[GENERAL] PGSQL backup options

[RPK]

We are planning a mission-critical client/server application and want to use
PGSQL as backend to VB.NET. Before designing I want to test all the backup
and recovery options available in PGSQL and also options with which we can
recover from PGSQL service failure.

Where I can find all the back/recover details of PGSQL?
-- 
View this message in context: 
http://www.nabble.com/PGSQL-backup-options-tf3219125.html#a8939844
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

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

Re: [GENERAL] Keyword OWNED not recognized in pg v. 8.1

[Tom Lane]

"dfx" <[EMAIL PROTECTED]> writes:
> How I can recreate (replicate) the same structure of a database originally
> made with pg v. 8.2 to a server v. 8.1?

pg_dump has never been intended to produce backward-compatible dump
scripts; in most versions it's easy to point to cases where it'd be
flat-out impossible to do so, because the new version has features
not present in the older.

If you really need to do the above then get out your text editor and
change the script ... but my advice would be to install 8.2 instead.

regards, tom lane

---(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] Keyword OWNED not recognized in pg v. 8.1

[dfx]

Dear Sirs,

I am trying to create a database in postgres v. 8.1 (Fedora core 6) using a
sql script created from pg version 8.2 (Windows) (using backup.text of
PgAdminIII) but I get an error with the keyword OWNED. (tables uses serial
type).

The fields with serial type are declared differently in the similar script:
as 'serial' in the version 8.1 and as 'integer' + association to sequence in
the version 8.2.

How I can recreate (replicate) the same structure of a database originally
made with pg v. 8.2 to a server v. 8.1?

Thank you in advance.

Domenico


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

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

Re: [GENERAL] daylight savings patches needed?

[Magnus Hagander]

> >> So what happens if you have an old os with a new postgresql install? 
> >> Will CURRENT_TIMESTAMP always return the correct value even if the 
> >> system 'date' command is showing the wrong time?
> 
> > Yes.
> 
> Unless the user changes the system's clock setting (ie, its notion of
> the current UTC time) so that what he sees from "date" looks right.
> 
> Also, what about Windows?  I'm not clear whether Windows tries to keep
> system time in UTC or not.  Even if it does, it'd be vulnerable to the
> same pilot-error problem.

It does.

/Magnus


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

Re: [GENERAL] Dumb question - how to tell if autovacuum is doing its job in 8.2.x

[Walter Vaughan]

Shoaib Mir wrote:


pg_stat_all_table view should help you:

select last_autovacuum, last_autoanalyze from pg_stat_all_tables;


select last_autovacuum, last_autoanalyze from pg_stat_all_tables;
 last_autovacuum | last_autoanalyze
-+--
 |
...snip lots of identically blank lines...
 |
 |
(939 rows)

Does that mean it's working or not configured right?

Thanks,
Walter

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

[Лохтин Константин Сергеевич]

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

   http://archives.postgresql.org/

Re: [GENERAL] Dumb question - how to tell if autovacuum is doing its job in 8.2.x

[Shoaib Mir]

pg_stat_all_table view should help you:

select last_autovacuum, last_autoanalyze from pg_stat_all_tables;

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/9/07, Sic Transit Gloria Mundi <[EMAIL PROTECTED]> wrote:



Hi,

I couldnt find this on google, the archives, or the manual.  But with the
changes to what the autovacuum daemon logs, how can we verify it's doing its
thing?  Is there a way to query the last time a table was vacuumed?  But I
don't see that in the system catalog.

Thanks!

--
Need Mail bonding?
Go to the Yahoo! Mail 
Q&Afor
 great
tips from Yahoo! 
Answersusers.

Re: [GENERAL] Dumb question - how to tell if autovacuum is doing its job in 8.2.x

[Tom Lane]

Sic Transit Gloria Mundi <[EMAIL PROTECTED]> writes:
> I couldnt find this on google, the archives, or the manual.  But with the 
> changes to what the autovacuum daemon logs, how can we verify it's doing its 
> thing?  Is there a way to query the last time a table was vacuumed?  But I 
> don't see that in the system catalog.

Try the pg_stat views.

regards, tom lane

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

Re: [GENERAL] Still unclear about PQexecParams and "create view"

[Tom Lane]

"Phil Endecott" <[EMAIL PROTECTED]> writes:
> - Can anyone offer a list of which commands are compatible with 
> PQexecParams and which aren't?

Plannable queries can take parameters: SELECT/INSERT/UPDATE/DELETE.
Utility commands (which is everything else) don't do any expression
evaluation and thus can't deal with parameters.

> - Is this likely to change any time soon?

No.

regards, tom lane

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

Re: [GENERAL] getting postgres to emulate mysql/sqlserver bit datatype

[Tom Lane]

"Anton Melser" <[EMAIL PROTECTED]> writes:
> ERROR: operator is not unique: boolean = integer

> I get this whether castcontext is 'a' or 'i'.

If you make both cast directions the same priority then the system has
no basis for choosing bool = bool over int = int or vice versa.  Try
making one direction 'i' and the other not.  I'm not sure which one
ought to be 'i', really --- it depends a lot on the details of the
queries you are trying to make work.

regards, tom lane

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

Re: [GENERAL] daylight savings patches needed?

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Joseph Shraibman wrote:
>> So what happens if you have an old os with a new postgresql install? 
>> Will CURRENT_TIMESTAMP always return the correct value even if the 
>> system 'date' command is showing the wrong time?

> Yes.

Unless the user changes the system's clock setting (ie, its notion of
the current UTC time) so that what he sees from "date" looks right.

Also, what about Windows?  I'm not clear whether Windows tries to keep
system time in UTC or not.  Even if it does, it'd be vulnerable to the
same pilot-error problem.

regards, tom lane

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

Re: [GENERAL] daylight savings patches needed?

[Bruce Momjian]

Joseph Shraibman wrote:
> Robert Treat wrote:
> 
> > If you are running pre-8.0 versions you need to update your operating 
> > system 
> > (as you indicated).  If you running an any 8.x version, you need to be on 
> > the 
> > most current corresponding 8.x.y release.
> > 
> So what happens if you have an old os with a new postgresql install? 
> Will CURRENT_TIMESTAMP always return the correct value even if the 
> system 'date' command is showing the wrong time?

Yes.

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

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

---(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] Function to return list of all prime numbers in range

[Adam Rich]

Hi Melvin,
Here is a slightly optimized version of this function   
It returns the exact same results, just runs about 1000x faster.
I've also marked it as "immutable", that's probably what you wanted, 
not Volatile.

select all_prime(1,11000)
-- Total runtime: 2868.264 ms

select all_prime2(1,11000);
-- Total runtime: 3.662 ms



CREATE OR REPLACE FUNCTION public.all_prime2(v_start INT4, v_end INT4) 
RETURNS TEXT AS $BODY$ 
DECLARE 
  v_testINT4; 
  v_divisor INT4; 
  v_prime_list  TEXT; 
BEGIN 
  <>
FOR v_test IN v_start .. v_end LOOP
  IF v_test = 2 THEN
v_prime_list = '2';
  END IF;

  CONTINUE WHEN mod(v_test,2) = 0;

  FOR v_divisor IN 3 .. ceil(sqrt(v_test)) BY 2 LOOP
CONTINUE OUTER WHEN mod(v_test,v_divisor) = 0;
  END LOOP;

IF v_prime_list IS NOT NULL THEN
  v_prime_list = v_prime_list || ',';
END IF;
v_prime_list = coalesce(v_prime_list,'') || v_test::text;
  END LOOP OUTER;

  RETURN v_prime_list; 
END; $BODY$ 
LANGUAGE 'plpgsql' IMMUTABLE; 





-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Melvin Davidson
Sent: Monday, February 12, 2007 2:03 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Function to return list of all prime numbers in range


My apologies if this is the wrong mailing list. 
I've created a function that returns a list of all prime numbers in a
range. 
eg: SELECT public.all_prime(190, 223); 
191,193,197,199,211,223 
I'd like to submit this the the contrib lib, but I could not find the
correct 
email list. Use as you wish. 
The code is below. 

== 
CREATE OR REPLACE FUNCTION public.all_prime(INT4, INT4) 
RETURNS TEXT AS 
-- Returns a list of all prime numbers in the range of $1 to $2 
-- Contibuted by Melvin Davidson 
-- Computer & Communication Technologies, Inc. 
-- [EMAIL PROTECTED] 
$BODY$ 
DECLARE 
  v_start   ALIAS FOR $1; 
  v_end ALIAS FOR $2; 
  v_testINT4; 
  v_divisor INT4; 
  v_prime_list  TEXT DEFAULT ''; 
  v_msg TEXT; 
BEGIN 
v_test = v_start; 
WHILE (v_test <= v_end) LOOP 
v_divisor = 2; 
WHILE (v_divisor <= v_test) LOOP 
IF mod(v_test, v_divisor) = 0 AND v_divisor <
v_test THEN 
EXIT; 
ELSE 
IF mod(v_test, v_divisor) = 0 AND
v_divisor = v_test THEN 
IF v_prime_list > '' THEN 
v_prime_list =
v_prime_list ||  ','; 
END IF; 
v_prime_list = v_prime_list ||
v_test::text; 
END IF; 
END IF; 
v_divisor = v_divisor +1; 
END LOOP; 
v_test = v_test + 1; 
END LOOP; 
RETURN v_prime_list; 
END; 
$BODY$ 
  LANGUAGE 'plpgsql' VOLATILE; 
GRANT EXECUTE ON FUNCTION public.all_prime(INT4, INT4) TO public; 
COMMENT ON FUNCTION public.all_prime(INT4, INT4) IS 'Returns list of all
prime numbers from $1 to $2'; 

== 


---(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] PostgreSQL and OpenLdap

[Cristiano Panvel]

Thanks Magnus,

Now I am passing the line thus pg_hba.conf

##
hostall all 10.193.4.0/24  ldap
ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br
##

However error in login is occurring

% psql postgresql -h server -U scott -W
psql: FATAL:  LDAP authentication failed for user "scott"

I must pass plus some thing.

My base Ldap:
dc=cb,dc=sc,dc=gov,dc=br

The Bind:
ou=Users

Cris.

On 2/12/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:

On Sun, Feb 11, 2007 at 08:54:56PM -0200, Cristiano Panvel wrote:
> Hi Friends,
>
> This is my first post in the list.
>
> I am not obtaining authentication my users of PostgreSQL in OpenLdap.
>
> 1) PostgreSQL was compiled with the support to ldap in a FreeBSD System.
> "option --with-ldap for configure".
>
> 2) The user also exists in the base of the OpenLdap
>
> 3) Already I also added the line of configuration in "pg_hba.conf" and
> in the "pg_service.conf"
>
> $ tail /etc/pg_service.conf
> ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br

This is not for LDAP authentication, it's for service lookup only. If
you just want LADP auth, you can get rid of it. If you want both, I'd
suggest doing one thing at a time - get rid of it for now until auth
works, then put it back in later.

> $ cat /usr/local/pgsql/data/pg_hba.conf
>
> local   all all   trust
>
> hostall all 10.193.4.0/24   md5
>
> ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br

I assume this is all on one line. In which case it's incorrect - you
can't have both md5 and ldap on teh same line. It should be:
host all all 10.193.4.0/24 ldap ldap://


> 4) To create the usuary I make thus
>
> # su - pgsql
> % createuser scott
>
> After the made configurations, I try to effect login and is error of
> password.
>
> remote# psql postgresql -h server -U scott -W
> psql: FATAL:  password authentication failed for user "scott"
>

What you really need to look at here is the server logs, not the client
output. It should tell you what it's trying to do (in your case, it
would indicate that it's not trying to do LDAP) and how its' failing.

//Magnus



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

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

Re: [GENERAL] daylight savings patches needed?

[Joseph Shraibman]

Robert Treat wrote:

If you are running pre-8.0 versions you need to update your operating system 
(as you indicated).  If you running an any 8.x version, you need to be on the 
most current corresponding 8.x.y release.


So what happens if you have an old os with a new postgresql install? 
Will CURRENT_TIMESTAMP always return the correct value even if the 
system 'date' command is showing the wrong time?


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

  http://archives.postgresql.org/

Re: [GENERAL] Adjacency List or Nested Sets to model file system hierarchy?

[Ian Harding]

On 2/12/07, Bill Moseley <[EMAIL PROTECTED]> wrote:

On Mon, Feb 12, 2007 at 10:53:53AM -0500, Merlin Moncure wrote:
> On 2/12/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
> >> Can you describe in a little bit more detail about what you mean by
> >> 'Adjaceny LIst'?
> >
> >Adjaceny list is the term used in the celko book to refer to a table that
> >is recurively related to
> >itself.
> >
> >create table foo (
> >idinteger  primary key,
> >parentid  integer references foo (id),
> >name  varchar not null,
> >);
>
> Above approach is ok but I can think of at least two other methods
> that are probably better.  First approach is to just store the whole
> path in every record for each file.  Yes, this is a pain for updates
> but searching and children discovery is simple.  in that case I would
> define pkey as (path, file).

Yes, that's what I meant by using a de-normalized table -- including
the full path in the row.  That would provide fast access to each row
via a path name.  And the parent id makes it easy to find all children
of a given node and, well, the parent too.

Separating the path and file as you suggest would make finding all
"files" at a given directory level simple, too.

But, I'm not thrilled about the possibility of the hard-coded path not
matching the path up the tree to the root node, though.  Which, of
course, is why I posted.  But, I'll give it a test.


The way I do it is to update the path to the parent's path, plus my id
on insert or update with a before trigger.  I have an after trigger
that simply updates any child record's parent_id, which forces an
update of the path, which forces update of their children, and so on.

You can, of course, cause a recursion problem if you're not careful...
Best to have a check for that too.

- Ian


Thanks,




--
Bill Moseley
[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



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

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

[GENERAL] Still unclear about PQexecParams and "create view"

[Phil Endecott]

Dear All,

I'm still unsure about whether I am allowed to use PQexecParams to 
execute a create view command with $n parameters; reading between the 
lines of Peter Eisentraut's message I get the impression that this 
isn't going to work.  So:


- Can anyone offer a list of which commands are compatible with 
PQexecParams and which aren't?


- Is this likely to change any time soon?

- Would you agree that not giving an error message immediately is a bug?

- Since I have now changed all my code to use PQexecParams and binary 
parameters, I think it is easiest for me to put a wrapper around it 
that substitutes $ parameters before passing them to PQexec.  I'll post 
the code when I have it working - but maybe someone has already done this?



Cheers,

Phil.





---(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] getting postgres to emulate mysql/sqlserver bit datatype

[Anton Melser]

On 12/02/07, Anton Melser <[EMAIL PROTECTED]> wrote:

> I think actually what he needs is what Peter suggested upthread, namely
> to weaken the context-restriction on the int-to-bool cast.

Indeed... Peter's suggestion seems to have solved all my problems. So
even though it probably shows just how embarrassingly bad my sql is...


I spoke too soon!

select executor_id, is_dedicated, is_connected, ping_time, host, port,
usr_name,
cpu_max, cpu_usage, cpu_avail, cpu_totalusage, mem_max, disk_max,
num_cpus, os, arch
from executor where  is_dedicated = 1 and  is_connected = 1


ERROR: operator is not unique: boolean = integer
État SQL :42725
Astuce : Could not choose a best candidate operator. You may need to
add explicit type casts.
Caractère : 201

I get this whether castcontext is 'a' or 'i'. I am so close to having
this fixed!!! Any ideas most welcome...
Cheers
Anton

---(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] Testing embedded SQL in C

[Reid Thompson]

On Mon, 2007-02-12 at 15:16 -0500, HT NB wrote:
> Hi,
> 
> How are you doing?
> 
> First, I am testing if this email address is valid. It is the first
> time that I am using this account. I have a question about how to
> start write embedded SQL in C programming code. What are the basic
> requirements in the .pgc file to have the embedded SQL running.
> 
> This is my coding which is aimed at testing if embedded SQL in C
> programming code is possible for my operating system Debian GNU/Linux.
> 
> #include 
> 
> 
> int main ( )
> {
> 
> EXEC SQL CONNECT TO [EMAIL PROTECTED] address}.com :80;
> 
> EXEC SQL INSERT INTO database (tab_number) VALUES (4);
> EXEC SQL SELECT * FROM database;
> 
> return 1;
> 
> }
> 
>  

see http://www.postgresql.org/docs/8.2/static/ecpg.html
see also
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/

Re: [GENERAL] getting postgres to emulate mysql/sqlserver bit datatype

[Anton Melser]

I think actually what he needs is what Peter suggested upthread, namely
to weaken the context-restriction on the int-to-bool cast.


Indeed... Peter's suggestion seems to have solved all my problems. So
even though it probably shows just how embarrassingly bad my sql is...

update pg_cast set castcontext = 'i'
where (castsource = (select oid from pg_type where typname = 'bool')
and casttarget = (select oid from pg_type where typname = 'int4'))
or (castsource = (select oid from pg_type where typname = 'int4')
and casttarget = (select oid from pg_type where typname = 'bool'))

For the archives.
Thanks to everyone.
Anton
ps. This is probably only for situations where it is absolutely
necessary... but I am now passing my nunit tests! :-)

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

[GENERAL] Testing embedded SQL in C

[HT NB]

Hi,
How are you doing?
First, I am testing if this email address is valid. It is the first time that I am using this account. I have a question about how to start write embedded SQL in C programming code. What are the basic requirements in the .pgc file to have the embedded SQL running.
This is my coding which is aimed at testing if embedded SQL in C programming code is possible for my operating system Debian GNU/Linux.
#include 
int main ( ){
EXEC SQL CONNECT TO [EMAIL PROTECTED] address}.com :80;
EXEC SQL INSERT INTO database (tab_number) VALUES (4);EXEC SQL SELECT * FROM database;
return 1;
}
 
Regards,
Huan
Huan Bui
347-602-7029
[EMAIL PROTECTED]
 

  Check out all that glitters with the MSN Entertainment Guide to the Academy Awards®  

[GENERAL] Function to return list of all prime numbers in range

[Melvin Davidson]

My apologies if this is the wrong mailing list.

I've created a function that returns a list of all prime numbers in a
range.
eg: SELECT public.all_prime(190, 223);

191,193,197,199,211,223

I'd like to submit this the the contrib lib, but I could not find the
correct
email list. Use as you wish.

The code is below. 

==
CREATE OR REPLACE FUNCTION public.all_prime(INT4, INT4)
RETURNS TEXT AS
-- Returns a list of all prime numbers in the range of $1 to $2
-- Contibuted by Melvin Davidson
-- Computer & Communication Technologies, Inc.
-- [EMAIL PROTECTED]
$BODY$
DECLARE 
  v_start   ALIAS FOR $1;
  v_end ALIAS FOR $2;
  v_testINT4;
  v_divisor INT4;
  v_prime_list  TEXT DEFAULT '';
  v_msg TEXT;
BEGIN

v_test = v_start;

WHILE (v_test <= v_end) LOOP

v_divisor = 2;
WHILE (v_divisor <= v_test) LOOP

IF mod(v_test, v_divisor) = 0 AND v_divisor <
v_test THEN
EXIT;
ELSE 
IF mod(v_test, v_divisor) = 0 AND
v_divisor = v_test THEN
IF v_prime_list > '' THEN
v_prime_list =
v_prime_list ||  ',';
END IF;
v_prime_list = v_prime_list ||
v_test::text;
END IF;
END IF;
v_divisor = v_divisor +1;

END LOOP;
v_test = v_test + 1;

END LOOP;

RETURN v_prime_list;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

GRANT EXECUTE ON FUNCTION public.all_prime(INT4, INT4) TO public;
COMMENT ON FUNCTION public.all_prime(INT4, INT4) IS 'Returns list of all
prime numbers from $1 to $2';

==

Re: [GENERAL] getting postgres to emulate mysql/sqlserver bit datatype

[Jeff Davis]

On Mon, 2007-02-12 at 09:09 +0100, Anton Melser wrote:
> In my searching I did turn up a comment (maybe from you even!) about
> how it wouldn't work (before at least). I guess my problem is that
> there is a body of sql that can't be changed, or at least the other
> devs aren't interested enough in pg support to let me add a ton of if
> pg else code. I think that creating a type is probably the way to go,
> though if anyone has any advice I'm all ears.
> I have .net code which has things like
> bool myBool = datareader.GetBoolean(datareader.GetOrdinal("my_bool"));
> Or something similar (I'm at work...). So I need to be able for npgsql
> to return a boolean, but also need to be able to insert and compare
> with straight 1, 0. I suppose there is a way that I can get around it
> but after a couple of hours I haven't been able to come up with
> anything.

Can you just do something simple like:

CREATE DOMAIN intbool AS integer CHECK (VALUE IN (0,1));

and convert the data type to that?

Then, can you tell npgsql to map the database's intbool type to the
application's bool type?

Regards,
Jeff Davis


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

Re: [GENERAL] Adjacency List or Nested Sets to model file system hierarchy?

[Bill Moseley]

On Mon, Feb 12, 2007 at 10:53:53AM -0500, Merlin Moncure wrote:
> On 2/12/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
> >> Can you describe in a little bit more detail about what you mean by
> >> 'Adjaceny LIst'?
> >
> >Adjaceny list is the term used in the celko book to refer to a table that 
> >is recurively related to
> >itself.
> >
> >create table foo (
> >idinteger  primary key,
> >parentid  integer references foo (id),
> >name  varchar not null,
> >);
> 
> Above approach is ok but I can think of at least two other methods
> that are probably better.  First approach is to just store the whole
> path in every record for each file.  Yes, this is a pain for updates
> but searching and children discovery is simple.  in that case I would
> define pkey as (path, file).

Yes, that's what I meant by using a de-normalized table -- including
the full path in the row.  That would provide fast access to each row
via a path name.  And the parent id makes it easy to find all children
of a given node and, well, the parent too.

Separating the path and file as you suggest would make finding all
"files" at a given directory level simple, too.

But, I'm not thrilled about the possibility of the hard-coded path not
matching the path up the tree to the root node, though.  Which, of
course, is why I posted.  But, I'll give it a test.

Thanks,




-- 
Bill Moseley
[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

Re: [GENERAL] Service startup - troubleshooting

[Andrew J. Kopciuch]

> I imagine the configuration was mangled somewhere down the line.
> Perhaps I need to reinstall Ubuntu, though it's not something I look
> forward to (I already tried uninstalling Postgres and wiping away all
> references to it prior to my last install).
>
> Thanks for taking a look!
>
> -
> [EMAIL PROTECTED]:~$ sudo /etc/init.d/postgresql-8.1 status
> Password:
> Version Cluster   Port Status OwnerData
> directory Log file

This shows that you do not have a cluster.

try running

sudo pg_createcluster 8.1 main

> [EMAIL PROTECTED]:~$ sudo /etc/init.d/postgresql-8.1 start -D /usr/
> local/pgsql/data

sudo /etc/init.d/postgresql-8.1 start

is all you should need.  Give that a try first, and the confirm you actually 
have a cluster with

sudo /etc/init.d/postgresql-8.1 status

> [EMAIL PROTECTED]:~$ sudo /etc/init.d/postgresql-8.1 status
> Version Cluster   Port Status OwnerData
> directory Log file
> [EMAIL PROTECTED]:~$ ls -a /var/log/postgresql
> .  ..

That seems strange.  It appears that your installation is missing something.

What do you get from :

dpkg -l *postgresql*




Andy

---(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 store whole XML file in database

[Richard Huxton]

Brandon Aiken wrote:

You'll have to escape any quotation marks or your SQL will not parse
your strings correctly.


And you have to do this for all your data, not just XML. Otherwise the 
first Mr. O'Malley you have a record for will break your application.



--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Adjacency List or Nested Sets to model file system hierarchy?

[Bill Moseley]

On Mon, Feb 12, 2007 at 05:36:37PM +, Ian Harding wrote:
> You don't mention the ltree contrib module, have you looked at it?  It
> can easily meet your requirements without having to reinvent anything.
> It may be what you're referring to as Nested Sets, I don't know.  I
> use it and like it a lot.

Yes, I have seen it.  I just thought it seemed like a very large
"hammer" to use form my task -- quite a few more query methods than I
need .  But, perhaps I should look at it again and get a better
understanding of what it can do.

Thanks,

-- 
Bill Moseley
[EMAIL PROTECTED]


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

Re: [GENERAL] how to store whole XML file in database

[Brandon Aiken]

You'll have to escape any quotation marks or your SQL will not parse
your strings correctly.

 

--

Brandon Aiken

CS/IT Systems Engineer



From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of deepak pal
Sent: Monday, February 12, 2007 4:07 AM
To: PostgreSQL - General
Subject: [GENERAL] how to store whole XML file in database

 

hi,,
   i have to insert whole xml file in database ,there is a text
field for that.but it shows error  parse error where there are attribute
..plz help me out.




** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and 
opinions of Wineman Technology, Inc. or its employees.

This e-mail message and any attachments may contain legally privileged, 
confidential or proprietary information. If you are not the intended 
recipient(s), or the employee or agent responsible for delivery of 
this message to the intended recipient(s), you are hereby notified 
that any dissemination, distribution or copying of this e-mail 
message is strictly prohibited. If you have received this message in 
error, please immediately notify the sender and delete this e-mail 
message from your computer.

Re: [GENERAL] how to store whole XML file in database

[Tommy Gildseth]

deepak pal wrote:

hi,,
   i have to insert whole xml file in database ,there is a text 
field for that.but it shows error  parse error where there are 
attribute ..plz help me out.


I'm guessing you have a quoting problem. Try escaping the XML-data 
before inserting it into the database, so that any single quotes doesn't 
mess up your insert statements.


--
Tommy

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

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

Re: [GENERAL] Client connection: Port 5432 / Postgres

[Nicolas Gignac]

Thanks. Finally, I discovered one line not uncomment, stupid typos error.

Nicolas

2007/2/7, Nicolas Gignac <[EMAIL PROTECTED]>:


Hello,

I have installed Postgres 8.2 on a internal server having Windows Server
2003 (IIS 6) up and running.
- I have configure the hp_config file to: host
all 0.0.0.0./0
md5
- I have change the listening address to '*' in the postgres.conf file
- No Firewall activated on this internal server
- I have restart the server and I can connect to postgres from remote
computer, PostgreSQL works only from the local host
- When I do a Netstat I got this: TCP   127.0.0.0.1:5432
0.0.0.0.0  LISTENING  440
- When I tried to connect from local host to the port with telnet
localhost 5432, I got an error: on port 23, connect failed.

What are the options/ways to allow connection by PostgreSQL remote Clients
to the 5432 port on my server in this internal network? Or is there any
problem with my steps, something I missed?

Thanks for your help.

Nicolas Gignac

Re: [GENERAL] Service startup - troubleshooting

[Mike]

Hi Richard,

Thanks for your response.

Below is a console session (again, this is on Ubuntu). I attempt to
run the init script but can't start Postgres and don't see any error
messages in the console or output in the log directory.

I then switch to the 'postgres' user and can run pg_ctl to start
Postgres.

I imagine the configuration was mangled somewhere down the line.
Perhaps I need to reinstall Ubuntu, though it's not something I look
forward to (I already tried uninstalling Postgres and wiping away all
references to it prior to my last install).

Thanks for taking a look!

-
[EMAIL PROTECTED]:~$ sudo /etc/init.d/postgresql-8.1 status
Password:
Version Cluster   Port Status OwnerData
directory Log file
[EMAIL PROTECTED]:~$ sudo /etc/init.d/postgresql-8.1 start -D /usr/
local/pgsql/data
[EMAIL PROTECTED]:~$ sudo /etc/init.d/postgresql-8.1 status
Version Cluster   Port Status OwnerData
directory Log file
[EMAIL PROTECTED]:~$ ls -a /var/log/postgresql
.  ..
[EMAIL PROTECTED]:~$ su postgres
Password:
[EMAIL PROTECTED]:/home/myuser$ /etc/init.d/postgresql-8.1 start -D /
usr/local/pgsql/data
[EMAIL PROTECTED]:/home/myuser$ /etc/init.d/postgresql-8.1 status
Version Cluster   Port Status OwnerData
directory Log file
[EMAIL PROTECTED]:/home/myuser$ pg_ctl -D /usr/local/pgsql/data
start
could not change directory to "/home/myuser"
postmaster starting
[EMAIL PROTECTED]:/home/myuser$ LOG:  database system was shut down
at 2007-02-08 14:08:58 EST
LOG:  checkpoint record is at 0/4BCDD4
LOG:  redo record is at 0/4BCDD4; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 1492; next OID: 16740
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system is ready
LOG:  transaction ID wrap limit is 2147484146, limited by database
"postgres"


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

   http://archives.postgresql.org/

[GENERAL] Dumb question - how to tell if autovacuum is doing its job in 8.2.x

[Sic Transit Gloria Mundi]

Hi,

I couldnt find this on google, the archives, or the manual.  But with the 
changes to what the autovacuum daemon logs, how can we verify it's doing its 
thing?  Is there a way to query the last time a table was vacuumed?  But I 
don't see that in the system catalog.

Thanks!


 
-
Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.

[GENERAL] flaky hardware?

[[EMAIL PROTECTED]]

Saw this in the postgres 8.2.3 server log today:

2007-02-10 00:27:12 PST PANIC:  XX001: corrupted item pointer: offset  
= 0, size = 0

2007-02-10 00:27:12 PST LOCATION:  PageIndexMultiDelete, bufpage.c:624
2007-02-10 00:27:56 PST LOG:  0: autovacuum process (PID 25471)  
was terminated by signal 6

2007-02-10 00:27:56 PST LOCATION:  LogChildExit, postmaster.c:2430
2007-02-10 00:27:56 PST LOG:  0: terminating any other active  
server processes

2007-02-10 00:27:56 PST LOCATION:  HandleChildCrash, postmaster.c:2315

Smells like flaky hardware. Anyone have any tips?
Incidentally, Does anyone know of a good linux hard drive test/ 
verification tool?


-jay


---(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] Help with install postgres 8.2 win32 version

[marcelo Cortez]

folks 

 I can't install postgresql on winxp profesional


 Installer fail ( creating cluster)
 Installer with out cluster . ok
 creating cluster on hand   .fail
 initdb 

The files belonging to this database system will be
owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale
Spanish_Argentina.28605.

fixing permissions on existing directory C:/Archivos
de programa/PostgreSQL/8.2/data ... ok
creating subdirectories ... initdb: could not create
directory "C:/Archivos de programa": File exists
initdb: removing contents of data directory
"C:/Archivos de programa/PostgreSQL/8.2/data"

ever error is File exists

Any clue?
 best regards
  MDC

 






__ 
Preguntá. Respondé. Descubrí. 
Todo lo que querías saber, y lo que ni imaginabas, 
está en Yahoo! Respuestas (Beta). 
¡Probalo ya! 
http://www.yahoo.com.ar/respuestas 


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

[GENERAL] Headache with a plpgsql function

[Dean Grubb]

I use pgmanager from EMS to code, and have a fucntion that is always returning 
a NULL value, when I run it in debug mode it works fine, I've traced the 
problem down to this line
 
tag := substring(s, '<\s*?[^>]+\s*?>');
 
are there issues the regexps? I am using - PostgreSQL 8.0.3 on 
i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.2 20050901 (prerelease) (SUSE 
Linux)
 
Dean

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

   http://archives.postgresql.org/

Re: [GENERAL] Client connection: Port 5432 / Postgres

[Tom Lane]

"Nicolas Gignac" <[EMAIL PROTECTED]> writes:
> I have installed Postgres 8.2 on a internal server having Windows Server
> 2003 (IIS 6) up and running.
> - I have configure the hp_config file to: host
> all 0.0.0.0./0
> md5
> - I have change the listening address to '*' in the postgres.conf file
> - No Firewall activated on this internal server
> - I have restart the server and I can connect to postgres from remote
> computer, PostgreSQL works only from the local host
> - When I do a Netstat I got this: TCP   127.0.0.0.1:5432
> 0.0.0.0.0  LISTENING  440

I'm pretty sure the above entry is only listening for local connections
--- I think there should be a netstat entry showing your machine's real
IP address and port 5432.  Since there's not, you probably didn't change
listen_addresses correctly; maybe you forgot to uncomment the
postgresql.conf line, or edited the wrong copy of the file, or didn't
really restart the server.

regards, tom lane

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

[GENERAL] how to store whole XML file in database

[deepak pal]

hi,,
  i have to insert whole xml file in database ,there is a text field
for that.but it shows error  parse error where there are attribute ..plz
help me out.

Re: [GENERAL] Adjacency List or Nested Sets to model file system hierarchy?

[Ian Harding]

On 2/12/07, Bill Moseley <[EMAIL PROTECTED]> wrote:

I'm looking for a little guidance in representing a file system --
well just the file and directory structure of a file system.

Often articles on representing a hierarchy discuss the advantages of
using Nested Sets (or nested intervals) it seems.  I'm not clear how
well they apply to a file system-like hierarchy, though.

The examples (and my limited understanding) of Nested Sets have the
leaf nodes at the end of the branches, where in a file system a node
can have both leaf nodes (files) and branches (directories).

Also, the Nested Sets seem to solve problems I don't have -- such as
finding all descendants of a given node.

My simple requirements are:

-- Quickly be able to lookup content by a full "path" name

-- Provide "directory" views that shows parent, list of contents
   including any "sub-directories".

-- To be able to easily move branches.

It will not be a large collection of "files" in the tree, so that's
not an issue.



You don't mention the ltree contrib module, have you looked at it?  It
can easily meet your requirements without having to reinvent anything.
It may be what you're referring to as Nested Sets, I don't know.  I
use it and like it a lot.

-Ian

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

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

Re: [GENERAL] Adjacency List or Nested Sets to model file system hierarchy?

[Merlin Moncure]

On 2/12/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote:

> Can you describe in a little bit more detail about what you mean by
> 'Adjaceny LIst'?

Adjaceny list is the term used in the celko book to refer to a table that is 
recurively related to
itself.

create table foo (
idinteger  primary key,
parentid  integer references foo (id),
name  varchar not null,
);


bleh.  requires 'n' queries to pull out data where n is nesting depth
(or a recursive function, or recursive query tricks which i dont
like).  or you can save of left, right extents on a key range (I've
seen that advocated by celko), but that appraoch is non-scalable imo.

Above approach is ok but I can think of at least two other methods
that are probably better.  First approach is to just store the whole
path in every record for each file.  Yes, this is a pain for updates
but searching and children discovery is simple.  in that case I would
define pkey as (path, file).

second approach which is a bit more complex but possibly a win if your
directories change a lot is to use array type to store segmented
paths.  These could be text segments (basically another spin on the
above approach) or integer keys out to another table.  In this case
you are buying a cheap rename in exchange for some complexity.  yes,
you can index an integer array type and yes, you can do children
discovery in a cheap operation.

merlin

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

Re: [GENERAL] Adjacency List or Nested Sets to model file system hierarchy?

[Richard Broersma Jr]

> Can you describe in a little bit more detail about what you mean by
> 'Adjaceny LIst'?

Adjaceny list is the term used in the celko book to refer to a table that is 
recurively related to
itself.

create table foo (
idinteger  primary key,
parentid  integer references foo (id),
name  varchar not null,
);


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

Re: [GENERAL] 8.2.3 initdb fails - invalid value for parameter "timezone_abbreviations": "Default"

[Tom Lane]

"Markus Wollny" <[EMAIL PROTECTED]> writes:
> I've just tried to initdb a fresh install of PostgreSQL 8.3.2 on Debian
> Etch (testing). My configure settings were
> ./configure --prefix=/opt/pgsql --datadir=/var/lib/pgsql/data/base
> --enable-locale --with-perl --enable-odbc --with-java 

I don't think --datadir means what you think (hint: its normal value for
that --prefix would be /opt/pgsql/share).  Leave that switch out.

regards, tom lane

---(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] pg_dump: [tar archiver] write error appending to tar archive

[Tom Lane]

"MG" <[EMAIL PROTECTED]> writes:
> Now we got the following error:
> pg_dump: [tar archiver] write error appending to tar archive (wrote =
> 28186, attempted 32767)
> There is enough space on the hard disk.

How big is the dump file?  I seem to recall that tar format has a limit
on the size of individual members (tables), and maybe an overall size
limit as well.  Another issue is that I think the members are first
built as temp files in /tmp, so the member size is constrained by how
much space you have on that filesystem.

If that's the story, switching to the "custom" format is the answer.

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] getting postgres to emulate mysql/sqlserver bit datatype

[Tom Lane]

Shane Ambler <[EMAIL PROTECTED]> writes:
> If you look at the bool type you will find it calls a function called 
> boolout(bool) which returns a cstring to return the data for the boolean 
> - you could replace this with your own function that returns a 1 or 0 
> instead of true or false. Similarly the functions to input/compare etc 
> can be changed/replaced.

I think actually what he needs is what Peter suggested upthread, namely
to weaken the context-restriction on the int-to-bool cast.  For a
comparison like
WHERE boolcol = 1
the system is still gonna see that as a bool vs int comparison, and it
won't take it unless int-to-bool is an implicit cast.

Another route might be to create a bool = int operator.  That seems less
likely to break expected behaviors, but it'd be more work.

regards, tom lane

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

Re: [GENERAL] Adjacency List or Nested Sets to model file system hierarchy?

[Merlin Moncure]

On 2/11/07, Bill Moseley <[EMAIL PROTECTED]> wrote:

I'm looking for a little guidance in representing a file system --
well just the file and directory structure of a file system.

Often articles on representing a hierarchy discuss the advantages of
using Nested Sets (or nested intervals) it seems.  I'm not clear how
well they apply to a file system-like hierarchy, though.

The examples (and my limited understanding) of Nested Sets have the
leaf nodes at the end of the branches, where in a file system a node
can have both leaf nodes (files) and branches (directories).

Also, the Nested Sets seem to solve problems I don't have -- such as
finding all descendants of a given node.

My simple requirements are:

-- Quickly be able to lookup content by a full "path" name

-- Provide "directory" views that shows parent, list of contents
   including any "sub-directories".

-- To be able to easily move branches.

It will not be a large collection of "files" in the tree, so that's
not an issue.

Seems like an Adjacency List along with a de-normalized "path" column
in the leaf nodes would meet the requirements.  But, as I see nested
sets discussed so often I wonder which is a better approach.


Can you describe in a little bit more detail about what you mean by
'Adjaceny LIst'?

merlin

---(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] getting postgres to emulate mysql/sqlserver bit datatype

[Shane Ambler]

Anton Melser wrote:

On 12/02/07, Tom Lane <[EMAIL PROTECTED]> wrote:

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> Anton Melser wrote:
>> Is there any way
>> to force pg to accept 1 and 0 for boolean?

> postgres=# insert into bool_test values(1::boolean);
> INSERT 166968558 1
> postgres=# insert into bool_test values(0::boolean);
> INSERT 166968559 1

Possibly Anton is using an old version in which there wasn't a built in
int-to-bool cast?


In my searching I did turn up a comment (maybe from you even!) about
how it wouldn't work (before at least). I guess my problem is that
there is a body of sql that can't be changed, or at least the other
devs aren't interested enough in pg support to let me add a ton of if
pg else code. I think that creating a type is probably the way to go,
though if anyone has any advice I'm all ears.
I have .net code which has things like
bool myBool = datareader.GetBoolean(datareader.GetOrdinal("my_bool"));
Or something similar (I'm at work...). So I need to be able for npgsql
to return a boolean, but also need to be able to insert and compare
with straight 1, 0. I suppose there is a way that I can get around it
but after a couple of hours I haven't been able to come up with
anything.
Cheers
Anton



I haven't tried this myself - you will want to look into the pg_catalog 
data, this is where postgres gets it's information on how to handle 
various tasks.


If you look at the bool type you will find it calls a function called 
boolout(bool) which returns a cstring to return the data for the boolean 
- you could replace this with your own function that returns a 1 or 0 
instead of true or false. Similarly the functions to input/compare etc 
can be changed/replaced.


A pg_dumpall should bring these changes across when upgrading.


The other option would be to change the internal functions used before 
you compile your own copy of postgres and to repeat these mods in any 
upgrades.


Otherwise you would need to change your client coding to work with
0/1 as well as true/false.


--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

Re: [GENERAL] How to avoid using sequential scan

[Richard Huxton]

Victor Adolfsson wrote:

Hi

My query is using a sequential scan and not an index scan even though 
that I

have indexes defined on the foreign keys.
This cases my query to take a long long time (10750.687 ms) when it should
have been completed in less than 1 second.
Any ideas on what may be the cause of this? I have done a re-index.

Below, I'm including the sql query, the sql schema with indexes and the
results of the explain, as well as the postgresql version.

-- SQLQUERY
select datetimestamptz, description from unithistory inner join event on
event_id=event.id;


You're returning all the rows.


-- EXPLAIN RESULTS
Hash Join  (cost= 1.12..82296.20 rows=2396163 width=26) (actual time=
24.885..8838.418 rows=2396163 loops=1)


I think it's unlikely you'll get 2.4 million rows back in under a 
second. Are you sure this is the query you want to test?


--
  Richard Huxton
  Archonet Ltd

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

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

[GENERAL] How to avoid using sequential scan

[Victor Adolfsson]

Hi

My query is using a sequential scan and not an index scan even though that I
have indexes defined on the foreign keys.
This cases my query to take a long long time (10750.687 ms) when it should
have been completed in less than 1 second.
Any ideas on what may be the cause of this? I have done a re-index.

Below, I'm including the sql query, the sql schema with indexes and the
results of the explain, as well as the postgresql version.

-- SQLQUERY
select datetimestamptz, description from unithistory inner join event on
event_id=event.id;


-- SQLSCHEMA

CREATE TABLE unithistory
(
 id serial NOT NULL,
 datetimestamptz timestamptz,
 data varchar(255),
 unit_id int4,
 event_id int4,
 enduser_id int4,
 installation_id int4,
 application_id int4,
 occurence_id int4,
 CONSTRAINT unithistory_pkey PRIMARY KEY (id),
 CONSTRAINT unithistory_application_id_fkey FOREIGN KEY (application_id)
 REFERENCES application (id) MATCH SIMPLE
 ON UPDATE NO ACTION ON DELETE NO ACTION,
 CONSTRAINT unithistory_enduser_id_fkey FOREIGN KEY (enduser_id)
 REFERENCES enduser (id) MATCH SIMPLE
 ON UPDATE NO ACTION ON DELETE NO ACTION,
 CONSTRAINT unithistory_event_id_fkey FOREIGN KEY (event_id)
 REFERENCES event (id) MATCH SIMPLE
 ON UPDATE NO ACTION ON DELETE NO ACTION,
 CONSTRAINT unithistory_installation_id_fkey FOREIGN KEY (installation_id)
 REFERENCES installation (id) MATCH SIMPLE
 ON UPDATE NO ACTION ON DELETE NO ACTION,
 CONSTRAINT unithistory_unit_id_fkey FOREIGN KEY (unit_id)
 REFERENCES unit (id) MATCH SIMPLE
 ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;
ALTER TABLE unithistory OWNER TO precondbuser;
COMMENT ON TABLE unithistory IS 'Where all events that happens on a unit are
stored.';


-
-- Index: idx_unithistory_event_id

CREATE INDEX idx_unithistory_event_id
 ON unithistory
 USING btree
 (event_id);


CREATE TABLE event
(
 id serial NOT NULL,
 description varchar(50), -- The name of an event
 longdescription text,
 severity_id int4,
 CONSTRAINT event_pkey PRIMARY KEY (id),
 CONSTRAINT event_severity_id_fkey FOREIGN KEY (severity_id)
 REFERENCES severity (id) MATCH SIMPLE
 ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;



-- EXPLAIN RESULTS
Hash Join  (cost= 1.12..82296.20 rows=2396163 width=26) (actual time=
24.885..8838.418 rows=2396163 loops=1)
 Hash Cond: (unithistory.event_id = event.id)
 ->  Seq Scan on unithistory  (cost=0.00..46352.63 rows=2396163 width=12)
(actual time=6.580..3597.683 rows=2396163 loops=1)
 ->  Hash  (cost=1.10..1.10 rows=10 width=22) (actual
time=18.257..18.257rows=10 loops=1)
   ->  Seq Scan on event  (cost=0.00..1.10 rows=10 width=22) (actual
time=18.223..18.235 rows=10 loops=1)
Total runtime: 10750.687 ms


VERSION
select version();
 version
---

PostgreSQL 8.2.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.1.120060525 (Red Hat
4.1.1-1)
(1 row)

(pgadmin 1.4.3)


best regards
Victor Adolfsson

[GENERAL] 8.2.3 initdb fails - invalid value for parameter "timezone_abbreviations": "Default"

[Markus Wollny]

Hello!

I've just tried to initdb a fresh install of PostgreSQL 8.3.2 on Debian
Etch (testing). My configure settings were
./configure --prefix=/opt/pgsql --datadir=/var/lib/pgsql/data/base
--enable-locale --with-perl --enable-odbc --with-java 

This is what happens:

[EMAIL PROTECTED]:~$ /opt/pgsql/bin/initdb -D -locale=de_DE.UTF-8
/var/lib/pgsql/data/
initdb: file "/var/lib/pgsql/data/base/postgres.bki" does not exist
This means you have a corrupted installation or identified
the wrong directory with the invocation option -L.

So I use the -L switch and try again:

[EMAIL PROTECTED]:~$ /opt/pgsql/bin/initdb -D -locale=de_DE.UTF-8
/var/lib/pgsql/data/base -L /var/lib/pgsql/data/input/
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.

The database cluster will be initialized with locale de_DE.UTF-8.
The default database encoding has accordingly been set to UTF8.

fixing permissions on existing directory /var/lib/pgsql/data/base ... ok
creating subdirectories ... ok
selecting default max_connections ... 10
selecting default shared_buffers/max_fsm_pages ... 400kB/2
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/data/base/base/1 ...
FATAL:  invalid value for parameter "timezone_abbreviations": "Default"
child process exited with exit code 1
initdb: removing contents of data directory "/var/lib/pgsql/data/base"

[EMAIL PROTECTED]:~$ ls -l /var/lib/pgsql/data/input/
insgesamt 624
drwxr-xr-x  2 postgres daemon   4096 2007-02-11 03:22 contrib
-rw-r--r--  1 postgres daemon  41682 2007-02-11 03:19
conversion_create.sql
-rw-r--r--  1 postgres daemon  82686 2007-02-11 03:19
information_schema.sql
-rw-r--r--  1 postgres daemon   3257 2007-02-11 03:19 pg_hba.conf.sample
-rw-r--r--  1 postgres daemon   1460 2007-02-11 03:19
pg_ident.conf.sample
-rw-r--r--  1 postgres daemon542 2007-02-11 03:19
pg_service.conf.sample
-rw-r--r--  1 postgres daemon 335770 2007-02-11 03:19 postgres.bki
-rw-r--r--  1 postgres daemon  61773 2007-02-11 03:19
postgres.description
-rw-r--r--  1 postgres daemon  15322 2007-02-11 03:19
postgresql.conf.sample
-rw-r--r--  1 postgres daemon 40 2007-02-11 03:19
postgres.shdescription
-rw-r--r--  1 postgres daemon211 2007-02-11 03:19 psqlrc.sample
-rw-r--r--  1 postgres daemon   2689 2007-02-11 03:19
recovery.conf.sample
-rw-r--r--  1 postgres daemon  22311 2007-02-11 03:19 sql_features.txt
-rw-r--r--  1 postgres daemon  13385 2007-02-11 03:19 system_views.sql
drwxr-xr-x 19 postgres daemon   4096 2007-02-11 03:19 timezone
drwxr-xr-x  2 postgres daemon   4096 2007-02-11 03:19 timezonesets

[EMAIL PROTECTED]:~$ ls -l /var/lib/pgsql/data/input/timezonesets/Default
-rw-r--r-- 1 postgres daemon 28709 2007-02-11 03:19
/var/lib/pgsql/data/input/timezonesets/Default 

LC_time is set to de_DE, LC_ALL is set to de_DE.UTF-8.

Does anybody have an idea what's going wrong here?

Kind regards

   Markus


Computec Media AG
Sitz der Gesellschaft und Registergericht: Furth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gozalan (Vorsitzender) und Niels Herrmann
Vorsitzender des Aufsichtsrates: Jurg Marquard 
Umsatzsteuer-Identifikationsnummer: DE 812 575 276



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

Re: [GENERAL] [ADMIN] Priorities for users or queries?

[Edwin Eyan Moragas]

On 2/11/07, Benjamin Arai <[EMAIL PROTECTED]> wrote:

Hi Edwin,

Which connection parameters effect system resources?


i remembered wrong. the connection parameters i was thinking of is here:
http://www.postgresql.org/docs/8.2/static/libpq-envars.html

however, looking more closely to the config file, it can be set here:
http://www.postgresql.org/docs/8.2/static/runtime-config-resource.html

but *i am not sure* if it can be set on a per user basis. internet too
slow just now.



Benjamin

Edwin Eyan Moragas wrote:
> On 2/11/07, Benjamin Arai <[EMAIL PROTECTED]> wrote:
>> Hi,
>>
>> Is there a way to give priorities to queries or users?  Something
>> similar to NICE in Linux.  My goal is to give the updating (backend)
>> application a very low priority and give the web application a high
>> priority to avoid disturbing the user experience.
>>
>> Thanks in advance!
>
> with all due respect to Josh who has replied to this question already.
>
> i have been thinking about this and would like to make sure i am on
> the right track.
>
> yes, there is no way to give priorities to queries or users in the db.
> however, please correct me if i'm wrong, if i want something to run
> with lower priority, i can use different connection parameters
> which would use lower system resources. thus, this connection
> would run "slower" that the other "normal" connections to the db.
>
> would that be right?
>
>>
>> Benjamin
>>
>>
>> ---(end of broadcast)---
>> TIP 7: You can help support the PostgreSQL project by donating at
>>
>> http://www.postgresql.org/about/donate
>>
>
>




--
edwin eyan moragas
aspiring programmer
garnet:jasmin:beryllium:gluon::90-12264
http://www.eyan.org/

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

[GENERAL] pg_dump: [tar archiver] write error appending to tar archive

[MG]

Hello,

we have a shell-script, which executes the pg_dump once a day. This script ran 
already for about 6 months successfully.

Now we got the following error:

pg_dump: [tar archiver] write error appending to tar archive (wrote 28186, 
attempted 32767)

There is enough space on the hard disk.

Any ideas?

Regards
Michaela

Re: [GENERAL] PostgreSQL and OpenLdap

[Magnus Hagander]

On Sun, Feb 11, 2007 at 08:54:56PM -0200, Cristiano Panvel wrote:
> Hi Friends,
> 
> This is my first post in the list.
> 
> I am not obtaining authentication my users of PostgreSQL in OpenLdap.
> 
> 1) PostgreSQL was compiled with the support to ldap in a FreeBSD System.
> "option --with-ldap for configure".
> 
> 2) The user also exists in the base of the OpenLdap
> 
> 3) Already I also added the line of configuration in "pg_hba.conf" and
> in the "pg_service.conf"
> 
> $ tail /etc/pg_service.conf
> ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br

This is not for LDAP authentication, it's for service lookup only. If
you just want LADP auth, you can get rid of it. If you want both, I'd
suggest doing one thing at a time - get rid of it for now until auth
works, then put it back in later.

> $ cat /usr/local/pgsql/data/pg_hba.conf
> 
> local   all all   trust
> 
> hostall all 10.193.4.0/24   md5
> 
> ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br

I assume this is all on one line. In which case it's incorrect - you
can't have both md5 and ldap on teh same line. It should be:
host all all 10.193.4.0/24 ldap ldap://


> 4) To create the usuary I make thus
> 
> # su - pgsql
> % createuser scott
> 
> After the made configurations, I try to effect login and is error of 
> password.
> 
> remote# psql postgresql -h server -U scott -W
> psql: FATAL:  password authentication failed for user "scott"
> 

What you really need to look at here is the server logs, not the client
output. It should tell you what it's trying to do (in your case, it
would indicate that it's not trying to do LDAP) and how its' failing.

//Magnus

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

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

Re: [GENERAL] How to set UTF8 to query browser of PgAdmin III

[Rafael Martinez, Guerrero]

On Mon, 2007-02-12 at 08:17 +0100, dfx wrote:
> Dear Sirs,
> 
> I am workink on Windows 2000 with PgAdmin III v. 1.6.2
> 
> If I open an sql file created with UTF8 encoding the characters with accent
> are not reproduced correctly.
> If I open the same file with MS Word or JEdit or also PgAdmin III on Fedora
> Core 5 it is all ok.
> Note, the file was created on window machine with the same PgAdmin, and was
> created correctly (UTF8 encoded).
> 
> Client encoding on postgresql.conf is set to UTF8 and checked.
> 
> What I have to do to instruct PgAdmin to load correctly UTF8 files?
> 

Hello

We have 'client_encoding = iso8859-1' in our postgresql.conf to see
properly our 'special' characters when the database is utf8. I suppose
you will have to choose the correct encoding for your language too
insteed of utf8.

-- 
Rafael Martinez, <[EMAIL PROTECTED]>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/


---(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] getting postgres to emulate mysql/sqlserver bit datatype

[Anton Melser]

On 12/02/07, Tom Lane <[EMAIL PROTECTED]> wrote:

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> Anton Melser wrote:
>> Is there any way
>> to force pg to accept 1 and 0 for boolean?

> postgres=# insert into bool_test values(1::boolean);
> INSERT 166968558 1
> postgres=# insert into bool_test values(0::boolean);
> INSERT 166968559 1

Possibly Anton is using an old version in which there wasn't a built in
int-to-bool cast?


In my searching I did turn up a comment (maybe from you even!) about
how it wouldn't work (before at least). I guess my problem is that
there is a body of sql that can't be changed, or at least the other
devs aren't interested enough in pg support to let me add a ton of if
pg else code. I think that creating a type is probably the way to go,
though if anyone has any advice I'm all ears.
I have .net code which has things like
bool myBool = datareader.GetBoolean(datareader.GetOrdinal("my_bool"));
Or something similar (I'm at work...). So I need to be able for npgsql
to return a boolean, but also need to be able to insert and compare
with straight 1, 0. I suppose there is a way that I can get around it
but after a couple of hours I haven't been able to come up with
anything.
Cheers
Anton

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