[GENERAL] How to add an INHERITS to an already populated table ?

[David Pradier]

Hi everybody,

is it possible to add some inheritance lively, without doing a
dump/restore ?

Some bits of information to explain why I'd like to do that :
I've got those big tables, without correct constraints, sometimes even
without foreign keys et with sometimes some problems of data corruption,
coming from the application part.
What I'd really like to do is to add to every important table some
inheritance to a table we have which contains the following information :
(created_by, modified_by, date_of_creation, date_of_modification).
And of course, I can't stop the server.

Best regards,
David

-- 
[EMAIL PROTECTED] - tel: 01.46.47.21.33 - fax: 01.46.47.21.37

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

   http://archives.postgresql.org

Re: [GENERAL] How to add an INHERITS to an already populated table

[Richard Huxton]

David Pradier wrote:

Hi everybody,

is it possible to add some inheritance lively, without doing a
dump/restore ?


Not AFAIK. Easiest solution is probably to script some ALTER TABLE ADD 
COLUMN commands.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 3: 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] a way to reset the postgres password to nothing..

[Richard Huxton]

bruce wrote:

hi...

i've set up a postrges db, and i can get to it using a user/passwd. however,
when i su into the postgres user, and i try to do a 'psql -U postgres' it
prompts me for the password.. i have no idea what the password would be.. i
created everything, and thought i had just given it a blank password...

any ideas as to how i can change this, or reset the passwd to nothing...


It's possible the installation setup a password. You might want to run a 
search for files called ".pgpass"


Update your pg_hba.conf to "trust" for local connections, connect as 
user postgres, set your password, undo your changes to pg_hba.conf.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] How to add an INHERITS to an already populated table ?

[Zlatko Matic]

Hi. Recently I have tried to do the same thing  and I coudn't include 
inheritence in existing tables. After a half of day of frustration, I have 
got an idea. I  have successfully done it by using EMS PostgreSQL Manager 
Lite (you can download it from the net).
There is an option "Duplicate" in EMS Manager, by which you can duplicate 
any table with all properties except foreign keys. During that process you 
can modify code (before Commit) so you can include inheritence in your new 
table. Then you delete original table, rename new table to old name and 
recreate foreign key...
Well, I'm a newbie, so maybe someone more experienced offer you some better 
advice. If not, this will work...

Bye.

- Original Message - 
From: "David Pradier" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, May 31, 2005 10:10 AM
Subject: [GENERAL] How to add an INHERITS to an already populated table ?



Hi everybody,

is it possible to add some inheritance lively, without doing a
dump/restore ?

Some bits of information to explain why I'd like to do that :
I've got those big tables, without correct constraints, sometimes even
without foreign keys et with sometimes some problems of data corruption,
coming from the application part.
What I'd really like to do is to add to every important table some
inheritance to a table we have which contains the following information :
(created_by, modified_by, date_of_creation, date_of_modification).
And of course, I can't stop the server.

Best regards,
David

--
[EMAIL PROTECTED] - tel: 01.46.47.21.33 - fax: 01.46.47.21.37

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

  http://archives.postgresql.org




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

Re: [GENERAL] How to add an INHERITS to an already populated table

[Oleg Bartunov]

look on pg_inherits table and pg_class.relhassubclass.
More info http://www.pgsql.ru/db/mw/msg.html?mid=2044343


On Tue, 31 May 2005, Richard Huxton wrote:


David Pradier wrote:

Hi everybody,

is it possible to add some inheritance lively, without doing a
dump/restore ?


Not AFAIK. Easiest solution is probably to script some ALTER TABLE ADD COLUMN 
commands.





Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

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

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

Re: [GENERAL] How to add an INHERITS to an already populated table

[Oleg Bartunov]

On Tue, 31 May 2005, Oleg Bartunov wrote:


look on pg_inherits table and pg_class.relhassubclass.
More info http://www.pgsql.ru/db/mw/msg.html?mid=2044343



example:

create table t (i int4);
create table t1 (i int4);
create table t2 (i int4);

-- mark 't' has children tables
update pg_class set relhassubclass='t' where relname='t';
-- get oid of child table 't1'
select relfilenode from pg_class where relname='t1';
-- get oid of parent table
select relfilenode from pg_class where relname='t';
-- add inheritance t-t1 
insert into pg_inherits values(15769046,15769044,1);

-- get oid of child table 't2'
select relfilenode from pg_class where relname='t2';
-- add inheritance t-t2
insert into pg_inherits values(15769048,15769044,1);

--test
test=# explain analyze select * from t;
  QUERY PLAN 
--

 Result  (cost=0.00..88.20 rows=5820 width=8) (actual time=0.057..0.057 rows=0 
loops=1)
   ->  Append  (cost=0.00..88.20 rows=5820 width=8) (actual time=0.044..0.044 
rows=0 loops=1)
 ->  Seq Scan on t  (cost=0.00..29.40 rows=1940 width=8) (actual 
time=0.008..0.008 rows=0 loops=1)
 ->  Seq Scan on t1 t  (cost=0.00..29.40 rows=1940 width=8) (actual 
time=0.007..0.007 rows=0 loops=1)
 ->  Seq Scan on t2 t  (cost=0.00..29.40 rows=1940 width=8) (actual 
time=0.006..0.006 rows=0 loops=1)
 Total runtime: 0.171 ms
(6 rows)

-- check if alter table works
alter table t add column x real;
test=# \d t
   Table "public.t"
 Column |  Type   | Modifiers 
+-+---

 i  | integer |
 x  | real|

test=# \d t1
  Table "public.t1"
 Column |  Type   | Modifiers 
+-+---

 i  | integer |
 x  | real| 
Inherits: t


test=# \d t2
  Table "public.t2"
 Column |  Type   | Modifiers 
+-+---

 i  | integer |
 x  | real| 
Inherits: t









On Tue, 31 May 2005, Richard Huxton wrote:


David Pradier wrote:

Hi everybody,

is it possible to add some inheritance lively, without doing a
dump/restore ?


Not AFAIK. Easiest solution is probably to script some ALTER TABLE ADD 
COLUMN commands.





Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

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

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



Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] pg_get_serial_sequence and table inheritence

[Timothy Perrigo]

Is there anything similar to pg_get_serial_sequence that will work  
with tables that have an inherited serial column?  For example, if I  
have 2 tables:


create table base (
idserial not null primary key
);

and

create table derived (
stufftext,
constraint derived_pkey primary key(id)
) inherits (base);

I'd like to be able to call pg_get_serial_sequence passing "derived"  
for the table and "id" for the sequence column (to get, in this case  
"base_id_seq").  If nothing like this currently exists, any  
suggestions on how I could write a plpgsql function to get this  
behavior?


Thank you for any suggestions!
Tim



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

  http://archives.postgresql.org

[GENERAL] database auto-commit

[FERREIRA, William (COFRAMI)]

hi

i writing a j2ee application working with postgresql and i meet a problem with 
autocommit
from my j2ee application i call a perl function and i get an error :
in french : Les Large Objects ne devraient pas être utilisés en mode 
auto-commit.
in english : Large Objects should not be used in mode auto-committed.

and i didn't find how to disable autocommit.

Thanks in advance

William

This mail has originated outside your organization, either from an external 
partner or the Global Internet. Keep this in mind if you answer this message.

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] How to add an INHERITS to an already populated table

[Alvaro Herrera]

On Tue, May 31, 2005 at 04:53:46PM +0400, Oleg Bartunov wrote:
> On Tue, 31 May 2005, Oleg Bartunov wrote:
> 
> >look on pg_inherits table and pg_class.relhassubclass.
> >More info http://www.pgsql.ru/db/mw/msg.html?mid=2044343
> >
> 
> example:
> 
> create table t (i int4);
> create table t1 (i int4);
> create table t2 (i int4);
> 
> -- mark 't' has children tables
> update pg_class set relhassubclass='t' where relname='t';
> -- get oid of child table 't1'
> select relfilenode from pg_class where relname='t1';
> -- get oid of parent table
> select relfilenode from pg_class where relname='t';
> -- add inheritance t-t1 
> insert into pg_inherits values(15769046,15769044,1);
> -- get oid of child table 't2'
> select relfilenode from pg_class where relname='t2';
> -- add inheritance t-t2
> insert into pg_inherits values(15769048,15769044,1);

Please note that the inheritance is not fully set -- if you discover
strange behavior e.g. when altering any of the tables, don't be
surprised.  In particular, you should set the attislocal and attinhcount
attributes in pg_attribute for the child tables; also pg_depend entries
are missing.  I don't know what else.

Also you definitely shouldn't be using relfilenode, but the real Oid of
the table (relfilenode is the filename only, not the internal identifier
of the table).

-- 
Alvaro Herrera ()
Essentially, you're proposing Kevlar shoes as a solution for the problem
that you want to walk around carrying a loaded gun aimed at your foot.
(Tom Lane)

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

Re: [GENERAL] How to add an INHERITS to an already populated table

[David Pradier]

Well, it seems very promising !
I think I'll make some tests and do it your way asap.

Thanks a lot !
Thanks to everybody else, too.

Best regards, David

-- 
[EMAIL PROTECTED] - tel: 01.46.47.21.33 - fax: 01.46.47.21.37

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

Re: [GENERAL] How to add an INHERITS to an already populated table

[David Pradier]

> Please note that the inheritance is not fully set -- if you discover
> strange behavior e.g. when altering any of the tables, don't be
> surprised.  In particular, you should set the attislocal and attinhcount
> attributes in pg_attribute for the child tables; also pg_depend entries
> are missing.  I don't know what else.
> 
> Also you definitely shouldn't be using relfilenode, but the real Oid of
> the table (relfilenode is the filename only, not the internal identifier
> of the table).

I think I'll make a script and propose it for revision to the list
before using it on any real database.

David
-- 
[EMAIL PROTECTED] - tel: 01.46.47.21.33 - fax: 01.46.47.21.37

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

[GENERAL] Accessing PostgreSQL from C++

[Jeff Brown]

Hi guys 

Is there some sort of C API available for PostgreSQL?

Cheers
Jeff

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

[GENERAL] tsearch2 + trigram pairing

[Viljo Marrandi]

Hello,

I'm trying to figure out how to get tsearch2 and pg_trgm working 
together nicely. I have successfully installed both of them on 
Postgresql 8.0.3.


Let's say I've one table 'info' with field 'words' in it. I created 
'words_idx tsvector' field from it and populated it with some data. 
Following README.pg_trgm I created new table for trigram like 'CREATE 
TABLE trgm_words AS ...' containing all unique words from 'words_idx'


Now the problem is, when I do trigram matching against 'trgm_words' 
table and find some matches, then I've no way to know to which record 
these belong in info table. Is there some easier or more better way to 
find it out, or do I have to make some subselect or something.


Thanks in advance,
Viljo

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

Re: [GENERAL] Using PG 8.0 with OpenSSL

