[GENERAL] database bloat, but vacuums are done, and fsm seems to be setup ok

2005-09-28 Thread hubert depesz lubaczewski
hi setup: postgresql 8.0.3 put on debian on dual xeon, 8GB ram, hardware raid. database just after recreation from dump takes 15gigabytes. after some time (up to 3 weeks) it gets really slow and has to be dump'ed and restored. as for fsm: end of vacuum info: INFO:  free space map: 248 relations,

[GENERAL] postgresql and C++/C

2005-09-28 Thread [EMAIL PROTECTED]
Context:FREEBSD 5.4, postgresql 8.0.3 I'm not a C newbye and I'm having a go at compiling my first, simple C(++) program: #include int main() { PGconn *psql; psql = PQconnectdb("hostaddr = '127.0.0.1' port = '' dbname = 'curve' user = 'vi return 0; } But when I compile it the following mess

Re: [GENERAL] postgresql and C++/C

2005-09-28 Thread Peter Wiersig
On Wed, Sep 28, 2005 at 10:11:45AM +0100, [EMAIL PROTECTED] wrote: > > vicbsd# gcc -I Don't use gcc to compile C++. Use g++. Peter ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/

Re: [GENERAL] postgresql and C++/C

2005-09-28 Thread Martijn van Oosterhout
On Wed, Sep 28, 2005 at 10:11:45AM +0100, [EMAIL PROTECTED] wrote: > Context:FREEBSD 5.4, postgresql 8.0.3 > > I'm not a C newbye and I'm > having a go at compiling my first, simple C(++) program: 1. If it's a C program, you should name the file .c otherwise gcc is likely to consider it a c++ pr

Re: [GENERAL] ERROR: type "temp_gc" already exists

2005-09-28 Thread Janning Vygen
Hi Tom, Hi Damon, Am Dienstag, 27. September 2005 20:36 schrieb Tom Lane: > Damon Hart <[EMAIL PROTECTED]> writes: > > I came across this thread after intermittently observing very similar > > error messages last week from a PostgreSQL (8.0.2) server, e.g.: > > SQL error: = 'type "local_roll" alre

[GENERAL] Triggers after a rule

2005-09-28 Thread Wijnand Wiersma
Hi list, I am currently trying to give normal users some read access to some tables in the database. I also need to give update access to one column of one table. I have the table contact, the user should not be able to read or update anything in it, except for his own record. So I created the vi

Re: [GENERAL] postgresql and C++/C

2005-09-28 Thread [EMAIL PROTECTED]
I've just modified the extension with .c instead of .cpp and run: vicbsd# gcc -I /usr/local/include/ prova.c -o prova -Lpq /var/tmp//cc86RZZl.o(.text+0x25): In function `main': : undefined reference to `PQconnectdb' Nothing seems to be changed! Thanks anyway. Vittorio Messaggio originale--

Re: [GENERAL] Triggers after a rule

2005-09-28 Thread Richard Huxton
Wijnand Wiersma wrote: I have the table contact, the user should not be able to read or update anything in it, except for his own record. So I created the view v_my_account. When the user selects * from it he only sees his own record. That works great. I also made a rule: CREATE RULE update_v_my_

Re: [GENERAL] ERROR: type "temp_gc" already exists

2005-09-28 Thread Patrick . FICHE
I agree with you that it's probably not hardware problem. I had the exactly the same issues some months ago with the 8.0 version when using temp tables in functions. I changed the temp tables to standard tables and used the PID (pg_backend_pid) to manage concurrent access to this table... The code

Re: [GENERAL] postgresql and C++/C

2005-09-28 Thread Martijn van Oosterhout
On Wed, Sep 28, 2005 at 10:59:04AM +0100, [EMAIL PROTECTED] wrote: > I've just modified the extension with .c instead of .cpp and run: > vicbsd# gcc -I /usr/local/include/ prova.c -o prova -Lpq > /var/tmp//cc86RZZl.o(.text+0x25): In function `main': > : undefined > reference to `PQconnectdb' > >

Re: [GENERAL] Triggers after a rule

2005-09-28 Thread Wijnand Wiersma
2005/9/28, Richard Huxton : > Wijnand Wiersma wrote: > > I thought that when a rule is applied the triggers which are triggerd > > would also have the same userid as the rule query, but I was wrong. > > Can you show the (cut down) contents of your trigger? It's difficult to > see what you mean. I c

Re: [GENERAL] SQL command to dump the contents of table failed: PQendcopy()

