Re: [GENERAL] Interval Question
On Tue, 11 Jan 2005, Geoffrey wrote: Tom Lane wrote: Terry Lee Tucker [EMAIL PROTECTED] writes: Apparently, if DateStyle is set to Sql, it always returns the absolute value. Is this due to some Sql standard or is it a bug? It's a bug in interval_out. Looks like it gets it wrong for GERMAN style too. Surprising no one noticed before. Any idea when I might be able to put my hands on Red Hat 3 rpm versions that include this fix? Currently running postgresql-server-7.4.6-2PGDG and would like to stick with the same version. It'd be a whole lot easier to apply the fix via rpm rather then having to build from source. If you really need it, you can rebuild the rpm itself from the .src.rpm. Adding a small patch is quite easy, even if you're not familiar with spec files. Usually there are other patches already, all you need is to move the patch file to the right place (SOURCES in the rpm tree) and add one PatchXXX: and one %patchXXX line. Then use rpmbuild (note that you need the development set of packages installed, of course). .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(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] OID of current function
Is there an easy way to get the OID of the currently running function? (IE: the function you're in when you execute the code to see what function you're in, if that makes any sense). -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Bug in pg_dump in 7.4.6?
On Tue, 11 Jan 2005, Tom Lane wrote: Greg Stark [EMAIL PROTECTED] writes: 7.4.6 pg_dump seems to be inserting a lot more SET SESSION AUTHORIZATION lines than previously. By previously do you mean 7.4.5 or thereabouts? I can't recall any late-7.4.* changes that might affect this. pg_dump is supposed to Maybe this one, from 7.4.4 relnotes? - pg_dump handled ACLs with grant options incorrectly optimize away redundant SET SESSION AUTH commands. Can you give a specific example of what you are seeing? regards, tom lane .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] OID of current function
Jim C. Nasby wrote: Is there an easy way to get the OID of the currently running function? (IE: the function you're in when you execute the code to see what function you're in, if that makes any sense). In what language? In C you can use: Datum your_function(PG_FUNCTION_ARGS) { Oid funcOid = fcinfo-flinfo-fn_oid; ... } Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Bug in pg_dump in 7.4.6?
Greg Stark [EMAIL PROTECTED] writes: It seems the spurious SET SESSION AUTHORIZATION commands appear after any REVOKE/GRANT pair. Oh, right. In order to handle grants with GRANT OPTION, the dump data may need to include SET SESSION AUTHORIZATION commands; so the code assumes that it doesn't know the authorization any more after emitting an ACL entry. Not a bug. It could possibly be smarter (eg grep the text for SET SESSION AUTHORIZATION before deciding this) but since that's not the default mode anymore anyway, I'm not very concerned. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OID of current function
On Wed, Jan 12, 2005 at 04:08:28PM +0100, Thomas Hallgren wrote: Jim C. Nasby wrote: Is there an easy way to get the OID of the currently running function? (IE: the function you're in when you execute the code to see what function you're in, if that makes any sense). In what language? In C you can use: Datum your_function(PG_FUNCTION_ARGS) { Oid funcOid = fcinfo-flinfo-fn_oid; ... } This would be in plpgsql. Some other info: What I'm trying to do is use contrib/userlock to serialize access to a function. The only effective way to come up with a unique lock number that I've been able to think of is to use the OID of the function itself. What I find somewhat interesting is every other database I've used that exposes some kind of 'object ID' has a set of functions to map between an object name and it's ID, and vice-versa. It seems like this is something that would be good for PostgreSQL to have. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Debugging SPI C functions
Hi all, We have a postgresql server running on a Linux machine. Our aim is to develop a bunch of SPI stored procedures to automate a production process. We use Eclipse with CDT plugin for C programming. Is there a mean to debug SPI functions with Eclipse (or any other tool) as it is possible with libpq based programs ? Thanks. Regards, Benjamin.
Re: [GENERAL] OID of current function
Jim C. Nasby [EMAIL PROTECTED] writes: What I find somewhat interesting is every other database I've used that exposes some kind of 'object ID' has a set of functions to map between an object name and it's ID, and vice-versa. regression=# create function myfunc(int) returns int as 'select $1' language sql; CREATE FUNCTION regression=# SELECT 'myfunc(int)'::regprocedure::oid; oid 431373 (1 row) regression=# select 431373::regprocedure; regprocedure - myfunc(integer) (1 row) regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Bug in pg_dump in 7.4.6?
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: It seems the spurious SET SESSION AUTHORIZATION commands appear after any REVOKE/GRANT pair. Oh, right. In order to handle grants with GRANT OPTION, the dump data may need to include SET SESSION AUTHORIZATION commands; so the code assumes that it doesn't know the authorization any more after emitting an ACL entry. Not a bug. It could possibly be smarter (eg grep the text for SET SESSION AUTHORIZATION before deciding this) Wouldn't it make more sense to have a global state variable that held the current user and anyone invoking SET SESSION AUTHORIZATION has to set that state variable? Or have a function responsible for emitting SET SESSION AUTHORIZATION and bar other functions from doing it manually. Then have a local static variable in that function responsible for keeping state. but since that's not the default mode anymore anyway, I'm not very concerned. What's not the default mode? I'm just running pg_dump -U postgresql -s db -- greg ---(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] Bug in pg_dump in 7.4.6?
Greg Stark [EMAIL PROTECTED] writes: but since that's not the default mode anymore anyway, I'm not very concerned. What's not the default mode? I'm just running pg_dump -U postgresql -s db As of 8.0, I meant. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Postgresql 8.0 and Cancel/Kill backend functions
Does anyone know if such functions have been added to version 8? I saw lots of discussion while searching google and saw references to patches applied and docs. Google link: http://groups-beta.google.com/group/comp.databases.postgresql.patches/browse_thread/thread/8dc627fdbe4b830d/2b4364d42a95b885?q=Cancel%2FKill+backend+functions+--+docs_done=%2Fgroups%3Fq%3DCancel%2FKill+backend+functions+--+docs%26qt_s%3DSearch+Groups%26_doneTitle=Back+to+Searchd#2b4364d42a95b885 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] vacuum vs open transactions
I'm looking at some 7.3.4 vacuum output, and at first glance it does not appear that vacuum is reclaiming any dead tuple space if there is even a single open transaction, even if the open transaction does not in any way reference the table being vacuumed. Is that correct? Is the behavior different in later versions? Ed ---(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] vacuum vs open transactions
On Wed, 2005-01-12 at 11:59, Ed L. wrote: I'm looking at some 7.3.4 vacuum output, and at first glance it does not appear that vacuum is reclaiming any dead tuple space if there is even a single open transaction, even if the open transaction does not in any way reference the table being vacuumed. Is that correct? Is the behavior different in later versions? I believe the problem is occurring if the open transaction is older than the tuples that could be vacuumed. The MVCC system means that as long as a transaction that started X hours ago is still open, the tuples that have been freed since then can't vacuumed because they need to stay visible for that transaction. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Postgresql 8.0 and Cancel/Kill backend functions
The cancel function is implemented. See http://developer.postgresql.org/docs/postgres/functions-admin.html#FUNCT IONS-ADMIN-SIGNAL-TABLE. Kill function was considered too dangerous. //Magnus -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tony Caduto Sent: Wednesday, January 12, 2005 7:00 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Postgresql 8.0 and Cancel/Kill backend functions Does anyone know if such functions have been added to version 8? I saw lots of discussion while searching google and saw references to patches applied and docs. Google link: http://groups-beta.google.com/group/comp.databases.postgresql. patches/browse_thread/thread/8dc627fdbe4b830d/2b4364d42a95b885?q=Cancel% 2FKill+backend+functions+--+docs_done=% 2Fgroups%3Fq%3DCancel%2FKill+backend+functions+--+docs%26qt_s% 3DSearch+Groups%26_doneTitle=Back+to+Searchd#2b4364d42a95b885 ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Postgresql 8.0 and Cancel/Kill backend functions
On Wed, Jan 12, 2005 at 12:00:22PM -0600, Tony Caduto wrote: Does anyone know if such functions have been added to version 8? See the 8.0 Release Notes in the development documentation: * Add function to send cancel request to other backends (Magnus) See also the System Administration Functions section in the Functions and Operators chapter. Here's an example: Session 1: CREATE FUNCTION sleep(integer) RETURNS void AS $$ sleep $_[0]; $$ LANGUAGE plperlu; SELECT sleep(60); Session 2: SELECT procpid, current_query FROM pg_stat_activity; procpid | current_query -+--- ... 95609 | SELECT sleep(60); ... SELECT pg_cancel_backend(95609); Session 1: ERROR: canceling query due to user request -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] vacuum vs open transactions
On Wednesday January 12 2005 11:10, Scott Marlowe wrote: I believe the problem is occurring if the open transaction is older than the tuples that could be vacuumed. The MVCC system means that as long as a transaction that started X hours ago is still open, the tuples that have been freed since then can't vacuumed because they need to stay visible for that transaction. Is it possible via SQL query to tell how long a transaction has been open? Ed ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Postgresql didn't start after power failure
There was a power failure and then the postgresql service didn't start on system restart: System restart after power failure: Jan 12 16:49:06 s1 syslogd 1.4.1: restart. Jan 12 16:49:18 s1 su(pam_unix)[2098]: session opened for user postgres by (uid=0) Jan 12 16:49:18 s1 su(pam_unix)[2098]: session closed for user postgres Jan 12 16:49:19 s1 postgresql: Iniciando serviço postgresql : failed When I manually rebooted the system postgres restarted: Jan 12 18:40:42 s1 su(pam_unix)[2083]: session opened for user postgres by (uid=0) Jan 12 18:40:43 s1 su(pam_unix)[2083]: session closed for user postgres Jan 12 18:40:44 s1 postgresql: Iniciando serviço postgresql : succeeded /var/log/pgsql is empty and is chmoded as executable (?). It is an Anaconda install in FC2. Now up to 7.4.6. The last activity before power failure was a vacuum full and after that nothing at all for more than one hour. Is there anyway to know why did it not start and prevent it to happen again? How to configure it to write a log at system boot? Regards, Clodoaldo Pinto ___ Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet rápida e grátis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Interval Question
We, that is, Geoffrey, applied the patch and rebuilt the rpm's. I have installed the new rpm and the problem is resolved. We are in the process of converting a transportation package from a commercial database product to Postgres. On at least two occassions, I have reported bugs to that software company, and the bugs were never fixed. We were given work-around hacks and that was it. We discovered this problem yesterday and it is fixed today. I guess that is one of many GREAT things about Open Source software ;o) Thanks to everyone who responded, and especially to Tom Lane, who provided the patch. On Wednesday 12 January 2005 04:32 am, Marco Colombo saith: On Tue, 11 Jan 2005, Geoffrey wrote: Tom Lane wrote: Terry Lee Tucker [EMAIL PROTECTED] writes: Apparently, if DateStyle is set to Sql, it always returns the absolute value. Is this due to some Sql standard or is it a bug? It's a bug in interval_out. Looks like it gets it wrong for GERMAN style too. Surprising no one noticed before. Any idea when I might be able to put my hands on Red Hat 3 rpm versions that include this fix? Currently running postgresql-server-7.4.6-2PGDG and would like to stick with the same version. It'd be a whole lot easier to apply the fix via rpm rather then having to build from source. If you really need it, you can rebuild the rpm itself from the .src.rpm. Adding a small patch is quite easy, even if you're not familiar with spec files. Usually there are other patches already, all you need is to move the patch file to the right place (SOURCES in the rpm tree) and add one PatchXXX: and one %patchXXX line. Then use rpmbuild (note that you need the development set of packages installed, of course). .TM. -- / / / / / /Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(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 Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [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
[GENERAL] index on user defined type
I think I created a type that was compatible with the btree index, and everything seems fine, except that it doesn't actually use the index. I created the operators and the opclass as well. = create type type2 as (i int); = create operator = (leftarg=type2,rightarg=type2,procedure=type2_eq); = create operator (leftarg=type2,rightarg=type2,procedure=type2_lt); = create operator =(leftarg=type2,rightarg=type2,procedure=type2_lte); = create operator =(leftarg=type2,rightarg=type2,procedure=type2_gte); = create operator (leftarg=type2,rightarg=type2,procedure=type2_gt); = create operator class type2_opclass default for type type2 using btree as operator 1 , operator 2 =, operator 3 =, operator 4 =, operator 5 , function 1 type2_cmp(type2,type2); = create table test(t type2 unique); ... insert '(1)', '(2)', and '(3)'; ... enable_seqscan is off = explain select * from test where t = '(2)'; QUERY PLAN - Seq Scan on test (cost=1.00..10002.54 rows=1 width=32) Filter: ((t).i = ('(2)'::type2).i) (2 rows) Is there something obvious that I'm doing wrong? The operators' functions are pretty basic sql functions that just do the obvious thing, and those operators seem to work correctly when used in queries. If I make a table except with an int instead of a type2, it uses the index as soon as I turn seqscan off. Regards, Jeff Davis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Postgresql didn't start after power failure
On Wednesday January 12 2005 1:08, Clodoaldo Pinto wrote: There was a power failure and then the postgresql service didn't start on system restart: The last activity before power failure was a vacuum full and after that nothing at all for more than one hour. Is there anyway to know why did it not start and prevent it to happen again? How to configure it to write a log at system boot? PostgreSQL has a safety check that prevents it from restarting if it thinks there may be residual shared memory segments that might cause a problem. Your server log will tell you how to check (see ipcs). It is rare in my experience that failed restarts due to the safety check are valid. You can usually just nuke the pid file and restart after checking the shmmem segment listings. I believe Tom Lane recently made the safety check in 8.0 is a lot smarter than prior versions. I vaguely recall he posted the patch a month or three ago... Ed ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] How to return a resultset/table from a sql function?
Hi, Is it possible to return the following (parameterized) qyery from a sql or plpsql function, and if so, what is the syntax? SELECT{ (SELECT COUNT(klantnummer) FROM abo_klt WHERE abonnement = $1), (SELECT SUM(aantal_abonnementen)FROM abo_klt WHERE abonnement = $1)); Groeten, Joost ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Postgresql Text field / Visual FoxPro Memo and ODBC
I am using ODBC to move Visual FoxPro7 tables (windows 2000 client) into Postgresql 7.x on a Redhat Linux server. All is working well except for Memo fields, which are Text fields in PostgreSql. I query the Postgresql table and get a cursor, update the cursor with the data, and send it back. The contents of the memo field never make it back to the Postgresql text field. Other changes to the record's fields are saved with no problem. At this time all my UI's have to be written in VFP. Is there a work around for this or am I going to have to eliminate Text/Memo fields from my tables? Thanks. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgresql Text field / Visual FoxPro Memo and ODBC
On Wed, 2005-01-12 at 14:59, [EMAIL PROTECTED] wrote: I am using ODBC to move Visual FoxPro7 tables (windows 2000 client) into Postgresql 7.x on a Redhat Linux server. All is working well except for Memo fields, which are Text fields in PostgreSql. I query the Postgresql table and get a cursor, update the cursor with the data, and send it back. The contents of the memo field never make it back to the Postgresql text field. Other changes to the record's fields are saved with no problem. At this time all my UI's have to be written in VFP. Is there a work around for this or am I going to have to eliminate Text/Memo fields from my tables? Postgresql doesn't support updatable cursors. ---(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] Postgresql Text field / Visual FoxPro Memo and ODBC
Hello, El 12/01/2005 2:59 PM, [EMAIL PROTECTED] en su mensaje escribio: I am using ODBC to move Visual FoxPro7 tables (windows 2000 client) into Postgresql 7.x on a Redhat Linux server. All is working well except for Memo fields, which are Text fields in PostgreSql. I query the Postgresql table and get a cursor, update the cursor with the data, and send it back. The contents of the memo field never make it back to the Postgresql text field. Other changes to the record's fields are saved with no problem. Are you using SPT or remote views? At this time all my UI's have to be written in VFP. Is there a work around for this or am I going to have to eliminate Text/Memo fields from my tables? I use VFP8 against Postgresql with ODBC without any issue with the memo fields. -- Sinceramente, Josué Maldonado. La TV es muy educativa. Cuando está encendida, me voy a otra habitación y me pongo a leer un libro. --Groucho Marx. ---(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] Postgresql Text field / Visual FoxPro Memo and ODBC
Scott Marlowe [EMAIL PROTECTED] wrote on 01/12/2005 01:06:30 PM: On Wed, 2005-01-12 at 14:59, [EMAIL PROTECTED] wrote: I am using ODBC to move Visual FoxPro7 tables (windows 2000 client) into Postgresql 7.x on a Redhat Linux server. All is working well except for Memo fields, which are Text fields in PostgreSql. I query the Postgresql table and get a cursor, update the cursor with the data, and send it back. The contents of the memo field never make it back to the Postgresql text field. Other changes to the record's fields are saved with no problem. At this time all my UI's have to be written in VFP. Is there a work around for this or am I going to have to eliminate Text/Memo fields from my tables? Postgresql doesn't support updatable cursors. The updateable cursors are inside the FoxPro software. By using them I always get the table's current structure to build a UI on. Foxpro has an UpdateTable command that is used with the cursor. The command sends the data back with updates and inserts via ODBC. I have been doing this for a month now, and it's going fine, but today is the first time I tried to include data in the TEXT field. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 ---(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] Postgresql Text field / Visual FoxPro Memo and ODBC
Josué Maldonado [EMAIL PROTECTED] wrote on 01/12/2005 01:14:39 PM: Hello, El 12/01/2005 2:59 PM, [EMAIL PROTECTED] en su mensaje escribio: I am using ODBC to move Visual FoxPro7 tables (windows 2000 client) into Postgresql 7.x on a Redhat Linux server. All is working well except for Memo fields, which are Text fields in PostgreSql. I query the Postgresql table and get a cursor, update the cursor with the data, and send it back. The contents of the memo field never make it back to the Postgresql text field. Other changes to the record's fields are saved with no problem. Are you using SPT or remote views? At this time all my UI's have to be written in VFP. Is there a work around for this or am I going to have to eliminate Text/Memo fields from my tables? I use VFP8 against Postgresql with ODBC without any issue with the memo fields. -- Sinceramente, Josué Maldonado. La TV es muy educativa. Cuando está encendida, me voy a otra habitación y me pongo a leer un libro. --Groucho Marx. Hi Josué , I am using updatable cursors. I set up the cursor and set the cursor properties to updateable and set the key field property so it knows the key on the Postgresql table. The cursors are managed by a VFP class written by a programmer named Andy Kramek. He uses this class to manage updateable cursors in UI's for Oracle databases, I think on Unix servers. I have also used the class to talk to an SQLServer on Windows2000. The advantage with cursors is that I can pull down a set of 100-200 records, edit them, and send them back to the server and only one round trip has occured. Saves a lot of time when loading new tables, which is what I'm doing currently. I looked in the archives and there are messages about this problem but the fix for it was in the PGAdmin software. I didn't see anything that talked about the same problem / fix for ODBC. Regards, Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Postgresql Text field / Visual FoxPro Memo and ODBC
On Wed, 2005-01-12 at 15:28, [EMAIL PROTECTED] wrote: Scott Marlowe [EMAIL PROTECTED] wrote on 01/12/2005 01:06:30 PM: On Wed, 2005-01-12 at 14:59, [EMAIL PROTECTED] wrote: I am using ODBC to move Visual FoxPro7 tables (windows 2000 client) into Postgresql 7.x on a Redhat Linux server. All is working well except for Memo fields, which are Text fields in PostgreSql. I query the Postgresql table and get a cursor, update the cursor with the data, and send it back. The contents of the memo field never make it back to the Postgresql text field. Other changes to the record's fields are saved with no problem. At this time all my UI's have to be written in VFP. Is there a work around for this or am I going to have to eliminate Text/Memo fields from my tables? Postgresql doesn't support updatable cursors. The updateable cursors are inside the FoxPro software. By using them I always get the table's current structure to build a UI on. Foxpro has an UpdateTable command that is used with the cursor. The command sends the data back with updates and inserts via ODBC. I have been doing this for a month now, and it's going fine, but today is the first time I tried to include data in the TEXT field. Ahh, ok. Misunderstood what your method was. Is there a setting in your ODBC driver for memo as text or something like that? It's been a while since I played with pgsql from a windows / odbc box, so I am a bit rusty here. It's just a wild guess. ---(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] index on user defined type
Jeff Davis [EMAIL PROTECTED] writes: I think I created a type that was compatible with the btree index, and everything seems fine, except that it doesn't actually use the index. I created the operators and the opclass as well. = explain select * from test where t = '(2)'; QUERY PLAN - Seq Scan on test (cost=1.00..10002.54 rows=1 width=32) Filter: ((t).i = ('(2)'::type2).i) (2 rows) The explain doesn't seem to quite match up with what you wrote in the command. How did those .i qualifiers get in there? regards, tom lane ---(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] Postgresql Text field / Visual FoxPro Memo and ODBC
Scott Marlowe [EMAIL PROTECTED] wrote on 01/12/2005 01:36:34 PM: On Wed, 2005-01-12 at 15:28, [EMAIL PROTECTED] wrote: Scott Marlowe [EMAIL PROTECTED] wrote on 01/12/2005 01:06:30 PM: On Wed, 2005-01-12 at 14:59, [EMAIL PROTECTED] wrote: I am using ODBC to move Visual FoxPro7 tables (windows 2000 client) into Postgresql 7.x on a Redhat Linux server. All is working well except for Memo fields, which are Text fields in PostgreSql. I query the Postgresql table and get a cursor, update the cursor with the data, and send it back. The contents of the memo field never make it back to the Postgresql text field. Other changes to the record's fields are saved with no problem. At this time all my UI's have to be written in VFP. Is there a work around for this or am I going to have to eliminate Text/Memo fields from my tables? Postgresql doesn't support updatable cursors. The updateable cursors are inside the FoxPro software. By using them I always get the table's current structure to build a UI on. Foxpro has an UpdateTable command that is used with the cursor. The command sends the data back with updates and inserts via ODBC. I have been doing this for a month now, and it's going fine, but today is the first time I tried to include data in the TEXT field. Ahh, ok. Misunderstood what your method was. Is there a setting in your ODBC driver for memo as text or something like that? It's been a while since I played with pgsql from a windows / odbc box, so I am a bit rusty here. It's just a wild guess. On my Windows box the Postgresql ODBC driver is set with TEXT AS LongVarChar, with a maximum length of 8190. FoxPro then turns the LongVarChar into a Memo. Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Postgresql Text field / Visual FoxPro Memo and ODBC
El 12/01/2005 3:38 PM, [EMAIL PROTECTED] en su mensaje escribio: I am using updatable cursors. I set up the cursor and set the cursor properties to updateable and set the key field property so it knows the key on the Postgresql table. The cursors are managed by a VFP class written by a programmer named Andy Kramek. He uses this class to manage updateable cursors in UI's for Oracle databases, I think on Unix servers. I have also used the class to talk to an SQLServer on Windows2000. The advantage with cursors is that I can pull down a set of 100-200 records, edit them, and send them back to the server and only one round trip has occured. Saves a lot of time when loading new tables, which is what I'm doing currently. True, I also use SPT cursors in VFP and haven't had any issues with text /memo columns so far. I looked in the archives and there are messages about this problem but the fix for it was in the PGAdmin software. I didn't see anything that talked about the same problem / fix for ODBC. How about ODBC version conflicts, I have Postgresql ODBC driver version 8, I got it downloading latest pgadmin 3 version. -- Sinceramente, Josué Maldonado. El aspecto más triste de la vida actual es que la ciencia gana en conocimiento más rápidamente que la sociedad en sabiduría. -- Isaac Asimov ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgresql Text field / Visual FoxPro Memo and ODBC
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 Josué Maldonado [EMAIL PROTECTED] wrote on 01/12/2005 01:50:07 PM: El 12/01/2005 3:38 PM, [EMAIL PROTECTED] en su mensaje escribio: I am using updatable cursors. I set up the cursor and set the cursor properties to updateable and set the key field property so it knows the key on the Postgresql table. The cursors are managed by a VFP class written by a programmer named Andy Kramek. He uses this class to manage updateable cursors in UI's for Oracle databases, I think on Unix servers. I have also used the class to talk to an SQLServer on Windows2000. The advantage with cursors is that I can pull down a set of 100-200 records, edit them, and send them back to the server and only one round trip has occured. Saves a lot of time when loading new tables, which is what I'm doing currently. True, I also use SPT cursors in VFP and haven't had any issues with text /memo columns so far. I looked in the archives and there are messages about this problem but the fix for it was in the PGAdmin software. I didn't see anything that talked about the same problem / fix for ODBC. How about ODBC version conflicts, I have Postgresql ODBC driver version 8, I got it downloading latest pgadmin 3 version. -- Sinceramente, Josué Maldonado. El aspecto más triste de la vida actual es que la ciencia gana en conocimiento más rápidamente que la sociedad en sabiduría. -- Isaac Asimov I just downloaded and installed the lastest PGADMIN III, but I didnt' get a new ODBC driver. The only file in the download was pgadmin3.msi. The driver I have is ODBC version 7.03.02.00 dated 10-22-2003. That is the newest driver I see listed on the Postgresql site. Where can I get the 8 version? Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Postgresql didn't start after power failure
On Wed, 12 Jan 2005 17:08:26 -0300 (ART), Clodoaldo Pinto [EMAIL PROTECTED] wrote: There was a power failure and then the postgresql service didn't start on system restart: System restart after power failure: Jan 12 16:49:06 s1 syslogd 1.4.1: restart. Jan 12 16:49:18 s1 su(pam_unix)[2098]: session opened for user postgres by (uid=0) Jan 12 16:49:18 s1 su(pam_unix)[2098]: session closed for user postgres Jan 12 16:49:19 s1 postgresql: Iniciando serviço postgresql : failed When I manually rebooted the system postgres restarted: Jan 12 18:40:42 s1 su(pam_unix)[2083]: session opened for user postgres by (uid=0) Jan 12 18:40:43 s1 su(pam_unix)[2083]: session closed for user postgres Jan 12 18:40:44 s1 postgresql: Iniciando serviço postgresql : succeeded /var/log/pgsql is empty and is chmoded as executable (?). It is an Anaconda install in FC2. Now up to 7.4.6. The last activity before power failure was a vacuum full and after that nothing at all for more than one hour. Is there anyway to know why did it not start and prevent it to happen again? How to configure it to write a log at system boot? So its silently failing without logging anything? Is it configured to log to /var/log/pgsql in /etc/init.d/postgresql ? Perhaps it left a stale pid file behind? Did you try running a 'postgresql service stop' ? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgresql Text field / Visual FoxPro Memo and ODBC
El 12/01/2005 4:40 PM, [EMAIL PROTECTED] en su mensaje escribio: I just downloaded and installed the lastest PGADMIN III, but I didnt' get a new ODBC driver. The only file in the download was pgadmin3.msi. The driver I have is ODBC version 7.03.02.00 dated 10-22-2003. That is the newest driver I see listed on the Postgresql site. Where can I get the 8 version? I believe odbc 8 is win32 server install http://pgfoundry.org/projects/pginstaller/ -- Sinceramente, Josué Maldonado. Toda ciencia viene del dolor. El dolor busca siempre la causa de las cosas, mientras que el bienestar se inclina a estar quieto y a no volver la mirada atrás. Stefan Zweig. Escritor austríaco. ---(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] index on user defined type
On Wed, Jan 12, 2005 at 04:43:13PM -0500, Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: = explain select * from test where t = '(2)'; QUERY PLAN - Seq Scan on test (cost=1.00..10002.54 rows=1 width=32) Filter: ((t).i = ('(2)'::type2).i) (2 rows) The explain doesn't seem to quite match up with what you wrote in the command. How did those .i qualifiers get in there? I started looking at this in 8.0.0rc5 and got the same thing: EXPLAIN ANALYZE SELECT * FROM test WHERE t = '(2)'; QUERY PLAN --- Seq Scan on test (cost=1.00..10001.04 rows=1 width=32) (actual time=0.078..0.088 rows=1 loops=1) Filter: ((t).i = ('(2)'::type2).i) Total runtime: 0.203 ms (3 rows) If I add another column to the type I get this: EXPLAIN ANALYZE SELECT * FROM test WHERE t = '(2,3)'; QUERY PLAN --- Seq Scan on test (cost=1.00..10001.05 rows=1 width=36) (actual time=0.093..0.107 rows=1 loops=1) Filter: (((t).i = ('(2,3)'::type2).i) AND ((t).j = ('(2,3)'::type2).j)) Total runtime: 0.226 ms (3 rows) Table columns having a composite type are new in 8.0, right? Has indexing a composite type been done before? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] index on user defined type
On Wed, 12 Jan 2005, Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: I think I created a type that was compatible with the btree index, and everything seems fine, except that it doesn't actually use the index. I created the operators and the opclass as well. = explain select * from test where t = '(2)'; QUERY PLAN - Seq Scan on test (cost=1.00..10002.54 rows=1 width=32) Filter: ((t).i = ('(2)'::type2).i) (2 rows) The explain doesn't seem to quite match up with what you wrote in the command. How did those .i qualifiers get in there? I'm wondering if the function under = is an SQL function being inlined. When I did a similar test, I got sszabo=# create type a as (a int, b int); CREATE TYPE sszabo=# create table q (a a); CREATE TABLE sszabo=# create function feq(a, a) returns boolean as 'select $1.a = $2.a and $1.b = $2.b;' language 'sql'; CREATE FUNCTION sszabo=# create operator = (leftarg=a, rightarg=a, procedure=feq); CREATE OPERATOR sszabo=# explain select * from q where a = '(1,2)'::a; QUERY PLAN --- Seq Scan on q (cost=0.00..0.00 rows=1 width=32) Filter: (((a).a = ('(1,2)'::a).a) AND ((a).b = ('(1,2)'::a).b)) (2 rows) sszabo=# drop operator=(a,a); DROP OPERATOR sszabo=# create function feq2(a, a) returns boolean as 'begin return $1.a = $2.a and $1.b = $2.b; end;' language 'plpgsql'; CREATE FUNCTION sszabo=# create operator = (leftarg=a, rightarg=a, procedure=feq2); CREATE OPERATOR sszabo=# explain select * from q where a = '(1,2)'::a; QUERY PLAN -- Seq Scan on q (cost=0.00..0.00 rows=1 width=32) Filter: (a = '(1,2)'::a) (2 rows) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to return a resultset/table from a sql function?
OK, to answer my own question (typo's possible, works here ;-)): CREATE TYPE abonnementartikelheader AS (col1 int4, col2 int4); CREATE OR REPLACE FUNCTION getabonnementartikelheader(int4) RETURNS SETOF abonnementartikelheader AS ' select (SELECT COUNT(klantnummer)::int4 FROM abo_klt WHERE abonnement = $1), (SELECT SUM(aantal_abonnementen)::int4 FROM abo_klt WHERE abonnement = $1); ' LANGUAGE 'sql' VOLATILE; Groeten, Joost ---(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] index on user defined type
Stephan Szabo [EMAIL PROTECTED] writes: I'm wondering if the function under = is an SQL function being inlined. Bingo --- that's surely it. After inlining, the expression would no longer look like it matched the index. You don't want to use SQL functions to define indexable operators anyway. They leak memory, and are slow, and neither of those are good properties for an index support function. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgresql Text field / Visual FoxPro Memo and ODBC
El 12/01/2005 4:40 PM, [EMAIL PROTECTED] en su mensaje escribio: I just downloaded and installed the lastest PGADMIN III, but I didnt' get a new ODBC driver. The only file in the download was pgadmin3.msi. The driver I have is ODBC version 7.03.02.00 dated 10-22-2003. That is the newest driver I see listed on the Postgresql site. Where can I get the 8 version? I believe odbc 8 is win32 server install http://pgfoundry.org/projects/pginstaller/ -- Sinceramente, Josué Maldonado. Toda ciencia viene del dolor. El dolor busca siempre la causa de las cosas, mientras que el bienestar se inclina a estar quieto y a no volver la mirada atrás. Stefan Zweig. Escritor austríaco. Josue, Are you using Postgresql on a Windows Server or on a Linux Server? Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 ---(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] Postgresql Text field / Visual FoxPro Memo and ODBC
The date on the ODBC driver I have is 10/8/2004. Running on WinXP Pro. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 12, 2005 5:23 PM To: Josué Maldonado Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgresql Text field / Visual FoxPro Memo and ODBC El 12/01/2005 4:40 PM, [EMAIL PROTECTED] en su mensaje escribio: I just downloaded and installed the lastest PGADMIN III, but I didnt' get a new ODBC driver. The only file in the download was pgadmin3.msi. The driver I have is ODBC version 7.03.02.00 dated 10-22-2003. That is the newest driver I see listed on the Postgresql site. Where can I get the 8 version? I believe odbc 8 is win32 server install http://pgfoundry.org/projects/pginstaller/ -- Sinceramente, Josué Maldonado. Toda ciencia viene del dolor. El dolor busca siempre la causa de las cosas, mientras que el bienestar se inclina a estar quieto y a no volver la mirada atrás. Stefan Zweig. Escritor austríaco. Josue, Are you using Postgresql on a Windows Server or on a Linux Server? Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Postgresql Text field / Visual FoxPro Memo and ODBC
Margaret, Josue, Are you using Postgresql on a Windows Server or on a Linux Server? Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 Both platforms, I'm developing with VFP for Postgresql 7.4.3 running on RH Linux 8 and also work in another app for Postgresql 8.0 RC4 win32. I guess your problem has something to do with ODBC settings or something could be wrong on the VFP side, but I'm just guessing. -- Sinceramente, Josué Maldonado. Toda la felicidad que la humanidad puede alcanzar, está, no en el placer, sino en el descanso del dolor. John Dryden. Poeta, dramaturgo y critico inglés. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] index on user defined type
On Wed, 2005-01-12 at 18:12 -0500, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: I'm wondering if the function under = is an SQL function being inlined. Bingo --- that's surely it. After inlining, the expression would no longer look like it matched the index. You don't want to use SQL functions to define indexable operators anyway. They leak memory, and are slow, and neither of those are good properties for an index support function. regards, tom lane Thanks very much guys. I got confused and tried to simplify it to a simple test case and confused myself more when it still didn't work. Just to be sure I redid it in plpgsql with no problems. I understand that index operations should be written in C for production use, or at least tested in C to see if it helps matters. Comparison operators are of course very simple to write in any language anyway. I attached a proposed documentation patch. I wasn't able to readily see the implications of writing a function in SQL regarding an index, so perhaps this will help someone in the future (not that many people will attempt writing index access methods in SQL, but someone could get confused like I did). Regards, Jeff Davis --- postgresql-8.0.0rc4/doc/src/sgml/ref/create_opclass.sgml 2003-11-29 11:51:38.0 -0800 +++ postgresql-8.0.0rc4.new/doc/src/sgml/ref/create_opclass.sgml 2005-01-12 18:06:44.0 -0800 @@ -128,6 +128,10 @@ para The name (optionally schema-qualified) of an operator associated with the operator class. + + Note: this operator should NOT be defined by a SQL function. + If the operator is defined with a SQL function, the SQL function + could be inlined, preventing the use of an index. /para /listitem /varlistentry @@ -172,6 +176,10 @@ para The name (optionally schema-qualified) of a function that is an index method support procedure for the operator class. + + Note: this function should NOT be written in SQL. + If the function is written in SQL, it could be inlined, + preventing the use of an index. /para /listitem /varlistentry ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgresql Text field / Visual FoxPro Memo and ODBC
Ahh, ok. Misunderstood what your method was. Is there a setting in your ODBC driver for memo as text or something like that? It's been a while since I played with pgsql from a windows / odbc box, so I am a bit rusty here. It's just a wild guess. On my Windows box the Postgresql ODBC driver is set with TEXT AS LongVarChar, with a maximum length of 8190. FoxPro then turns the LongVarChar into a Memo. Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 Margaret, I haven't tried using text, but I came across a very similar problem when using VARCHAR(n) columns with foxpro: Since foxpro supports only CHAR (not VARCHAR), then if you have a table like this: create table foo (t varchar(6)); insert into foo (t) values ('aaa'); And, in foxpro, you do this to an updateable view of the table: replace t with 'bbb' Then the SQL that foxpro actually generates looks like this: UPDATE foo SET t = 'bbb' WHERE t = 'aaa '; Notice the WHERE clause says t = 'aaaspacespacespace'; Trailing whitespace is significant for varchar columns, and so the update hits no rows. Now, if foxpro truly thinks that your text columns are of type MEMO then I believe it would generate the correct SQL; however, if you have your ODBC driver set to turn text into VARCHAR(n) columns, then I believe you're being bit by the same bug that I was: foxpro is probably padding your value with 8000 or so blanks because it thinks your text column is varchar(8192). How do you figure this out? Use ethereal. http://www.ethereal.com/ Paul Tillotson ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] pgEdit 1.0b6
One final pgEdit beta before the release of PostgreSQL 8.0. In addition to bug fixes and usability improvements, 1.0b6 includes the following new features: * Direct execution of SQL and psql commands without including them in the document. Commands can be executed at the bottom of the editor window with full completion support, command history navigation, and syntax coloring. * Support for copying syntax colored SQL with HTML and DocBook markup. This produces nice looking output for documentation and publishing. HTML and XSL style specifications are generated from your pgEdit syntax color preferences. * Special editor support for PostgreSQL DROP and EXPLAIN commands. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Question about pg_stat_activity
Hi, When I run the following on my server: SELECT * FROM pg_stat_activity I get back command string not enabled for the field current_query. I have stats enabled in my config file, is there some other setting that I missed so I can see the current query? I also thought that in 7.x it would say idle if the connection was just sitting there. Thanks, Tony ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Working on a new Postgres Admin tool and was wondering...
Would anyone be interested in beta testing it? If so please respond directly to me and I will get back to you with details. It has a really advanced function editor complete with code completion and parameter hinting. It also has a tabbed Enterprise Manager where each database opens in it's own tab and it has a filtering MDI task bar, i.e when you select a DB tab in the enterprise manager it filters the MDI task bar to open windows for that DB. We are only going to support Postgres 8 going forward since it's a windows based app created with Borland Delphi and Zeos and since 8.0 is the only native version for windows, it of course will work on version 8 on any server. We originally were going to support 7.x and 8.0 but after we started to use 8.0 and saw how well 7.0 DBs moved over, we decided to focus on 8 and the win32 users that we hope will be wildly using 8.0 :-) It also has a threaded query editor with the ablility to cancel long running querys plus tons of other really cool features(like a GUI query builder). This app is designed and coded by several Corporate DBAs and programmers who just where not satisfied with PGadmin III (in particular the function editor) here are some rough/quick screen shots http://www.amsoftwaredesign.com/pg_ss.asp.asp We plan on donating a percentage of any sales(it will be shareware to start) back to the project, I don't know who I should contact about that, if anyone knows please let me know. Thanks, Tony Caduto AM Software Design http://www.amsoftwaredesign.com ---(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] vacuum vs open transactions
On Wed, Jan 12, 2005 at 11:49:12AM -0700, Ed L. wrote: Is it possible via SQL query to tell how long a transaction has been open? I'm not aware of a way to find out when a transaction started, but if you have stats_command_string enabled then you can query pg_stat_activity to see when a session's current query started. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Question about pg_stat_activity
On Wed, Jan 12, 2005 at 11:57:18PM -0600, Tony Caduto wrote: When I run the following on my server: SELECT * FROM pg_stat_activity I get back command string not enabled for the field current_query. I have stats enabled in my config file, is there some other setting that I missed so I can see the current query? Is stats_command_string set to true? Did you restart the backend after changing postgresql.conf? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Question about pg_stat_activity
Hi, I have it working now, I must have missed the stats_command_string setting in the conf file. Thanks, Tony Michael Fuhr wrote: On Wed, Jan 12, 2005 at 11:57:18PM -0600, Tony Caduto wrote: When I run the following on my server: SELECT * FROM pg_stat_activity I get back command string not enabled for the field current_query. I have stats enabled in my config file, is there some other setting that I missed so I can see the current query? Is stats_command_string set to true? Did you restart the backend after changing postgresql.conf? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings