Re: [GENERAL] Cannot login for short period of time
Quoting "Thomas Markus" : check your hardware (especially harddrive) for errors. Ja, that was my first suspicion as well, but no. Using a RAID5 setup, with smart monitoring, etc ... no errors. I've also just bumped up max_fsm_relations to 1 - it was using the default of 1000 or something. My database reindex in single-user mode kindly made the suggestion (we have many, many table partitions with hordes of indexes - relations approaching 9000+). reindexing due to "Cannot find namespace X" error on insert. Henry pgpPJ4fIBJg92.pgp Description: PGP Digital Signature
Re: [GENERAL] Cannot login for short period of time
On Wed, May 13, 2009 at 1:42 AM, Henry wrote: > Quoting "Thomas Markus" : >> >> check your hardware (especially harddrive) for errors. > > Ja, that was my first suspicion as well, but no. Using a RAID5 setup, with > smart monitoring, etc ... no errors. > > I've also just bumped up max_fsm_relations to 1 - it was using the > default of 1000 or something. My database reindex in single-user mode > kindly made the suggestion (we have many, many table partitions with hordes > of indexes - relations approaching 9000+). reindexing due to "Cannot find > namespace X" error on insert. Whether or not max relations is the root of the login hang problem, you likely have gotten a fair bit of bloat in your database if your setting was too low by a factor of 10 for so long.You may need to look at recovering lost space in bloated tables and / or indexes. If the bloat is real bad, look at dumping / restoring the database for a fresh start. It's a pain because it requires downtime, but it's often faster than anything else for a badly bloated database. -- 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] regexp_matches problem
WP Perquin wrote:
> When I make the following simplified example:
>
> SELECT regexp_matches(''
>
> ,'((title\s*=\s*\"([^"]*)")+)|((src\s*=\s*\"([^"]*)")+)','ig')
>
> My result are 2 rows:
>
> "{NULL,NULL,NULL,"src=\"\"","src=\"\"",}"
>
> "{"title=\"dit is een title tekst\"","title=\"dit is een
> title tekst\"","dit is een title tekst",NULL,NULL,NULL}"
>
> I would like to have 1 row which contains both the records.
> Does anyone know how I can solve this?
Do you really want all those NULLs?
Is that what you want:
SELECT match[1]
FROM regexp_matches('',
'(title\s*=\s*\"[^"]*"|src\s*=\s*\"[^"]*")',
'ig') AS match;
This query returns two rows.
When you write that you want to have one row that contains both
records, do you mean:
a) one string that is the concatenation of both strings
or
b) one row that is a single array with two string elements
Whatever it is you want, you will probably need to write a
little aggregate function that does that for you, something like
CREATE FUNCTION text_cats(state text[], nextv text) RETURNS text[]
IMMUTABLE STRICT LANGUAGE sql
AS 'SELECT $1 || $2';
CREATE AGGREGATE text_cat(text) (
SFUNC = text_cats,
STYPE = text[],
INITCOND = '{}' );
for variant b).
Then you can
SELECT text_cat(match[1])
FROM regexp_matches('',
'(title\s*=\s*\"[^"]*"|src\s*=\s*\"[^"]*")',
'ig') AS match;
Yours,
Laurenz Albe
--
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] Cannot login for short period of time
Quoting "Scott Marlowe" : Whether or not max relations is the root of the login hang problem, you likely have gotten a fair bit of bloat in your database if your setting was too low by a factor of 10 for so long.You may need to look at recovering lost space in bloated tables and / or indexes. If the bloat is real bad, look at dumping / restoring the database for a fresh start. It's a pain because it requires downtime, but it's often faster than anything else for a badly bloated database. This occurred just after a dump/restore, so things were nice and lean. I have a suspicion this is related to bgwriter_* and checkpoint I/O. I've tweaked bgwriter_* and am awaiting an opportunity to test this again (busy reindexing all) and will report back. Interestingly, this behaviour didn't occur pre-partitioning. It seems the sheer number of additional relations tipped things over -- suddenly many defaults are just too low and I'm having to dig into arcane settings. This thread seems to be related: http://archives.postgresql.org//pgsql-admin/2008-10/msg00041.php Cheers Henry pgpQgMvsm37S7.pgp Description: PGP Digital Signature
[GENERAL] Failure during initdb - creating dictionaries ... FATAL: could not access file "$libdir/libdict_snowball": No such file or directory
Hopefully this is a quick one to answer, but it's got me tearing my hair out. Custom built postgresql 8.3.5 using the pkgsrc build system on OS X Leopard; when running: /usr/pkg/bin/initdb -D /usr/pkg/pgsql/data (I also tried adding the -d flag but no other useful information was shown) as the pgsql user, it seems to be dying with a dictionary problem as follows: sh-3.2$ /usr/pkg/bin/initdb -D /usr/pkg/pgsql/data The files belonging to this database system will be owned by user "pgsql". This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. The default database encoding has accordingly been set to UTF8. The default text search configuration will be set to "english". fixing permissions on existing directory /usr/pkg/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 12MB/76800 creating configuration files ... ok creating template1 database in /usr/pkg/pgsql/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... FATAL: could not access file "$libdir/libdict_snowball": No such file or directory STATEMENT: -- Language-specific snowball dictionaries -- $PostgreSQL: pgsql/src/backend/snowball/snowball_func.sql.in,v 1.2 2007/09/03 02:30:43 tgl Exp $$ SET search_path = pg_catalog; CREATE FUNCTION dsnowball_init(INTERNAL) RETURNS INTERNAL AS '$libdir/libdict_snowball', 'dsnowball_init' LANGUAGE C STRICT; (snip) ..many many lines of snowball specific SQL and then finally ALTER TEXT SEARCH CONFIGURATION turkish ADD MAPPING FOR asciiword, hword_asciipart, asciihword WITH turkish_stem; ALTER TEXT SEARCH CONFIGURATION turkish ADD MAPPING FOR word, hword_part, hword WITH turkish_stem; child process exited with exit code 1 initdb: removing contents of data directory "/usr/pkg/pgsql/data" === So, presumably the fatal error which resulted in exit code 1 was: FATAL: could not access file "$libdir/libdict_snowball": No such file If so, how to fix so that initdb can complete? Further details: pg_config --libdir /Volumes/pkgsrc/pkg/lib pg_config --pkglibdir /Volumes/pkgsrc/pkg/lib/postgresql sh-3.2$ ls -l /Volumes/pkgsrc/pkg/lib/postgresql | grep snowball -rwxr-xr-x 1 root wheel 341480 Feb 27 17:06 libdict_snowball.0.0.0.dylib lrwxr-xr-x 1 root wheel 28 Feb 27 17:06 libdict_snowball.0.dylib -> libdict_snowball.0.0.0.dylib lrwxr-xr-x 1 root wheel 28 Feb 27 17:06 libdict_snowball.dylib -> libdict_snowball.0.0.0.dylib -rwxr-xr-x 1 root wheel 893 Feb 27 17:06 libdict_snowball.la And I've symlinked these all from /Volumes/pkgsrc/pkg/lib as well, but initdb still fails to complete. Any help will be gratefully apreciated. Regards, Jonathan. -- 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] Failure during initdb - creating dictionaries ... FATAL: could not access file "$libdir/libdict_snowball": No such file or directory
Jonathan Groll writes: > Custom built postgresql 8.3.5 using the pkgsrc build system on OS X > Leopard; Uh ... what is the "pkgsrc build system", and what changes does it make to a straight-from-source PG build? > creating conversions ... ok > creating dictionaries ... FATAL: could not access file > "$libdir/libdict_snowball": No such file or directory What you've apparently got here is a problem with shared-library building, but it's odd that it would've got through the "creating conversions" step, which also relies heavily on shared libraries. > sh-3.2$ ls -l /Volumes/pkgsrc/pkg/lib/postgresql | grep snowball > -rwxr-xr-x 1 root wheel 341480 Feb 27 17:06 > libdict_snowball.0.0.0.dylib > lrwxr-xr-x 1 root wheel 28 Feb 27 17:06 > libdict_snowball.0.dylib -> libdict_snowball.0.0.0.dylib > lrwxr-xr-x 1 root wheel 28 Feb 27 17:06 libdict_snowball.dylib > -> libdict_snowball.0.0.0.dylib > -rwxr-xr-x 1 root wheel 893 Feb 27 17:06 libdict_snowball.la I find this pretty suspicious. On my OSX machine, the contents of the pkglibdir directory are all named something.so: $ ls version83/lib/postgresql ascii_and_mic.so* utf8_and_euc_cn.so* cyrillic_and_mic.so*utf8_and_euc_jis_2004.so* dict_snowball.so* utf8_and_euc_jp.so* euc_cn_and_mic.so* utf8_and_euc_kr.so* euc_jis_2004_and_shift_jis_2004.so* utf8_and_euc_tw.so* euc_jp_and_sjis.so* utf8_and_gb18030.so* euc_kr_and_mic.so* utf8_and_gbk.so* euc_tw_and_big5.so* utf8_and_iso8859.so* latin2_and_win1250.so* utf8_and_iso8859_1.so* latin_and_mic.so* utf8_and_johab.so* pgxs/ utf8_and_shift_jis_2004.so* plpgsql.so* utf8_and_sjis.so* utf8_and_ascii.so* utf8_and_uhc.so* utf8_and_big5.so* utf8_and_win.so* utf8_and_cyrillic.so* $ Notice also the lack of the "lib" prefix. The source code that you are quoting should look like this: CREATE FUNCTION dsnowball_init(INTERNAL) RETURNS INTERNAL AS '$libdir/dict_snowball', 'dsnowball_init' LANGUAGE C STRICT; so how did it get to be 'libdir/libdict_snowball'? It looks to me like you are using code that someone has hacked up to try to impose their own ideas of how to build/name shared libraries for OS X, and done a pretty darn poor job of it. Recommendation: get an unmodified copy of Postgres and build it yourself. It works fine out-of-the-box on OS X. 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] Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER
Philipp Marek wrote: > Hello everybody, > > we're using postgresql 8.3 for some logging framework. > > There are several tables for each day (which are inherited from a common > base), which > - are filled during the day, > - after midnight the indizes are changed to FILLFACTOR=100, and > - the tables get CLUSTERed by the most important index. > - Some time much later the tables that aren't needed anymore are DROPped. > > So far, so fine. Do say, do you have any long-running transactions, or "idle" transactions? Maybe someone opened a terminal somewhere and left it open for days? Have a look at pg_stat_activity. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] lack in psql console
Hello Craig. Thanks for your answer. I did what you say (use the default size of the terminal) but the problem persist, the psql still start to write the new senteces above the old one, it is to weird. Best regards, 2009/5/10 Craig Ringer > Juan Camilo Marín wrote: > > > => select * from \d summary_table; > > > > The sentense start after "=> select * from" I don't know why this is > > happen. > > Probably a terminal emulation issue. Readline will be sending the wrong > escape sequences to control your terminal or your terminal will be > misbehaving somehow. > > A common cause of readline issues is resizing your terminal window. Try > testing in a terminal window that's the default size. If that works OK, > try resizing it while it's still running the shell, THEN start psql. > > -- > Craig Ringer > -- Life may only be understood backwards but it must be lived forwards
Re: [GENERAL] how do you get there from here?
Michael P. Soulier wrote: > So, I'm working around it by creating the new columns with the default > value that I want populated, and then resetting the default. This works, > but it doesn't feel like a best practice. > > Can someone recommend a better way to say, consolidate 3 columns down to > 2 and conditionally move contents around to the new columns, inside of a > transaction involving ALTER TABLE calls? > > I'm just wondering if there is a better solution than what I've done. In fact, I just ran into this again and I don't know, off the top of my head, how to solve this one completely. Postgres7 code: BEGIN; ALTER TABLE clients ADD COLUMN typelabel varchar(128); UPDATE clients SET typelabel = settype; UPDATE clients SET settype = 'Unknown'; ALTER TABLE clients ALTER COLUMN typelabel SET DEFAULT 'Unknown'; COMMIT; Working Postgres8 code: BEGIN; ALTER TABLE clients ADD COLUMN typelabel varchar(128) DEFAULT 'Unknown'; COMMIT; But this way I lose the copy of the settype column data over to the typelabel column. Can someone suggest a better way that doesn't hit this "pending trigger" issue while in a transaction? Thanks, Mike -- Michael P. Soulier , 613-592-2122 x2522 "Any intelligent fool can make things bigger and more complex... It takes a touch of genius - and a lot of courage to move in the opposite direction." --Albert Einstein signature.asc Description: OpenPGP digital signature
Re: [GENERAL] regexp_matches problem
Who many thanks Laurenz.
You gave me the right direction.
The following is what I made up and it does exactly what I want it to do:
--collect the specific string data for each result row
SELECT (
SELECT match[1] FROM
regexp_matches(secondresult.imgstring,'title="([^"]+)"','ig') AS match
) AS titletekst
,(
SELECT match[1] FROM
regexp_matches(secondresult.imgstring,'alt="([^"]+)"','ig') AS match
) AS alttekst
,(
SELECT match[1] FROM
regexp_matches(secondresult.imgstring,'src="([^"]+)"','ig') AS match
) AS imgsrc
FROM (
--collect the image string of every found image
SELECT firstresult[1] AS imgstring
FROM regexp_matches(''
,']*)>'
,'ig'
) AS firstresult
) as secondresult
This is it. Very simple and very efficient.
It took me ages to find out. Hopefully it helps other people.
Willem
-Oorspronkelijk bericht-
Van: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] Namens Albe Laurenz
Verzonden: woensdag 13 mei 2009 10:50
Aan: WP Perquin *EXTERN*; pgsql-general@postgresql.org
Onderwerp: Re: [GENERAL] regexp_matches problem
WP Perquin wrote:
> When I make the following simplified example:
>
> SELECT regexp_matches(''
>
> ,'((title\s*=\s*\"([^"]*)")+)|((src\s*=\s*\"([^"]*)")+)','ig')
>
> My result are 2 rows:
>
> "{NULL,NULL,NULL,"src=\"\"","src=\"\"",}"
>
> "{"title=\"dit is een title tekst\"","title=\"dit is een
> title tekst\"","dit is een title tekst",NULL,NULL,NULL}"
>
> I would like to have 1 row which contains both the records.
> Does anyone know how I can solve this?
Do you really want all those NULLs?
Is that what you want:
SELECT match[1]
FROM regexp_matches('',
'(title\s*=\s*\"[^"]*"|src\s*=\s*\"[^"]*")',
'ig') AS match;
This query returns two rows.
When you write that you want to have one row that contains both
records, do you mean:
a) one string that is the concatenation of both strings
or
b) one row that is a single array with two string elements
Whatever it is you want, you will probably need to write a
little aggregate function that does that for you, something like
CREATE FUNCTION text_cats(state text[], nextv text) RETURNS text[]
IMMUTABLE STRICT LANGUAGE sql
AS 'SELECT $1 || $2';
CREATE AGGREGATE text_cat(text) (
SFUNC = text_cats,
STYPE = text[],
INITCOND = '{}' );
for variant b).
Then you can
SELECT text_cat(match[1])
FROM regexp_matches('',
'(title\s*=\s*\"[^"]*"|src\s*=\s*\"[^"]*")',
'ig') AS match;
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Connections Crashing
Hi, We are using Postgres 8.3.1 binary distribution, and copying the same along with software installation. >From last some time we have started getting error as message dialog showing error 0xC142 and after that, connections are crashing. Windows login is administrator user and Postgres user, we are creating before hand. Postgres logs shows following LOG: execute : select stpsetting0_.NF_DEVICE_ID as NF14_1_, stpsetting0_.ID as ID1_, stpsetting0_.ID as ID74_0_, stpsetting0_.NF_ENABLED as NF2_74_0_, stpsetting0_.NF_BASE_TYPE as NF3_74_0_, stpsetting0_.NF_PROTOCOL_SPECS as NF4_74_0_, stpsetting0_.NF_PRIORITY as NF5_74_0_, stpsetting0_.NF_VLAN_ID as NF6_74_0_, stpsetting0_.NF_OPTION1 as NF7_74_0_, stpsetting0_.NF_OPTION2 as NF8_74_0_, stpsetting0_.NF_HOLD_TIME as NF9_74_0_, stpsetting0_.NF_FORWARD_DELAY as NF10_74_0_, stpsetting0_.NF_BRIDGE_MAX_AGE as NF11_74_0_, stpsetting0_.NF_BRIDGE_HELLO_TIME as NF12_74_0_, stpsetting0_.NF_BRIDGE_FORWARD_DELAY as NF13_74_0_, stpsetting0_.NF_DEVICE_ID as NF14_74_0_ from networkManager.NT_STP_SETTINGS stpsetting0_ where stpsetting0_.NF_DEVICE_ID=$1 DETAIL: parameters: $1 = '6698' LOG: execute S_2: COMMIT LOG: server process (PID 8156) was terminated by exception 0xC142 HINT: See C include file "ntstatus.h" for a description of the hexadecimal value. LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process What can be possible reasons for that and whats the solution. Thanks,
Re: [GENERAL] how to extract data from bytea so it is be used in blob for mysql database
On 13/05/2009 21:56, George Kao wrote: > I used pg_dump to extract the data from my database with bytea fields > (having pictures) in plain text format. I encoutered some problems to > restore those bytea data to the blobs field in a Mysql database. Any advice? A bit more detail would be nice we're only guessing otherwise. - Platform(s)? - Version? - Commands executed? - Error messages? - Log entries? - 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] Connections Crashing
Shahbaz A. Tyagi wrote: We are using Postgres 8.3.1 binary distribution, and copying the same along with software installation. Can you reproduce this with a more recent version of 8.3 (8.3.7 is current). From last some time we have started getting error as message dialog showing error 0xC142 and after that, connections are crashing. LOG: server process (PID 8156) was terminated by exception 0xC142 HINT: See C include file "ntstatus.h" for a description of the hexadecimal value. A bit of googling suggests this is STATUS_DLL_INIT_FAILED, and although I'm not a Windows expert I'd expect that to be an error you only see on startup, rather than after PostgreSQL has been running for a while. Could it be an anti-virus package interfering? Do you have any more information on the situation? Are the machines running XP/Vista/Server 2003? Do you only see it on 32/64-bit? Do some machines show this problem while others are OK? -- Richard Huxton Archonet Ltd -- 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] Connections Crashing
Hi, Its not happening in all the machines, its only some machines. I am testing on XP 32 bit. Its coming only while running Postgres and accessing the DB. Strange part is that some machines are getting restarted as soon as it gets the error. Thanks, -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Richard Huxton Sent: Thursday, May 14, 2009 1:59 AM To: Shahbaz A. Tyagi Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Connections Crashing Shahbaz A. Tyagi wrote: > We are using Postgres 8.3.1 binary distribution, and copying the same along > with software installation. Can you reproduce this with a more recent version of 8.3 (8.3.7 is current). > From last some time we have started getting error as message dialog showing > error 0xC142 and after that, connections are crashing. > LOG: server process (PID 8156) was terminated by exception 0xC142 > > HINT: See C include file "ntstatus.h" for a description of the hexadecimal > value. A bit of googling suggests this is STATUS_DLL_INIT_FAILED, and although I'm not a Windows expert I'd expect that to be an error you only see on startup, rather than after PostgreSQL has been running for a while. Could it be an anti-virus package interfering? Do you have any more information on the situation? Are the machines running XP/Vista/Server 2003? Do you only see it on 32/64-bit? Do some machines show this problem while others are OK? -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Connections Crashing
On Wed, May 13, 2009 at 4:22 PM, Shahbaz A. Tyagi wrote: > Hi, > > Its not happening in all the machines, its only some machines. I am testing > on XP 32 bit. Its coming only while running Postgres and accessing the DB. > > Strange part is that some machines are getting restarted as soon as it gets > the error. So, do you have antivirus on these machines or not? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres 3.7 unexpected closing of connections
I have the following error message: "Error connecting to the server: server closed the connection unexpectedly. This probably means that the server terminated abnormally before or while processing the request. " It happens intermittently both when using pgAdminIII and when using QT so it is not a pgAdminIII issue. Often the connection seems to succeed but you get a series of error messages like the one above when attempting to open a database. It seems to be a timing issue. This does not happen with postgresql-8.3.5 but does happen with postgresql-8.3.7. I used the One-click installer from EnterpriseDB and am running Vista SP1. Has anyone any ideas on how to fix this? it is really annoying because it is intermittent. I have done a complete reinstallation and deleted everything before reinstalling it. Lucky it is only a test installation! Andrew -- ___ Andrew J. P. Maclean Centre for Autonomous Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___ -- 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] postgres 3.7 unexpected closing of connections
On Wed, May 13, 2009 at 5:36 PM, Andrew Maclean wrote: > I have the following error message: > > "Error connecting to the server: server closed the connection unexpectedly. > This probably means that the server terminated abnormally before or > while processing the request. > " > It happens intermittently both when using pgAdminIII and when using QT > so it is not a pgAdminIII issue. > > Often the connection seems to succeed but you get a series of error > messages like the one above when attempting to open a database. It > seems to be a timing issue. > > This does not happen with postgresql-8.3.5 but does happen with > postgresql-8.3.7. I used the One-click installer from EnterpriseDB and > am running Vista SP1. First suspect when running on windows is antivirus software. -- 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] postgres 3.7 unexpected closing of connections
Actually it is happening on two different Vista platforms one running McAfee and the other Symantec. Andrew -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Thursday, 14 May 2009 09:40 To: a.macl...@cas.edu.au Cc: General Subject: Re: [GENERAL] postgres 3.7 unexpected closing of connections On Wed, May 13, 2009 at 5:36 PM, Andrew Maclean wrote: > I have the following error message: > > "Error connecting to the server: server closed the connection unexpectedly. > This probably means that the server terminated abnormally before or > while processing the request. > " > It happens intermittently both when using pgAdminIII and when using QT > so it is not a pgAdminIII issue. > > Often the connection seems to succeed but you get a series of error > messages like the one above when attempting to open a database. It > seems to be a timing issue. > > This does not happen with postgresql-8.3.5 but does happen with > postgresql-8.3.7. I used the One-click installer from EnterpriseDB and > am running Vista SP1. First suspect when running on windows is antivirus software. -- 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] postgres 3.7 unexpected closing of connections
I'll check this out but I am puzzled as to why it suddenly appeared after upgrading. -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Thursday, 14 May 2009 09:40 To: a.macl...@cas.edu.au Cc: General Subject: Re: [GENERAL] postgres 3.7 unexpected closing of connections On Wed, May 13, 2009 at 5:36 PM, Andrew Maclean wrote: > I have the following error message: > > "Error connecting to the server: server closed the connection unexpectedly. > This probably means that the server terminated abnormally before or > while processing the request. > " > It happens intermittently both when using pgAdminIII and when using QT > so it is not a pgAdminIII issue. > > Often the connection seems to succeed but you get a series of error > messages like the one above when attempting to open a database. It > seems to be a timing issue. > > This does not happen with postgresql-8.3.5 but does happen with > postgresql-8.3.7. I used the One-click installer from EnterpriseDB and > am running Vista SP1. First suspect when running on windows is antivirus software. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Warm standby: 1 to N
Hi All, Let's consider the following case: WAL segments from a master have been shipped to N warm standby servers, and now the master fails. Using this or that mechanism, one of the warm standbys takes over and becomes the new master. Now the question is what to do with the other N-1 warm standbys. By the failure, all N warm standbys were the same exact copies of the master. So at least in theory, the N-1 warm standbys left can be fed with WAL segments from the new master. Do you think it will work in practice? Are there any pitfalls? Thanks! Yar -- 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 to extract data from bytea so it is be used in blob for mysql database
I used pg_dump to extract the data from my database with bytea fields (having pictures) in plain text format. I encoutered some problems to restore those bytea data to the blobs field in a Mysql database. Any advice?
Re: [GENERAL] postgres 3.7 unexpected closing of connections
I did a fresh installation after deleting every reference to postgres and pgAdmin in the registry and removing the relevant folders. It looks as if the installation fails when loading additional modules. I also think host should be localhost at this stage. Here is a portion of the log that shows a definite error: *** Script output: Starting postgresql-8.3 Service postgresql-8.3 started successfully startserver.vbs ran to completion Script stderr: Loading additional SQL modules... Executing cscript Script exit code: 2 Script output: Installing pl/pgsql in the template1 databases... psql: could not connect to server: Connection refused (0x274D/10061) Is the server running on host "???" and accepting TCP/IP connections on port 5432? Failed to install pl/pgsql in the 'template1' database Installing the adminpack module in the postgres database... psql: could not connect to server: Connection refused (0x274D/10061) Is the server running on host "???" and accepting TCP/IP connections on port 5432? Failed to install the 'adminpack' module in the 'postgres' database loadmodules.vbs ran to completion Script stderr: Program ended with an error exit code Error running cscript //NoLogo "C:\Program Files\PostgreSQL\8.3\installer\server\loadmodules.vbs" "postgres" "" "C:\Program Files\PostgreSQL\8.3" "C:\Program Files\PostgreSQL\8.3\data" 5432 : Program ended with an error exit code Error running cscript //NoLogo "C:\Program Files\PostgreSQL\8.3\installer\server\loadmodules.vbs" "postgres" "x" "C:\Program Files\PostgreSQL\8.3" "C:\Program Files\PostgreSQL\8.3\data" 5432 : Program ended with an error exit code Write the data directory to the ini file... Write the port number, service ID, locale and superuser to the ini file... *** On Thu, May 14, 2009 at 9:57 AM, Andrew Maclean wrote: > I'll check this out but I am puzzled as to why it suddenly appeared after > upgrading. > > > -Original Message- > From: Scott Marlowe [mailto:scott.marl...@gmail.com] > Sent: Thursday, 14 May 2009 09:40 > To: a.macl...@cas.edu.au > Cc: General > Subject: Re: [GENERAL] postgres 3.7 unexpected closing of connections > > On Wed, May 13, 2009 at 5:36 PM, Andrew Maclean > wrote: >> I have the following error message: >> >> "Error connecting to the server: server closed the connection > unexpectedly. >> This probably means that the server terminated abnormally before or >> while processing the request. >> " >> It happens intermittently both when using pgAdminIII and when using QT >> so it is not a pgAdminIII issue. >> >> Often the connection seems to succeed but you get a series of error >> messages like the one above when attempting to open a database. It >> seems to be a timing issue. >> >> This does not happen with postgresql-8.3.5 but does happen with >> postgresql-8.3.7. I used the One-click installer from EnterpriseDB and >> am running Vista SP1. > > First suspect when running on windows is antivirus software. > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- ___ Andrew J. P. Maclean Centre for Autonomous Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___ -- 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] postgres 3.7 unexpected closing of connections
I tried the pgInstaller instead of the One Click Installer and everything installed correctly. Andrew On Thu, May 14, 2009 at 10:34 AM, Andrew Maclean wrote: > I did a fresh installation after deleting every reference to postgres > and pgAdmin in the registry and removing the relevant folders. > It looks as if the installation fails when loading additional modules. > I also think host should be localhost at this stage. > > Here is a portion of the log that shows a definite error: > *** > > Script output: > Starting postgresql-8.3 > Service postgresql-8.3 started successfully > startserver.vbs ran to completion > > Script stderr: > > > Loading additional SQL modules... > Executing cscript > Script exit code: 2 > > Script output: > Installing pl/pgsql in the template1 databases... > psql: could not connect to server: Connection refused (0x274D/10061) > Is the server running on host "???" and accepting > TCP/IP connections on port 5432? > > Failed to install pl/pgsql in the 'template1' database > Installing the adminpack module in the postgres database... > psql: could not connect to server: Connection refused (0x274D/10061) > Is the server running on host "???" and accepting > TCP/IP connections on port 5432? > > Failed to install the 'adminpack' module in the 'postgres' database > loadmodules.vbs ran to completion > > Script stderr: > Program ended with an error exit code > > Error running cscript //NoLogo "C:\Program > Files\PostgreSQL\8.3\installer\server\loadmodules.vbs" "postgres" "" > "C:\Program Files\PostgreSQL\8.3" "C:\Program > Files\PostgreSQL\8.3\data" 5432 : Program ended with an error exit > code > Error running cscript //NoLogo "C:\Program > Files\PostgreSQL\8.3\installer\server\loadmodules.vbs" "postgres" "x" > "C:\Program Files\PostgreSQL\8.3" "C:\Program > Files\PostgreSQL\8.3\data" 5432 : Program ended with an error exit > code > Write the data directory to the ini file... > Write the port number, service ID, locale and superuser to the ini file... > *** > > > > > On Thu, May 14, 2009 at 9:57 AM, Andrew Maclean wrote: >> I'll check this out but I am puzzled as to why it suddenly appeared after >> upgrading. >> >> >> -Original Message- >> From: Scott Marlowe [mailto:scott.marl...@gmail.com] >> Sent: Thursday, 14 May 2009 09:40 >> To: a.macl...@cas.edu.au >> Cc: General >> Subject: Re: [GENERAL] postgres 3.7 unexpected closing of connections >> >> On Wed, May 13, 2009 at 5:36 PM, Andrew Maclean >> wrote: >>> I have the following error message: >>> >>> "Error connecting to the server: server closed the connection >> unexpectedly. >>> This probably means that the server terminated abnormally before or >>> while processing the request. >>> " >>> It happens intermittently both when using pgAdminIII and when using QT >>> so it is not a pgAdminIII issue. >>> >>> Often the connection seems to succeed but you get a series of error >>> messages like the one above when attempting to open a database. It >>> seems to be a timing issue. >>> >>> This does not happen with postgresql-8.3.5 but does happen with >>> postgresql-8.3.7. I used the One-click installer from EnterpriseDB and >>> am running Vista SP1. >> >> First suspect when running on windows is antivirus software. >> >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > > > -- > ___ > Andrew J. P. Maclean > Centre for Autonomous Systems > The Rose Street Building J04 > The University of Sydney 2006 NSW > AUSTRALIA > Ph: +61 2 9351 3283 > Fax: +61 2 9351 7474 > URL: http://www.acfr.usyd.edu.au/ > ___ > -- ___ Andrew J. P. Maclean Centre for Autonomous Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
FW: [GENERAL] lack in psql console
in the psql prompt try this: \! stty sane Ing. Edmundo Robles Lopez. Analista Programador. -- -Original Message- From: pgsql-general-ow...@postgresql.org on behalf of Juan Camilo Marín Sent: Wed 5/13/2009 12:58 PM To: Craig Ringer Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] lack in psql console Hello Craig. Thanks for your answer. I did what you say (use the default size of the terminal) but the problem persist, the psql still start to write the new senteces above the old one, it is to weird. Best regards, 2009/5/10 Craig Ringer > Juan Camilo Marín wrote: > > > => select * from \d summary_table; > > > > The sentense start after "=> select * from" I don't know why this is > > happen. > > Probably a terminal emulation issue. Readline will be sending the wrong > escape sequences to control your terminal or your terminal will be > misbehaving somehow. > > A common cause of readline issues is resizing your terminal window. Try > testing in a terminal window that's the default size. If that works OK, > try resizing it while it's still running the shell, THEN start psql. > > -- > Craig Ringer > -- Life may only be understood backwards but it must be lived forwards -- 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] Connections Crashing
AntiVirus is not running. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Scott Marlowe Sent: Thursday, May 14, 2009 3:35 AM To: Shahbaz A. Tyagi Cc: Richard Huxton; pgsql-general@postgresql.org Subject: Re: [GENERAL] Connections Crashing On Wed, May 13, 2009 at 4:22 PM, Shahbaz A. Tyagi wrote: > Hi, > > Its not happening in all the machines, its only some machines. I am testing > on XP 32 bit. Its coming only while running Postgres and accessing the DB. > > Strange part is that some machines are getting restarted as soon as it gets > the error. So, do you have antivirus on these machines or not? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Connections Crashing
Shahbaz A. Tyagi wrote: Hi, Its not happening in all the machines, its only some machines. I am testing on XP 32 bit. Its coming only while running Postgres and accessing the DB. Strange part is that some machines are getting restarted as soon as it gets the error. You mean the whole machine crashes and restarts? That shouldn't even be possible. From last some time we have started getting error as message dialog showing error 0xC142 and after that, connections are crashing. LOG: server process (PID 8156) was terminated by exception 0xC142 It's probably worth making sure you are logging connections (log_connections) and are recording the PID in the log-lines (log_line_prefix). http://www.postgresql.org/docs/8.3/interactive/runtime-config-logging.html Presumably this error is occuring when a new connection is made, but it would be sensible to make sure. -- Richard Huxton Archonet Ltd -- 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] Space for pg_dump
On Mon, 20 Apr 2009, Jan Otto wrote: If you have big toast tables you get wrong results with the query suggested at http://wiki.postgresql.org/wiki/Disk_Usage because it takes the toasted values not into account. I can't recall why I wrote that to filter out things in the pg_toast namespace in the first place. I just took the easy way out here--the query has been updated to not exclude relations in that namespace anymore and I link to the TOAST docs for more details. I consider combining all the values together, as you did in your example code and as pg_total_relation_size does, as a different type of report. Accordingly, I just updated with examples of both types, as well as something to work against pre-8.1 databases. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER
On Mittwoch, 13. Mai 2009, Alvaro Herrera wrote: > > we're using postgresql 8.3 for some logging framework. > > > > There are several tables for each day (which are inherited from a common > > base), which > > - are filled during the day, > > - after midnight the indizes are changed to FILLFACTOR=100, and > > - the tables get CLUSTERed by the most important index. > > - Some time much later the tables that aren't needed anymore are DROPped. > > > > So far, so fine. > > Do say, do you have any long-running transactions, or "idle" > transactions? Maybe someone opened a terminal somewhere and left it > open for days? Have a look at pg_stat_activity. Yes, I have two terminal windows for different users/schemas in the same DB open - but they're set to auto-commit, and have no tables open or locked. Please, let me repeat myself: > So, as summary: "vacuum_freeze_min_age=0" seems to interfere with btree > indizes with FILLFACTOR=100 in some way, so that CLUSTER doesn't return > space to the filesystem. Might the open connections make a difference? Regards, Phil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