2005-09-28 Thread ruben
Hi Tom: No way to get the core dump, this is what I did: 1) Add "ulimit" to /etc/rc.d/init.d/postgresql just before pg_ctl start in the "start" section of the script: ... ulimit -c unlimited su -l postgres -s /bin/sh -c "/usr/local/pgsql/bin/pg_ctl -D $PGDATA -p /usr/local/pgsql/bin/po

[GENERAL] trigger self recursion

2005-09-28 Thread Sim Zacks
Is there a way to avoid trigger self-recursion? In other words, update a table and have the trigger update another row in the same table without calling the same trigger? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ig

Re: [GENERAL] trigger self recursion

2005-09-28 Thread Martijn van Oosterhout
On Wed, Sep 28, 2005 at 01:41:29PM +0200, Sim Zacks wrote: > Is there a way to avoid trigger self-recursion? > In other words, update a table and have the trigger update another row in > the same table without calling the same trigger? No, although generally it's a sign of a coding problem. If you

Re: [GENERAL] SQL command to dump the contents of table failed: PQendcopy()

2005-09-28 Thread Martijn van Oosterhout
On Wed, Sep 28, 2005 at 11:40:11AM +0200, ruben wrote: > Hi Tom: > > No way to get the core dump, this is what I did: > > 1) Add "ulimit" to /etc/rc.d/init.d/postgresql just before pg_ctl start > in the "start" section of the script: > 2) Restart postmaster > >/etc/rc.d/init.d/postgresql

Re: [GENERAL] Triggers after a rule

2005-09-28 Thread Richard Huxton
Wijnand Wiersma wrote: 2005/9/28, Richard Huxton : Wijnand Wiersma wrote: I thought that when a rule is applied the triggers which are triggerd would also have the same userid as the rule query, but I was wrong. Can you show the (cut down) contents of your trigger? It's difficult to see wha

Re: [GENERAL] trigger self recursion

2005-09-28 Thread Richard Huxton
Sim Zacks wrote: Is there a way to avoid trigger self-recursion? In other words, update a table and have the trigger update another row in the same table without calling the same trigger? No a per-row trigger will fire for every row updated. There is presumably some test you can make to see wh

Re: [GENERAL] trigger self recursion

2005-09-28 Thread Sim Zacks
Basically I have a table that is not fully normalized. When the user updates a field that has a "duplicate" I would like it to update those duplicate rows as well. The code is very straightforward. Update table1 set f1=new.f1,f2=new.f2,f3=new.f3 where pk<>new.pk and f4=new.f4 and f5=new.f5

Re: [GENERAL] trigger self recursion

2005-09-28 Thread Martijn van Oosterhout
On Wed, Sep 28, 2005 at 02:20:57PM +0200, Sim Zacks wrote: > Basically I have a table that is not fully normalized. When the user > updates a field that has a "duplicate" I would like it to update those > duplicate rows as well. > The code is very straightforward. > > Update table1 set f1=new.

Re: [GENERAL] SQL command to dump the contents of table failed: PQendcopy()

2005-09-28 Thread ruben
Thanks Martijn: Martijn van Oosterhout wrote: On Wed, Sep 28, 2005 at 11:40:11AM +0200, ruben wrote: Hi Tom: No way to get the core dump, this is what I did: 1) Add "ulimit" to /etc/rc.d/init.d/postgresql just before pg_ctl start in the "start" section of the script: 2) Restart postma

Re: [GENERAL] trigger self recursion

2005-09-28 Thread Sim Zacks
Good point. I don't know how I missed that one. Thank You Sim On Wed, Sep 28, 2005 at 02:20:57PM +0200, Sim Zacks wrote: > Basically I have a table that is not fully normalized. When the user > updates a field that

Re: [GENERAL] Problems creating view

2005-09-28 Thread Martín Marqués
El Sáb 24 Sep 2005 13:15, Tom Lane escribió: > =?iso-8859-1?q?Mart=EDn_Marqu=E9s?= writes: > > I can't create any VIEW at all. I'm using the latest debian testing > > PostgreSQL (8.0.3): > > Maybe you got a corrupt download? How can I seek if it's a corrupt download or something else? OK, I file

Re: [GENERAL] Triggers after a rule

2005-09-28 Thread Wijnand Wiersma
2005/9/28, Richard Huxton : > The solution is to mark your trigger functions with the "SECURITY > DEFINER" attribute and create them as a user who can access relation > "contact". > http://www.postgresql.org/docs/8.0/static/sql-createfunction.html Damn, I really really missed that one :-( Thank yo

[GENERAL] Migration From MS SQL 2K