[Kay-Uwe Genz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi Joshua,

you're right. But it's no problem for me, if I change the concept.

Thanks a lot.

Kay-Uwe Genz
-BEGIN PGP SIGNATURE-
Version: PGP 8.0.3 - not licensed for commercial use: www.pgp.com

iQA/AwUBQph3lpdFGcTeBMrrEQK1awCg7Fa0D2Zp86s394mHlCTxNyGB0GYAn2Pb
ddgHgK2VIDnr5vNcE8SiPCJ5
=8Ot0
-END PGP SIGNATURE-



Am 25.05.2005 um 19:10 schrieb Joshua D. Drake:



My PG running with OpenSSL and I have no problems  connecting from my 
laptop (running the client) to my desktop (running the server) over 
ethernet. But I wish, that PG use certificated connections. I've 
create certificates for every user (all user are in pg_hba.conf with 
hostssl), but I didn't find information, where I should save the 
certificates, in Users home directory?


I don't think that PG can do that. I think the SSL is server wide.

Sincerely,

Joshua D. Drake


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/





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

[GENERAL] For Tom Lane

[[EMAIL PROTECTED]]

From  : "Tom Lane" [EMAIL PROTECTED]
To  : "gabriele zelasco" [EMAIL PROTECTED]
Cc  : pgsql-general@postgresql.org
Date  : Thu, 26 May 2005 17:50:29 -0400
Subject : Re: [GENERAL] Locking rows

> "gabriele zelasco" <[EMAIL PROTECTED]> writes:
> > I would like to start a transaction with a sql function.
> > When user press "edit" button on my form, i would lock the current row.
> > After user has modified data on form, pressing "save" button I would save t=
> > he modified row by sql update function and so commit.
>


> This is widely considered a very bad way to design an application.
> Consider what happens when the user leaves for lunch, or otherwise
> lets the app sit for a long time.  See the list archives for prior
> discussions of the issue.
>
> But in any case, the answer to your question is to use "SELECT FOR
> UPDATE" to retrieve the row.  And you can't start a transaction
> inside a function, because by definition you'll already be in one.
> 
>   regards, tom lane
>

Thanks for answer Tom

"Consider what happens when the user leaves for lunch"

Well, I've already thought about it.But I'm working with
VS2003 and disconnected dataset.. so when user edit data
he's modifying an "old" disconnected row, while real updated row
is in the database..
So my strategy would be (as I already written):

1. refresh data recalling current row from database to the form's fields
2. lock the row
3. update modified data in the database through stored procedure (function)
4. commit and unlock the row

Have you another idea that could work better with disconnected objects ?



the answer to your question is to use "SELECT FOR UPDATE" to retrieve the row

Well, I've created a sql function with just sql command :
select * from table where condition FOR UPDATE
but it doesn't lock the row !

If I write "begin;" before command , inside function,
I get the error (begin is not allowed...)

So what I have to do if I would lock a row from inside vb net code?
Thanx in advance




Navighi a 4 MEGA e i primi 3 mesi sono GRATIS. 
Scegli Libero Adsl Flat senza limiti su http://www.libero.it



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

Re: [GENERAL] pg_get_serial_sequence and table inheritence

[Rafa Couto]

2005/5/31, Timothy Perrigo <[EMAIL PROTECTED]>:

> I'd like to be able to call pg_get_serial_sequence passing "derived"
> for the table and "id" for the sequence column (to get, in this case
> "base_id_seq").  If nothing like this currently exists, any
> suggestions on how I could write a plpgsql function to get this
> behavior?

  You have one sequence for every serial field in sequences (from psql: "\ds").
  

-- 
Rafa Couto (caligari)
mailto:[EMAIL PROTECTED]

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

Re: [GENERAL] enable_sort optimization problem

[dave]

tom lane wrote:


Why does it think that only 159 of the 132245 rows in outages will have
join partners in ipinterface?  The actual results look like they all do.
It might be worth looking at the pg_stats rows for the join columns to
see if there's something odd about the statistics.




Here are the pg_stats (as of today, I haven't done any analyzes or 
vacuums since the night of my first posting) for outages and ipinterface 
(I've obscured some addresses in the ipiddr row, and removed iphostname):


schemaname | tablename |  attname   |  null_frac  | avg_width | 
n_distinct 
|   
most_common_vals
| 
most_common_freqs 
|  
histogram_bounds   
| correlation

+---++-+---++---+---+-+-
public | outages   | outageid   |   0 | 4 
| -1 
|   
|   
| 
{201,14775,27621,39600,53231,66043,79629,92779,105267,119744,134644}
|0.390484
public | outages   | svclosteventid |   0 | 4 
|  -0.945011 | 
{280277,356772}   
| 
{0.00067,0.00067} 
| 
{17842,54830,81745,107030,135793,163240,191819,219223,268449,309128,371234} 
|0.390484
public | outages   | svcregainedeventid | 0.00033 | 4 
|  -0.958031 | 
{280279}  
| 
{0.00067} 
| 
{17844,54856,81787,107063,135821,163381,191856,219405,268450,309572,371494} 
|0.390818
public | outages   | nodeid |   0 | 4 
|396 | 
{962,397,191,185,377,139,237,378,1295,231}
| 
{0.017,0.0097,0.0087,0.0083,0.0083,0.0077,0.0077,0.0077,0.0077,0.007} 
| 
{3,158,178,206,236,258,293,316,358,395,1452}
|  -0.0325868
public | outages   | ipaddr |   0 |16 
|396 | 
{208,172.22.0.158,172.20.0.237,172.20.0.231,172.22.35.56,172.17.2.5,172.20.0.180,172.21.240.91,172.23.181.16,172.21.240.93}
| 
{0.017,0.0097,0.0087,0.0083,0.0083,0.0077,0.0077,0.0077,0.007

[GENERAL] Connecting to Postgres from LAN

[Andrus]

I installed Postgres 8 to Windows XP and added a line

hostall all 168.179.0.1/32  trust

to pg_hba.conf file


When connection from LAN to the Postgres with user name postgres I got error

no pg_hba.conf entry for host "168.179.0.10", user "postgres", database 
"mydb", SSL off


How  to enable connection from LAN ?

Andrus 



---(end of broadcast)---
TIP 3: 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] For Tom Lane (Msg-ID: <[EMAIL PROTECTED]>)

[[EMAIL PROTECTED]]

> "gabriele zelasco" <[EMAIL PROTECTED]> writes:
> > I would like to start a transaction with a sql function.
> > When user press "edit" button on my form, i would lock the current row.
> > After user has modified data on form, pressing "save" button I would save t=
> > he modified row by sql update function and so commit.
>


> This is widely considered a very bad way to design an application.
> Consider what happens when the user leaves for lunch, or otherwise
> lets the app sit for a long time.  See the list archives for prior
> discussions of the issue.
>
> But in any case, the answer to your question is to use "SELECT FOR
> UPDATE" to retrieve the row.  And you can't start a transaction
> inside a function, because by definition you'll already be in one.
> 
>   regards, tom lane
>

Thanks for answer Tom

"Consider what happens when the user leaves for lunch"

Well, I've already thought about it.But I'm working with
VS2003 and disconnected dataset.. so when user edit data
he's modifying an "old" disconnected row, while real updated row
is in the database..
So my strategy would be (as I already written):

1. refresh data recalling current row from database to the form's fields
2. lock the row
3. update modified data in the database through stored procedure (function)
4. commit and unlock the row

Have you another idea that could work better with disconnected objects ?



the answer to your question is to use "SELECT FOR UPDATE" to retrieve the row

Well, I've created a sql function with just sql command :
select * from table where condition FOR UPDATE
but it doesn't lock the row !

If I write "begin;" before command , inside function,
I get the error (begin is not allowed...)

So what I have to do if I would lock a row from inside vb net code?
Thanx in advance




Navighi a 4 MEGA e i primi 3 mesi sono GRATIS. 
Scegli Libero Adsl Flat senza limiti su http://www.libero.it



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

---(end of broadcast)---
TIP 3: 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] Missing numbers

[josue]

Hello list,

I need to track down the missing check numbers in a serie, table 
contains a column for check numbers and series like this:


dbalm=# select doc_numero,doc_ckseriesfk from bdocs where doc_cta=1
dbalm-# and doc_tipo='CHE' order by doc_numero;
 doc_numero | doc_ckseriesfk
+
 19200  | 856938
 19201  | 856938
 19215  | 856938
 19216  | 856938
 19219  | 856938

Missing numbers are:
from 19202 to 19214 and 19217,19218


Does anyone knows a way to get that done in SQL or plpgsql, thanks in 
advance



--
Sinceramente,
Josué Maldonado.

... "De hecho el paso de compilación a objeto suele atravesar una fase 
intermedia en que se genera un fichero en lenguaje ensamblador y se 
invoca al programa ensamblador del sistema." -- Tutorial de C


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

[GENERAL] System info in/through database on Windows 2000 Postres 8.x

[Oisin Glynn]

Hi,

I am wondering if there is a way to get simple (or more advanced ) system 
information from the database about the server.  Free disk space, Memory 
usage, CPU usage.  And any other  health of system information in general. 
I have done some archive searching but not turned up very much. This is 
Postgres 8.x on Windows 2000


Thanks in advance,

Oisin






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

Re: [GENERAL] Accessing PostgreSQL from C++

[Alvaro Herrera]

On Mon, May 30, 2005 at 11:31:17PM -0700, Jeff Brown wrote:
> Hi guys 
> 
> Is there some sort of C API available for PostgreSQL?

For C you can use libpq.  For C++, see libpqxx in gborg.org (or did it
move to pgfoundry.org?)

-- 
Alvaro Herrera ()
"Oh, great altar of passive entertainment, bestow upon me thy discordant images
at such speed as to render linear thought impossible" (Calvin a la TV)

---(end of broadcast)---
TIP 3: 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] interval integer comparison

[Havasvölgyi Ottó]

Hi all,

Pg 8.0.3 allows me to compare interval with integer, but I cannot see any
reasonable rule:

These are true:


1 < '1 days'::interval

2 > '1 days'::interval

999 > '1 days'::interval

1999 < '2 days'::interval

2000 != '2 days'::interval

2001 > '2 days'::interval
...

20999 < '21 days'::interval

21000 != '21 days'::interval

21001 > '21 days'::interval

and so on

Is this a bug?

Best Regards,
Otto



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

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

Re: [GENERAL] Accessing PostgreSQL from C++

[Havasvölgyi Ottó]

Jeff,

Yes, libpq. Look at the 27th chapter in the manual.

Otto


- Original Message - 
From: "Jeff Brown" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, May 31, 2005 8:31 AM
Subject: [GENERAL] Accessing PostgreSQL from C++


> Hi guys 
> 
> Is there some sort of C API available for PostgreSQL?
> 
> Cheers
> Jeff
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
> 
> 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Accessing PostgreSQL from C++

[Christopher Browne]

[EMAIL PROTECTED] (Jeff Brown) wrote:
> Is there some sort of C API available for PostgreSQL?

Yes, there is a C API available for PostgreSQL.  It is called libpq.

Was there some particular reason why you could not find it in the
documentation?  It has an entire chapter devoted to it in the
documentation tree...


-- 
output = ("cbbrowne" "@" "ntlug.org")
http://linuxdatabases.info/info/lsf.html
Rules of the Evil Overlord #25.  "No matter how well it would perform,
I  will never  construct any  sort  of machinery  which is  completely
indestructible  except  for   one  small  and  virtually  inaccessible
vulnerable spot." 

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

Re: [GENERAL] interval integer comparison

[Tom Lane]

=?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?= <[EMAIL PROTECTED]> writes:
> Pg 8.0.3 allows me to compare interval with integer, but I cannot see any
> reasonable rule:

> 1 < '1 days'::interval

The reason that doesn't fail outright is that both integer and
interval have implicit coercions to text.  So the only interpretation
the parser can find is to convert both sides to text and use the text <
operator.  As text comparisons your answers all make sense.

I've been arguing for a long time that we need to cut down on the number
of implicit coercions to text...

regards, tom lane

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

Re: [GENERAL] Missing numbers

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
josue <[EMAIL PROTECTED]> writes:

> Hello list,
> I need to track down the missing check numbers in a serie, table
> contains a column for check numbers and series like this:


> dbalm=# select doc_numero,doc_ckseriesfk from bdocs where doc_cta=1
> dbalm-# and doc_tipo='CHE' order by doc_numero;
>   doc_numero | doc_ckseriesfk
> +
>   19200  | 856938
>   19201  | 856938
>   19215  | 856938
>   19216  | 856938
>   19219  | 856938

> Missing numbers are:
> from 19202 to 19214 and 19217,19218


> Does anyone knows a way to get that done in SQL or plpgsql, thanks in
> advance

You could use something like that:

SELECT g.num
FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
  (SELECT max(doc_numero) FROM bdocs)) AS g(num)
LEFT JOIN bdocs ON bdocs.doc_numero = g.num
WHERE bdocs.doc_numero IS NULL


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

Re: [GENERAL] For Tom Lane

[Scott Marlowe]

On Fri, 2005-05-27 at 09:57, [EMAIL PROTECTED] wrote:

> 
> Thanks for answer Tom
> 
> "Consider what happens when the user leaves for lunch"
> 
> Well, I've already thought about it.But I'm working with
> VS2003 and disconnected dataset.. so when user edit data
> he's modifying an "old" disconnected row, while real updated row
> is in the database..
> So my strategy would be (as I already written):
> 
> 1. refresh data recalling current row from database to the form's fields
> 2. lock the row
> 3. update modified data in the database through stored procedure (function)
> 4. commit and unlock the row
> 
> Have you another idea that could work better with disconnected objects ?

While this ensures that the update is atomic, it doesn't ensure that no
one else is trying to edit it at the same time.

What you might want to do is either optimistically lock it, or use
application level locking.  To use optimistic locking, you'll need to do
something like make an md5 of all the fields being edited, then, right
before you write back the data, check to see if the md5 you created at
the beginning still matches by re-reading the data and md5ing it again. 
If it doesn't match, then you can throw a "mid air collision" error, so
to speak, and tell them that the record changed underneath them, or do
some kind of merging / or whatnot.

If you want to do application level locking, then create a field and use
that for locks.  Just make it a timestamp field and put in the current
time value when the lock is taken.  When the predetermined timeout
occurs, the user lock is removed by the next person to access it, or
offer them chance to, or email the original locker, etc...  Handle it
the way you want or need to.

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

[GENERAL] Primary key column numbers...

[Justin Tocci]

What I'm trying to do is
IF the relation given is a TABLE, give the primary key column numbers.
IF the relation is a VIEW, just give an array with a '1' in it.
ELSE NULL.

Later I'll put in some hocus-pocus to be more intelligent about VIEWs  
but right now this would do me fine.


I've read the docs on arrays and such, I just can't seem to put  
together nor cast an array that's compatible with int2vector. There  
was some notice to look at contrib/array but that has been removed in  
version 8, which is what I'm using.


This is what I have so far.

SELECT indkey
FROM (SELECT relname, indkey
FROM pg_catalog.pg_index join pg_catalog.pg_class
ON pg_index.indrelid = pg_class.oid
WHERE indisprimary=true
UNION
SELECT viewname, ('{1}')::int2vector[] as indkey
FROM pg_catalog.pg_views ) t
WHERE relname = ''

Result: ERROR: UNION/INTERSECT/EXCEPT could not convert type  
int2vector[] to int2vector


Any help here would be appreciated.

justin tocci
fort worth, tx

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

Re: [GENERAL] Connecting to Postgres from LAN

[Alexandre Lollini]

on 30/05/05 20:47, Andrus <[EMAIL PROTECTED]> wrote:

> I installed Postgres 8 to Windows XP and added a line
> 
> hostall all 168.179.0.1/32  trust
> 
> to pg_hba.conf file
> 
> 
> When connection from LAN to the Postgres with user name postgres I got error
> 
> no pg_hba.conf entry for host "168.179.0.10", user "postgres", database
> "mydb", SSL off
> 
> 
> How  to enable connection from LAN ?
> 
> Andrus 

The solution is to edit the postgresql.conf file
And then enable LAN connections.

But before doing so, I urge you :

Your line in pg_hba.conf as is is EXTREMELY INSECURE
I suggest to be more tight than "all" and "trust"

Use a comma separated list of users (exclude postgres)
Use a comma separated list of databases (excluding templates and pg_)

Use "password" instead of "trust", at least.

So create some users with limited grants, with a password, even if these are
script users.

No network can be trusted.

User postgres is (and should stay) the only one user capable of DROP
DATABASE x

I strongly recomment not to use user postgres for runtime remote
connections.


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

[GENERAL] postgreSQL 7.3.8, pg_dump not able to find large object

[Ron Snyder]

We've been getting errors similar to the following (the specific large
object that is "missing" is different every time) during our nightly
pg_dump:

pg_dump: dumpBlobs(): could not open large object: ERROR:  inv_open: large
object 48217896 not found

I found a suggestion
(http://archives.postgresql.org/pgsql-admin/2003-02/msg00326.php ) from Tom
L that this might indicate the index on pg_largeobject needed to be
reindexed, so we first put a test server into single user mode and did the
reindex. When we did the reindex on our test box and then immediately ran a
pg_dump everything worked great and it resolved our problem, so I do believe
that our index was messed up.  We then tried it on our production server,
but the regularly scheduled pg_dump failed with the error above.

Possibilities that I can think of:
1) pg_dump is failing because large objects are being added and removed
while the pg_dump is running
  (I think I've proved to myself that this is possible, by starting a
transaction in session1, deleting a blob in session2, and then trying to
\lo_export the blob from session1.)
2) something is so screwed up that causes the pg_largeobject index to get
corrupted (in a matter of 6 hours?)

Obviously we can't upgrade to a newer version of PostgreSQL until we can get
good backups, so somehow I need to get this resolved using this version.

I've considered patching pg_dump to not exit upon failure to retrieve a
blob, so that I can at least have a backup (albeit missing some of my
blobs), but can't help but think I'm not the first person to have come
across this problem (of having lots of blob activity occurring at the same
time as a pg_dump).  Are there better avenues for me to pursue?  

Thanks,
-ron




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] [PORTS] Major flood of mail to lists ...

[Mohan, Ross]

Wow, you mean mail besides my own is considering to be "junk"?

;-)

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marc G. Fournier
Sent: Tuesday, May 31, 2005 1:46 PM
To: pgsql-announce@postgresql.org
Cc: pgsql-general@postgresql.org; pgsql-hackers@postgresql.org; [EMAIL 
PROTECTED]; [EMAIL PROTECTED]; pgsql-docs@postgresql.org; [EMAIL PROTECTED]; 
pgsql-performance@postgresql.org; [EMAIL PROTECTED]; pgsql-sql@postgresql.org; 
pgsql-admin@postgresql.org
Subject: [PORTS] Major flood of mail to lists ...



Do to moderator error (namely, mine), several hundred messages (spread 
across all the lists) were just approved ...

