[GENERAL] warm standby sheme and postgres service stoping
I use postgres 8.3 on Windows I try to realize warm standby sheme, described in Chapter 24. Backup and Restore in postgres docs (http://www.postgresql.org/docs/8.3/interactive/warm-standby.html#WARM-STANDBY-RECORD). When i try to stop postgres server, it can't be stop because 'restore_command' program is running. What is the right way to stop standby server in warm standby sheme. PS. sorry for bad english -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error log fillingup
Hi All I'm running postgreSQL 7.3 on 2 installations, both with the same problem. The error logs for both installations are filling up with the following; ESTERROR: 42703: column options does not exist ESTLOCATION: transformColumnRef, parse_expr.c: 396 ESTSTATEMENT: select @@options This has become a real nuisance. I have not defined a table 'options' and non of the tables I have defined have a column called 'options'. I am not aware of this call being made, but it is filling up the error logs at an alarming rate. All connections to the databases are through unixODBC. I hope someone can shine some light on this problem. It would have to be affecting the performance of the DB. The DB's have been running for a few years and appear to be stable. Just this error reoccurring. My concern is that it could mask a real error when it happens. Many thanks ;-) attachment: stock_smiley-3.png
Re: [GENERAL] pg_dump exists without any message when running from windows task scheduler
Chris, Thank you. I don't know if you can do that. Why do you need to? I need to run pg_dump at 2:00 AM every night automatically in Windows computer where PostgreSql server is not installed. If you reference the original files (c:\program files\... or where-ever you installed postgres to), does it work through a scheduled task? Yes, scheduled task it works in this case. In some backup clients PostgreSql server is not installed. pg_dump 8.4 and required dlls are simply copied to backup computer from server bin directory. How to create autobackups in this case ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error log fillingup
On 09/09/2009 09:27, Chris Leahy wrote: Hi All I'm running postgreSQL 7.3 on 2 installations, both with the same problem. The error logs for both installations are filling up with the following; ESTERROR: 42703: column options does not exist ESTLOCATION: transformColumnRef, parse_expr.c: 396 ESTSTATEMENT: select @@options This has become a real nuisance. I have not defined a table 'options' and non of the tables I have defined have a column called 'options'. That may well be the problem - a query is referring to a column called options which as you've said doesn't exist, hence the error message. I've also seen this when a string value isn't quoted, e.g. - insert into () values ('abc', options, ...) ... When this happens the server thinks that the query is taking the value of a column called 'options', instead of the literal value 'options'. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error log fillingup
That query looks suspiciously similar to a SQL Server (Sybase or Microsoft) query. @@options is the name of a SQL Server global variable. It looks like your client application thinks that it is connected to a copy of SQL Server, not Postgres. -- Korry On Sep 9, 2009, at 4:27 AM, Chris Leahy cle...@mystrata.com.au wrote: Hi All I'm running postgreSQL 7.3 on 2 installations, both with the same problem. The error logs for both installations are filling up with the following; ESTERROR: 42703: column options does not exist ESTLOCATION: transformColumnRef, parse_expr.c: 396 ESTSTATEMENT: select @@options This has become a real nuisance. I have not defined a table 'options' and non of the tables I have defined have a column called 'options'. I am not aware of this call being made, but it is filling up the error logs at an alarming rate. All connections to the databases are through unixODBC. I hope someone can shine some light on this problem. It would have to be affecting the performance of the DB. The DB's have been running for a few years and appear to be stable. Just this error reoccurring. My concern is that it could mask a real error when it happens. Many thanks stock_smiley-3.png -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] WAL archiving file name collision
Greetings! We've got a moderately busy DB host running PostgreSQL 8.3.7 from Debian Lenny with WAL archiving. It's been rsyncing its WAL files to a backup host for the last year or so, with the following archive_command: ssh pgbac...@backup test ! -f pgbackup-cirkus/%f rsync -az %p pgbac...@backup:pgbackup-cirkus/%f Recently, WAL archiving begain failing on the test which checks wether the file exists. This first occured two hours after an incident where someone edited pg_hba.conf and left it with permissions denying Postgres read access to it. Upon SIGHUP the cluster naturally shut down. It was discovered promptly, and according to this person, there were some processes named postgres still running. He ran /etc/init.d/postgresql-8.3 start anyway, which brought up the cluster: 2009-09-07 20:39:55 CEST 5782 LOG: received SIGHUP, reloading configuration files 2009-09-07 20:39:55 CEST 5782 FATAL: could not open configuration file /etc/postgresql/8.3/main/pg_hba.conf: Permission denied 2009-09-07 20:40:07 CEST 14398 mdb2 mdb2web LOG: could not receive data from client: Connection reset by peer 2009-09-07 20:40:07 CEST 14398 mdb2 mdb2web LOG: unexpected EOF on client connection 2009-09-07 20:40:15 CEST 14485 mdb2 billig_web LOG: could not receive data from client: Connection reset by peer 2009-09-07 20:40:15 CEST 14485 mdb2 billig_web LOG: unexpected EOF on client connection 2009-09-07 20:41:29 CEST 16197 LOG: could not load root certificate file root.crt: no SSL error reported 2009-09-07 20:41:29 CEST 16197 DETAIL: Will not verify client certificates. 2009-09-07 20:41:29 CEST 16197 FATAL: could not open configuration file /etc/postgresql/8.3/main/pg_hba.conf: Permission denied 2009-09-07 20:42:04 CEST 16748 LOG: could not load root certificate file root.crt: no SSL error reported 2009-09-07 20:42:04 CEST 16748 DETAIL: Will not verify client certificates. 2009-09-07 20:42:04 CEST 16749 LOG: database system was interrupted; last known up at 2009-09-07 20:37:38 CEST 2009-09-07 20:42:04 CEST 16749 LOG: database system was not properly shut down; automatic recovery in progress 2009-09-07 20:42:04 CEST 16749 LOG: redo starts at 65/F00718D0 2009-09-07 20:42:04 CEST 16749 LOG: record with zero length at 65/F1039488 2009-09-07 20:42:04 CEST 16749 LOG: redo done at 65/F1039458 2009-09-07 20:42:04 CEST 16749 LOG: last completed transaction was at log time 2009-09-07 20:39:52.010594+02 2009-09-07 20:42:04 CEST 16749 LOG: checkpoint starting: shutdown immediate 2009-09-07 20:42:04 CEST 16750 [unknown] [unknown] LOG: connection received: host=[local] 2009-09-07 20:42:04 CEST 16750 [unknown] [unknown] LOG: incomplete startup packet 2009-09-07 20:42:04 CEST 16749 LOG: checkpoint complete: wrote 43 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 6 recycled; write=0.001 s, sync=0.429 s, total=0.464 s 2009-09-07 20:42:04 CEST 16756 [unknown] [unknown] LOG: connection received: host=2001:700:300:1800::1917 port=50140 2009-09-07 20:42:04 CEST 16749 LOG: recovering prepared transaction 809084 2009-09-07 20:42:04 CEST 16756 mdb2 uka_web FATAL: the database system is starting up 2009-09-07 20:42:04 CEST 16757 [unknown] [unknown] LOG: connection received: host=2001:700:300:1800::1917 port=50141 2009-09-07 20:42:04 CEST 16757 mdb2 uka_web FATAL: the database system is starting up 2009-09-07 20:42:05 CEST 16762 [unknown] [unknown] LOG: connection received: host=2001:700:300:1800::1919 port=36523 2009-09-07 20:42:05 CEST 16762 greylisting exim FATAL: the database system is starting up 2009-09-07 20:42:05 CEST 16763 [unknown] [unknown] LOG: connection received: host=2001:700:300:1800::1919 port=36524 2009-09-07 20:42:05 CEST 16763 greylisting exim FATAL: the database system is starting up 2009-09-07 20:42:05 CEST 16764 [unknown] [unknown] LOG: connection received: host=2001:700:300:1800::1919 port=36525 2009-09-07 20:42:05 CEST 16764 greylisting exim FATAL: the database system is starting up 2009-09-07 20:42:05 CEST 16765 [unknown] [unknown] LOG: connection received: host=2001:700:300:1800::1919 port=36526 2009-09-07 20:42:05 CEST 16765 greylisting exim FATAL: the database system is starting up 2009-09-07 20:42:05 CEST 16768 [unknown] [unknown] LOG: connection received: host=[local] 2009-09-07 20:42:05 CEST 16768 postgres postgres FATAL: the database system is starting up 2009-09-07 20:42:05 CEST 16776 LOG: autovacuum launcher started 2009-09-07 20:42:05 CEST 16748 LOG: database system is ready to accept connections 2009-09-07 20:42:05 CEST 16786 [unknown] [unknown] LOG: connection received: host=[local] 2009-09-07 20:42:05 CEST 16786 postgres postgres LOG: connection authorized: user=postgres database=postgres The cluster recovered, which strikes me as odd - shouldn't Postgres at least rollback uncommited transactions and shut down cleanly upon an unreadable configuration file? Anyway, the cluster ran fine,
Re: [GENERAL] where clauses and multiple tables
On Tue, 08 Sep 2009 18:50:49 -0700, John R Pierce wrote about Re: [GENERAL] where clauses and multiple tables: Yaroslav Tykhiy wrote: By the way, folks, do you think there may be performance gain or loss from rewriting this with an explicit JOIN? E.g.: SELECT DISTINCT foo.foo_id, foo.name FROM foo JOIN bar ON foo.bar_id = bar.bar_id WHERE bar.name='martini'; I would expect that to be more efficient as its the 'proper' SQL way of doing things, Actually, since the bar table does not supply any of the result columns, the IN predicate is a more idiomatic (or proper) way of coding the query. and the optimizer will do a better job on it, especially if foo.bar_id is a FK to bar.bar_id's primary key. The optimizer *should* produce the same plan, either way. btw, can't this be written... SELECT DISTINCT foo.foo_id, foo.name FROM foo JOIN bar ON bar_id WHERE bar.name='martini'; The DISTINCT qualifier potentially changes the semantics, so the immediate answer is No. -- Regards, Dave [RLU #314465] === david.w.n...@ntlworld.com (David W Noon) === -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to store data on an external drive
Hi Sam, Thanks for your help. Your solution seems to work. I may let you know if I run into problems. Thanks again. Best, Jia On Tue, Sep 8, 2009 at 11:31 AM, Sam Masons...@samason.me.uk wrote: [ please CC the mailing list and not the list owner, they answer mailing list questions not PG questions ] On Tue, Sep 08, 2009 at 10:31:50AM -0400, Jia Chen wrote: Sam Mason wrote: I don't think you need to go that far. I'd just do an initdb somewhere on the removable disk and then start PG pointing at where the cluster was (i.e. postgres -D /media/disk/psqldata) and all should be good. I'd stay away from the official system startup scripts for PG. If I am not mistaken, the paragraph above means that I don't need to reinstall postgresql from source. Yes; these are all standard programs included with Debian/Ubuntu packages as normal. Have a look through the man pages for: update-rc.d initdb postgres Yup, the table data is very tied to the state of transactions and other system level information, you need to keep everything together unfortunately. This is the price of having transactions with ACID semantics. However, this paragraph implies that I do need to put other system level information together on the external drive. Do you mean that I can put it together without re-installation? If so, could you offer some hints on how to do that? Thanks. initdb creates a new PG cluster (i.e. the set of files that PG considers to be a database). You should direct this to be run on your external disk and then get PG running using this cluster. This is what postgres -D /media/disk/psqldata does, i.e. start the postgres server. Once it's started you can connect to it from the normal clients, psql, odbc whatever you want. I expect all you need to run is: sudo /etc/init.d/postgresql-8.3 stop sudo update-rc.d -f postgresql-8.3 remove initdb /media/disk/psqldata postgres -D /media/disk/psqldata from there on, all you need to do is to run the last line when you plug the drive in. Before you take the drive out, just hit the normal Ctrl+C and PG will shutdown cleanly. -- Sam http://samason.me.uk/ -- Ohio State University - Finance 248 Fisher Hall 2100 Neil Ave. Columbus, Ohio 43210 Telephone: 614-292-2979 http://www.fisher.osu.edu/~chen_1002/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Swapped download links for 8.4.1 zip binaries
Hi, if someone from EnterpriseDB is listening: The links for Windows and OSX binaries at http://www.enterprisedb.com/products/pgbindownload.do are interchanged. When clicking on the Windows icon, you'll get the Mac binaries, when clicking on the Mac icon, you'll get the windows binaries :) Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Swapped download links for 8.4.1 zip binaries
that's part of action 'if you have a PC, buy a mac. But if you're tired of Mac os x, install Windows' ;) Probably part of soon coming windows7 happening. ;) ;) ;) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] vacuum won't even start
Hi all, I've a problem on a heavy loaded database: vacuums don't work since about a week. All I got is: mybase=# vacuum verbose analyze public.mytable; INFO: vacuuming public.mytable (I stop it after hours) Looking with top and iotop, I see the process takes some cpu and disk io time during several minutes, then it seems to fall asleep. The process isn't locked according to pg_stat_activity. My setup: - postgresql 8.3.7 with contribs ltree and pgcrypto - OS: debian etch kernel 2.6.24 - HW: 8cores Xeon/32GB RAM/3RAID10 volumes(index, data, pgxlog) - dbase size: about 240GB - millions of queries/day - 1000 locks continually - about 200 simultanous connections - load: 30%iowait, 60%user, 10%sys Autovacuum is disabled to prevent it from loading the server during peak hours. Regular vacuums running each night as cron job Since about a week the nightly vacuums don't work. I tried manual ones with no avail, same symptoms as above on small tables (350 rows) as well as on big ones (almost 1 billion rows) As the croned vacuums don't run anymore, I see now autovacuums (to prevent wraparound) running all the time, but their process don't use any cpu time nor disk io. Autovacuum seems to work well on the pg_catalog schema. The problem seems to start with some queries lasting more 15 hours. I tried to kill them (signal 15) with no avail. I can't restart the server as it's a big production server. We're planning to upgrade the hardware soon, but I suspect we'll have the same problems in the future as our platform is growing. Does anyone have any info about this problem, and the means to prevent it ? Thanks in advance. Regards, -- JC Ph'nglui mglw'nafh Cthulhu n'gah Bill R'lyeh Wgah'nagl fhtagn! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] vacuum won't even start
Jean-Christophe Praud wrote: Hi all, I've a problem on a heavy loaded database: vacuums don't work since about a week. All I got is: mybase=# vacuum verbose analyze public.mytable; INFO: vacuuming public.mytable (I stop it after hours) Looking with top and iotop, I see the process takes some cpu and disk io time during several minutes, then it seems to fall asleep. The process isn't locked according to pg_stat_activity. What are your vacuum_cost_% parameters? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] vacuum won't even start
Jean-Christophe Praud j...@steek.com writes: I've a problem on a heavy loaded database: vacuums don't work since about a week. All I got is: mybase=# vacuum verbose analyze public.mytable; INFO: vacuuming public.mytable (I stop it after hours) Looking with top and iotop, I see the process takes some cpu and disk io time during several minutes, then it seems to fall asleep. The process isn't locked according to pg_stat_activity. When vacuum wants to clean up a particular table page, it will wait until no other process is examining that page; and this wait is not visible in pg_locks. Perhaps you have got some queries referencing those tables that have stopped midway and are just sitting? Although pg_locks won't immediately show the wait, it could be useful to help identify the culprit --- look for other processes holding any type of lock on the table the vacuum is stuck on, and then go to pg_stat_activity to see how old their current query is. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] vacuum won't even start
Alvaro Herrera a écrit : Jean-Christophe Praud wrote: Hi all, I've a problem on a heavy loaded database: vacuums don't work since about a week. All I got is: mybase=# vacuum verbose analyze public.mytable; INFO: vacuuming public.mytable (I stop it after hours) Looking with top and iotop, I see the process takes some cpu and disk io time during several minutes, then it seems to fall asleep. The process isn't locked according to pg_stat_activity. What are your vacuum_cost_% parameters? I've let the default values (not even uncommented in the conf file ;) ): #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-1 credits #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty = 20# 0-1 credits #vacuum_cost_limit = 200# 1-1 credits -- JC Ph'nglui mglw'nafh Cthulhu n'gah Bill R'lyeh Wgah'nagl fhtagn!
Re: [GENERAL] vacuum won't even start
Tom Lane a écrit : Jean-Christophe Praud j...@steek.com writes: I've a problem on a heavy loaded database: vacuums don't work since about a week. All I got is: mybase=# vacuum verbose analyze public.mytable; INFO: vacuuming public.mytable (I stop it after hours) Looking with top and iotop, I see the process takes some cpu and disk io time during several minutes, then it seems to fall asleep. The process isn't locked according to pg_stat_activity. When vacuum wants to clean up a particular table page, it will wait until no other process is examining that page; and this wait is not visible in pg_locks. Perhaps you have got some queries referencing those tables that have stopped midway and are just sitting? Although pg_locks won't immediately show the wait, it could be useful to help identify the culprit --- look for other processes holding any type of lock on the table the vacuum is stuck on, and then go to pg_stat_activity to see how old their current query is. regards, tom lane Indeed, the tables I tried to vacuum have locks on them. AccessShareLock belonging to queries which seem sleeping. I tried to kill these queries but pg_cancel_backend() has no effect, and the process doesn't get the 15 signal. How can I get rid of these blocking queries without restarting the server ? They are not listed as waiting in pg_stat_activity. These queries are MOVE FORWARD on cursors, the underlying query is a rather complex one (unions, joins, functions calls) Regards, -- JC Ph'nglui mglw'nafh Cthulhu n'gah Bill R'lyeh Wgah'nagl fhtagn!
Re: [GENERAL] Swapped download links for 8.4.1 zip binaries
On Wed, Sep 9, 2009 at 11:01 AM, Thomas Kellerer spam_ea...@gmx.net wrote: Hi, if someone from EnterpriseDB is listening: The links for Windows and OSX binaries at http://www.enterprisedb.com/products/pgbindownload.do are interchanged. When clicking on the Windows icon, you'll get the Mac binaries, when clicking on the Mac icon, you'll get the windows binaries :) This is now fixed. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- -- Jim Mlodgenski EnterpriseDB (http://www.enterprisedb.com)
Re: [GENERAL] vacuum won't even start
Jean-Christophe Praud j...@steek.com writes: Indeed, the tables I tried to vacuum have locks on them. AccessShareLock belonging to queries which seem sleeping. I tried to kill these queries but pg_cancel_backend() has no effect, and the process doesn't get the 15 signal. How can I get rid of these blocking queries without restarting the server ? They are not listed as waiting in pg_stat_activity. Have you tried killing the connected client sessions? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] vacuum won't even start
Tom Lane a écrit : Jean-Christophe Praud j...@steek.com writes: Indeed, the tables I tried to vacuum have locks on them. AccessShareLock belonging to queries which seem sleeping. I tried to kill these queries but pg_cancel_backend() has no effect, and the process doesn't get the 15 signal. How can I get rid of these blocking queries without restarting the server ? They are not listed as waiting in pg_stat_activity. Have you tried killing the connected client sessions? regards, tom lane It works ! I had pgbouncer connections hanging for several days. Thanks for your help :) Regards, -- JC Ph'nglui mglw'nafh Cthulhu n'gah Bill R'lyeh Wgah'nagl fhtagn!
[GENERAL] help me please with function
Help me please with this function writed in MSSQL. I want to pass on Postgres CREATE OR REPLACE FUNCTION GetProductsByCategoryId (IN CategoryId int, IN PageIndex int, IN NumRows int, OUT CategoryName varchar(50), OUT CategoryProductCount int) RETURNS SETOF RECORD LANGUAGE plpgsql as $$ DECLARE startRowIndex int; BEGIN /* The below statements enable returning the Total Product Count and friendly Name for the CategoryId as output paramsters to our SPROC. This enables us to avoid having to make a separate call to the database to retrieve them, and can help improve performance quite a bit */ CategoryProductCount = (SELECT COUNT(*) FROM Products where Products.CategoryId = CategoryId) CategoryName = (SELECT Name FROM Categories Where Categories.CategoryID = CategoryId) /* The below statements use the new ROW_NUMBER() function in SQL 2005 to return only the specified rows we want to retrieve from the Products table */ --Declare @startRowIndex INT; --set @startRowIndex = (@PageIndex * @NumRows) + 1; startRowIndex := (PageIndex * NumRows) + 1; With ProductEntries as ( (SELECT ROW_NUMBER() OVER (ORDER BY ProductId ASC) as Row, ProductId, CategoryId, Description, ProductImage, UnitCost FROM Products WHERE CategoryId=CategoryId) ) (Select ProductId, CategoryId, Description, ProductImage, UnitCost FROM ProductEntries WHERE Row between startRowIndex and StartRowIndex+NumRows-1) END; $$; I have a problem whis instraction With ProductEntries as ( (SELECT ROW_NUMBER() OVER (ORDER BY ProductId ASC) as Row, ProductId, CategoryId, Description, ProductImage, UnitCost FROM Products WHERE CategoryId=CategoryId) ) I can not find command in postgres - With ... as My changed function is CREATE OR REPLACE FUNCTION public.getproductsbycategoryid_refcursor (categoryid integer, pageindex integer, numrows integer, out product_id integer, out category_id integer, out description varchar, out product_image varchar, out unit_cost varchar) RETURNS SETOF record AS $body$ DECLARE startrowindex int; categoryproductcount int; categoryname varchar(50); BEGIN SELECT COUNT(*) INTO categoryproductcount FROM products where products.category_id=categoryid; SELECT name INTO categoryname FROM categories Where categories.category_id=categoryid; startrowindex = (pageindex * numrows) + 1; -- ERROR HERE With productentries as SELECT ROW_NUMBER() OVER (ORDER BY products.category_id ASC) as Row, products.product_id, products.category_id, products.description, products.product_image, products.unit_cost FROM products WHERE products.category_id=categoryid; RETURN QUERY SELECT productentries.product_id, productentries.category_id, productentries.description, productentries.product_image, productentries.unit_cost FROM productentries WHERE productentries.row between startrowindex and startrowindex+numrows-1; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100 ROWS 1000; Help me please, excuse me for bad inglish ;)
Re: [GENERAL] help me please with function
I can not find command in postgres - With ... as You need Postgres 8.4 for that: http://www.postgresql.org/docs/current/static/queries-with.html Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump exists without any message when running from windows task scheduler
Andrus wrote: Chris, Thank you. I don't know if you can do that. Why do you need to? I need to run pg_dump at 2:00 AM every night automatically in Windows computer where PostgreSql server is not installed. The installer should have an option for 'client only' so you end up with psql, pg_dump and friends but not the database. -- Postgresql php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Force termination of an idle connection
Hi - I have a multi-threaded application that spawns child processes, inserts information into the database, then disconnects. For whatever reason, some of the children do not disconnect and the database is left with idle connections (that eventually max out over time). How can I either force the child thread to terminate the connection or have the database force a disconnect after a specified time of inactivity? This is not an embedded C application, and I believe the `statement_timeout` option only applies to the database latency for each statement (though please correct me if I am wrong). Ideally, the solution would be the logical equivalent of either `terminate after n (milli)seconds of inactivity` or, a less desirable, `keep this connection open for at most n seconds/minutes.` I'd prefer to avoid polling the pg_stat_activity table to kill pids Thanks! -- View this message in context: http://www.nabble.com/Force-termination-of-an-idle-connection-tp25375135p25375135.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] COPY command character set
The documentation of the COPY command does not state what character set(s) are recognized or written. I need to import and export UTF-8 data; how can I do that? -- Peter Headland Architect Actuate Corporation
Re: [GENERAL] COPY command character set
Peter Headland pheadl...@actuate.com writes: The documentation of the COPY command does not state what character set(s) are recognized or written. I need to import and export UTF-8 data; how can I do that? set client_encoding = 'utf8'; copy from stdin/to stdout; regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general