2005-09-28 Thread William IT
Hi,   Is there any tools to migrate MS SQL 2K including: - PK and FK - Index and Contraints - Trigger - View - Store Procedure - User Accounts   I use Postgres 8.0.3 Windows.   Thanks.

Re: [GENERAL] ERROR: type "temp_gc" already exists

2005-09-28 Thread Tom Lane
Janning Vygen <[EMAIL PROTECTED]> writes: > I recently reported this problem and i would like to help solving it. But how > can i build a self-contained test-case? It just happens sometimes under load. I didn't say you need to make it 100% reproducible; you just have to make a case that someone el

Re: [GENERAL] RI_ConstraintTrigger question

2005-09-28 Thread Florian G. Pflug
[EMAIL PROTECTED] wrote: Tom Lane wrote: Jan Wieck <[EMAIL PROTECTED]> writes: On 9/27/2005 12:20 AM, George Essig wrote: We have a database with about 30 tables and some RI. The RI constraints, however, were not named upon creation of the database 2-3 years ago and now when we get an

Re: [GENERAL] [PG7.4] Using the data from temp table within a function

2005-09-28 Thread codeWarrior
"Mario Splivalo" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I've learned that one can't use temporary tables within the function > unless > EXECUTE'd the SELECTS from that temp table. > > So, I have a function like this: > > CREATE FUNCTION Foo1(int4, int4) RETURNS SETOF myType

Re: [GENERAL] Function keys cause psql to segfault

2005-09-28 Thread Chris Travers
Douglas McNaught wrote: CSN <[EMAIL PROTECTED]> writes: Hmm, in putty (Terminal->Keyboard) I changed "the function keys and keypad" from "ESC[n~" to "Linux". Hitting F1-5 in psql outputs "ABCDE" - no segfaults! Setting it to "Xterm R6" also results in function keys 1-4 causing segfaults (th

[GENERAL] Setting up a fine-grained permission system

2005-09-28 Thread David Garamond
Hi, Our current project requires a fine-grained permission system (row-level and possibly column-level as well). We have a pretty large (tens of thousands) of users in the 'party' table. I'm thinking of choosing Unix-style security for now (adding 'ugo' and 'owner' and 'group' columns to each tabl

[GENERAL] [EMAIL PROTECTED] and http://www.postgresql.org/community/lists/

2005-09-28 Thread pkondratev
Hi, two questions 1. Why on page http://www.postgresql.org/community/lists/ no reference to [EMAIL PROTECTED] 2. Somebody tried work Kernel Mode Linux and PostgreSQL, there were problems? ---(end of broadcast)--- TIP 1: if posting/reading through Us

[GENERAL] Migration from MS SQL 2K

2005-09-28 Thread TheNice Spider
Hi,   Currently I have serious problem to migrate a "production" MS SQL 2K to Postgres 8 for Windows. Is there any tools to import MS SQL 2K to Postgres 8 including: - PK and FK - View, Trigger, Store Procedure - Username - Index and Constraint - Diagrams   Regards. Yahoo! for Good Click

[GENERAL] Hardware requirements

2005-09-28 Thread Rafael Montoya
Hello everybody, i really need to know hardware requirements for installing PostgreSQL 8.0.3. I'm in a database migration project and it is important to work with the appropiate hardware. DB must work in windows. There are 50 tables aprox and data size is near 6 GB. Thank for your answers. Rafa

Re: [GENERAL] Migration from MS SQL 2K

2005-09-28 Thread Roger Hand
PGAdmin II had a "Migration Wizard" plugin that did a great job. I used it a lot to convert MS SQL 7 and 2000 db's to Postgres 7.x.   It did not convert views, but I wrote some code that did that.   Unfortunately, PGAdmin II doesn't work with PG 8.   And PGAdmin III doesn't have the Migration

Re: [GENERAL] Migration from MS SQL 2K

2005-09-28 Thread A. Kretschmer
am 28.09.2005, um 22:48:06 -0700 mailte TheNice Spider folgendes: > Hi, > > Currently I have serious problem to migrate a "production" MS SQL 2K to > Postgres 8 for Windows. > Is there any tools to import MS SQL 2K to Postgres 8 including: > - PK and FK > - View, Trigger, Store Procedure > - U

Re: [GENERAL] Hardware requirements

2005-09-28 Thread Ben
Unless you need all that data in ram (and you probably don't), then any machine should be capable. The real questions are, how many concurrent clients? How static is the data? What is your query complexity? On Sep 28, 2005, at 11:08 PM, Rafael Montoya wrote: Hello everybody, i really need