Re: [GENERAL] Win32 8.3.3 install fail (sufficient privileges to install system services).

[Michael Orechoff]

I have the same problem. I use postgres83 as the login for the service.

The way I get around it is I delete the "InstallServices" row from the
MSI file using ORCA. This has always worked. I made the assumption the
service is just fine and needs no changes.

I am not sure if deleting the service would achieve the same result.
Never tried that.
 

[GENERAL] problem with to_ascii() function in version 8.3.3

[Pavel Arnošt]

Hi,

i have a problem with to_ascii() function in version 8.3.3. I have
read Mr. Gonzales's post at
http://groups.google.com/group/pgsql.general/browse_thread/thread/f74650e3b3248ff0/9f36fb072ea1dc98?lnk=gst&q=to_ascii#9f36fb072ea1dc98
and he managed to use to_ascii function with following command
sequence:

create table chartest ( c text);
insert into chartest (c) values ('á');
select to_ascii(encode(convert_to(c,'LATIN9'),'escape'),'LATIN9') from
chartest;

His output was:

to_ascii
--
 a

but i'm getting:

 to_ascii
--
 \341

What am I doing wrong? How to_ascii function should be used?

Regards
Pavel Arnošt

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

[GENERAL] Error when trying to drop a tablespace

[Cyril SCETBON]

Hi guys,

I get the following error :

postgres=# DROP TABLESPACE IF EXISTS my_tbs;
ERROR:  tablespace "my_tbs" is not empty

I've searched in pg_class and I'm not able to find a relation which 
refers to my_tbs with :


postgres=# select * from pg_class where reltablespace=100456;
relname | relnamespace | reltype | relowner | relam | relfilenode | 
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | 
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers 
| relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules 
| relhassubclass | relfrozenxid | relacl | reloptions

-+--+-+--+---+-+---+--+---+---+---+-+-+-+--+---+-+--+--+-+++-++--++
(0 rows)

100456 has been found with :

/oid2name -s|grep my_tbs

Any idea ?
--
Cyril SCETBON

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

[GENERAL] Sequences

[Oscar Uribe]

Hi, 

I need to do something with a sequence. This is the example:

CREATE TABLE TABLE1(

PRIMARY1 INTEGER NOT NULL,
OTHER1 VARCHAR (20) NOT NULL,
CONSTRAINT "MYPK1" PRIMARY KEY (PRIMARY1)

);

CREATE SEQUENCE "SEQ_1" INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH 1 
CACHE 1 NO CYCLE OWNED BY TABLE1.PRIMARY1;

CREATE TABLE TABLE2(

PRIMARY1 INTEGER NOT NULL,
REF1 INTEGER NOT NULL,
OTHER1 INTEGER NOT NULL,
OTHER2 VARCHAR(20),
CONSTRAINT "MYPK2" PRIMARY KEY (PRIMARY1),
CONSTRAINT "MYREF1" FOREIGN KEY (REF1) REFERENCES TABLE1(PRIMARY1),
CONSTRAINT "MYUK1" UNIQUE (REF1,OTHER1)

);

CREATE SEQUENCE "SEQ_1" INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH 1 
CACHE 1 NO CYCLE OWNED BY TABLE2.PRIMARY1;



TABLE1

PRIMARY1OTHER1
1A
2B
3C


With this two tables I wan to create a sequence that do:


TABLE2

PRIMARY1REFOTHER1OTHER2
111   A
212   B
313   C
421   A
522   B
631   A
732   B
833   C
934   D
10  14   D
11  15   E
12  35   E
13  23   E


I want to create a sequence that increases in unit column 3 for each record 
individually in column 2

How do i create a sequence that can manage this?
Is there a solution for this?


Thanks

Oscar Uribe

Re: [GENERAL] inserting to a multi-table view

[Klint Gore]

Michael Shulman wrote:

On Mon, Jun 16, 2008 at 10:03 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
  

I can write a trigger
function that does the right thing, with 'INSERT ... RETURNING
person_id INTO ...', but Postgres will not let me add an INSERT
trigger to a view; it says 'ERROR: "studentinfo" is not a table'.
  

Got a short example of what you've tried so far?



create function ins_st() returns trigger as $$
declare
  id integer;
begin
  insert into person (...) values (NEW) returning person_id into id;
  insert into student (person_id, ...) values (id, NEW);
end;
$$ language plpgsql;

create trigger ins_student before insert on studentinfo
  for each row execute procedure ins_st();

ERROR:  "studentinfo" is not a table

Mike

  


The only way I could find to make this work is to use a rule and wrap 
the inner "insert returning" in a function.


create or replace function newperson (studentinfo) returns setof person as
$$
declare
  arec person%rowtype;
begin
  for arec in
 insert into person (foo,bar) values ($1.foo,$1.bar) returning *
  loop
-- insert into address (...) values (arec.person_id, $1)
-- insert into phone (...) values (arec.person_id, $1)
 return next arec;
  end loop;
  return;
end;
$$
language plpgsql volatile;
create rule atest as on insert to studentinfo do instead (
  insert into student (person_id) select (select person_id from 
newperson(new));

);


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] PostgreSQL and AMD?

[John Tregea]

Dear Greg,

Thanks for your valuable and extensive reply. You were right that it is 
the HP machine the client is wanting, I did also find a configuration 
that has a Quad Core Intel processor and they will probably go with that 
for continuity with their current hardware. The PostgreSQL database on 
the server is to be used as an authentication gateway for enterprise 
installations of SAP, SQL Server and a bunch of GIS and other data 
applications, so there won't be a big processing or data transfer load. 
There will be two identical machines each with a hot swap drive bay as 
well as an internal 160GB drive. Initially there will only be around 50 
non concurrent users so again, low load. I have used PostgreSQL for 
around four years, but always on Intel chipsets and I had never thought 
to investigate processor brands. When the client mentioned AMD I thought 
"uh oh" this could be a black hole here.


I note your comments about disk controllers and will investigate that 
area too for our next, larger, install.


Thanks again and regards

John

Greg Smith wrote:

On Sun, 15 Jun 2008, John Tregea wrote:

