[GENERAL] Postgres Disconnection problems
Hi, We are using PostgresDAC 2.2.1 and PostgreSQL 8.0.2 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 4.0.0 20050412 (Red Hat 4.0.0-0.42). I perform a simple test as follows. 1. I connect to the database, which is located on a LAN. 2. I simulate Internet problems by unplugging the Ethernet cable of the client. There is no PSQL activity going on. 3. Plug the ethernet cable back in 4. Run some sql, which gives me a Postgres SQL error -1, Server closed connection unexpectedly So far so good. Problem is that the postmaster does not detect this connection as dead and keeps it idle for an unknown amount of time. This is a real problem for us because we use persistent connections to authorize access to a custom built 68030 based system, which has a limited number of slots that we can use. By not releasing a dead connection we are also holding that 68030 slot busy. We could develop some kind of watchdog timer in the Daemon that handles the authorization but I was wondering if Postgres already have something similar built in. Any ideas ? Thanks /Otto Blomqvist ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Custom C function shutdown-signaling
Once again Tom comes threw and saves the day...! Thanks Tom ! /Otto Blomqvist Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Otto Blomqvist [EMAIL PROTECTED] writes: How do I signal a custom C function that the Postmaster wants to shut down ? Do CHECK_FOR_INTERRUPTS(); at a suitable spot in the C function's main loop. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Adding miscadmin.h generates errors ?
After adding miscadmin.h to my source I get some errors, shown below. I'm sure its something simple like adding a flag or similar, but I'v spent hours on this now. Im running Fedora 3 and Postgres 8.0.3. GCC 3.4.3 20050227. Any ideas ? Thanks /Otto Blomqvist gcc -Wall -c commfunct.c In file included from commfunct.c:62: miscadmin.h:67: error: syntax error before volatile miscadmin.h:67: warning: type defaults to `int' in declaration of `InterruptPending' miscadmin.h:67: warning: data definition has no type or storage class miscadmin.h:68: error: syntax error before QueryCancelPending miscadmin.h:68: warning: type defaults to `int' in declaration of `QueryCancelPending' miscadmin.h:68: warning: data definition has no type or storage class miscadmin.h:69: error: syntax error before ProcDiePending miscadmin.h:69: warning: type defaults to `int' in declaration of `ProcDiePending' miscadmin.h:69: warning: data definition has no type or storage class miscadmin.h:72: error: syntax error before ImmediateInterruptOK miscadmin.h:72: warning: type defaults to `int' in declaration of `ImmediateInterruptOK' miscadmin.h:72: warning: data definition has no type or storage class miscadmin.h:73: error: syntax error before InterruptHoldoffCount miscadmin.h:73: warning: type defaults to `int' in declaration of `InterruptHoldoffCount' miscadmin.h:73: warning: data definition has no type or storage class miscadmin.h:74: error: syntax error before CritSectionCount miscadmin.h:74: warning: type defaults to `int' in declaration of `CritSectionCount' miscadmin.h:74: warning: data definition has no type or storage class miscadmin.h:124: error: syntax error before IsPostmasterEnvironment miscadmin.h:124: warning: type defaults to `int' in declaration of `IsPostmasterEnvironment' miscadmin.h:124: warning: data definition has no type or storage class miscadmin.h:125: error: syntax error before IsUnderPostmaster miscadmin.h:125: warning: type defaults to `int' in declaration of `IsUnderPostmaster' miscadmin.h:125: warning: data definition has no type or storage class miscadmin.h:127: error: syntax error before ExitOnAnyError miscadmin.h:127: warning: type defaults to `int' in declaration of `ExitOnAnyError' miscadmin.h:127: warning: data definition has no type or storage class miscadmin.h:131: error: syntax error before int miscadmin.h:134: error: syntax error before int miscadmin.h:139: error: syntax error before char miscadmin.h:151: error: syntax error before Oid miscadmin.h:151: warning: type defaults to `int' in declaration of `MyDatabaseId' miscadmin.h:151: warning: data definition has no type or storage class miscadmin.h:153: error: syntax error before Oid miscadmin.h:153: warning: type defaults to `int' in declaration of `MyDatabaseTableSpace' miscadmin.h:153: warning: data definition has no type or storage class miscadmin.h:197: error: syntax error before HasCTZSet miscadmin.h:197: warning: type defaults to `int' in declaration of `HasCTZSet' miscadmin.h:197: warning: data definition has no type or storage class miscadmin.h:202: error: syntax error before enableFsync miscadmin.h:202: warning: type defaults to `int' in declaration of `enableFsync' miscadmin.h:202: warning: data definition has no type or storage class miscadmin.h:203: error: syntax error before allowSystemTableMods miscadmin.h:203: warning: type defaults to `int' in declaration of `allowSystemTableMods' miscadmin.h:203: warning: data definition has no type or storage class miscadmin.h:204: error: syntax error before int miscadmin.h:205: error: syntax error before int miscadmin.h:214: error: syntax error before VacuumCostActive miscadmin.h:214: warning: type defaults to `int' in declaration of `VacuumCostActive' miscadmin.h:214: warning: data definition has no type or storage class miscadmin.h:229: error: syntax error before Oid miscadmin.h:234: error: syntax error before userid miscadmin.h:235: error: syntax error before GetUserId miscadmin.h:235: warning: type defaults to `int' in declaration of `GetUserId' miscadmin.h:235: warning: data definition has no type or storage class miscadmin.h:236: error: syntax error before userid miscadmin.h:237: error: syntax error before GetSessionUserId miscadmin.h:237: warning: type defaults to `int' in declaration of `GetSessionUserId' miscadmin.h:237: warning: data definition has no type or storage class miscadmin.h:238: error: syntax error before userid miscadmin.h:241: error: syntax error before userid miscadmin.h:247: error: syntax error before superuser miscadmin.h:247: warning: type defaults to `int' in declaration of `superuser' miscadmin.h:247: warning: data definition has no type or storage class miscadmin.h:248: error: syntax error before superuser_arg miscadmin.h:248: error: syntax error before userid miscadmin.h:248: warning: type defaults to `int' in declaration of `superuser_arg' miscadmin.h:248: warning: data definition has no type or storage class miscadmin.h:305: error: syntax error before InitPostgres
[GENERAL] Custom C function shutdown-signaling
How do I signal a custom C function that the Postmaster wants to shut down ? I want to use pg_ctl restart -mf and not pg_ctl restart -mi because of data integrity concerns... Any ideas ? Thanks /Otto Blomqvist ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Setting all elements in an Bool[] array to the same value
Hello ! Is there any way to set all elements in a long boolean array (bool[]) to the same value ? update testbool set all elements = false;or so ? ;) Any ideas ? Thanks /Otto Blomqvist ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Problems with Set Returning Functions (SRFs)
Helloo ! We have a database that contains data that we need to Parse. Ideally I would like write a C-function, ParseData, and run select ParseData([data_column]) from datatable where date='2005-05-05'; and have it return 5 columns with the parsed data. Each row in Data_column will potentially create multiple output-rows. I did some research and SRF seems to be the solution (?). After playing around with the TestPassByVal example on the postgres website (http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html) I'v ran into troubles. Here is the type definion CREATE TYPE __testpassbyval AS (f1 integer, f2 integer, f3 integer); CREATE OR REPLACE FUNCTION testpassbyval(integer, integer) RETURNS SETOF __testpassbyval AS 'filename', 'testpassbyval' LANGUAGE C IMMUTABLE STRICT; First paramter is the number of rows the function returns. Second Parameter is the multiplier. First we Try secom=# select testpassbyval(2, 5); testpassbyval --- (5,10,15) (5,10,15) (2 rows) Then we can extract the columns using secom=# select f1, f2, f3 from testpassbyval(2, 5); f1 | f2 | f3 ++ 5 | 10 | 15 5 | 10 | 15 (2 rows) So far so good. But What I want is to feed the testpassbyval function with data from a column (data_column) Creating a test table with column data_column having integers from 1 trew 9 we get secom=# select testpassbyval(2, data_column) from datatable; testpassbyval --- (1,2,3) (1,2,3) (2,4,6) (2,4,6) (3,6,9) (3,6,9) (4,8,12) (4,8,12) (5,10,15) (5,10,15) (6,12,18) (6,12,18) (7,14,21) (7,14,21) (8,16,24) (8,16,24) (9,18,27) (9,18,27) (18 rows) Looking good. Now I try to extract the columns secom=# select f1, f2, f3 from testpassbyval(1, (Select number1 from test)); ERROR: more than one row returned by a subquery used as an expression This is where I fail. Am I even on the right path here ? Writing the actual parsing function will be easy once I have a working concept. Any ideas ? Thanks a lot /Otto Blomqvist I'm Running PSQL 8.0.0 on Linux 8.0 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Views + UNION ALL = Slow ?
Hello ! I have two tables (which contains individual months' data). One of them contains 500 thousand records and the other one about 40k, 8 columns. When I do a simple query on them individually it takes milli-seconds to complete (see gory details below). For some querys I want to include data from multiple months so I created a view using Union all. But Using the view it takes 31 Seconds to complete the same query. I am obviously doing something wrong or using something the wrong way. Any Ideas ? /Otto Blomqvist test=# explain analyze select fid_2 from file_92_904 where fid_4=1024; NOTICE: QUERY PLAN: Index Scan using file_92_904_ltn_idx on file_92_904 (cost=0.00..219.90 rows=65 width=4) (actual time=0.49..0.49 rows=0 loops=1) Total runtime: 0.57 msec EXPLAIN test=# explain analyze select fid_2 from file_92_1004 where fid_4=1024; NOTICE: QUERY PLAN: Index Scan using file_92_1004_ltn_idx on file_92_1004 (cost=0.00..4505.20 rows=1197 width=4) (actual time=32.36..32.36 rows=0 loops=1) Total runtime: 32.46 msec EXPLAIN test=# create view twotables as select * from file_92_1004 UNION ALL Select * from file_92_904; CREATE test=# explain analyze select fid_2 from twotables where fid_4=1024; NOTICE: QUERY PLAN: Subquery Scan twotables (cost=1.00..200023000.53 rows=569553 width=203) (actual time=31590.97..31590.97 rows=0 loops=1) - Append (cost=1.00..200023000.53 rows=569553 width=203) (actual time=12.13..30683.67 rows=569553 loops=1) - Subquery Scan *SELECT* 1 (cost=1.00..100021799.06 rows=540306 width=199) (actual time=12.12..28417.81 rows=540306 loops=1) - Seq Scan on file_92_1004 (cost=1.00..100021799.06 rows=540306 width=199) (actual time=12.09..14946.47 rows=540306 loops=1) - Subquery Scan *SELECT* 2 (cost=1.00..11201.47 rows=29247 width=203) (actual time=0.19..1525.18 rows=29247 loops=1) - Seq Scan on file_92_904 (cost=1.00..11201.47 rows=29247 width=203) (actual time=0.14..793.34 rows=29247 loops=1) Total runtime: 31591.34 msec EXPLAIN ---(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] Returning Errors from User Defined C-functions
Hello ! I have several functions that are compiled as .so and runs pretty well under PSQL. However I would like to be able to return errors (for debugging) using something like this fprintf(stderr, BEGIN command failed\n); Problem is I don't know where this would show up..? In some Postgres log maybe ? I am not directly calling these functions, they are called from a Daemon, also written in C running on the same box. Any ideas ? Thanks a lot /Otto Blomqvist ---(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] pg_dump and pg_restore problems
Hello ! I was just wondering if anyone knows if this is a bug or whats up. Im using psql 7.2.2 I can do full restores and dumps all day long, however when I do the following I run into problems Do a full dump with pg_dump -Fc -O Database1 dump.tar I restore one table to Database1 using pg_restore -t table_25 -c -O -d database1 dump.tar I dump database1 again pg_dump -Fc -O Database1 dump2.tar Then I create a new Database and attempt a full pg_restore into the new DB. Which, does not work. It does not restore the database but throws me a table_25 relation does not exist - message instead. But the table_25 is present in database1, it just seems like the dump does not see it, after a partial restore. Any ideas? Thanks /Otto Blomqvist ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Speed of pg_dump -l -s (List Schema) Variations
Hello! I have a small database (10MB gz dump). When I do a pg_dump -l -s (to list the schema) of the original database it takes below 1 second. But when I do dump of a copy of the database (using a full restore into a new DB) it takes like 10-15 seconds to do the schema list (pg_dump -l -s). I need to compare the schemes of about 20 tables and this takes a while... Anyone have any ideas ? I can't figure out why the newly created copy would be so much slower. Thanks /Otto Blomqvist ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