Sorry for all the incoming junk :(


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

---(end of broadcast)---
TIP 3: 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: [ODBC] [GENERAL] how to unsubscribe

[Goulet, Dick]

 BTW: Who let all the spammers into the list(s)?

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe
Sent: Tuesday, May 24, 2005 10:06 AM
To: [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org; [EMAIL PROTECTED]
Subject: Re: [ODBC] [GENERAL] how to unsubscribe

On Tue, 2005-05-24 at 08:51, [EMAIL PROTECTED] wrote:
> I went to the link given to unsubscribe... (someone said it was
> SIMPLER), and I did not find it simple,.  I clicked and
> clicked tried all sorts of thingsbut...
>  
> no luck...
>  
> Then I sent emails with word unsubscribe both in the subject and in
> the body

The emails have to go to [EMAIL PROTECTED], not the list name...
 
> but no luck...
>  
> could someone please email to me the exact format to unsubscribe via
> email... 
>  
> sorry to be so dense...

Hey, we all have our days.

Try this page, and set it to unsubscribe.

http://www.postgresql.org/community/lists/subscribe

OR

send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]

Notice who you need to send it to, it's not the list name.

Good luck!

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

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

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

Re: [GENERAL] Primary key column numbers...

[Tom Lane]

Justin Tocci <[EMAIL PROTECTED]> writes:
> SELECT indkey
> FROM (SELECT relname, indkey
> FROM pg_catalog.pg_index join pg_catalog.pg_class
>  ON pg_index.indrelid = pg_class.oid
> WHERE indisprimary=true
> UNION
> SELECT viewname, ('{1}')::int2vector[] as indkey
> FROM pg_catalog.pg_views ) t
> WHERE relname = ''

> Result: ERROR: UNION/INTERSECT/EXCEPT could not convert type  
> int2vector[] to int2vector

I think you want just

  SELECT viewname, '1'::int2vector as indkey

for the second arm of the union.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] CREATE TEMP TABLE AS SELECT/ GET DIAGNOSTICS ROW_COUNT

[Alvaro Herrera]

On Tue, May 31, 2005 at 01:03:30AM -0400, Tom Lane wrote:

> So the initial evidence is that this was not an intentional change.
> Do we want to revert it?  The behavior has been in the field now for
> more than a full release cycle --- all 7.4.* releases behave this way
> --- so one could argue that we should leave it be.

Well, probably nobody has complained because nobody uses the behavior.
I think it's wrong to assume that people really depend on this behavior
-- they would be doing something like

SELECT some_columns ...
CREATE TABLE AS SELECT ...
GET DIAGNOSTICS row_count

and expect to get the row_count from the first SELECT rather than CREATE
TABLE AS.  I wouldn't expect that, for one.  Personally I think it
should be reverted.

One thing: I couldn't find GET DIAGNOSTICS documentation for Oracle's
PL/SQL by googling around.  Is this PL/pgSQL-specific stuff?

-- 
Alvaro Herrera ()
"Hay quien adquiere la mala costumbre de ser infeliz" (M. A. Evans)

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

Re: [GENERAL] CREATE TEMP TABLE AS SELECT/ GET DIAGNOSTICS ROW_COUNT

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> On Tue, May 31, 2005 at 01:03:30AM -0400, Tom Lane wrote:
>> So the initial evidence is that this was not an intentional change.
>> Do we want to revert it?  The behavior has been in the field now for
>> more than a full release cycle --- all 7.4.* releases behave this way
>> --- so one could argue that we should leave it be.

> ...  Personally I think it should be reverted.

OK, next question: is this a bug fix we should back-patch into 7.4,
or just change it in HEAD?

> One thing: I couldn't find GET DIAGNOSTICS documentation for Oracle's
> PL/SQL by googling around.  Is this PL/pgSQL-specific stuff?

It's in the SQL99 spec, though arguably plpgsql is misusing it.

regards, tom lane

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

[GENERAL] using pg_dump to dump only a schema?

[thomas]

hi there,

i'm wondering, if i can use pg_dump to move only one schema of a db from 
the one server, intoan other schema on the other server?


for example:

Server A:
Database: xyz_test
Schema: 123

Server B:
Database: xyz_prod
Schema: 999


The tablenames and so on are equal. and i wan't to transfer the data 
with it. is it possible? and how? today i played a little bit with it, 
but could not find a proper way with it. the description in the docu 
didn't helped me either. any solution anyone?


thanx in advance!

bye,
thomas


---(end of broadcast)---
TIP 3: 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] Accessing PostgreSQL from C++

[Gianni Mariani]

Jeff Brown wrote:

Hi guys 


Is there some sort of C API available for PostgreSQL?
 



I'm quite happy with libpqxx.

ftp://gborg.postgresql.org/pub/libpqxx/stable/libpqxx-2.5.0.tar.gz


Cheers
Jeff

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




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

Re: [GENERAL] using pg_dump to dump only a schema?

[Scott Marlowe]

On Tue, 2005-05-31 at 15:03, thomas wrote:
> hi there,
> 
> i'm wondering, if i can use pg_dump to move only one schema of a db from 
> the one server, intoan other schema on the other server?
> 
> for example:
> 
> Server A:
> Database: xyz_test
> Schema: 123
> 
> Server B:
> Database: xyz_prod
> Schema: 999
> 
> 
> The tablenames and so on are equal. and i wan't to transfer the data 
> with it. is it possible? and how? today i played a little bit with it, 
> but could not find a proper way with it. the description in the docu 
> didn't helped me either. any solution anyone?

To dump a certain schema, use the -n switch, then edit / sed the output
to change the schemaname to something else and load it back into the
other database with psql.

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

   http://archives.postgresql.org

[GENERAL] Major flood of mail to lists ...

[Marc G. Fournier]

Do to moderator error (namely, mine), several hundred messages (spread 
across all the lists) were just approved ...


Sorry for all the incoming junk :(


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] CREATE TEMP TABLE AS SELECT/ GET DIAGNOSTICS ROW_COUNT

[Alvaro Herrera]

On Tue, May 31, 2005 at 03:43:56PM -0400, Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > On Tue, May 31, 2005 at 01:03:30AM -0400, Tom Lane wrote:
> >> So the initial evidence is that this was not an intentional change.
> >> Do we want to revert it?  The behavior has been in the field now for
> >> more than a full release cycle --- all 7.4.* releases behave this way
> >> --- so one could argue that we should leave it be.
> 
> > ...  Personally I think it should be reverted.
> 
> OK, next question: is this a bug fix we should back-patch into 7.4,
> or just change it in HEAD?