The machines would be running Windows XP Pro (our clients 
requirement). Can anyone tell me if PostgreSQL runs fine on the AMD 
platform and specifically does anyone have experience with the AMD 
Phenom™ Quad Core Processors 9600B.


Once you've settled on Windows as your PostgreSQL platform, you've 
kind of given up on prioritizing performance at that point--there's a 
couple of issues that limit how good that can possibly be no matter 
what hardware you throw at it. Details like which processor you're 
using are pretty trivial in comparision. Also, the real questions you 
should be asking are ones like "did I get a good disk controller for 
database use?" which is a really serious concern in this space. My 
guess is you're talking about an HP DC5850. I am rather skeptical of 
the disk subsystem in that system (at most two disks and just a crappy 
BIOS RAID) working well in a database context. It's probably fine for 
a non-critical system, but I wouldn't run a business on it.


In general, AMD has been lagging just a bit behind Intel's products 
recently on systems with a small number of sockets. There are 
occasional reports where multi-socket multi-core systems from AMD are 
claimed to do better than similar Intel systems due to AMD's better 
bus design, I haven't seen that big difference either way myself in 
recent products.


I've been using several different types of Opteron and X2 processors 
systems from AMD the last couple of years and typically they work just 
fine. But Phenom has really been a troubled platform launch for AMD 
and I think that's why nobody has offered any suggestions to you 
yet--I haven't heard any reports from people using that chip in a 
server environment yet.


--
* 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] Error when trying to drop a tablespace

[Albe Laurenz]

Cyril SCETBON wrote:
> I get the following error :
> 
> postgres=# DROP TABLESPACE IF EXISTS my_tbs;
> ERROR:  tablespace "my_tbs" is not empty
> 
> I've searched in pg_class and I'm not able to find a relation which 
> refers to my_tbs with :
> 
> postgres=# select * from pg_class where reltablespace=100456;
[...]
> (0 rows)
> 
> 100456 has been found with :
> 
> /oid2name -s|grep my_tbs
> 
> Any idea ?

You can find the dependent objects with:

SELECT t.relname AS catalog, d.objid AS oid_dependent
FROM pg_catalog.pg_class t JOIN
 pg_catalog.pg_depend d ON (t.oid = d.classid)
WHERE refobjid = 100456;

This will give you a list of system catalog tables and OIDs.
You can find out more about an dependent object by searching
for the OID in the system catalog.

Yours,
Laurenz Albe

-- 
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] ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join

[m laks]

--- On Tue, 6/17/08, Tommy Gildseth <[EMAIL PROTECTED]> wrote:
F

How about something along these lines:


delete from instancetable
USING imagelevel
WHERE (
   (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and
   (instancetable.imageuid= imagelevel.sopinsuid)
)

Thank You so much!! It works perfectly!

1. Where is this documented, and 
2. where can I read more about what changed
between the 7 series and 8 series that caused this?

Thank you so much!
Mitchell



  

Re: [GENERAL] ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join

[m laks]

--- On Tue, 6/17/08, Jaime Casanova <[EMAIL PROTECTED]> wrote:

maybe the add_missing_from parameter in postgresql.conf is what you
need to get some sleep :)
it's there for older application like yours


Dear Jaime,

Tommy's suggestion worked right away. I wanted to 
understand more of the philiosphy behind what you wrote.

Looking around, I just found many references to changes like this
 
From: Neil Conway To: pgsql-patches 
Subject: add_missing_from = 
falseDate: Thu, 07 Apr 2005 17:04:26 +1000






Now that DELETE has a USING clause, we should be able to make 
add_missing_from=false the default in 8.1; the attached patch implements 
this and updates the documentation.
Barring any objections, I'll apply this tomorrow.

 Is there a canonical place to read more about this issue?

Thank you all so much!
Mitchell



  

Re: [GENERAL] problem with to_ascii() function in version 8.3.3

[Albe Laurenz]

Pavel Arnošt wrote:
> i have a problem with to_ascii() function in version 8.3.3. I have read Mr. 
> Gonzales's post at
> http://groups.google.com/group/pgsql.general/browse_thread/thread/f74650e3b3248ff0/9f36fb072ea1dc98?lnk=gst&q=to_ascii#9f36fb072ea1dc98
> and he managed to use to_ascii function with following command
> sequence:
>
> create table chartest ( c text);
> insert into chartest (c) values ('á');
> select to_ascii(encode(convert_to(c,'LATIN9'),'escape'),'LATIN9') from
> chartest;
>
> His output was:
>
> to_ascii
> --
>  a
>
> but i'm getting:
>
>  to_ascii
> --
>  \341
>
> What am I doing wrong? How to_ascii function should be used?

What answer do you get to the following two SQL statements:

SHOW server_encoding;
SHOW client_encoding;

Maybe that will help to understand.

Yours,
Laurenz Albe

-- 
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] ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join

[Tommy Gildseth]

m laks wrote:

