Re: [GENERAL] PG 924, Windows 2012, error code 487
Hi, Does anyone know whether this link has an open bug? Maybe a fix? http://www.postgresql.org/message-id/5046caeb.4010...@grammatech.com Thanks Danny -- 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 924, Windows 2012, error code 487
Hi, Are the servers on the Windows versions you mention or the clients or both? Both clients as well as server are located on the same Windows Server machine. Where is this error showing up, the Windows or Postgres logs? Both. It appears in the pg_log as well as failing the client What does the other log show? Nothing special. Once logged in PG is fine. Is there anything preceding the above that might shed light? Unfortunately not. Memory problems within Windows (Error 487) used to be common in 8.3.7 ... but got solved in 8.3.15. We are now having them again with 9.2.4. Are the connections across a network and if so is there a firewall between the clients and server(s)? No FW involved. All the connections are local. This is a pure , inconsistent Server problem. See BUG #5578: postrgesql database crash Thanks Danny -- 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 924, Windows 2012, error code 487
Hi, Thanks a lot for your reply ... From what I can find WSACancelBlockingCall is a Winsock function has to do with networking/messaging and not memory issues per se. So what makes you think it is a memory issue or are the memory problems a separate issue? See below for more detail on WSACancelBlockingCall, it might help you determine how to debug: http://www.sockets.com/winsock.htm#CancelBlockingCall Looked at the bug report and the last comment was from Robert Haas who indicated there was not enough information to work with. I would say we are at that same point now. In your first post you mention that this is related to connection attempts. So: What clients are connecting and is it restricted to particular clients? This is in fact a long installation thread that is using JDBC code, some libpq/C, some psql scripts. Not many connections in parallel. Just one thread that is opening and closing a few connections to the PG server. How many connections are you attempting at a time? Very few of them You said many connection refusals are happening, how many as percent of total? This is a brand new server, automatically installed. Once initdb is over ... PG will not allow connections. You also mentioned 'Sometimes it even fails the AUTOVACUM process'. 2014-01-07 15:56:11.232 GMTLOG: autovacuum launcher started 2014-01-07 15:56:23.687 GMTLOG: could not reserve shared memory region (addr=0108) for child 1354: error code 487 2014-01-07 15:56:23.687 GMTLOG: could not fork new process for connection: A blocking operation was interrupted by a call to WSACancelBlockingCall. What does that mean, the AUTOVACUM process cannot connect, it connects but then fails, other? As you see above, it does not connect... Also what is the log entry when AUTOVACUM fails? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG 924, Windows 2012, error code 487
Hi, Since moving to PG 9.2.4 we are facing many connection refusals from PG Mainly on Windows 8, Server 2012 and Server2012R2. Error ... could not fork new process for connection: A blocking operation was interrupted by a call to WSACancelBlockingCall Sometimes it even fails the AUTOVACUM process. Thanks Danny
[GENERAL] Query fluctuates .... Strange strange .....
PG 8.3.7 on HP 11.31. Very strong Server with lots of memory. A query runs that inserts 40K records takes 1 second when run from psql. It takes 20 minutes!! And 100% CPU !! when called from a standalone JDBC program. No other users are running !!! The problematic query: 2013-01-02 15:00:38.008 ISTLOG: duration: 1207547.589 ms execute unnamed: insert into CMR_IOALOG_2 select * from CMR_IOALOG_PRE where ORDERNO in (select ORDERNO from CMR_AJF_PRE) or ORDERNO=0 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] FATAL : could not read directory base: Invalid argument
FATAL : could not read directory base: Invalid argument 8.2.4 on Windows. Service will not start. Any idea -- 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] sort order
This is exactly what I am trying to do - To find a CREATE DATABASE statement that will produce a database that has a binary sorting. Thanks Danny -Original Message- From: Adrian Klaver [mailto:adrian.kla...@gmail.com] Sent: Monday, December 12, 2011 5:53 PM To: pgsql-general@postgresql.org Cc: Abraham, Danny Subject: Re: [GENERAL] sort order On Sunday, December 11, 2011 8:17:41 am Abraham, Danny wrote: On PG 9.0.4, Windows, Encoding and Collate WIN1252 trying to get a database to sort according to the ascii order. Example: Select t from test order by t Should be exactly like Select t from test order by ascii(t). Can it be done? How? Not after a database has been created. For more info see: http://www.postgresql.org/docs/9.0/interactive/locale.html In particular LC_COLLATE. Thanks Danny -- Adrian Klaver adrian.kla...@gmail.com -- 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] sort order
Thanks a lot. Worked fine with . CREATE DATABASE danny Template template0 LC_CTYPE = 'C' LC_COLLATE = 'C'; -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, December 12, 2011 6:07 PM To: Abraham, Danny Cc: Adrian Klaver; pgsql-general@postgresql.org Subject: Re: [GENERAL] sort order Abraham, Danny danny_abra...@bmc.com writes: This is exactly what I am trying to do - To find a CREATE DATABASE statement that will produce a database that has a binary sorting. Setting its LC_COLLATE to C ought to do that. BTW, as of 9.1 you can control this at finer granularity than database level, see the COLLATE clause. 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
[GENERAL] sort order
On PG 9.0.4, Windows, Encoding and Collate WIN1252 trying to get a database to sort according to the ascii order. Example: Select t from test order by t Should be exactly like Select t from test order by ascii(t). Can it be done? How? Thanks Danny
[GENERAL] initdb locale WIN1252
What's wrong with locale WIN1252 ? Running successfully initdb, but strange warnings show ... The files belonging to this database system will be owned by user EMPG. This user must also own the server process. The database cluster will be initialized with locale English_United States.1252. The default database encoding has accordingly been set to WIN1252. The default text search configuration will be set to english. fixing permissions on existing directory C:/Program Files/BMC Software/Control-M EM 8.0.00/Default/pgsql/Data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 32MB creating configuration files ... ok creating template1 database in C:/Program Files/BMC Software/Control-M EM 8.0.00/Default/pgsql/Data/base/1 ... initdb: invalid locale name WIN1252 initdb: invalid locale name WIN1252 == initdb: invalid locale name WIN1252 == initdb: invalid locale name WIN1252 initdb: invalid locale name WIN1252 initdb: invalid locale name WIN1252 ok
[GENERAL] Is 9.1 considered more stable/robust than 9.0.4 ?
Thanks Danny
[GENERAL] Identify release contents
Hi, We are on 8.3.7 and interested on upgrading to the 9.0.4. How can I verify that the bug below is fixed for 9.0.4? ( Fix Windows shared-memory allocation code (Tsutomu Yamada, Magnus) This bug led to the often-reported could not reattach to shared memory error message.) I can tell for sure, reading the release notes that it is fixed for 8.2.18, 8.3.8 and 8.4.1. What about the 9.X ? Thanks a lot Danny
Re: [GENERAL] Identify release contents
Thanks Craig Yes, I am trying to get to a full confidence level. How can I tell which version was 9.0 branched off? Any git 101 document? From: Abraham, Danny Sent: Tuesday, July 19, 2011 1:49 PM To: pgsql-general@postgresql.org Cc: Abraham, Danny Subject: Identify release contents Hi, We are on 8.3.7 and interested on upgrading to the 9.0.4. How can I verify that the bug below is fixed for 9.0.4? ( Fix Windows shared-memory allocation code (Tsutomu Yamada, Magnus) This bug led to the often-reported could not reattach to shared memory error message.) I can tell for sure, reading the release notes that it is fixed for 8.2.18, 8.3.8 and 8.4.1. What about the 9.X ? Thanks a lot Danny
[GENERAL] How do I elimitae ANY loggin to Windows Eventlog? 8.2.4.
Danny Abraham BMC Software CTMD Business Unit 972-52-4286-513 danny_abra...@bmc.com
[GENERAL] PG 8.3.7. Windows 7. select inet_server_addr() returns ::1 - the loopback adrs.
The machine is on IPV4. How can I retrieve the real IP adrs? Is 8.3.7 supported on Windows 7? Thanks a lot Danny Abraham BMC Software CTMD Business Unit 972-52-4286-513 danny_abra...@bmc.com
[GENERAL] Who is locking me?
Hi, A process hangs forever. When using this query... SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s where pg_stat_get_backend_activity(s.backendid) not like 'insuff%' and pg_stat_get_backend_activity(s.backendid) not like 'IDLE'; I get... procpid |current_query -+-- -+--- 26702 | SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, :pg_stat_get_backend_activity(s.backendid) AS current_query : FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s : where pg_stat_get_backend_activity(s.backendid) not like 'insuff%' : and pg_stat_get_backend_activity(s.backendid) not like 'IDLE'; 21282 | update cmr_lastno set lastisn=lastisn+1 where tablename='DANNY'; 25936 | IDLE in transaction == This is the locker And the question: - What exactly is the locker doing? - Can I retrieve the Client PID somewhere (then by process name I will dive into the code). Thanks a lot Danny Abraham BMC Software CTMD Business Unit 972-52-4286-513 danny_abra...@bmc.commailto:danny_abra...@bmc.com
[GENERAL] Log full of: statement_timeout out of the valid range.
Our setup is a real valid one. Looks like it has to do more with remote connections. Any idea? Thanks Danny
Re: [GENERAL] Log full of: statement_timeout out of the valid range.
ctrlm700= show statement_timeout; statement_timeout --- 1h (1 row)
[GENERAL] FW: postgres 8.2.4 cores on SUN
Hi, postgres cores on SUN 2.9. Disk is in 100% IO. (dbx) where [1] 0x65eacdd4(0x7fffa36c, 0x400, 0x10032d4e8, 0x1, 0x48, 0xd6), at 0x65eacdd4 [2] XLogFileInit(0x48, 0xd6, 0x7fffa85d, 0x1, 0x488000, 0x0), at 0x10007955c [3] XLogWrite(0x48, 0x6, 0x1, 0x1004883f8, 0x100, 0x1192ca8), at 0x100078990 [4] XLogFlush(0x7, 0x1192cf0, 0x10048844c, 0x7, 0x1004a3524, 0x0), at 0x100079428 [5] RecordTransactionCommit(0x0, 0x296f6120, 0x1004883d0, 0x0, 0x1004883d5, 0x0), at 0x1000713c0 [6] CommitTransaction(0x1, 0x49fc00, 0x100488320, 0x488000, 0x1, 0x1), at 0x1000720a8 [7] CommitTransactionCommand(0x1, 0x108, 0x4, 0x100488320, 0x0, 0x100072af8), at 0x100072c00 [8] finish_xact_command(0x0, 0x28, 0x1, 0x4a, 0x1, 0x1004a00cc), at 0x100202adc [9] exec_simple_query(0x1005f7050, 0x1005f8b68, 0x2, 0x1006c42e8, 0x0, 0x1005f8b88), at 0x1002000fc [10] PostgresMain(0x0, 0x0, 0x1, 0x1, 0x1004a3400, 0x51), at 0x10020581c [11] BackendRun(0x4, 0x0, 0x35b800, 0x35b800, 0x10035b800, 0x0), at 0x1001cd0d8 [12] BackendStartup(0x100540400, 0x100543150, 0x314c00, 0x1004a3400, 0x0, 0x100314e72), at 0x1001cc300 [13] ServerLoop(0x100540400, 0x4, 0x6, 0x4a54c86f, 0x, 0x1004bd370), at 0x1001c943c [14] PostmasterMain(0x10049f400, 0x1cac00, 0x100507c00, 0x1, 0x1, 0x1), at 0x1001c8bc0 [15] main(0x1, 0x1, 0x352c00, 0x0, 0x10051d4a0, 0x100507f50), at 0x10016faa4 Any idea? Thanks Danny
[GENERAL] postgres 8.2.4 cores on SUN
Hi, postgres cores on SUN 2.9. Disk is in 100% IO. (dbx) where [1] 0x65eacdd4(0x7fffa36c, 0x400, 0x10032d4e8, 0x1, 0x48, 0xd6), at 0x65eacdd4 [2] XLogFileInit(0x48, 0xd6, 0x7fffa85d, 0x1, 0x488000, 0x0), at 0x10007955c [3] XLogWrite(0x48, 0x6, 0x1, 0x1004883f8, 0x100, 0x1192ca8), at 0x100078990 [4] XLogFlush(0x7, 0x1192cf0, 0x10048844c, 0x7, 0x1004a3524, 0x0), at 0x100079428 [5] RecordTransactionCommit(0x0, 0x296f6120, 0x1004883d0, 0x0, 0x1004883d5, 0x0), at 0x1000713c0 [6] CommitTransaction(0x1, 0x49fc00, 0x100488320, 0x488000, 0x1, 0x1), at 0x1000720a8 [7] CommitTransactionCommand(0x1, 0x108, 0x4, 0x100488320, 0x0, 0x100072af8), at 0x100072c00 [8] finish_xact_command(0x0, 0x28, 0x1, 0x4a, 0x1, 0x1004a00cc), at 0x100202adc [9] exec_simple_query(0x1005f7050, 0x1005f8b68, 0x2, 0x1006c42e8, 0x0, 0x1005f8b88), at 0x1002000fc [10] PostgresMain(0x0, 0x0, 0x1, 0x1, 0x1004a3400, 0x51), at 0x10020581c [11] BackendRun(0x4, 0x0, 0x35b800, 0x35b800, 0x10035b800, 0x0), at 0x1001cd0d8 [12] BackendStartup(0x100540400, 0x100543150, 0x314c00, 0x1004a3400, 0x0, 0x100314e72), at 0x1001cc300 [13] ServerLoop(0x100540400, 0x4, 0x6, 0x4a54c86f, 0x, 0x1004bd370), at 0x1001c943c [14] PostmasterMain(0x10049f400, 0x1cac00, 0x100507c00, 0x1, 0x1, 0x1), at 0x1001c8bc0 [15] main(0x1, 0x1, 0x352c00, 0x0, 0x10051d4a0, 0x100507f50), at 0x10016faa4 Any idea? Thanks Danny
[GENERAL] PL/PG SQL: select count(*) into var from tab where cond - does not compute the where cond
Instead it returns the full count of the table. Any idea? Thanks Danny Abraham BMC Software CTMD Business Unit 972-52-4286-513 danny_abra...@bmc.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] Re: PL/PG SQL: select count(*) into var from tab where cond - does not compute the where cond
... forgot the most import issue The problem shows when the selection is from a view. The where condition does not have names contention. It is: select count(*) into var from view_name where cond -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: PL/PG SQL: select count(*) into var from ANY VIEW where cond - does not compute the where cond - returns always total count
Any idea? Thanks Danny -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: PG fails on Windows Server 2008: could not reattach to shared memory ... : 487
Does anybody have a proven way to regenerate this problem? SO that I can tell that the patch really does fix it? Thanks Danny -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: PG fails on Windows Server 2008: could not reattach to shared memory ... : 487
Does anybody have a proven way to regenerate this problem? This way I can decide that the patch does fix it? Thanks Danny -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG fails on Windows Server 2008: could not reattach to shared memory ... : 487
The server intermittently fails on simple queries. Log full of the above error log. Does anyone know on which version this problem is fixed? Or a specific patch? Thanks Danny Abraham BMC Software CTMD Business Unit 972-52-4286-513 danny_abra...@bmc.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] Re: InitDB 8.3.7 fails on Windows - Failed system call was MapViewOfFileEx
Adding memory and releasing several memory consumers did the trick. Thanks a lot. Danny Abraham BMC Software CTMD Business Unit 972-52-4286-513 danny_abra...@bmc.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] InitDB 8.3.7 fails on Windows - Failed system call was MapViewOfFileEx
Hi, InitDB of 8.3.7 fails. Windows XP. Compiled with VC2008, SP1 creating template1 database in ... 2009-08-04 11:46:53.954 GMTFATAL: could not create shared memory segment: 8 2009-08-04 11:46:53.970 GMTDETAIL: Failed system call was MapViewOfFileEx. child process exited with exit code 1 Any idea? Thanks Danny -- 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] initdb fails on Windows with encoding=LATIN1
We do need LATIN1. The error message is: initdb: encoding mismatch The encoding you selected (LATIN1) and the encoding that the selected locale uses (WIN1252) do not match. This would lead to misbehavior in various character string processing functions. Rerun initdb and either do not specify an encoding explicitly, or choose a matching combination. The version is 8.3.7. It worked fine with 8.2.4 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] initdb fails on Windows with encoding=LATIN1
It runs fine with WIN1252. The error message says something about lc_type. Tried with various code pages: 850,1252. Nothing goes through. Any thoughts? -- 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] initdb --encoding=LATIN1 fails on Windows
Thanks. I guess my question is then, how should I configure Windows to have the right locale so that I can create a LATIN1 encoding cluster. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] initdb --locale=LATIN1 fails on Windows
The same command worked fine on PG 8.2.4. Now, using 8.3.7, the command succeeds only with locale 1252. It comments something on wrong lc_ctype. Any idea? Thanks Danny -- 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] initdb --locale=LATIN1 fails on Windows
Do you by chance know what is the ISO88591 locale to be used on Windows? It should be something like Americam_United States.ISO88591. Thanks Danny -- 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] initdb --locale=LATIN1 fails on Windows
Actually the command is:initdb --encoding=LATIN1. It fails on Windos. fails on Windows The same command worked fine on PG 8.2.4. Now, using 8.3.7, the command succeeds only with encoding 1252. It comments something on wrong lc_ctype. Thanks Danny -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] initdb --encoding=LATIN1 fails on Windows
The same command worked fine on PG 8.2.4. Now, using 8.3.7, we are forced into WIN1252. Thanks Danny -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG 8.3.7 initdb -E LATIN1 fails on Windows
Hi, Runnning: initdb -E LATIN1 -D . Error: encoding mismatch Used to run fine on 8.2.4 on the same Windows platform. OUTPUT === The database cluster will be initialized with locale English_United States.1252. initdb: encoding mismatch The encoding you selected (LATIN1) and the encoding that the selected locale uses (WIN1252) do not match. This would lead to misbehavior in various character string processing functions. Rerun initdb and either do not specify an encoding explicitly, or choose a matching combination. Thanks Danny Abraham -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How do I add text to a thread?
I am not .2 instance that won't come up after shutdown But cannot add more posts. Thanks a lot Danny -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.2 instance that won't come up after shutdown
Hi We have a PG version 8.2 instance that won't come up after shutdown. The log shows the following: 2009-06-12 21:39:02.669 MDTLOG: database system was shut down at 2009-06-12 20:56:51 MDT 2009-06-12 21:39:02.748 MDTLOG: checkpoint record is at 9/D712F330 2009-06-12 21:39:02.748 MDTLOG: redo record is at 9/D712F330; undo record is at 0/0; shutdown TRUE 2009-06-12 21:39:02.748 MDTLOG: next transaction ID: 0/8239765; next OID: 25433 2009-06-12 21:39:02.748 MDTLOG: next MultiXactId: 1; next MultiXactOffset: 0 2009-06-12 21:39:02.838 MDTLOG: database system is ready 2009-06-12 21:39:02.846 MDTFATAL: invalid memory alloc request size 8455717278 2009-06-12 21:39:02.862 MDTLOG: startup process (PID 6198) exited with exit code 1 2009-06-12 21:39:02.864 MDTLOG: aborting startup due to startup process failure 2009-06-12 21:39:02.928 MDTLOG: logger shutting down We tried to set the autovacuum parameter value to off but unfortunately it did not help 1. Is this bug defined for the 8.2.4 code line also (We know that it is defined for 8.1.X) 2. How can we recreate the problem? 3. Is there a fix for that bug? Any help will be appreciated. Best regards, Danny Abraham -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] invalid value for parameter lc_messages: en_US.ISO8859-1
Hi, Details below: Initdb fails on: creating template1 database in /ema640/pgsql/data/base/1 ... FATAL: invalid value for parameter lc_messages: en_US.ISO8859-1 PG 8.2.4, AIX 5.3 German customer (I assume that it has some implications of locale). Can anyone help me in recreating the problem? Everything runs Fine for me ona US machine, for both LC_MESSAGES=C as Well as LC_MESSAGES=en_US.ISO8859-1 Thanks a lot Danny Abraham -- 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 TO duplicates \ signs
Hi, String in DB: D:\Program Files\BMC Software\CONTROL-D\wa/reports In the output files \| are duplicated: The string in the output text fileis D:\\Program Files\\BMC Software\\CONTROL-D\\wa/reports == ==== == Standard_conforming_strings will not help here. Any ideas? Help will be appreciated. Thanks Danny Abraham BMC Software CTMD Business Unit 972-52-4286-513 [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can anyone explain?
set standard_conforming_strings=on; select 'abcd\efg' like 'abcd\efg' == F (I expected it to be T) select 'abcd\efg' like 'abcd\\efg' == T (I expected it to be F) Thanks Danny Abraham BMC Software CTMD Business Unit 972-52-4286-513 [EMAIL PROTECTED] -- 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 fails
I am loading a huge file using C, STDIN The program fails immediately on canceling statement due to statement timeout Any idea? Thanks Danny -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can postgres handle group by queries?
Hi, The following query SELECT START_DATE_IDX, COUNT(*) FROM RUNINFO_HISTORY WHERE START_DATE_IDX=1005 group by START_DATE_IDX runs for about 200s on Postgres, but 13s on MS SQL Server, on the same machine. The searched column is indexed. By reading the explain you can tell that the index is being used. Any idea? Danny Abraham BMC Software CTMD Business Unit 972-52-4286-513 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] pg_ctl start fails on Windows
The error message is could not open PID file. I can manually create the file postmaster.pid, so the problem Does not look like a permission problem. Thanks Danny ---(end of broadcast)--- TIP 1: 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] dblink does not connect when activated in a remote server
dblink problem:
I am connecting to a remote server and run a functions that calls
another one using db_link.
It fails with the error message is 08001 - could not establish
connection.
When the function is located in a local server - it runs fine.
The connection string is:
'hostaddr=127.0.0.1 .'
The following were tried but did not help:
'host=localhost' and even providing the real target server
('host=tlvl0390') did not help.
Happens on WIndows as well as Unix
Can you help?
Thanks
Danny
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] LIBPQ Exception
How do I program a DML statement that does not roll back using C. res=PQexec(conn,BEGIN; insert into x values(100); exception when others then null; end;) fails on syntax. Any other alternative? Thanks Danny ---(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
[GENERAL] Instaltiating an ARRAY within a function
=== CREATE OR REPLACE FUNCTION arr( inout x varchar[] ) AS $Z$ DECLARE i integer; BEGIN select ARRAY['Danny','Eissam','Moshe'] into x; end; $Z$ LANGUAGE 'plpgsql' VOLATILE; === CREATE OR REPLACE FUNCTION callarr() returns integer AS $Z$ DECLARE x varchar[6]; BEGIN perform arr(x); RAISE NOTICE 'x[1]=%',x[1]; return 0; end; $Z$ LANGUAGE 'plpgsql' VOLATILE; === select callarr(); NOTICE: x[1]=NULL ??? Should have been DANNY Should it work? Thanks Danny ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Question in dblink
I would like the code below to accept the returned value from t2.
How do I do it?
Thanks
Danny
===
err := dblink_connect('C',cname);
begin
execute dblink('C','SELECT t2()');
exception
when others then null;
end;
err := dblink_disconnect('C');
==
CREATE OR REPLACE FUNCTION t2() RETURNS integer
AS
$$
DECLARE
i integer;
BEGIN
execute 'insert into x values(12)';
return 67;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;
==
Danny Abraham
BMC Software
CTMD Business Unit
972-52-4286-513
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] HD is flooded by Error Log info
Hello Recently an application endless loop crashed the PG server. I am looking for the safest way to limit the growth of the all the files in the pg_log directory, While still maintain reasonable period of log. Any suggestions? Our current setup is: #--- # ERROR REPORTING AND LOGGING #--- redirect_stderr = on log_line_prefix = '%m' client_min_messages = error log_min_messages = error log_filename = 'CTMD-%d.log log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 10MB Thanks Danny Abraham BMC Software CTMD Business Unit 972-52-4286-513 [EMAIL PROTECTED]
Re: [GENERAL] HD is flooded by Error Log info
Hello Recently an application endless loop crashed the PG server. I am looking for the safest way to limit the growth of the all the files in the “pg_log” directory, While still maintain reasonable period of log. Any suggestions? Our current setup is: #--- # ERROR REPORTING AND LOGGING #--- redirect_stderr = on log_line_prefix = '%m' client_min_messages = error log_min_messages = error log_filename = 'CTMD-%d.log log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 10MB Thanks Danny Abraham BMC Software CTMD Business Unit 972-52-4286-513 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: 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] Streaming LIBPQ? Sliding Window in LIBPQ?
Is there a way to break the PGresult array to chuncks Like Oracle? I mean, without changing the text of given queries, can we somehow limit the memory consumption of a client that is using LIBPQ? The API is ... extern PGresult *PQexec(PGconn *conn, const char *query); Is there any SLiding Window layer that breaks the PGresult into chuncks? Big queries take a lot more memory on the client side compared to other DB clients. Thanks Danny Danny Abraham BMC Software CTMD Business Unit 972-52-4286-513 [EMAIL PROTECTED]
[GENERAL] LIBPQ: Can we have buffered PGresult (i.e. a retreival by chuncks?)
I am referring to client side computing - no SQL changes can be applied. Is there a LIBPQ dll extension that supports sliding windows like management Of the *PGresult array? Thanks Danny Abraham BMC Software CTMD Business Unit 972-52-4286-513 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] LIBPQ: Can we have buffered PGresult (i.e. a retreival bu chuncks?)
Is there a way to break the PGresult array to chunks Like Oracle? Thanks Danny Abraham BMC Software CTMD Business Unit 972-52-4286-513 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Chunk Delete
Say we have a FIFO of 800,000,000 records. No primary key is required - this is only audit information. We do not use it on Oracle too. Based on a condition, 2,000,000 records should be deleted daily. We have a background process that wakes up every X minutes and deletes Y records. With Oracle we do it with: delete ,tname where cond and rownum Y; Can we have the same goody on Postgres?
[GENERAL] Chunk Delete
Hi, I am wondering if I can do in PG Chunck Delete, like the Oracle example below. In Oracle we erase 50,000 records using the following: Delete table name where condition and ROWNUM 5; Do we have such a feature in PG? Thanks Danny ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Chunk Delete
Say we have a FIFO of 800,000,000 records. No primary key is required - this is only audit information. We do not use it on Oracle too. Based on a condition, 2,000,000 records should be deleted daily. We have a background process that wakes up every X minutes and deletes Y records. With Oracle we do it with: delete ,tname where cond and rownum Y; Can we have the same goody on Postgres?
[GENERAL] Chunk Delete
THE problem is that the table does not have a primary key; Too expensive.
Re: [GENERAL] Chunk Delete
The temporary sequence works perfectly for me. Thanks ---(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