I guess apply only in HEAD, and provide the patch for MLikharev so he
can solve his immediate problem.

-- 
Alvaro Herrera ()
Thou shalt study thy libraries and strive not to reinvent them without
cause, that thy code may be short and readable and thy days pleasant
and productive. (7th Commandment for C Programmers)

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] How to wait until startup completes

[Gary Horton]

I'm starting up postgresql with this command line:

/usr/bin/setpgrp ${POSTGRESQL_HOME}/bin/pg_ctl -w -o "-i" start

...and there are two things about this that raise a question. First, we 
use the setpgrp because, although pg_ctl documentation (7.3.4) states 
that it can be used for  "properly detaching from the terminal and 
process group", we have not found this to work as expected. In 
particular, when a ctl-C is issued from the same tty as was used for the 
pg_ctl command (without the setpgrp), the associated postmaster 
processes are killed (with a fast shutdown, i.e. they receive the SIGINT 
signal from the ctl-C). Prefacing pg_ctl with setpgrp addresses this 
problem for us. So, the first question would be, what am I not 
understanding here?


More importantly, we want to start up the database "completely" before 
any client connections are attempted. This matters to us in our 
build/test environment where we have complete control over client 
connections; we're only trying to be certain the database is ready 
before launching a series of builds/tests that don't initialize properly 
due to the "FATAL: The database system is starting up" messages, which 
are received on each attempted client connection, and wasting a whole 
build-test cycle as a result. According the pg_ctl documentation again, 
we're told that the -w flag will cause pg_ctl to "Wait for the start or 
shutdown to complete", and we hoped this would effectively cause pg_ctl 
to block until the database startup is "complete"; however this too does 
not work as expected. We are testing for "startup-is-complete" with the 
following:


until ${POSTGRESQL_HOME}/bin/psql -l > /dev/null 2>&1 || 
${POSTGRESQL_HOME}/bin/psql -l -d template1 -U $PORTAL_DB_OWNER


...and it happens that /sometimes/ this test succeeds and yet subsequent 
client connections fail due to the "FATAL: The database system is 
starting up" condition.


From this, a whole series of questions comes up in my mind:

- is this the proper idiom to test for database startup being "complete" 
(i.e. "complete" means that the "FATAL: The database system is starting 
up" error messages should not occur)?

- If not, what test should we be using instead?
- If so, what could be happening here?
- is there a better way to start up postresql to achieve what we want?
- is setpgrp somehow getting in the way of the -w flag working as desired?

Thanks for any insights -
Gary Horton


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

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

[GENERAL] US Phone Number Data

[Matthew T. O'Connor]

Hello,

Not sure if this is a good place to ask this question, but it is the 
general list


My company is looking for a way to get a list of all the names and phone 
numbers with addresses for New Jersey.  Does anyone know where / how I 
can get this dataset?   I have done some googling and haven't found 
anything useful.


Thanks,

Matt

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Missing numbers

[Simon Riggs]

On Tue, 2005-05-31 at 18:28 +0200, Harald Fuchs wrote:
> In article <[EMAIL PROTECTED]>,
> josue <[EMAIL PROTECTED]> writes:
> 
> > Hello list,
> > I need to track down the missing check numbers in a serie, table
> > contains a column for check numbers and series like this:
> 
> 
> > dbalm=# select doc_numero,doc_ckseriesfk from bdocs where doc_cta=1
> > dbalm-# and doc_tipo='CHE' order by doc_numero;
> >   doc_numero | doc_ckseriesfk
> > +
> >   19200  | 856938
> >   19201  | 856938
> >   19215  | 856938
> >   19216  | 856938
> >   19219  | 856938
> 
> > Missing numbers are:
> > from 19202 to 19214 and 19217,19218
> 
> 
> > Does anyone knows a way to get that done in SQL or plpgsql, thanks in
> > advance
> 
> You could use something like that:
> 
> SELECT g.num
> FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
>   (SELECT max(doc_numero) FROM bdocs)) AS g(num)
> LEFT JOIN bdocs ON bdocs.doc_numero = g.num
> WHERE bdocs.doc_numero IS NULL

SELECT g.num
FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
  (SELECT max(doc_numero) FROM bdocs)) AS g(num)
WHERE g.num NOT IN (select doc_numero
   from bdocs
   where doc_numero is not null)

is more likely to return a correct answer, since
bdocs.doc_numero will never equal g,num when it is also NULL

Best Regards, Simon Riggs






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

[GENERAL] freebsd port successful/failed install

[Matthew Terenzio]

Any FreeBSD experts know what conditions might have the port 
(postgresql-server-8.0.1_3) look like like it installs successfully but 
/usr/local/pgsql/data is not created and so there is no postgresql.conf 
to find.


also running  /usr/local/etc/rc.d/010.pgsql.sh initdb looks like it 
just shoots blanks.


I'd supply more info if I knew what to offer you on this one.

Thanks,

Matt


---(end of broadcast)---
TIP 3: 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] freebsd port successful/failed install

[Marc G. Fournier]

On Tue, 31 May 2005, Matthew Terenzio wrote:



Any FreeBSD experts know what conditions might have the port 
(postgresql-server-8.0.1_3) look like like it installs successfully but 
/usr/local/pgsql/data is not created and so there is no postgresql.conf to 
find.


also running  /usr/local/etc/rc.d/010.pgsql.sh initdb looks like it just 
shoots blanks.


did you add postgresql_enable="YES" to /etc/rc.conf like the install 
process tells you?


more /usr/ports/databases/postgresql80-server/pkg-message-server :

To run PostgreSQL at startup, add
'postgresql_enable="YES"' to /etc/rc.conf



Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

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

Re: [GENERAL] CREATE TEMP TABLE AS SELECT/ GET DIAGNOSTICS

[Neil Conway]