--- On *Tue, 6/17/08, Tommy Gildseth /<[EMAIL PROTECTED]>/* wrote:

F



How about something along these lines:


delete from instancetable
USING imagelevel
WHERE (
   (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and
   (instancetable.imageuid= imagelevel.sopinsuid)
)

Thank You so much!! It works perfectly!

1. Where is this documented, and 
2. where can I read more about what changed

between the 7 series and 8 series that caused this?

Thank you so much!
Mitchell





As usual, the fine manual :-)
http://www.postgresql.org/docs/8.1/interactive/sql-delete.html

--
Tommy Gildseth

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

[GENERAL] Slony-I configuration problem, probably due to custom casts in 8.3

[Markus Wollny]

Hi

I'm trying to get Slony-I up and running; master-db is PostgreSQL 8.3.1, slave 
is PostgreSQL 8.2.4, Slony-I is 1.2.14 on both machines. 

This is the DDL for the table I wish to replicate:

CREATE TABLE stats.article_impressions_day
(
site_id integer NOT NULL,
article_id integer NOT NULL,
date_day date NOT NULL,
impressions_p1 integer,
impressions_total integer NOT NULL,
impressions_pages integer,
CONSTRAINT "pk_article_impressions_day" PRIMARY KEY (site_id, article_id, 
date_day)
)WITHOUT OIDS;
-- Indexes
CREATE INDEX idx_article_impressions_day_total ON stats.article_impressions_day 
USING btree (impressions_total);
CREATE INDEX idx_article_impressions_day_site ON stats.article_impressions_day 
USING btree (site_id);
CREATE INDEX idx_article_impressions_day_p1 ON stats.article_impressions_day 
USING btree (impressions_p1);
CREATE INDEX idx_article_impressions_day_date_day ON 
stats.article_impressions_day USING btree (date_day);
CREATE INDEX idx_article_impressions_day_aid ON stats.article_impressions_day 
USING btree (article_id);

This is my slonik-script:


#!/bin/sh

CLUSTER=stats
DBNAME1=community
DBNAME2=cbox
HOST1=ciadb2
HOST2=ciadb1
SLONY_USER=postgres
PGBENCH_USER=postgres

/opt/pgsql/bin/slonik <<_EOF_
cluster name = $CLUSTER;
node 1 admin conninfo = 'dbname=$DBNAME1 host=$HOST1 user=$SLONY_USER';
node 2 admin conninfo = 'dbname=$DBNAME2 host=$HOST2 user=$SLONY_USER';
init cluster ( id = 1, comment = 'Node 1' );

 create set ( id = 1, origin = 1, comment = 'All stats tables' );
set add table ( set id = 1, origin = 1,
id = 1, full qualified name = 'stats.article_impressions_day',
comment = 'daily article stats' );
set add table ( set id = 1, origin = 1,
id = 2, full qualified name = 'stats.entity_impressions_day',
comment = 'daily entity stats' );

store node ( id = 2, comment = 'Node 2' );
store path ( server = 1, client = 2,
conninfo = 'dbname=$DBNAME1 host=$HOST1 user=$SLONY_USER');
store path ( server = 2, client = 1,
conninfo = 'dbname=$DBNAME2 host=$HOST2 user=$SLONY_USER');
store listen ( origin = 1, provider = 1, receiver = 2 );
store listen ( origin = 2, provider = 2, receiver = 1 );



When I run this script, I get some error messages:
:41: PGRES_FATAL_ERROR select "_stats".setAddTable(1, 1, 
'stats.article_impressions_day', 'pk_article_impressions_day', 'daily article 
stats');  - ERROR:  operator is not unique: unknown || integer
LINE 1: SELECT  'create trigger "_stats_logtrigger_' ||  $1  || '" a...
 ^
HINT:  Could not choose a best candidate operator. You might need to add 
explicit type casts.
QUERY:  SELECT  'create trigger "_stats_logtrigger_' ||  $1  || '" after insert 
or update or delete on ' ||  $2  || ' for each row execute procedure
"_stats".logTrigger (''_stats'', ''' ||  $1  || 
''', ''' ||  $3  || ''');'
CONTEXT:  PL/pgSQL function "altertableforreplication" line 62 at EXECUTE 
statement
SQL statement "SELECT  "_stats".alterTableForReplication( $1 )"
PL/pgSQL function "setaddtable_int" line 109 at PERFORM
SQL statement "SELECT  "_stats".setAddTable_int( $1 ,  $2 ,  $3 ,  $4 ,  $5 )"
PL/pgSQL function "setaddtable" line 37 at PERFORM

I strongly suspect that there is some conflict with the implicit casts I added 
in the master-db - I used 
http://people.planetpostgresql.org/peter/uploads/pg83-implicit-casts.sqlx in 
order to restore pre-8.3 cast behaviour. As our application still depends on 
this behaviour I cannot simply drop the casts. Now what could I do to get 
replication with these casts in place on the master-db?

Kind regards

   Markus


Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jürg Marquard 
Umsatzsteuer-Identifikationsnummer: DE 812 575 276



-- 
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] ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join

[Richard Broersma]

On Tue, Jun 17, 2008 at 5:03 AM, m laks <[EMAIL PROTECTED]> wrote:
> --- On Tue, 6/17/08, Tommy Gildseth <[EMAIL PROTECTED]> wrote:

> 1. Where is this documented, and
> 2. where can I read more about what changed
> between the 7 series and 8 series that caused this?

http://www.postgresql.org/docs/8.0/interactive/release-8-0.html
http://www.postgresql.org/docs/8.1/interactive/release-8-1.html


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angles 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] Slony-I configuration problem, probably due to custom casts in 8.3

[Markus Wollny]

Hi,

Sorry for the bother - found this: 
http://archives.postgresql.org/pgsql-general/2008-03/msg01159.php

That seems to solve the problem. Thank you!

Kind regards

  Markus



Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jürg Marquard 
Umsatzsteuer-Identifikationsnummer: DE 812 575 276



-- 
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] Error when trying to drop a tablespace

[Tom Lane]

Cyril SCETBON <[EMAIL PROTECTED]> writes:
> I get the following error :

> postgres=# DROP TABLESPACE IF EXISTS my_tbs;
> ERROR:  tablespace "my_tbs" is not empty

> I've searched in pg_class and I'm not able to find a relation which 
> refers to my_tbs with :

Maybe there's something in a different database?

I'd just look into the filesystem and see what's underneath the
tablespace directory ...

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] inserting to a multi-table view

[Philippe Grégoire]

Michael,

You can try the following:

CREATE RULE studentinro_insert AS ON INSERT TO studentinfo
DO INSTEAD
(
INSERT INTO person ...;
INSERT INTO student(person_id,...) VALUES 
(currval('person_person_id_seq'),...);

);

The currval() function gives you the value of the sequence associated to 
your field. The name of the sequence should be the one in my example.


Philippe Gregoire
Information Manager
www.boreal-is.com



Michael Shulman wrote:

Hi,

This feels like a very basic question but I cannot figure it out.
Suppose I have two tables and a view that combines their data:

CREATE TABLE person
 (person_id SERIAL PRIMARY KEY,
  ...);

CREATE TABLE student
 (student_id SERIAL PRIMARY KEY,
  person_id INTEGER REFERENCES person,
  ...)

CREATE VIEW studentinfo AS
  SELECT * FROM person JOIN student USING person_id;

I want to be able to do INSERTs on "studentinfo" and have rows created
in both "person" and "student".  This requires first inserting into
"person", capturing the "person_id" of the resulting row, and using it
to insert into "student".  This seems as though it must be a common
situation.

I am happy to use either rules or triggers, but I can't figure
out how to do it with either.  I can write a rule that does two
INSERTs but I don't know how to capture the id resulting from the
first insert and put it into the second.  I can write a trigger
function that does the right thing, with 'INSERT ... RETURNING
person_id INTO ...', but Postgres will not let me add an INSERT
trigger to a view; it says 'ERROR: "studentinfo" is not a table'.

The Postgres manual:
  http://www.postgresql.org/docs/8.3/static/rules-triggers.html
says "a trigger that is fired on INSERT on a view can do the same as
a rule: put the data somewhere else and suppress the insert in the
view."  So what do I need to do to make an INSERT trigger on a view?

Thanks,
Mike

  


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

[GENERAL] Migration Articles.. ???

[sathiya psql]

Dear All,

Am going to do migration of database from one version to another., is there
any article or any other document explaining the possibilities and other
things.

Further Explanation:

I have a database in postgres X.Y which has around 90 tables, and lot of
data in it.
In the next version of that product, i had some more tables, so how to
migrate that,. there may be 150 tables., in that 90 tables, 70 may be the
same, 20 got deleted, and 80 may be new., i want the 70 tables to have same
data as it is.,

How to do this migration ??? any ways ???

Re: [GENERAL] [PERFORM] Migration Articles.. ???

[Matthew Wakeling]

On Tue, 17 Jun 2008, sathiya psql wrote:

I have a database in postgres X.Y which has around 90 tables, and lot of
data in it.
In the next version of that product, i had some more tables, so how to
migrate that,. there may be 150 tables., in that 90 tables, 70 may be the
same, 20 got deleted, and 80 may be new., i want the 70 tables to have same
data as it is.,


Please do not cross-post. This question has nothing to do with 
performance. (Cross-posting answer so everyone else doesn't answer the 
same.)


You'll want to dump the source database selectively, and then reload the 
dump into a new database. RTFM on pg_dump, especially the "-t" and "-T" 
options.


Matthew

--
All of this sounds mildly turgid and messy and confusing... but what the
heck. That's what programming's all about, really
   -- Computer Science Lecturer

--
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] Error when trying to drop a tablespace

[Albe Laurenz]

Cyril SCETBON wrote:
>>> I get the following error :
>>>
>>> postgres=# DROP TABLESPACE IF EXISTS my_tbs;
>>> ERROR:  tablespace "my_tbs" is not empty
>>>
>>> I've searched in pg_class and I'm not able to find a relation which 
>>> refers to my_tbs with :
>>>
>>> postgres=# select * from pg_class where reltablespace=100456;
>>> 
>> [...]
>>   
>>> (0 rows)
>>>
>>> 100456 has been found with :
>>>
>>> /oid2name -s|grep my_tbs
>>>
>>> Any idea ?
>>
>> You can find the dependent objects with:
>>
>> SELECT t.relname AS catalog, d.objid AS oid_dependent
>> FROM pg_catalog.pg_class t JOIN
>>  pg_catalog.pg_depend d ON (t.oid = d.classid)
>> WHERE refobjid = 100456;
>
> postgres=# SELECT t.relname AS catalog, d.objid AS oid_dependent
> postgres-# FROM pg_catalog.pg_class t JOIN
> postgres-#  pg_catalog.pg_depend d ON (t.oid = d.classid)
> postgres-# WHERE refobjid = 100456;
>  catalog | oid_dependent
> -+---
> (0 rows)
> 
> nothing...

Hmm.
Find out the directory:

SELECT oid, spclocation FROM pg_catalog.pg_tablespace WHERE spcname = 'my_tbs';

is there anything in this directory?

Yours,
Laurenz Albe

-- 
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] inserting to a multi-table view

[Michael Shulman]

On Tue, Jun 17, 2008 at 3:46 AM, Klint Gore <[EMAIL PROTECTED]> wrote:
> The only way I could find to make this work is to use a rule and wrap the
> inner "insert returning" in a function.

Thanks, this works!  Although it feels like something of a hack;
shouldn't there be a more elegant solution?

Also, I don't understand why

> create rule atest as on insert to studentinfo do instead (
>  insert into student (person_id) select (select person_id from 
> newperson(new));
> );

is necessary; what is wrong with

create rule atest as on insert to studentinfo do instead (
 insert into student (person_id) select person_id from newperson(new);
);

?  (Other than the evident fact that it doesn't work; the error
message "function expression in FROM cannot refer to other relations
of same query level" is not illuminating to me.)


Additionally, is there a reason why you put one of the inserts in the
function and the other in the rule?  Why not have the function do both
inserts and then the rule just invoke the function?

Mike

-- 
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] problem with to_ascii() function in version 8.3.3

[Pavel Arnošt]

> What answer do you get to the following two SQL statements:
>
> SHOW server_encoding;
> SHOW client_encoding;
>
> Maybe that will help to understand.

Hi,

both commands shows "UTF8". Changing client encoding with "\encoding
LATIN9" does not have any effect on to_ascii command (I don't know if
it should have).

regards
Pavel

-- 
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] inserting to a multi-table view

[Michael Shulman]

On Tue, Jun 17, 2008 at 7:56 AM, Philippe Grégoire
<[EMAIL PROTECTED]> wrote:
> CREATE RULE studentinro_insert AS ON INSERT TO studentinfo
> DO INSTEAD
> (
> INSERT INTO person ...;
> INSERT INTO student(person_id,...) VALUES
> (currval('person_person_id_seq'),...);
> );

I initially thought of this, but discounted it because if the original
insert query tried to set person_id directly (instead of getting it from
the default sequence) the supplied value would have to be discarded.  I
have any plans to do anything of the sort, though, and I suppose the user
has little right to expect to be able to do such a thing safely anyway.
So perhaps this is the simplest solution; thanks.

Mike

-- 
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] inserting to a multi-table view

[Michael Shulman]

On Tue, Jun 17, 2008 at 12:24 AM, Richard Broersma
<[EMAIL PROTECTED]> wrote:
> Anyway, here is a link discussing a generalized vertical partitioned
> view.  Perhaps it can give you some idea to get yourself rolling.
> http://archives.postgresql.org/pgsql-general/2006-12/msg01119.php

Thank you very much for pointing this out!  I am somewhat disturbed by
the example in that thread of a "partially executed update" resulting
from the obvious way to write an update rule for a view.  I guess I
need to be calling a function to do it instead, but this again seems
somewhat kludgy.  Perhaps rules are not as wonderful as they seemed
when I first encountered them.

> (I wished that the postgresql update rules where executed as
> serializable transactions, that way if one of the joined tables in
> the view was updated during your change, it would though an
> exception rollback your update instead of writing over the other
> persons work.)

This is also disturbing!  I'm not completely clear on what sort of
overwriting can happen, though; could you give an example or a link?

Are there any plans to fix these problems?  In any case, it seems as
though these sorts of caveats should appear in the documentation.

Mike

-- 
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] Clustering with minimal locking

[Scott Ribe]

> BOOM! Deadlock.

No more likely than with the current cluster command. Acquiring the lock is
the same risk; but it is held for much less time.

> ...I think what makes a lot
> more sense is to have a form of clustering that puts effort into
> placing tuples in the correct location.

Agreed that would be more desirable; thought it might be more difficult.


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



-- 
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] inserting to a multi-table view

[Richard Broersma]

On Tue, Jun 17, 2008 at 8:34 AM, Michael Shulman <[EMAIL PROTECTED]> wrote:

> Thank you very much for pointing this out!  I am somewhat disturbed by
> the example in that thread of a "partially executed update" resulting
> from the obvious way to write an update rule for a view.  I guess I
> need to be calling a function to do it instead, but this again seems
> somewhat kludgy.  Perhaps rules are not as wonderful as they seemed
> when I first encountered them.

Ya, there are a couple of things that I've learned while using rule.
1) Update-able views are only 100% safe when the view is based on a
single base table.
2) Update-able views based on joined tables require you to use
surrogate primary keys.  I.E. if your primary key were natural, and
there was a possibility that it could be changed, the resulting
updates would break.  Since on UPDATE CASCADE Referential Integrity
will cascade to primary key update before the rule is fired. (when the
rule is fired, it will still be using the old primary key before the
cascade occurred.

> This is also disturbing!  I'm not completely clear on what sort of
> overwriting can happen, though; could you give an example or a link?

Lets say you had a view based one the following select:

SELECT T1.att1, T1.att2, (...), T[n-1].att[n-1], T[n].att[n]
  FROM T1
  JOIN T2 ON T1.id = T2.id
  JOIN (...) ON T1.id = (...).id
  JOIN T[n-1] ON T1.id = T[n-1].id
  JOIN T[n] ON ON T1.id = T[n].id;

While your individual update rules are firing for each of your tables
from T1 thru T[n] to change your OLD row to NEW.  Another client could
also at the same time be updating any of the other tables before and
after your update Rules take affect.  The net result is that, some of
what you've changed could over write what the other client commited.
And some of what you've commited could be over written by what the
other client wrote.  The end result is that the view's virtual "row"
appears to be left in an inconsistant state.  This is a case where the
PostgreSQL inheritance has an advantage.  Since you are dealing with
an actual table record, MVCC unsures that only one of the changes will
be commited, not parts of both.

> Are there any plans to fix these problems?  In any case, it seems as
> though these sorts of caveats should appear in the documentation.



I think the reason that it isn't in the documentation is that the
problem is really a design problem and not really a PostgreSQL rule
problem.  As soon as you split a table in to two using a form of
vertical partitioning,  you've introduce the opportunity for update
anomalies to occur when dealing with concurrent database operations.
Since it is by design that the table is split, it is therefore up to
the designer to choose a method to ensure that consistant concurrent
updates are achieved.

Basically what you want to achieve is something like:

begin:
Select for update table T1 where id = old.id;
Select for update table T2 where id = old.id;
Select for update table (...) where id = old.id;
Select for update table T[n-1] where id = old.id;
Select for update table T[n] where id = old.id;
if all the needed row lock are aquired, then
 begin the updates
else rollback
commit;

I also recall a discussion for allowing trigger to be attached to
views.  However, IIRC, Tom Lane indicated that UPDATE triggers would
not be added to views since the possibility for update anomalies would
still exist.


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angles 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] inserting to a multi-table view

[Michael Shulman]

On Tue, Jun 17, 2008 at 11:50 AM, Richard Broersma
<[EMAIL PROTECTED]> wrote:
> While your individual update rules are firing for each of your tables
> from T1 thru T[n] to change your OLD row to NEW.  Another client could
> also at the same time be updating any of the other tables before and
> after your update Rules take affect.  The net result is that, some of
> what you've changed could over write what the other client commited.
> And some of what you've commited could be over written by what the
> other client wrote.  The end result is that the view's virtual "row"
> appears to be left in an inconsistant state.

Got it; thanks.

> Basically what you want to achieve is something like:
>
> begin:
> Select for update table T1 where id = old.id;
> Select for update table T2 where id = old.id;
> Select for update table (...) where id = old.id;
> Select for update table T[n-1] where id = old.id;
> Select for update table T[n] where id = old.id;
> if all the needed row lock are aquired, then
>  begin the updates
> else rollback
> commit;

Would it be possible to actually do something like this in an update
rule?  You couldn't write the "begin/commit", but it seems that you
wouldn't need to either, since the UPDATE command invoking the rule
will be wrapped in its own begin/commit (automatic or explicit).

Mike

-- 
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] inserting to a multi-table view

[Richard Broersma]

On Tue, Jun 17, 2008 at 12:34 PM, Michael Shulman <[EMAIL PROTECTED]> wrote:
> Would it be possible to actually do something like this in an update
> rule?  You couldn't write the "begin/commit", but it seems that you
> wouldn't need to either, since the UPDATE command invoking the rule
> will be wrapped in its own begin/commit (automatic or explicit).

Thats a good question. I've never tried it. and since then, I gotten
away from using update-able view.  In my case, I like using Natural
Primary keys so update-able views wouldn't work for me any more. :o)


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angles 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

[GENERAL] UTF8 encoding problem

[Garry Saddington]

I am getting illegal UTF8 encoding errors and I have traced it to the £ sign.
I have set lc_monetary to "lc_monetary = 'en_GB.UTF-8'" in postgresql.conf but 
this has no effect. How can I sort this problem? Client_encoding =UTF8.
Regards
Garry

-- 
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] PostgreSQL and AMD?

[Chris Browne]

[EMAIL PROTECTED] ("John Tregea") writes:
> I have been asked to specify a pair of HP PC's to set up a
> PostGreSQL server (and backup) for a client. The HP model we are
> looking at has an AMD Phenomâ,,¢ Quad Core Processor (9600B). The
> machines would be running Windows XP Pro (our clients
> requirement). Can anyone tell me if PostgreSQL runs fine on the AMD
> platform and specifically does anyone have experience with the AMD
> Phenomâ,,¢ Quad Core Processors 9600B.

Get thee to the Build Farm.
http://pgbuildfarm.org/

In particular, PostgreSQL most certainly "runs fine on the AMD
platform;" the following nodes each ran full regression tests within
the last week.

http://pgbuildfarm.org/cgi-bin/show_status.pl?member=beaver&member=beluga&member=boodie&member=bustard&member=caribou&member=chamois&member=chinchilla&member=clownfish&member=codlin_moth&member=dove&member=dugong&member=dungbeetle&member=eland&member=emperor_moth&member=ermine&member=fennec&member=finch&member=ghost_moth&member=heron&member=impala&member=kite&member=leveret&member=luna_moth&member=ostrich&member=panda&member=platypus&member=rhea&member=rosella&member=Shad&member=shark&member=tapir&member=viper&member=viscacha&member=wasp&member=wolf&member=zebra

Mind you, not one of them are running Windows XP Pro.
-- 
output = ("cbbrowne" "@" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/finances.html
Rules of  the Evil Overlord #76.  "If the hero  runs up to my  roof, I
will not run up after him and  struggle with him in an attempt to push
him  over the  edge. I  will also  not  engage him  at the  edge of  a
cliff. (In the middle of a  rope-bridge over a river of molten lava is
not even worth considering.)" 

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

[GENERAL] postgres-devel for 8.3.3

[Graeme Gemmill]

I've downloaded v8.3.3 and successfully installed it. I now have to 
configure/make/install an application that will use PostgreSQL, and 
think I need the postgresql-devel that corresponds to 8.3.3. Can someone 
point me to where it is please? Running Mandriva 2008.0, I've found I 
could have installed the rpms for 8.2.6, but I may as well look for the 
latest version.

Thanks
Graeme

--
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] inserting to a multi-table view

[Klint Gore]

Michael Shulman wrote:

On Tue, Jun 17, 2008 at 3:46 AM, Klint Gore <[EMAIL PROTECTED]> wrote:
  

The only way I could find to make this work is to use a rule and wrap the
inner "insert returning" in a function.



Thanks, this works!  Although it feels like something of a hack;
shouldn't there be a more elegant solution?
  

The more elegant statements don't work.

The ideal solution would be if some variant of
 insert into student (person_id) values ((insert into person (...) 
values (...) returning person_id));

worked.



Also, I don't understand why

  

create rule atest as on insert to studentinfo do instead (
 insert into student (person_id) select (select person_id from newperson(new));
);



is necessary; what is wrong with

create rule atest as on insert to studentinfo do instead (
 insert into student (person_id) select person_id from newperson(new);
);

?  (Other than the evident fact that it doesn't work; the error
message "function expression in FROM cannot refer to other relations
of same query level" is not illuminating to me.)
  
Got it in 1 - it doesn't work.  I'm sure there's a good reason for the 
error message that someone who knows more about rules can explain.




Additionally, is there a reason why you put one of the inserts in the
function and the other in the rule?  Why not have the function do both
inserts and then the rule just invoke the function?

  
If the rule does the insert into student, then the return to the 
application looks like a normal insert (e.g. you can check rows affected).


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] Need Help Recovering from Botched Upgrade Attempt

[Rich Shepard]

  Despite trying to be careful, I managed to mess up the upgrade from -8.1.4
to -8.3.3 on my Slackware-11.0 server/workstation. I expect that someone
here will see my error and point me in the right direction to recover a
working dbms.

  Here's what I did:

  1.) As a user, I ran pg_dumpall on version 8.1.4 and had that written to
/usr4/postgres-backups/.

  2.) Created /usr4/pgsql_old/, and copied all of /var/lib/pgsql/ there ('cp
-a /var/lib/pgsql/* .')

  3.) In /usr4/pgsql_old/data/postgresql.conf, changed port to 5466.

  4.) As root, ran '/etc/rc.d/rc.postgresql stop', which reported that's
what it did.

  5.) Built postgresql-8.3.3 using the SlackBuild script, then ran
'upgradepkg postgresql-8.3.3*tgz'; other than reporting not finding an
expected pid file, that went smoothly.

  6.) Tried starting the new version, /etc/rc.d/rc.postgresql start. It
reported that the server was already running, but that it started anyway.

  7.) A search of the process list could not find the postmaster process, or
any other indication of pgsql running. Trying to run pgsql as a user also
failed. Tried, as user postgres, to run 'pg_sql start' but that also failed.

TIA,

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] UTF8 encoding problem

[Michael Fuhr]

On Tue, Jun 17, 2008 at 10:48:34PM +0100, Garry Saddington wrote:
> I am getting illegal UTF8 encoding errors and I have traced it to the £ sign.

What's the exact error message?

> I have set lc_monetary to "lc_monetary = 'en_GB.UTF-8'" in postgresql.conf 
> but 
> this has no effect. How can I sort this problem? Client_encoding =UTF8.

Is the data UTF-8?  If the error is 'invalid byte sequence for encoding
"UTF8": 0xa3' then you probably need to set client_encoding to latin1,
latin9, or win1252.

-- 
Michael Fuhr

-- 
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] Need Help Recovering from Botched Upgrade Attempt

[Klint Gore]

Rich Shepard wrote:

   Despite trying to be careful, I managed to mess up the upgrade from -8.1.4
to -8.3.3 on my Slackware-11.0 server/workstation. I expect that someone
here will see my error and point me in the right direction to recover a
working dbms.

   Here's what I did:

   1.) As a user, I ran pg_dumpall on version 8.1.4 and had that written to
/usr4/postgres-backups/.

   2.) Created /usr4/pgsql_old/, and copied all of /var/lib/pgsql/ there ('cp
-a /var/lib/pgsql/* .')

   3.) In /usr4/pgsql_old/data/postgresql.conf, changed port to 5466.

   4.) As root, ran '/etc/rc.d/rc.postgresql stop', which reported that's
what it did.
  
You copied the files without stopping the database?  move 4 to 2. 




   5.) Built postgresql-8.3.3 using the SlackBuild script, then ran
'upgradepkg postgresql-8.3.3*tgz'; other than reporting not finding an
expected pid file, that went smoothly.
  
Is there an initdb in here somewhere?  Or is the 8.3 server trying to 
start with an 8.1 file structure?



   6.) Tried starting the new version, /etc/rc.d/rc.postgresql start. It
reported that the server was already running, but that it started anyway.
  

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] Forcibly vacating locks

[Laurent Birtz]

Hello,

I am using Postgres in a high-availability environment and I'd like to
know whether Postgres has provisions to kick off a misbehaving client
that has obtained an advisory lock on the database and won't release it
in a timely fashion. I am not worried about malicious clients, however I
am concerned that a client may hang for a very long time in the middle of
a transaction due to a programming error, an overloaded machine or
another bizarre set of circumstances. TCP keepalive packets can improve
the situation, but they won't prevent some problems from occurring.

For this reason, it is the policy of my company to avoid using explicit
locks in Postgres altogether. However, as you can imagine, it is hard at
times to avoid race conditions with this programming model.

Thus, I'd like Postgres to offer a function like set_watchdog(int nb_ms).
I would call set_watchdog(1) to enable the watchdog just before I
obtained the lock, then I would call set_watchdog(0) to disable the
watchdog after I released the lock. If a client froze, the watchdog would
eventually trigger and drop the connection to the client, thereby
preventing the whole system from freezing.

I have three specific questions:

1) Does Postgres offer something like this already? I'm aware of
   statement_timeout, but it doesn't do exactly what I need. A possible
   kludge would be to parse the 'pg_locks' table and kill the offending
   Postgres backend, but I'd rather avoid doing this.

2) Is there any hostility about the notion of implementing this feature
   into Postgres?

3) Would it be hard to implement it? After a brief code review, I think
   it would make sense to reuse the SIGALARM signal used by
   statement_timeout to forcibly close the Postgres connection when
   the watchdog triggers.


Thanks a lot for any response!
Laurent Birtz

--
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] Need Help Recovering from Botched Upgrade Attempt

[Rich Shepard]

On Wed, 18 Jun 2008, Klint Gore wrote:


You copied the files without stopping the database?  move 4 to 2.


Klint,

  Yes, actually. There was no activity on any of the databases.

Is there an initdb in here somewhere?  Or is the 8.3 server trying to start 
with an 8.1 file structure?


  Ah, yes. I missed stating that. I switched to user postgres and ran:

[EMAIL PROTECTED]:/var/lib/pgsql$ initdb -D ./data/
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 locales
  COLLATE:  C
  CTYPE:en_US
  MESSAGES: en_US
  MONETARY: en_US
  NUMERIC:  en_US
  TIME: en_US
could not determine encoding for locale "en_US": codeset is "ANSI_X3.4-1968"
initdb: could not find suitable encoding for locale en_US
Rerun initdb with the -E option.
Try "initdb --help" for more information.

  But, using the -E option with en_US tells me that it's not a valid locale.

Thanks,

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] Need Help Recovering from Botched Upgrade Attempt

[Rich Shepard]

On Wed, 18 Jun 2008, Klint Gore wrote:


   5.) Built postgresql-8.3.3 using the SlackBuild script, then ran
'upgradepkg postgresql-8.3.3*tgz'; other than reporting not finding an
expected pid file, that went smoothly.

Is there an initdb in here somewhere?  Or is the 8.3 server trying to start 
with an 8.1 file structure?


Klint,

  Backed up a couple of steps, and tried again. Removed postgresql-8.3.3;
deleted all contents of /var/lib/pgsql/data (because initdb is supposed to
create the contents, if I correctly read the Postgresql book); re-installed
postgresql-8.3.3; ran (as user postgres) 'initdb -D /var/lib/pgsql/data'.
Nothing!

  I've really FUBARed this and don't understand how, or what to do to
recover.

Thanks,

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] Need Help Recovering from Botched Upgrade Attempt

[Adrian Klaver]

On Tuesday 17 June 2008 7:18 pm, Rich Shepard wrote:
> On Wed, 18 Jun 2008, Klint Gore wrote:
> >>5.) Built postgresql-8.3.3 using the SlackBuild script, then ran
> >> 'upgradepkg postgresql-8.3.3*tgz'; other than reporting not finding an
> >> expected pid file, that went smoothly.
> >
> > Is there an initdb in here somewhere?  Or is the 8.3 server trying to
> > start with an 8.1 file structure?
>
> Klint,
>
>Backed up a couple of steps, and tried again. Removed postgresql-8.3.3;
> deleted all contents of /var/lib/pgsql/data (because initdb is supposed to
> create the contents, if I correctly read the Postgresql book); re-installed
> postgresql-8.3.3; ran (as user postgres) 'initdb -D /var/lib/pgsql/data'.
> Nothing!

Define nothing. When you ran initdb there where no messages? Also when in 
doubt I use the full path /var/lib/pgsql/bin/initdb as you have an old 
version of initdb present in the old version directory you copied. When you 
have two versions  present at the same time it is easy to get cross reference 
problems.

>
>I've really FUBARed this and don't understand how, or what to do to
> recover.
>
> Thanks,
>
> Rich
>
> --
> Richard B. Shepard, Ph.D.   |  IntegrityCredibility
> Applied Ecosystem Services, Inc.|Innovation
>  Voice: 503-667-4517  Fax: 503-667-8863

-- 
Adrian Klaver
[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] Need Help Recovering from Botched Upgrade Attempt

[Klint Gore]

Rich Shepard wrote:

On Wed, 18 Jun 2008, Klint Gore wrote:

>>5.) Built postgresql-8.3.3 using the SlackBuild script, then ran
>> 'upgradepkg postgresql-8.3.3*tgz'; other than reporting not finding an
>> expected pid file, that went smoothly.
>> 
> Is there an initdb in here somewhere?  Or is the 8.3 server trying to start 
> with an 8.1 file structure?


Klint,

   Backed up a couple of steps, and tried again. Removed postgresql-8.3.3;
deleted all contents of /var/lib/pgsql/data (because initdb is supposed to
create the contents, if I correctly read the Postgresql book); re-installed
postgresql-8.3.3; ran (as user postgres) 'initdb -D /var/lib/pgsql/data'.
Nothing!

   I've really FUBARed this and don't understand how, or what to do to
recover.

Thanks,

  

Make sure that initdb is the version you want
  initdb --version

then
  initdb -E UTF8 -D /var/lib/pgsql/data

then post the output of that.

kllint.

--
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] postgres-devel for 8.3.3

[Tom Lane]

Graeme Gemmill <[EMAIL PROTECTED]> writes:
> I've downloaded v8.3.3 and successfully installed it. I now have to 
> configure/make/install an application that will use PostgreSQL, and 
> think I need the postgresql-devel that corresponds to 8.3.3. Can someone 
> point me to where it is please?

Er ... wherever you got the base 8.3.3 RPM from.  You really can't
mix-and-match on this, because different builders might have picked
different configuration options.  You need to get the exact matching
-devel RPM.

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] Need Help Recovering from Botched Upgrade Attempt

[Craig Ringer]

Klint Gore wrote:
> Rich Shepard wrote:
>>Despite trying to be careful, I managed to mess up the upgrade from
>> -8.1.4
>> to -8.3.3 on my Slackware-11.0 server/workstation. I expect that someone
>> here will see my error and point me in the right direction to recover a
>> working dbms.
>>
>>Here's what I did:
>>
>>1.) As a user, I ran pg_dumpall on version 8.1.4 and had that
>> written to
>> /usr4/postgres-backups/.
>>
>>2.) Created /usr4/pgsql_old/, and copied all of /var/lib/pgsql/
>> there ('cp
>> -a /var/lib/pgsql/* .')

I hope you mean cp -aR , because you need those subdirectories if you're
ever going to try to use the _old copy. Even if you actually did a
recursive copy, if you really copied the data directories with the DB
server running and without executing:

select pg_start_backup('migrate');

or similar before starting the copy then you're going to have problems
using that data. You can copy a working postgresql instance's data
directories, but only if you've enabled WAL logging and you tell Pg
about it so it can write appropriate markers for recovery.



It would probably have been better to:

- pg_dumpall

- STOP THE 8.1 DATABASE SERVER and make sure it's stopped (no postmaster
or postgres processes hanging around).

- Make the old DB server binaries non-executable with chmod and/or
remove them from the PATH

- mv /var/lib/pgsql to /usr4/pgsql_old

- /path/to/8.3/bin/initdb -D /var/lib/pgsql/data

- Adjust postgresql.conf and pg_hba.conf as required

- Start the 8.3 server

- pg_restore all databases from dumps



Right now, you probably need to make REALLY sure you've put a copy of
those dumps somewhere safe, because I suspect your _old copy will be
useless. Then use 8.3's initdb on a new, empty directory, verify that
the config files are correct, and start the 8.3 server.

--
Craig Ringer


-- 
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] UTF8 encoding problem

[Garry Saddington]

On Wednesday 18 June 2008 02:04, Michael Fuhr wrote:
> On Tue, Jun 17, 2008 at 10:48:34PM +0100, Garry Saddington wrote:
> > I am getting illegal UTF8 encoding errors and I have traced it to the £
> > sign.
>
> What's the exact error message?
>
> > I have set lc_monetary to "lc_monetary = 'en_GB.UTF-8'" in
> > postgresql.conf but this has no effect. How can I sort this problem?
> > Client_encoding =UTF8.
>
> Is the data UTF-8?  If the error is 'invalid byte sequence for encoding
> "UTF8": 0xa3' then you probably need to set client_encoding to latin1,
> latin9, or win1252.
>
Thanks, that's fixed it.
Garry

-- 
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] UTF8 encoding problem

[Giorgio Valoti]

On 18/giu/08, at 03:04, Michael Fuhr wrote:


On Tue, Jun 17, 2008 at 10:48:34PM +0100, Garry Saddington wrote:
I am getting illegal UTF8 encoding errors and I have traced it to  
the £ sign.


What's the exact error message?

I have set lc_monetary to "lc_monetary = 'en_GB.UTF-8'" in  
postgresql.conf but
this has no effect. How can I sort this problem? Client_encoding  
=UTF8.


Is the data UTF-8?  If the error is 'invalid byte sequence for  
encoding

"UTF8": 0xa3' then you probably need to set client_encoding to latin1,
latin9, or win1252.


Why?

--
Giorgio Valoti
--
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] Clustering with minimal locking

[Decibel!]

On Jun 17, 2008, at 11:37 AM, Scott Ribe wrote:

BOOM! Deadlock.


No more likely than with the current cluster command. Acquiring the  
lock is

the same risk; but it is held for much less time.



Actually, no (at least in 8.2). CLUSTER grabs an exclusive lock  
before it does any work meaning that it can't deadlock by itself. Of  
course you could always do something like


BEGIN;
SELECT * FROM a;
CLUSTER .. ON a;
COMMIT;

Which does introduce the risk of a deadlock, but that's your fault,  
not Postgres.

--
Decibel!, aka Jim C. Nasby, Database Architect  [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828




smime.p7s
Description: S/MIME cryptographic signature