[GENERAL] How to reset a sequence so it will start with 1 again?

[Nico Grubert]

Hi there,

I would like to reset a sequence so its id will start with 1 if I insert 
a new record into the table, after I have deleted all records from the 
table.


I am using Postgres 8.03 and here is what I tried:

test=# create table tblperson (
test(# id SERIAL NOT NULL,
test(# name VARCHAR(200)
test(# );
NOTICE:  CREATE TABLE will create implicit sequence tblperson_id_seq 
for serial column tblperson.id

CREATE TABLE

test=# INSERT INTO tblperson (name) VALUES ('John Phelps');
INSERT 27562 1
test=# SELECT * from tblperson;
 id |name
+-
  1 | John Phelps
(1 row)
test=# SELECT * from tblperson_id_seq;
  sequence_name   | last_value | increment_by |  max_value  | 
min_value | cache_value | log_cnt | is_cycled | is_called

--++--+-+
 tblperson_id_seq |  1 |1 | 9223372036854775807 | 
   1 |   1 |   0 | f | t

(1 row)


Then, I truncated the table in order to delete all records and insert a 
new record to see if it's id will start with 1 - but it starts with 2.


test=# TRUNCATE tblperson;
TRUNCATE TABLE
test=# INSERT INTO tblperson (name) VALUES ('John Phelps');
INSERT 27564 1
test=# SELECT * from tblperson;
 id |name
+-
  2 | John Phelps
(1 row)


After I truncated tblperson I supposed that the Id will start with 1 
again if I insert a new record into tblperson. I thought, truncating the 
 table tblperson will also reset its sequence tblperson_id_seq!? Am I 
wrong?



After that, I tried to set the sequence back to 1 since I cannot set the 
sequence to 0 using setval() (error: value 0 is out of bounds for 
sequence). Unfortunately, setting the sequence back to 1 will start with 
id = 2

test=# SELECT setval('tblperson_id_seq', 0);
ERROR:  setval: value 0 is out of bounds for sequence tblperson_id_seq 
(1..9223372036854775807)

test=# SELECT setval('tblperson_id_seq', 1);
 setval

  1
(1 row)

test=# INSERT INTO tblperson (name) VALUES ('John Phelps');
INSERT 27566 1
test=# SELECT * from tblperson;
 id |name
+-
  2 | John Phelps
(1 row)


I could do the following, but I don't know if this is a clean solution:

 TRUNCATE tblperson;
 SELECT setval('tblperson_id_seq', 1);
 INSERT INTO tblperson (name) VALUES ('test1');
 INSERT INTO tblperson (name) VALUES ('test2');
 INSERT INTO tblperson (name) VALUES ('test3');
 UPDATE tblperson set id = id-1;

test=# SELECT * from tblperson;
 id | name
+---
  2 | test2
  3 | test3
  1 | test
(3 rows)


Any idea, how I can reset the sequence so it will start with 1 again?


Many thanks in advance,
Nico

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

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

Re: [GENERAL] How to reset a sequence so it will start with 1 again?

[Michael Glaesemann]

On Jan 13, 2006, at 17:02 , Nico Grubert wrote:

 SELECT setval('tblperson_id_seq', 1);



Any idea, how I can reset the sequence so it will start with 1 again?


Take a look at the docs, in particular the three-parameter version of  
setval and the is_called flag.
http://www.postgresql.org/docs/current/interactive/functions- 
sequence.html


Michael Glaesemann
grzm myrealbox com




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

[GENERAL] Plans for 8.2?

[H.J. Sanders]

Hello.

Just one request that would make the transition from another great database to 
PostgreSQL a lot easier:

SET LOCK MODE TO WAIT n

n = the max.time in second to wait.

Please? My Christmas present??

For Christmas 2007 I like:

statistics about how many sequential scans where have been for a given table.

Regards

Henk Sanders


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

Re: [GENERAL] How to reset a sequence so it will start with 1 again?

[Michael Fuhr]

On Fri, Jan 13, 2006 at 09:02:17AM +0100, Nico Grubert wrote:
 After I truncated tblperson I supposed that the Id will start with 1 
 again if I insert a new record into tblperson. I thought, truncating the 
  table tblperson will also reset its sequence tblperson_id_seq!? Am I 
 wrong?

Yes, that's wrong.  Deleting from or truncating a table doesn't
modify any sequences.

 After that, I tried to set the sequence back to 1 since I cannot set the 
 sequence to 0 using setval() (error: value 0 is out of bounds for 
 sequence). Unfortunately, setting the sequence back to 1 will start with 
 id = 2

Not if you use the three-argument form of setval() with the third
argument set to false or if you use ALTER SEQUENCE.

http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
http://www.postgresql.org/docs/8.0/interactive/sql-altersequence.html

test= CREATE SEQUENCE foo;
CREATE SEQUENCE
test= SELECT nextval('foo');
 nextval 
-
   1
(1 row)

test= SELECT nextval('foo');
 nextval 
-
   2
(1 row)

test= SELECT setval('foo', 1, false);
 setval 

  1
(1 row)

test= SELECT nextval('foo');
 nextval 
-
   1
(1 row)

test= ALTER SEQUENCE foo RESTART WITH 1;
ALTER SEQUENCE
test= SELECT nextval('foo');
 nextval 
-
   1
(1 row)

-- 
Michael Fuhr

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

Re: [GENERAL] How to reset a sequence so it will start with 1 again?

[Nico Grubert]

Take a look at the docs, in particular the three-parameter version of  
setval and the is_called flag.

http://www.postgresql.org/docs/current/interactive/functions- sequence.html


Thanks Michael,

SELECT setval('tblperson_id_seq', 1, false);
will do exactly what I supposed to get.

Nico

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

  http://archives.postgresql.org

Re: [GENERAL] Plans for 8.2?

[Michael Fuhr]

On Fri, Jan 13, 2006 at 09:30:22AM +0100, H.J. Sanders wrote:
 Just one request that would make the transition from another
 great database to PostgreSQL a lot easier:
 
 SET LOCK MODE TO WAIT n
 
 n = the max.time in second to wait.

Will statement_timeout suffice?

http://www.postgresql.org/docs/8.1/interactive/runtime-config-client.html

 For Christmas 2007 I like:
 
 statistics about how many sequential scans where have been for a given table.

Is pg_stat_{all,sys,user}_tables.seq_scan not what you're looking for?

http://www.postgresql.org/docs/8.1/interactive/monitoring-stats.html

-- 
Michael Fuhr

---(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] Locales problems with debian sarge3.1

[Martijn van Oosterhout]

On Thu, Jan 12, 2006 at 06:15:23PM +0100, Amédée wrote:
 Hello,
 
 I saw lot of topics treating about the subject, but nowhere i found
 real solutions. Has the bug been fixed?

snip

 Before initialising DB I fixed these variables :
 (I don't use -E option of initdb)
 
 export LC_ALL=C
 export LANG=fr_FR.UTF-8
 
 Before launching application I fixed these variables :
 
 export LC_ALL=C
 export LANG=fr_FR
 
 Do i have to fix environment variables in postgres.conf?

No, you have to initdb again.

 And eventually , when attempting to DB , I have this:
 
 waiting for postmaster to startFATAL:  invalid value for parameter 
 lc_messages: fr_FR.UTF-8
 
 Who ask lc_message to associated to fr_FR.UTF-8 ?

When you initialise a cluster it remembers the locale you created it
under and uses that for everything. Apparently you got rid of the
locale at some stage? It wouldn't have let you initdb with a
non-existant locale.

 Is there a way to make it valid value?

Add it to /etc/locale.gen and rerun locale-gen

 Have I made wrong variable association?
 Is there a generic option to make postgres to work?

Either create the locale, or initdb with a locale that exists.

Hope this helps,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

Re: [GENERAL] Plans for 8.2?

[Michael Fuhr]

On Fri, Jan 13, 2006 at 01:49:02AM -0700, Michael Fuhr wrote:
 On Fri, Jan 13, 2006 at 09:30:22AM +0100, H.J. Sanders wrote:
  Just one request that would make the transition from another
  great database to PostgreSQL a lot easier:
  
  SET LOCK MODE TO WAIT n
  
  n = the max.time in second to wait.
 
 Will statement_timeout suffice?

(I'm not implying that statement_timeout is equivalent, I'm just
wondering if you might be able to use it in certain circumstances.)

-- 
Michael Fuhr

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

Re: [GENERAL] Plans for 8.2?

[Karsten Hilbert]

On Thu, Jan 12, 2006 at 07:46:18PM -0500, Tom Lane wrote:

* Transaction was committed/aborted/crashed - we have to update pg_clog
* if transaction is still marked as running.
*/
   if (!TransactionIdDidCommit(xid)  !TransactionIdDidAbort(xid))
TransactionIdAbort(xid);
 
 The comment's have to is an overstatement.  The transaction would be
 treated as crashed anyway, it's just that this is a convenient place to
 make pg_clog a bit cleaner.  I'm not real sure why we bother, actually.

Because that's what makes PostgreSQL such a reliable
product. You follow your intuition and taste and bother
doing cleanup even if you cannot immediately tell whether
it's *really* needed.

Better safe than sorry. Not a bad idea for a database.

Karsten (who is storing clinical data in PostgreSQL)
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

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

[GENERAL] FATAL: terminating connection due to administrator command

[surabhi.ahuja]

Title: FATAL:  terminating connection due to administrator command







i am seeing the following in the postgreslog

why is it coming:
FATAL: terminating connection due to administrator command

thanks,
regards
Surabhi

Re: [GENERAL] Returning SQL statement

[rlee0001]

Thanks guys but I cannot use C on the database server. I am a lowly
coder in a large organization and even getting pl/PGSQL loaded into the
production database practically took an act of congress. So for now
solutions that require stored procedures to be written in C are not an
option.

pl/Perl would almost work. They loaded it onto the production database
when I requested pl/PGSQL but they didn't load it into my sandbox
(development) database.

You have no idea has bureaucratic a place can be. :o) I think I'm just
going to build a SQL statement by comparing each field in turn between
OLD and NEW. Thanks for all your help everyone! Its highly appreciated.
Maybe in the next version of postgresql some of this stuff will be
built in since appearantly it is so desirable to people.

-Robert


---(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] Plans for 8.2?

[Patrick TJ McPhee]

In article [EMAIL PROTECTED],
Harry Jackson [EMAIL PROTECTED] wrote:

% I am not aware of Oracle etc having a seperate company that sells
% replication on top of their database although I could be wrong.

There's more than one third-party replication offering for Oracle.
-- 

Patrick TJ McPhee
North York  Canada
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] Problem with restoring database from 7.3.1 to 8.0.1

[Kai Hessing]

Hi Folks,

I have a small problem.  We're currently using an old PostgreSQL 7.3.1
database. In near future we want to migrate to a new server and taking
the chance to upgrade postgres. Now I have a testsystem with postgres
8.0.1 where I tried to import a dump from our database. Everthing works
fine except 74 error messages which all look simliar to this one:

pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 639;
0 33230571 CONSTRAINT $3 mh1004
pg_restore: [Archivierer (DB)] could not execute query: FEHLER:
Relation »public.$3« existiert nicht
Command was: ALTER INDEX public.$3 OWNER TO mh1004;

Sorry it's the german version. I think the translation could be:

pg_restore: [Archivierer (DB)] Error in index directory 639; 0 33230571
CONSTRAINT $3 mh1004
pg_restore: [Archivierer (DB)] could not execute query: Error:  relation
»public.$3« does not exist
Command was: ALTER INDEX public.$3 OWNER TO mh1004;

mh1004 is the postgres user for this database. Everthing seems to work
fine but I would really like to know what this error does mean.

Thanks and *greets*
Kai

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

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

[GENERAL] PostgreSQL Top 10 Wishlist

[rlee0001]

I've been using PostgreSQL 8.1 with EMS PostgreSQL Manager and PHP for
about a month now and here are the top 10 features I'd like to see.
Keep in mind that I'm a novice so we might have some of this and I just
can't find it in the docs.

1.
Two new special variables in triggers functions (TG_STATEMENT and
TG_EFFECTIVE_STATEMENT) which returns the statement that triggered the
trigger. This should be able to be used in row- or statement-level
triggers. For row level triggers I would like TG_EFFECTIVE_STATEMENT to
return the valid statement that operates on that row only. For example
the actual statement:
   UPDATE inventory SET status = 0 WHERE status = 1;
...would be rewritten as:
   UPDATE inventory SET status = 0 WHERE id = 2335;
...when accessed from within a row-level trigger for the row who's
primary key (id) equals 2335.

2.
The ability to typecast from boolean to other datatypes. For example:
false::varchar
...would return varchar 'false' while:
false::integer
...would return integer 0. Currently there seems to be no way to
typecast from boolean (please correct me if I'm wrong). This is quite
disappointing since you can typecast into boolean.

3.
The ability to disable rules, triggers, views, functions, languages and
the like without dropping them. Maybe we have this and EMS just doesn't
impliment it?

4.
The ability to view the DDL for objects. Logically I know that this HAS
to be possible already but I can't figure it out and a search of the
documentation doesn't mention it. You can do this in EMS PostgreSQL
Manager but I can't figure out how to query it on my own.

5.
The SET and ENUM data types. I know MySQL is cheap and evil but even it
has them. Both are really just Integers attached to some Metadata. You
have no idea how many descriptor tables I have for simple enumerations.
Some have less than 10 items in them!

6.
Cross database queries. I'd like to be able to query a MS SQL Server
database from within PL/PGSQL. Or at least other databases on the same
server. Granted it might not be possible to JOIN, UNION or Subquery
against them but I'd at least like to be able to perform a query and
work with the results. We currently have to feed a postgresql database
daily snapshots the live Microsoft SMS network data using a DTS
package. Being able to access the Live data (especially if we could
join against it) would be awesome.

7.
An XML field type and associated XPath/DOM functions. Other exotic
field types like Image might be nice for some people as well. But XML
would be awesome.

8.
The ability to use procedural-language extensions everywhere, not just
in functions.

9.
The ability to nest fields within fields. For example:
  PERSON
 NAME
 LAST
 FIRST
 PHONE

10.
Or an alternative to views where tables can be defined with virtual
fields which point to functions. So for example I can say:
  SELECT balance, name FROM customers WHERE balance  0;
...where balance actually performs a behind the scenes JOIN against a
transactions table and totals the customers credits and debits. I
realize views can do this but for adding a single dynamic field they
are cumbersome and correct me if I'm wrong but I don't think you can
UPDATE against a view. Such fields can have two functions: GET and SET.
SET executes when the field is updated. If the SET procedure is not
specified updating the field could throw an exception (e.g. read only).
If SET is specfied but doesn't do anything the update would be ignored.
This effectively impliments triggers with column granularity. DELETE
and INSERT clauses could be added as well. This is really borrowing
heavily from object oriented concepts (class properties in VB are
defined like this). Now suppose we take this a step farther down the
road of rows being objects and give them private and public fields.
Public fields can be queried against from outside the table's own
virtual field functions while private fields are hidden. Public fields
can validate and normalize data before storing that data internally for
example. For example:
  In: 123 456-7890
  Out: (123) 456-7890
  Stored As:
   PHONE = (Virtual Function, with Regexp input parser)
   AREA_CODE = 123
   PREFIX = 456
   SUFFIX = 7890
It would be interesting. Combine with item 9 above and you can make
name output in a structured format like Last, First. Vb.Net's IDE
does this in the properties list for nested properties.

Just some stupid ideas.

-Robert


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

[GENERAL] Indexing Question

[Mike]

Hi,

My table structure is the following:

tbl_A one-to-many--- tbl_B one-to-many--- tbl_C

Since it was important for me to trace back tbl_C records back to
tbl_A, I placed a tbl_A_id inside tbl_C.

Now, in order to optimize my database for speed, I want to index my
tbl_B for it's tbl_A_id. So far so good. Now, with tbl_C, it makes
sense that all records of tbl_A sit next to eachother so I could index
tbl_A_id (which are not used as often in my queries), or index by
tbl_B_id. Or both of them.

To be clear, my question is: Does it make sense for me to index a table
by field_1 with the intention of having postgreSQL place those records
next to each other for faster queries that wouldn't necessarily
reference field_1?

Thanks,
Mike


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

Re: [GENERAL] Plans for 8.2?

[Baldur Norddahl]

Jeff Trout skrev:



Built In Failover/Clustering
This won't happen. The community stance, which is a good one is 
that no single replication solutions fits everyone's needs and 
therefore we rely out the outside
sources. Slony-I, Mammoth Replicator and pgpool being the most 
popular.
Too bad - I think that will keep a lot of potential users from 
evaluating Pg as a serious alternative. Good or bad, decide for 
yourself :)




Isn't the [expensive db name here]'s replication/failover just an 
expensive addon?

As in if you don't pay for it you don't get it.

So we're basically in the same boat as them.. just an add on. we just 
offer more variety.



Not really. The available options for postgresql are simply not as good 
as what the big databases offer. For some problems the non-transaction 
master/slave Slony-I is good enough. But to claim it is good enough for 
all, is like when MySQL claimed nobody really needs transactions.


I am a big postgresql fan, and I have several production clusters using 
DRBD to replicate postgresql databases in an active/failover 
configuration. But some day I am going to need a cluster that can do 
active/active, and that day I will be forced to adopt a different database.


I will also point out that none of the replication solutions have the 
same solid reputation as postgresql. As long the postgresql team will 
not endorse a replication solution, you can not expect people to put the 
same trust in these solutions as we put into postgresql itself.


Oracle do endorse their own replication solution after all.

Baldur

---(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 Top 10 Wishlist

[Martijn van Oosterhout]

On Thu, Jan 12, 2006 at 08:51:44PM -0800, rlee0001 wrote:
 1.
 Two new special variables in triggers functions (TG_STATEMENT and
 TG_EFFECTIVE_STATEMENT) which returns the statement that triggered the
 trigger.

Which would that be? The statement that directly invoked the trigger,
or the one the user typed, or would you want a list of all of them?

 This should be able to be used in row- or statement-level
 triggers. For row level triggers I would like TG_EFFECTIVE_STATEMENT to
 return the valid statement that operates on that row only. For example
 the actual statement:
UPDATE inventory SET status = 0 WHERE status = 1;
 ...would be rewritten as:
UPDATE inventory SET status = 0 WHERE id = 2335;
 ...when accessed from within a row-level trigger for the row who's
 primary key (id) equals 2335.

Why, when NEW has all the info you need in a much easier to use format?
Seems pretty pointless to me...

 2.
 The ability to typecast from boolean to other datatypes. For example:
 false::varchar
 ...would return varchar 'false' while:
 false::integer
 ...would return integer 0. Currently there seems to be no way to
 typecast from boolean (please correct me if I'm wrong). This is quite
 disappointing since you can typecast into boolean.

So make them? It's not like it's hard:

CREATE CAST (boolean AS varchar) USING FUNCTION bool_to_varchar(bool);

 4.
 The ability to view the DDL for objects. Logically I know that this HAS
 to be possible already but I can't figure it out and a search of the
 documentation doesn't mention it. You can do this in EMS PostgreSQL
 Manager but I can't figure out how to query it on my own.

psql gives you that. If you give -E it'll even show you the queries it
uses to make the info. Also, the information_schema should have most
stuff you want.

 5.
 The SET and ENUM data types. I know MySQL is cheap and evil but even it
 has them. Both are really just Integers attached to some Metadata. You
 have no idea how many descriptor tables I have for simple enumerations.
 Some have less than 10 items in them!

Someone actually mosted a patch that did this. Funnily enough, it'd
probably be implemented by creating seperate tables for each ENUM to do
the lookup. It's just suger-coating really...

 6.
 Cross database queries. I'd like to be able to query a MS SQL Server
 database from within PL/PGSQL. Or at least other databases on the same
 server. Granted it might not be possible to JOIN, UNION or Subquery
 against them but I'd at least like to be able to perform a query and
 work with the results. We currently have to feed a postgresql database
 daily snapshots the live Microsoft SMS network data using a DTS
 package. Being able to access the Live data (especially if we could
 join against it) would be awesome.

dblink does it for postgres DBs, there are similar modules for
connections to other databases.

 8.
 The ability to use procedural-language extensions everywhere, not just
 in functions.

Like where? Give an example.

 9.
 The ability to nest fields within fields. For example:
   PERSON
  NAME
  LAST
  FIRST
  PHONE

You can sort of do this, using rowtypes. Havn't nested more than one
level though. Not sure why you'd want this though. A database stores
data, presentation is the application's job.

 10.
 Or an alternative to views where tables can be defined with virtual
 fields which point to functions. So for example I can say:
   SELECT balance, name FROM customers WHERE balance  0;
 ...where balance actually performs a behind the scenes JOIN against a
 transactions table and totals the customers credits and debits. I
 realize views can do this but for adding a single dynamic field they
 are cumbersome and correct me if I'm wrong but I don't think you can
 UPDATE against a view.

You are wrong, you can make updatable views.

 example. For example:
   In: 123 456-7890
   Out: (123) 456-7890
   Stored As:
PHONE = (Virtual Function, with Regexp input parser)
AREA_CODE = 123
PREFIX = 456
SUFFIX = 7890
 It would be interesting. Combine with item 9 above and you can make
 name output in a structured format like Last, First. Vb.Net's IDE
 does this in the properties list for nested properties.

So, create a type that does that. PostgreSQL is extensible. It's got
data types for ISBNs, Internet addresses and even an XML document type.
Compared to that a simple phone number field would be trivial.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

Re: [GENERAL] Plans for 8.2?

[Martijn van Oosterhout]

On Thu, Jan 12, 2006 at 10:40:40PM +0100, Baldur Norddahl wrote:
 I will also point out that none of the replication solutions have the 
 same solid reputation as postgresql. As long the postgresql team will 
 not endorse a replication solution, you can not expect people to put the 
 same trust in these solutions as we put into postgresql itself.

So you're saying that unless PostgreSQL Core (which I assume you're
referring to with postgresql team) endorse a pile of code that they
neither wrote, audited nor have any experience with, it won't be good
enough for you?

I rather they didn't endorse anything they wern't sure of. Replication
is hard. There are many replication solutions for Postgres, both
multi-master and master/slave and sync/async. I'd rather these products
prove themselves than by anyone stamping them Endorsed.

 Oracle do endorse their own replication solution after all.

I suppose they had a hand in writing it too...

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

Re: [GENERAL] PostgreSQL Top 10 Wishlist

[Michael Glaesemann]

On Jan 13, 2006, at 13:51 , rlee0001 wrote:


I've been using PostgreSQL 8.1 with EMS PostgreSQL Manager and PHP for
about a month now and here are the top 10 features I'd like to see.
Keep in mind that I'm a novice so we might have some of this and I  
just

can't find it in the docs.


There *is* a lot of documentation, but it's also quite thorough. You  
might want to take some time and look through it.


http://www.postgresql.org/docs/current/interactive/index.html

Selected responses below.


2.
The ability to typecast from boolean to other datatypes. For example:
false::varchar
...would return varchar 'false' while:
false::integer
...would return integer 0. Currently there seems to be no way to
typecast from boolean (please correct me if I'm wrong). This is quite
disappointing since you can typecast into boolean.


You can definitely cast boolean to integer:

test=# select version();

version
 
--
PostgreSQL 8.1.0 on powerpc-apple-darwin8.3.0, compiled by GCC  
powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 (Apple Computer, Inc.  
build 5026)

(1 row)

test=# select false::boolean::integer;
int4
--
0
(1 row)

test=# select true::boolean::integer;
int4
--
1
(1 row)

And you can create your own casts to text if you'd like. See the docs:

http://www.postgresql.org/docs/current/interactive/sql-createcast.html


4.
The ability to view the DDL for objects. Logically I know that this  
HAS

to be possible already but I can't figure it out and a search of the
documentation doesn't mention it. You can do this in EMS PostgreSQL
Manager but I can't figure out how to query it on my own.


You can dump the schema of a database using pg_dump --schema-only.  
Does this do what you want?


http://www.postgresql.org/docs/current/interactive/app-pgdump.html


5.
The SET and ENUM data types. I know MySQL is cheap and evil but  
even it

has them. Both are really just Integers attached to some Metadata. You
have no idea how many descriptor tables I have for simple  
enumerations.

Some have less than 10 items in them!


Andrew Dunstan has developed EnumKit to allow you to have enumerated  
data types in PostgreSQL. Hopefully this can help you.


http://www.oreillynet.com/pub/a/databases/2006/01/06/enumerated- 
fields-in-postgresql.html



6.
Cross database queries. I'd like to be able to query a MS SQL Server
database from within PL/PGSQL. Or at least other databases on the same
server. Granted it might not be possible to JOIN, UNION or Subquery
against them but I'd at least like to be able to perform a query and
work with the results. We currently have to feed a postgresql database
daily snapshots the live Microsoft SMS network data using a DTS
package. Being able to access the Live data (especially if we could
join against it) would be awesome.


While PL/pgsql won't let you do this, you can probably do some things  
with pl/perlu or some of the other untrusted languages. Also, dbi- 
link may help you as well.


http://pgfoundry.org/projects/dbi-link/


9.
The ability to nest fields within fields. For example:
  PERSON
 NAME
 LAST
 FIRST
 PHONE


Have you looked at composite types?
http://www.postgresql.org/docs/current/interactive/sql-createtype.html




10.
Or an alternative to views where tables can be defined with virtual
fields which point to functions. So for example I can say:
  SELECT balance, name FROM customers WHERE balance  0;
...where balance actually performs a behind the scenes JOIN against a
transactions table and totals the customers credits and debits. I
realize views can do this but for adding a single dynamic field they
are cumbersome and correct me if I'm wrong but I don't think you can
UPDATE against a view.


You can update a view if you create rules to do so:

http://www.postgresql.org/docs/current/interactive/rules.html


This is really borrowing
heavily from object oriented concepts (class properties in VB are
defined like this).


While there are some similarities between classes and tables, and  
objects and rows, they're not the same thing. Some of the things you  
describe can be done using user-defined functions, while others are  
probably better done in your application. You can also create your  
own datatypes that give you all the functionality you want:  
PostgreSQL is *very* extensible.


Hope this helps a bit.

Michael Glaesemann
grzm myrealbox com




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

  http://archives.postgresql.org

Re: [GENERAL] PostgreSQL Top 10 Wishlist

[John D. Burger]

rlee0001 wrote:


2.
The ability to typecast from boolean to other datatypes. For example:
false::varchar
...would return varchar 'false' while:


Why should it return 'false'?  If anything, it seems to me it should do 
the same as this:


  # select false;
   bool
  --
   f
  (1 row)

That is, false::varchar = 'f'.

- John D. Burger
  MITRE


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

Re: [GENERAL] PostgreSQL Top 10 Wishlist

[John DeSoi]

On Jan 13, 2006, at 8:47 AM, Michael Glaesemann wrote:


You can definitely cast boolean to integer:


The poster mentioned using PostgreSQL/PHP which may be the real  
source of the issue. Boolean values are returned to PHP as strings  
't' and 'f'. Of course, 'f' is not equivalent to FALSE in PHP. It  
would be really nice if the PHP module returned a false value instead  
of 'f'.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(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] Plans for 8.2?

[Mikael Carneholm]

 Too bad - I think that will keep a lot of potential users from
 evaluating Pg as a serious alternative. Good or bad, decide for
 yourself :)

Why on earth should that be?

Citing Baldur Norddahl 
(http://archives.postgresql.org/pgsql-general/2006-01/msg00597.php):

I will also point out that none of the replication solutions have the same 
solid reputation as postgresql. As long the postgresql team will not endorse a 
replication solution, you can not expect people to put the same trust in these 
solutions as we put into postgresql itself.

/Mikael

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

Re: [GENERAL] Plans for 8.2?

[Joshua D. Drake]

Not really. The available options for postgresql are simply not as 
good as what the big databases offer. For some problems the 
non-transaction master/slave Slony-I is good enough. But to claim it 
is good enough for all, is like when MySQL claimed nobody really needs 
transactions.
Nobody claimed Slony-I is good for all. That is why there are other 
products out there. Mammoth Replicator (blatant plug and which is a 
transaction log based replication) for example.


I am a big postgresql fan, and I have several production clusters 
using DRBD to replicate postgresql databases in an active/failover 
configuration. But some day I am going to need a cluster that can do 
active/active, and that day I will be forced to adopt a different 
database.

Active, active as in multi-master?

I will also point out that none of the replication solutions have the 
same solid reputation as postgresql. As long the postgresql team will 
not endorse a replication solution, you can not expect people to put 
the same trust in these solutions as we put into postgresql itself.

That's true enough.


Oracle do endorse their own replication solution after all.

Well they wrote it, they have to.

Joshua D. Drake



Baldur

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly



--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PLphp, PLperl - http://www.commandprompt.com/


---(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] Plans for 8.2?

[Tom Lane]

Karsten Hilbert [EMAIL PROTECTED] writes:
 On Thu, Jan 12, 2006 at 07:46:18PM -0500, Tom Lane wrote:
 The comment's have to is an overstatement.  The transaction would be
 treated as crashed anyway, it's just that this is a convenient place to
 make pg_clog a bit cleaner.  I'm not real sure why we bother, actually.

 Because that's what makes PostgreSQL such a reliable
 product. You follow your intuition and taste and bother
 doing cleanup even if you cannot immediately tell whether
 it's *really* needed.

Well, this is not a correctness issue.  If it were, XactLockTableWait
would be quite the wrong place for it, because there's no guarantee
anyone will ever do XactLockTableWait for any particular transaction ID.

In fact, now that I look at it, I'm pretty well convinced this is dead
code: we only ever wait for XIDs that are known to have been alive in
the recent past, ie, within the lifespan of the current backend.  So the
case should never occur ... unless possibly in the microseconds before
we receive SIGQUIT from the postmaster because the other guy crashed,
and in that situation trying to issue a write on pg_clog is probably
not the brightest action to be taking anyway.  This could easily
represent a net minus for reliability, not a plus, if it increases the
risk of pg_clog getting corrupted during a crash sequence.

regards, tom lane

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

[GENERAL] Moving PostgreSQL data directory on Windows

[Ottó Havasvölgyi]

Hello,

I would like to move the data directory to another location. I have done this:
1. Stop PostgreSQL
2. Move data directory
3. Create a PGDATA env. variable to the new location
4. Start PostgreSQL

And it cannot start, because it cannot find postgresql.conf. (in Event log)
What should I do now?

Thanks,
Otto

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

Re: [GENERAL] FATAL: terminating connection due to administrator command

[Tom Lane]

surabhi.ahuja [EMAIL PROTECTED] writes:
 why is it coming:
 FATAL:  terminating connection due to administrator command

Something sent the backend a SIGTERM signal.  With no more information
than that, it's difficult to say more.

regards, tom lane

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

Re: [GENERAL] Problem with restoring database from 7.3.1 to 8.0.1

[Tom Lane]

Kai Hessing [EMAIL PROTECTED] writes:
 I have a small problem.  We're currently using an old PostgreSQL 7.3.1
 database. In near future we want to migrate to a new server and taking
 the chance to upgrade postgres. Now I have a testsystem with postgres
 8.0.1 where I tried to import a dump from our database. Everthing works
 fine except 74 error messages which all look simliar to this one:

 pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 639;
 0 33230571 CONSTRAINT $3 mh1004
 pg_restore: [Archivierer (DB)] could not execute query: FEHLER:
 Relation »public.$3« existiert nicht
 Command was: ALTER INDEX public.$3 OWNER TO mh1004;

Hmm.  This looks like a pg_dump bug, ie, issuing ALTER OWNER commands
for the wrong index name (or, perhaps, issuing them before having
created the index ... do you use any indexes named like that?).

Which pg_dump version did you use to make the dump?  The usual
recommendation is to use the newer version's pg_dump in this sort
of situation.

BTW, you should *not* be updating to 8.0.1.  8.0.6 is the latest release
in that series.  It is almost never a good idea to be running anything
but the latest minor release in a series.  (The fact that you're still
on 7.3.1 doesn't speak well for your attentiveness to updates either.
We don't make minor releases just to keep busy, you know.)

regards, tom lane

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

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

Re: [GENERAL] Moving PostgreSQL data directory on Windows

[Doug McNaught]

Ottó Havasvölgyi [EMAIL PROTECTED] writes:

 Hello,

 I would like to move the data directory to another location. I have done this:
 1. Stop PostgreSQL
 2. Move data directory
 3. Create a PGDATA env. variable to the new location
 4. Start PostgreSQL

 And it cannot start, because it cannot find postgresql.conf. (in Event log)
 What should I do now?


Dumb question, but are you sure you exported the PGDATA variable?  Is
it pointing to the directory that actually contains postgresql.conf?

If you still have problems, post the exact error message that appears
in the logs.

-Doug

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

Re: [GENERAL] Moving PostgreSQL data directory on Windows

[Magnus Hagander]

 Hello,
 
 I would like to move the data directory to another location. 
 I have done this:
 1. Stop PostgreSQL
 2. Move data directory
 3. Create a PGDATA env. variable to the new location 4. Start 
 PostgreSQL
 
 And it cannot start, because it cannot find postgresql.conf. 
 (in Event log) What should I do now?

Are you running it as a service:? If so you need to reconfigure the service. 
Unfortunatly,the only way to do this is using regedit. Go to
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-8.1

Open the value ImagePath and change the data directory.

//Magnus

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

   http://archives.postgresql.org

Re: [GENERAL] Moving PostgreSQL data directory on Windows

[Doug McNaught]

Doug McNaught [EMAIL PROTECTED] writes:

 Dumb question, but are you sure you exported the PGDATA variable?  Is
 it pointing to the directory that actually contains postgresql.conf?

 If you still have problems, post the exact error message that appears
 in the logs.

Duh, I didn't read the subject line and assumed it was on Unix.  :)

-Doug

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

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

[GENERAL] Temporary tables

[Raymond O'Donnell]

Hi all,

When you create a temporary table using the CREATE TEMPORARY TABLE 
AS... syntax, does the table get created (but left empty) if the 
query returns no rows?

I'm seeing funny behaviour, and don't know whether it's my incomplete 
understanding or whether something weird really is happening. Here's 
a test case.

-[begin]-

create table t1(f1 int4);

create or replace function testfunc() returns int4 as
$$
declare
  TempInt int4;
begin
  create temporary table TestTbl as
select * from t1;
  select count(*) into TempInt from TestTbl;
  drop table TestTbl;

  return TempInt;
end;
$$ language 'plpgsql';

-[end]-

The first time I call select testfunc();, I get 0 as expected. 
However, subsequent calls return an error:

ERROR:  relation with OID 80845 does not exist
CONTEXT:  SQL statement SELECT  count(*) from TestTbl
PL/pgSQL function testfunc line 7 at select into variables

Why should the temporary table be there the first time around and not 
after that, since it's created anew at the start of the function?

Thanks for any help!

--Ray O'Donnell

-
Raymond O'Donnell http://www.galwaycathedral.org/recitals
[EMAIL PROTECTED]  Galway Cathedral Recitals
-


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

   http://archives.postgresql.org

Re: [GENERAL] PostgreSQL Top 10 Wishlist

[Aly Dharshi]

10.
Or an alternative to views where tables can be defined with virtual
fields which point to functions. So for example I can say:
  SELECT balance, name FROM customers WHERE balance  0;
...where balance actually performs a behind the scenes JOIN against a
transactions table and totals the customers credits and debits. I
realize views can do this but for adding a single dynamic field they
are cumbersome and correct me if I'm wrong but I don't think you can
UPDATE against a view.


You are wrong, you can make updatable views.


	Yes you can but not out of the box. You have to write some rules to make this 
go, isn't it ?


Aly.

--
Aly S.P Dharshi
[EMAIL PROTECTED]

 A good speech is like a good dress
  that's short enough to be interesting
  and long enough to cover the subject

---(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] Plans for 8.2?

[Aly Dharshi]

Active, active as in multi-master?


More like a Oracle grid system. 10g.

--
Aly S.P Dharshi
[EMAIL PROTECTED]

 A good speech is like a good dress
  that's short enough to be interesting
  and long enough to cover the subject

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

Re: [GENERAL] PostgreSQL Top 10 Wishlist

[Joshua D. Drake]

You are wrong, you can make updatable views.


Yes you can but not out of the box. You have to write some rules to 
make this go, isn't it ?


I think the point is that out of the box, yes you can create an 
updateable view using rules.


You can not just say: create view and have it updateable but the rules 
are rather trivial.


Joshua D. Drake



Aly.




--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

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

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

Re: [GENERAL] Plans for 8.2?

[Joshua D. Drake]

Aly Dharshi wrote:



Active, active as in multi-master?


More like a Oracle grid system. 10g.


Heh. Well here is the thing. Oracle has billions of dollars.

Plus.

1. We really don't compete against Oracle grid. The people that *need* 
Oracle grid, are going to buy Oracle grid.


2. We don't need to compete against Oracle grid as nobody else that 
could be considered competition has it either.


3. We will get there in time but we have more important things to worry 
about.


I would rather us be a 100% solution for 98% of the people then a 100% 
solution for 2%.


Sincerely,

Joshua D. Drake








--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

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

Re: [GENERAL] Temporary tables

[Michael Fuhr]

On Fri, Jan 13, 2006 at 05:23:42PM -, Raymond O'Donnell wrote:
 The first time I call select testfunc();, I get 0 as expected. 
 However, subsequent calls return an error:
 
 ERROR:  relation with OID 80845 does not exist
 CONTEXT:  SQL statement SELECT  count(*) from TestTbl
 PL/pgSQL function testfunc line 7 at select into variables
 
 Why should the temporary table be there the first time around and not 
 after that, since it's created anew at the start of the function?

See 'Why do I get relation with OID # does not exist errors
when accessing temporary tables in PL/PgSQL functions?' in the FAQ:

http://www.postgresql.org/docs/faqs.FAQ.html#item4.19

See also numerous past discussions in the list archives.

-- 
Michael Fuhr

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

Re: [GENERAL] PostgreSQL Top 10 Wishlist

[Jaime Casanova]

On 1/13/06, Aly Dharshi [EMAIL PROTECTED] wrote:

 
  10.
  Or an alternative to views where tables can be defined with virtual
  fields which point to functions. So for example I can say:
SELECT balance, name FROM customers WHERE balance  0;
  ...where balance actually performs a behind the scenes JOIN against a
  transactions table and totals the customers credits and debits. I
  realize views can do this but for adding a single dynamic field they
  are cumbersome and correct me if I'm wrong but I don't think you can
  UPDATE against a view.
 
  You are wrong, you can make updatable views.

Yes you can but not out of the box. You have to write some rules to 
 make this
 go, isn't it ?

Aly.

 --
 Aly S.P Dharshi
 [EMAIL PROTECTED]


There's some work on making views updatable, but even if the patch get
ready and accepted it will be just for simple views (just one table,
at least for the first release) for complex queries you still have to
write the rules...

The poster mentioned he want a column that behinds the scene do some
calculations, and that it's obviously a function as a column of the
view... and there is no chance that that column will be updatable
(rules created manually nor automatic)

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---(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] Moving PostgreSQL data directory on Windows

[Tom Lane]

Magnus Hagander [EMAIL PROTECTED] writes:
 [ changing PGDATA doesn't seem to work on Windows ]

 Are you running it as a service:? If so you need to reconfigure the service. 
 Unfortunatly,the only way to do this is using regedit. Go to
 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-8.1

Is this documented anywhere?  Certainly our SGML manuals don't talk
about anything except the Unix case.  Perhaps there needs to be a
chapter in the Server Administration section about managing a Windows
PG server.  Or is all that in the installer docs?

regards, tom lane

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

Re: [GENERAL] Error when inserting millions of records

[Qingqing Zhou]

Rudolph [EMAIL PROTECTED] wrote
 Me and a friend are testing some operations for a search application in
 PostgreSQL. In version 8.1 my friend tried to copy the result of a join
 into another table, about 45.000.000 (small) records. This is the
 result:

 testsearch= insert into t_documentword2 (SELECT document_id,
 t_word2.id,
 frequency from t_documentword, t_word2 where t_documentword.word =
 t_word2.word);
 server closed the connection unexpectedly
   This probably means the server terminated abnormally
   before or while processing the request.
 The connection to the server was lost. Attempting reset: Failed.

 He got the same result in version 7.5.15.


Do we have a 7.5.15 version? You can get the right version using SELECT 
version();.

What's the structure of t_documentword2 and t_documentword? If you got core 
dumps, it will be useful to print the backtrace.

Regards,
Qingqing 



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

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

[GENERAL] Encoding Problem

[Marcos]

Hi.

I'm receiving this error message:

invalid UTF-8 byte sequence detected

SET search_path TO administracao,public;

CREATE OR REPLACE FUNCTION inserirTela(text,text,text) RETURNS text AS '
DECLARE
sName ALIAS FOR $1;
sDesc ALIAS FOR $2;
cSist ALIAS FOR $3;
BEGIN
SET search_path TO administracao,public;

INSERT INTO telas (nome_tela,descricao,sistema) VALUES( sName, sDesc, 
cSist );
RETURN ''Registro inserido com sucesso'';
EXCEPTION 
WHEN unique_violation THEN RETURN ''Ja existe uma tela com este nome'';
WHEN others THEN RETURN ''Erro ao inserir registro'';
END;
' LANGUAGE plpgsql;

The problem is in the RETURN messages... if it's oculted the error not occur.

What i do?

My database is UTF-8.

Thanks.


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

Re: [GENERAL] PostgreSQL Top 10 Wishlist

[John Gray]

Robert,

Others have covered some of your topics.

On Thu, 12 Jan 2006 20:51:44 -0800, rlee0001 wrote:

 7.
 An XML field type and associated XPath/DOM functions. Other exotic
 field types like Image might be nice for some people as well. But XML
 would be awesome.
 

The contrib module xml2 (in the core source distribution and also
built in some bianry versions) provides some support functions for XPath,
where you store the documents in columns of type text. There is also a
project, XpSQL that does decomposition of XML documents - see
http://gborg.postgresql.org/project/xpsql/projdisplay.php

 Just some stupid ideas.

It's always valuable to see what people are interested in - but there are
sometimes reasons why it's not seen as practical or appropriate (standards
compliance, namespace pollution etc.) to include a requested feature.

Regards

John


---(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 Top 10 Wishlist

[David Fetter]

On Fri, Jan 13, 2006 at 02:23:29PM +0100, Martijn van Oosterhout wrote:
 On Thu, Jan 12, 2006 at 08:51:44PM -0800, rlee0001 wrote:
  1.
  Two new special variables in triggers functions (TG_STATEMENT and
  TG_EFFECTIVE_STATEMENT) which returns the statement that triggered
  the trigger.
 
 Which would that be? The statement that directly invoked the
 trigger, or the one the user typed, or would you want a list of all
 of them?

This would be pretty cool either way.

  4.
  The ability to view the DDL for objects. Logically I know that
  this HAS to be possible already but I can't figure it out and a
  search of the documentation doesn't mention it. You can do this in
  EMS PostgreSQL Manager but I can't figure out how to query it on
  my own.
 
 psql gives you that. If you give -E it'll even show you the queries
 it uses to make the info. Also, the information_schema should have
 most stuff you want.

Actually, this is a TODO :)

  5.
  The SET and ENUM data types. I know MySQL is cheap and evil but
  even it has them. Both are really just Integers attached to some
  Metadata. You have no idea how many descriptor tables I have for
  simple enumerations.  Some have less than 10 items in them!
 
 Someone actually mosted a patch that did this. Funnily enough, it'd
 probably be implemented by creating seperate tables for each ENUM to
 do the lookup. It's just suger-coating really...

But it's *tasty* sugar coating, and you don't have to mess around with
extra messing to get the ordering you've set.

  6.
  Cross database queries. I'd like to be able to query a MS SQL
  Server database from within PL/PGSQL. Or at least other databases
  on the same server. Granted it might not be possible to JOIN,
  UNION or Subquery against them but I'd at least like to be able to
  perform a query and work with the results. We currently have to
  feed a postgresql database daily snapshots the live Microsoft SMS
  network data using a DTS package. Being able to access the Live
  data (especially if we could join against it) would be awesome.
 
 dblink does it for postgres DBs, there are similar modules for
 connections to other databases.

DBI-Link for other data sources.

  8.
  The ability to use procedural-language extensions everywhere, not just
  in functions.
 
 Like where? Give an example.

I'm thinking in-line anonymous blocks of your favorite PL.

  9.
  The ability to nest fields within fields. For example:
PERSON
   NAME
   LAST
   FIRST
   PHONE
 
 You can sort of do this, using rowtypes. Havn't nested more than one
 level though. Not sure why you'd want this though. A database stores
 data, presentation is the application's job.

WITH RECURSIVE is on the TODO list.  Maybe he's referring to that.

 So, create a type that does that. PostgreSQL is extensible. It's got
 data types for ISBNs, Internet addresses and even an XML document
 type.  Compared to that a simple phone number field would be
 trivial.

I'd say a phone number is *much* harder to do right if you're storing
phone numbers from more than one country.

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778

Remember to vote!

---(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] Concept about stored procedures

[Jim C. Nasby]

Using stored procedures as an API to the database is a perfectly
acceptable way of doing things, and provides some nice benefits:

You can lock out all other access to tables. This limits what can be
done if the application gets compromised.

It means you don't have to worry about people embedding SQL into the
bowels of the application.

It provides a well-defined API into the database.

As an added bonus, in 8.1 there is syntax checking done on
functions when they are created. This makes catching bugs much easier...
previously you'd have to actually run the code to see if there were any
syntax errors. Now, if you use functions, syntax errors will be found as
soon as you load the code into the database. This feature alone makes
using stored procs as an API worth it.

On Wed, Jan 11, 2006 at 05:13:01PM +, Marcos wrote:
 Hi,
 
 I'm developing one application using this wonderful Database, and I've
 like of use the concepts correctly.
 
 Then, i decided that all my SQL statements will be in database using the
 procedure language (plpgsql or plpython), I go create functions for all
 interactions in database, and use  in the my application the SELECT for
 call him.
 
 Is its the correct way of working? Or the correct way is create
 procedure functions for big and complex statements?
 
 The application will work with a large Database and will have many
 simultaneous access.
 
 Very Thanks
 
 
 ---(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
 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

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

Re: [GENERAL] Create Produre for DROP row

[Jim C. Nasby]

On Wed, Jan 11, 2006 at 03:40:22PM -0600, Guy Rouillier wrote:
 Marcos wrote:
  Hi,
  
  Does have possible create one procedure to delete a row that return
  TRUE if the deletion was success or FALSE if a error occur? 
  
  The procedure for deletion I already create but I don't know how
  detect if the deletion was success executed. 
 
 How do you define success?  A delete can do nothing and be considered
 successfully executed as long as no DB errors occur.  If all you want to
 know is whether or not DB errors occurred, then Jaime supplied a
 solution to that.

I suspect http://lnk.nu/postgresql.org/7ma.html will be of use to the
original poster, in particular FOUND.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(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] Plans for 8.2?

[Jim C. Nasby]

On Fri, Jan 13, 2006 at 01:59:19AM -0700, Michael Fuhr wrote:
 On Fri, Jan 13, 2006 at 01:49:02AM -0700, Michael Fuhr wrote:
  On Fri, Jan 13, 2006 at 09:30:22AM +0100, H.J. Sanders wrote:
   Just one request that would make the transition from another
   great database to PostgreSQL a lot easier:
   
   SET LOCK MODE TO WAIT n
   
   n = the max.time in second to wait.
  
  Will statement_timeout suffice?
 
 (I'm not implying that statement_timeout is equivalent, I'm just
 wondering if you might be able to use it in certain circumstances.)

It strikes me that if we had a way to abort a statement on another
backend, you could abort anything that's been waiting more than x
seconds for a lock via an external process watching pg_locks. Of course,
that would be much more cumbersom than SET LOCK MODE TO WAIT n...
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

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

Re: [GENERAL] Plans for 8.2?

[Jaime Casanova]

On 1/13/06, Jim C. Nasby [EMAIL PROTECTED] wrote:
 On Thu, Jan 12, 2006 at 10:36:39AM -0800, Joshua D. Drake wrote:
  Mikael Carneholm wrote:
  In terms of statistics we do have statistics and exhaustive logging that
  can provide you with all of that information. Is there something
  specific that
  the information already provided really doesn't give you?
  
  Can you give an example query for list all queries executed since 12.00
  AM, order by block_reads desc? What I'm aiming for
  is the ability to turn measuring on, regression test my application,
  turn measuring off again, and list the most offensive queries executed
  during the regression test. I know of at least one other DBMS that is
  capable of this...won't mention which one :)
 
  You can use timestamp and one of the duration logging options for this.

 But AFAIK that won't provide any information on IO used, or even blocks
 read. Not to mention that parsing the logs is a PITA.


There has been announce recently about a project for analizing slow queries:
http://archives.postgresql.org/pgsql-announce/2006-01/msg7.php

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---(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] Plans for 8.2?

[Michael Fuhr]

On Fri, Jan 13, 2006 at 03:40:48PM -0600, Jim C. Nasby wrote:
 It strikes me that if we had a way to abort a statement on another
 backend, you could abort anything that's been waiting more than x
 seconds for a lock via an external process watching pg_locks. Of course,
 that would be much more cumbersom than SET LOCK MODE TO WAIT n...

There's pg_cancel_backend(), but a mechanism like you describe seems
subject to race conditions: by the time you decide to cancel a query
and send the signal, the offending query might have completed and
you end up cancelling some subsequent query that just started.

-- 
Michael Fuhr

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

Re: [GENERAL] Plans for 8.2?

[Jim C. Nasby]

On Fri, Jan 13, 2006 at 03:15:06PM +0100, Mikael Carneholm wrote:
 
  Too bad - I think that will keep a lot of potential users from
  evaluating Pg as a serious alternative. Good or bad, decide for
  yourself :)
 
 Why on earth should that be?
 
 Citing Baldur Norddahl 
 (http://archives.postgresql.org/pgsql-general/2006-01/msg00597.php):
 
 I will also point out that none of the replication solutions have the same 
 solid reputation as postgresql. As long the postgresql team will not endorse 
 a replication solution, you can not expect people to put the same trust in 
 these solutions as we put into postgresql itself.

I'm not really sure what you're looking for here. None of the
replication solutions have the same reputation as PostgreSQL itself
because they're both newer than PostgreSQL itself and used by a much
smaller number of people.

If you want to increase the reputation of a replication solution, it's
going to take something other than trying to get core to put out some
kind of endorsement. Case studies of real-world users is something that
would help. Showing what kind of test coverage there is wouldn't hurt.
Performance tests would be good.

In other words, if promoting replication is important to you, there's
plenty of things you can do that will help on that front. But as others
have said, the various replication solutions are going to have to stand
(or fall) on their own merits.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [GENERAL] Plans for 8.2?

[Jim C. Nasby]

On Fri, Jan 13, 2006 at 04:49:02PM -0500, Jaime Casanova wrote:
 There has been announce recently about a project for analizing slow queries:
 http://archives.postgresql.org/pgsql-announce/2006-01/msg7.php

Sure, but it still means installing an external tool, which requires
PHP, which isn't trivial to install in it's own right. And afaik there's
still no way to find out how much IO each query did, how much CPU was
spent, if any sorts overflowed, etc., etc.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [GENERAL] Plans for 8.2?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


Easy everyone. Let's not bite the newcomers too hard here.

 2.  Define endorse.  Does that mean in the backend?  So
 everyone has to pay the performance penalty even though they won't
 all use it?  Even though no other database system makes you make that
 compromise?

I would presume that at least packaged with PG (in the contrib section)
would be a good start. A prominent, east to find link to Slony on
the website would help too.

I just did a test to see what comes up when I typed replication in
the search box at postgresql.org. Got a 503 error. We really need to
work on that. Bad enough we don't use Postgres to do the searching.

I'd better stop here before I start ranting myself. I didn't expect
that 503 error when I started this letter.

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200601131734
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-BEGIN PGP SIGNATURE-

iD8DBQFDyCt4vJuQZxSWSsgRAkXZAJ4hvwlENtOxGPh1x+vNu3++izLQCQCgsqCa
rW1MUxPxDqYFbdgontgxuwk=
=ZlIa
-END PGP SIGNATURE-



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

   http://archives.postgresql.org

[GENERAL] Unable to connect to a dabase

[Sally Sally]

I had to kill a vacuum in the middle with -9. I shut down and restarted the 
postgres server several times after that but I am unable to connect to the 
db that I was initially running vacuum on
I'm doing psql dbname and it hangs for a while. I'm still waiting. Any 
ideas?

Thanks



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

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

Re: [GENERAL] Plans for 8.2?

[Jim C. Nasby]

On Fri, Jan 13, 2006 at 10:39:06PM -, Greg Sabino Mullane wrote:
 
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1
 
 
 Easy everyone. Let's not bite the newcomers too hard here.
 
  2.  Define endorse.  Does that mean in the backend?  So
  everyone has to pay the performance penalty even though they won't
  all use it?  Even though no other database system makes you make that
  compromise?
 
 I would presume that at least packaged with PG (in the contrib section)
 would be a good start. A prominent, east to find link to Slony on
 the website would help too.

Why just Slony? There's at least 2 other free replication solutions I
can think of off the top of my head, and I'm sure I'm missing some.

And there was rather extensive discussion about contrib on -hackers
about 6 months ago. IIRC the decision was that the only reason to put
something in contrib was if it was either dependant on specific backend
code or if it was targeted for inclusion into the backend.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [GENERAL] Unable to connect to a dabase

[Jim C. Nasby]

Please don't hijack threads fo new questions.

On Fri, Jan 13, 2006 at 10:45:51PM +, Sally Sally wrote:
 I had to kill a vacuum in the middle with -9. I shut down and restarted the 
 postgres server several times after that but I am unable to connect to the 
 db that I was initially running vacuum on
 I'm doing psql dbname and it hangs for a while. I'm still waiting. Any 
 ideas?

What's the logfile say about it?
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

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

Re: [GENERAL] Plans for 8.2?

[Joshua D. Drake]

Jim C. Nasby wrote:

On Fri, Jan 13, 2006 at 10:39:06PM -, Greg Sabino Mullane wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


Easy everyone. Let's not bite the newcomers too hard here.


2.  Define endorse.  Does that mean in the backend?  So
everyone has to pay the performance penalty even though they won't
all use it?  Even though no other database system makes you make that
compromise?

I would presume that at least packaged with PG (in the contrib section)
would be a good start. A prominent, east to find link to Slony on
the website would help too.


Why just Slony? There's at least 2 other free replication solutions I
can think of off the top of my head, and I'm sure I'm missing some.


Slony is the only free OSS postgreSQL replication solution that I would 
ever suggest to someone.


However if the project is going to start suggesting replication 
solutions it should suggest all of them.


Sincerely,

Joshua D. Drake


--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

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

  http://archives.postgresql.org

Re: [GENERAL] Plans for 8.2?

[Andrew Sullivan]

On Fri, Jan 13, 2006 at 10:39:06PM -, Greg Sabino Mullane wrote:
 
 I would presume that at least packaged with PG (in the contrib section)
 would be a good start. A prominent, east to find link to Slony on

But in Slony's case, that'd be a regression, not an improvement.  It
is designed, on purpose, as a bolt-on.  That's a feature, not a bug. 
It allows you to do version upgrades with just a few minutes'
switchover time, to begin with, which is something that we don't
otherwise have.

If we want to fix the in the tarball, or it's not real, we need to
continue to make packages easy to install.  Nobody thinks that the
DBI is some sort of stupid tacky not-ready tool just because every
installation of Perl doesn't have it automatically.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton

---(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] Unable to connect to a dabase

[Sally Sally]

I'm sorry that was not intentional.




From: Jim C. Nasby [EMAIL PROTECTED]
To: Sally Sally [EMAIL PROTECTED]
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unable to connect to a dabase
Date: Fri, 13 Jan 2006 16:55:16 -0600

Please don't hijack threads fo new questions.

On Fri, Jan 13, 2006 at 10:45:51PM +, Sally Sally wrote:
 I had to kill a vacuum in the middle with -9. I shut down and restarted 
the
 postgres server several times after that but I am unable to connect to 
the

 db that I was initially running vacuum on
 I'm doing psql dbname and it hangs for a while. I'm still waiting. Any
 ideas?

What's the logfile say about it?
--
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

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




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

Re: [GENERAL] Unable to connect to a dabase

[Jim Nasby]

Please do group replies so the list gets cc'd.

 From: Sally Sally [mailto:[EMAIL PROTECTED]
 The main reason I decided to kill the vacuum was because 
 there were several 
 postmaster processes spewed from scripts trying to access the 
 database and 
 they were all hanging. After killing the vacuum I decided to 
 restart the 
 server. However because of the hanging processes it was not 
 letting me shut 
 down so I had to kill the postmaster processes. The log gave 
 a warning about 
 shared memory corruption at this point. After this I started 
 the postmaster 
 did a proper shut down and restarted again. Now when I try to 
 connect to the 
 db it just hangs and there are no logs.
 
 The last log was
 LOG:  database system was shut down at 2006-01-13 18:04:05 EST
 LOG:  checkpoint record is at 505/C80F1010
 LOG:  redo record is at 505/C80F1010; undo record is at 0/0; 
 shutdown TRUE
 LOG:  next transaction ID: 1341794294; next OID: 1358710904
 LOG:  database system is ready

Well, that looks like a clean startup. I'm not sure if anyone else on the list 
has any ideas. Maybe getting a stack trace of psql trying to connect would shed 
some light...

Does a backend get spawned when you try running psql? (Look at ps aux|grep 
postgres before and after running psql).

 From: Jim C. Nasby [EMAIL PROTECTED]
 To: Sally Sally [EMAIL PROTECTED]
 CC: pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Unable to connect to a dabase
 Date: Fri, 13 Jan 2006 16:55:16 -0600
 
 Please don't hijack threads fo new questions.
 
 On Fri, Jan 13, 2006 at 10:45:51PM +, Sally Sally wrote:
   I had to kill a vacuum in the middle with -9. I shut down 
 and restarted 
 the
   postgres server several times after that but I am unable 
 to connect to 
 the
   db that I was initially running vacuum on
   I'm doing psql dbname and it hangs for a while. I'm 
 still waiting. Any
   ideas?
 
 What's the logfile say about it?
--
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

[GENERAL] Apache::DBI and DBD::Pg

[Tyler MacDonald]

Apache::DBI claims that it will reconnect to a database if it's gone away.
DBD::Pg claims that it supports the ping method. However, when I restart my
database server while apache2 is running, all mod_perl pages that are
database driven return internal server errors, no matter how many times I
refresh, with errors like this:

[Fri Jan 13 23:46:28 2006] [error] [client 192.168.99.112] DBD::Pg::db
prepare_cached failed: FATAL: terminating connection due to administrator
command\nserver closed the connection unexpectedly\n\tThis probably means
the server terminated abnormally\n\tbefore or while processing the request.

Here's what I'm using:

DBI - 1.48
DBD::Pg - 1.42
mod_perl2   - 2.01
Apache::DBI - 0.9901


Has anybody run into this before? Any known workarounds/config changes I
need?

Thanks,
Tyler


---(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] Unable to connect to a dabase

[Joshua D. Drake]

LOG:  database system was shut down at 2006-01-13 18:04:05 EST
LOG:  checkpoint record is at 505/C80F1010
LOG:  redo record is at 505/C80F1010; undo record is at 0/0; 
shutdown TRUE

LOG:  next transaction ID: 1341794294; next OID: 1358710904
LOG:  database system is ready


Well, that looks like a clean startup. I'm not sure if anyone else on the list 
has any ideas. Maybe getting a stack trace of psql trying to connect would shed 
some light...

Does a backend get spawned when you try running psql? (Look at ps aux|grep 
postgres before and after running psql).


If you telnet to localhost 5432 is it listening or does it hang? If it 
hangs I would guess a firewall but I would think you would pick up on 
the unix socket.


J






From: Jim C. Nasby [EMAIL PROTECTED]
To: Sally Sally [EMAIL PROTECTED]
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unable to connect to a dabase
Date: Fri, 13 Jan 2006 16:55:16 -0600

Please don't hijack threads fo new questions.

On Fri, Jan 13, 2006 at 10:45:51PM +, Sally Sally wrote:
I had to kill a vacuum in the middle with -9. I shut down 
and restarted 

the
postgres server several times after that but I am unable 
to connect to 

the

db that I was initially running vacuum on
I'm doing psql dbname and it hangs for a while. I'm 

still waiting. Any

ideas?

What's the logfile say about it?

--
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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



--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

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

Re: [GENERAL] Moving PostgreSQL data directory on Windows

[Ottó Havasvölgyi]

Magnus,

It's a service, and it is 8.0.6. I have fixed the registry to this
value: C:\Program Files\PostgreSQL\8.0\bin\pg_ctl.exe runservice -N
pgsql-8.0 -D E:\PostgreSQL\data\

The service does not start.
This new directory definetely exists. But now there is no Event log
entry about the error. The log file says nothing because it is in the
data/pg_log directory.

Thanks,
Otto



2006/1/13, Magnus Hagander [EMAIL PROTECTED]:
  Hello,
 
  I would like to move the data directory to another location.
  I have done this:
  1. Stop PostgreSQL
  2. Move data directory
  3. Create a PGDATA env. variable to the new location 4. Start
  PostgreSQL
 
  And it cannot start, because it cannot find postgresql.conf.
  (in Event log) What should I do now?

 Are you running it as a service:? If so you need to reconfigure the service. 
 Unfortunatly,the only way to do this is using regedit. Go to
 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-8.1

 Open the value ImagePath and change the data directory.

 //Magnus


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

Re: [GENERAL] Plans for 8.2?

[Brendan Duddridge]

On Jan 13, 2006, at 4:00 PM, Joshua D. Drake wrote:



Why just Slony? There's at least 2 other free replication solutions I
can think of off the top of my head, and I'm sure I'm missing some.


Slony is the only free OSS postgreSQL replication solution that I  
would ever suggest to someone.


However if the project is going to start suggesting replication  
solutions it should suggest all of them.


Sincerely,

Joshua D. Drake



Why would you only recommend Slony? How does it compare to Sequoia or  
p/cluster? I have to admit that reading about Slony II sounded very  
good, but it's apparently far off from reality. What's the best  
solution that would work on OS X Server?


Thanks,


Brendan Duddridge | CTO | 403-277-5591 x24 |  [EMAIL PROTECTED]

ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9

http://www.clickspace.com 

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Plans for 8.2?

[Joshua D. Drake]

Sincerely,

Joshua D. Drake



Why would you only recommend Slony? How does it compare to Sequoia or 
p/cluster?


Well p/cluster is not OSS. Sequioa is but is query based and doesn't 
correctly deal with things like now().


I was speaking directly about OSS replication.

Sincerely,

Joshua D. Drake



--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

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

  http://archives.postgresql.org

Re: [GENERAL] Unable to connect to a dabase

[Sally Sally]

Yes a backend gets spewed. Actually it did connect me finally (I am not sure 
exactly how long it took). I checked back after about four hours and I was 
connected. I have also noticed in the past that after running vacuum the 
client (psql) takes longer than usual to connect.

Thanks all
sally



From: Joshua D. Drake [EMAIL PROTECTED]
To: Jim Nasby [EMAIL PROTECTED]
CC: Sally Sally [EMAIL PROTECTED], pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unable to connect to a dabase
Date: Fri, 13 Jan 2006 16:02:11 -0800



LOG:  database system was shut down at 2006-01-13 18:04:05 EST
LOG:  checkpoint record is at 505/C80F1010
LOG:  redo record is at 505/C80F1010; undo record is at 0/0; shutdown 
TRUE

LOG:  next transaction ID: 1341794294; next OID: 1358710904
LOG:  database system is ready


Well, that looks like a clean startup. I'm not sure if anyone else on the 
list has any ideas. Maybe getting a stack trace of psql trying to connect 
would shed some light...


Does a backend get spawned when you try running psql? (Look at ps aux|grep 
postgres before and after running psql).


If you telnet to localhost 5432 is it listening or does it hang? If it 
hangs I would guess a firewall but I would think you would pick up on the 
unix socket.


J






From: Jim C. Nasby [EMAIL PROTECTED]
To: Sally Sally [EMAIL PROTECTED]
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unable to connect to a dabase
Date: Fri, 13 Jan 2006 16:55:16 -0600

Please don't hijack threads fo new questions.

On Fri, Jan 13, 2006 at 10:45:51PM +, Sally Sally wrote:

I had to kill a vacuum in the middle with -9. I shut down

and restarted

the

postgres server several times after that but I am unable

to connect to

the

db that I was initially running vacuum on
I'm doing psql dbname and it hangs for a while. I'm

still waiting. Any

ideas?

What's the logfile say about it?

--
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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



--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

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




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

Re: [GENERAL] Moving PostgreSQL data directory on Windows

[Rodrigo Gonzalez]

Directory permissions for user the user running the service?

Ottó Havasvölgyi wrote:


Magnus,

It's a service, and it is 8.0.6. I have fixed the registry to this
value: C:\Program Files\PostgreSQL\8.0\bin\pg_ctl.exe runservice -N
pgsql-8.0 -D E:\PostgreSQL\data\

The service does not start.
This new directory definetely exists. But now there is no Event log
entry about the error. The log file says nothing because it is in the
data/pg_log directory.

Thanks,
Otto



2006/1/13, Magnus Hagander [EMAIL PROTECTED]:
 


Hello,

I would like to move the data directory to another location.
I have done this:
1. Stop PostgreSQL
2. Move data directory
3. Create a PGDATA env. variable to the new location 4. Start
PostgreSQL

And it cannot start, because it cannot find postgresql.conf.
(in Event log) What should I do now?
 


Are you running it as a service:? If so you need to reconfigure the service. 
Unfortunatly,the only way to do this is using regedit. Go to
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-8.1

Open the value ImagePath and change the data directory.

//Magnus

   



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

 



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

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