On Tue, 2005-05-31 at 15:43 -0400, Tom Lane wrote:
> OK, next question: is this a bug fix we should back-patch into 7.4,
> or just change it in HEAD?

I agree with Alvaro: fix it in HEAD, but don't backport the change to
8.0 or 7.4.

-Neil



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

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

Re: [GENERAL] How to wait until startup completes

[Tom Lane]

Gary Horton <[EMAIL PROTECTED]> writes:
> [ assorted startup problems ]

You did not say what platform this is on, nor which Postgres version
you are running.  Tsk tsk.

As for the setpgrp business, that doesn't sound real unreasonable.
I use nohup for that purpose, and it seems to work fine on all the
platforms I use, but perhaps on yours setpgrp is the best incantation.
(Of course, for ordinary production work you should be launching the
postmaster from an init script and not from a manual command at all...)

The -w-doesn't-wait-long-enough bit needs investigation.  There are
known failure modes for -w, like setting up your access permissions
so that pg_ctl can't log in, but AFAIK that results in waiting till
timeout not in falling through immediately.  Are any messages produced
when you do this?  If you don't see anything, try running the script
with -x to see what it's doing exactly.

regards, tom lane

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

Re: [GENERAL] Missing numbers

[Alvaro Herrera]

On Wed, Jun 01, 2005 at 01:21:28AM +0100, Simon Riggs wrote:
> On Tue, 2005-05-31 at 18:28 +0200, Harald Fuchs wrote:

> > SELECT g.num
> > FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
> >   (SELECT max(doc_numero) FROM bdocs)) AS g(num)
> > LEFT JOIN bdocs ON bdocs.doc_numero = g.num
> > WHERE bdocs.doc_numero IS NULL
> 
> SELECT g.num
> FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
>   (SELECT max(doc_numero) FROM bdocs)) AS g(num)
> WHERE g.num NOT IN (select doc_numero
>  from bdocs
>  where doc_numero is not null)
> 
> is more likely to return a correct answer, since
> bdocs.doc_numero will never equal g,num when it is also NULL

Oh, but it is an outer join, so it should generate the NULLs, yes?

-- 
Alvaro Herrera ()
"Vivir y dejar de vivir son soluciones imaginarias.
La existencia está en otra parte" (Andre Breton)

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] unsubscribe

[E U]

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Issue with OS X

[Jamie Deppeler]

Hi,
I am trying to install postgresql 8 on a G5 OS X machine and cannot get 
past this error i have tried changing the shared buffer with no luck.


Error message:
/usr/local/pgsql/data postgres$ FATAL:  could not create shared memory 
segment: Invalid argument


DETAIL:  Failed system call was shmget(key=5432001, size=10338304, 03600).

HINT:  This error usually means that PostgreSQL's request for a shared 
memory segment exceeded your kernel's SHMMAX parameter.  You can either 
reduce the request size or reconfigure the kernel with larger SHMMAX.  
To reduce the request size (currently 10338304 bytes), reduce 
PostgreSQL's shared_buffers parameter (currently 1000) and/or its 
max_connections parameter (currently 100).


   If the request size is already small, it's possible that it is 
less than your kernel's SHMMIN parameter, in which case raising the 
request size or reconfiguring SHMMIN is called for.


   The PostgreSQL documentation contains more information about 
shared memory configuration.



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

MD5 computation (was: RE: [GENERAL] For Tom Lane)

[Philippe Lang]

Hi,

What is the best way to calculate an MD5 Sum for a set of rows in a table, on a 
Postgresql server?

-Message d'origine-
De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Scott Marlowe
Envoyé : mardi, 31. mai 2005 18:37
À : [EMAIL PROTECTED]
Cc : pgsql-general
Objet : Re: [GENERAL] For Tom Lane

On Fri, 2005-05-27 at 09:57, [EMAIL PROTECTED] wrote:

> 
> Thanks for answer Tom
> 
> "Consider what happens when the user leaves for lunch"
> 
> Well, I've already thought about it.But I'm working with
> VS2003 and disconnected dataset.. so when user edit data he's 
> modifying an "old" disconnected row, while real updated row is in the 
> database..
> So my strategy would be (as I already written):
> 
> 1. refresh data recalling current row from database to the form's 
> fields 2. lock the row 3. update modified data in the database through 
> stored procedure (function) 4. commit and unlock the row
> 
> Have you another idea that could work better with disconnected objects ?

While this ensures that the update is atomic, it doesn't ensure that no one 
else is trying to edit it at the same time.

What you might want to do is either optimistically lock it, or use application 
level locking.  To use optimistic locking, you'll need to do something like 
make an md5 of all the fields being edited, then, right before you write back 
the data, check to see if the md5 you created at the beginning still matches by 
re-reading the data and md5ing it again. 
If it doesn't match, then you can throw a "mid air collision" error, so to 
speak, and tell them that the record changed underneath them, or do some kind 
of merging / or whatnot.

If you want to do application level locking, then create a field and use that 
for locks.  Just make it a timestamp field and put in the current time value 
when the lock is taken.  When the predetermined timeout occurs, the user lock 
is removed by the next person to access it, or offer them chance to, or email 
the original locker, etc...  Handle it the way you want or need to.

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



---(end of broadcast)---
TIP 3: 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] TableSpaces across partitions and xlog

[Himanshu Baweja]

Moving table spaces along raids definately helps but will moving tablespaces across partitions help??? in my opinion it should lead to degradation of performance because read/write head will have to move more.
 
also, i have two raids my data is in first one... and xlog in the other. will moving some of the data to the raid with xlog help
 
my application is read and write intensive... but the bottle so far has been the update queries
 
Regards
Himanshu__Do You Yahoo!?Tired of spam?  Yahoo! Mail has the best spam protection around http://mail.yahoo.com 

Re: MD5 computation (was: RE: [GENERAL] For Tom Lane)

[Neil Conway]

On Wed, 2005-06-01 at 08:22 +0200, Philippe Lang wrote:
> What is the best way to calculate an MD5 Sum for a set of rows in a
> table, on a Postgresql server?

The md5() builtin function. contrib/pgcrypto is available if you need
more sophisticated hashing / encryption.

-Neil



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

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