[GENERAL] debugging postgres server memory checker
hi List, As a result of writing a C 'postgres' function, I have a memory corruption problem. Something is either being free twice or I am accessing beyond the data structure and corrupting the malloc/free/sbrk memory pool. My attempts to memory support in glibc, that been stopped because something seems to clear the envron before the postgres/postmaster program is started(well ps does not report an envronment setting for this process) Memory checking programs such as valgrind want direct control over the postgres/postmaster process, ie they must fork the process and not the postgres startup script from /etc/rc2.d Q. How do inoke the postgres startup in such a way that my current terminal becomes the controling terminal for the postgres/postmaster process? I do not want to run the postgres/postmaster program as a background deamin style process. Thanks in advance Dave. -- -- 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] what Linux to run
On Wed, Feb 29, 2012 at 3:58 PM, Scott Marlowe wrote: > Note that Ubuntu also comes in a GUI free server edition as well. I > can definitely state that Ubuntu 10.04 LTS Server edition is rock > solid stable for the hardware I've run it on (48 core AMD and 40 core > Intel machines with LSI, Arecam and 3Ware cards) Ubuntu 9.10 isn't LTS, but it's served me just fine. I have a server that's not been rebooted since July 2010 (including a database-using application process that has been running since boot, and is in constant use), and I don't feel like bringing it down to bring it up to date! Really, any of the main-stream Linuxes should be fine. Chris Angelico -- 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] Yearly date comparison?
Nick wrote:
> What is the best way to find an event with a yearly occurrence?
>
> CREATE TABLE events (
> start_date DATE,
> end_date DATE,
> recurring TEXT
> );
> INSERT INTO events (start_date, end_date, recurring) VALUES
> ('2010-02-28','2010-03-01','yearly');
>
> SELECT * FROM events WHERE (start_date+'2 YEARS'::INTERVAL) >= NOW()
> AND (end_date+'2 YEARS'::INTERVAL) < NOW();
>
> Since I may not know how many years back the start/end_date is, is
> there a way to just ignore the year or make it the current year,
> without killing performance?
I guess that you mixed up < and > in your sample query.
What about
WITH n AS
(SELECT EXTRACT(DAY FROM current_timestamp)
+ 100*EXTRACT(MONTH FROM current_timestamp) AS d)
SELECT events.*
FROM events CROSS JOIN n
WHERE EXTRACT(DAY FROM start_date)
+ 100*EXTRACT(MONTH FROM start_date) <= n.d
AND EXTRACT(DAY FROM end_date)
+ 100*EXTRACT(MONTH FROM end_date) > n.d;
If you define an SQL function for
EXTRACT(DAY FROM dat) + 100*EXTRACT(MONTH FROM dat)
it will look much nicer.
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] Correct way for locking a row for long time without blocking another transactions (=nowait)?
On Wed, Feb 29, 2012 at 8:18 AM, Durumdara wrote: > 2012/2/28 Filip Rembiałkowski : >> A way to force error when any statement takes more than 200 msec: >> SET statement_timeout TO '200ms'; > > As I see that is not needed here. > Only for normal updates. > > And how I can "reset" statement_timeout after this command to "default" value? SET statement_timeout TO DEFAULT; >> The waiting that you observed is normal - there is no way in >> PostgreSQL to force _other_ transactions into NOWAIT mode. All >> transactions that do not want to wait, should use explicit locking >> with NOWAIT option. > > If I understand it well, I must follow NOWAIT schema for update to > avoid long updates (waiting for error). > > 1.) I starting everywhere with select for update nowait > 2.) Next I make update command > 3.) Commit > > So if I starting with point 2, I got long blocking because of waiting > for release row lock? Yes, you _can_ get into long waiting siutuation this way. > May the solution is if PGSQL support that: > > create temporary table tmp_update as > select id from atable > where ... > > select * from atable for update nowait > where id in (select id from tmp_update) > > update atable set value = 1 > where id in (select id from tmp_update) > > Is this correct? > yes I think so. -- 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 installing plpythonu in 9.1
Em 28 de fevereiro de 2012 17:48, Devrim GÜNDÜZ escreveu: > > Hi, > > On Tue, 2012-02-28 at 17:39 -0300, Clodoaldo Neto wrote: > > template1=# create extension plpythonu; > > ERROR: could not open extension control file > > "/usr/pgsql-9.1/share/extension/plpythonu.control": No such file or > > directory > > This is a packaging bug :-( > > That file is installed with -contrib subpackage. Please install -contrib > package until I push the new set of RPMs. > > Very easy. Thanks. Clodoaldo > Created http://wiki.pgrpms.org/ticket/76 if you want to keep track of > the bug. > > Regards, > -- > Devrim GÜNDÜZ > Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com > PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer > Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr > http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz >
Re: [GENERAL] Error installing postgresq91-python package
Em 28 de fevereiro de 2012 17:54, Devrim GÜNDÜZ escreveu: > > Hi, > > On Tue, 2012-02-28 at 10:33 -0300, Clodoaldo Neto wrote: > > > > > Transaction Check Error: > > file /usr/lib64/python2.6/site-packages/_pg.so from install of > > postgresql91-python-0:4.0-2PGDG.rhel6.x86_64 conflicts with file from > > package PyGreSQL-3.8.1-2.el6.x86_64 > > > What is unusual here? I'd probably add a Conflicts: line, so that the > error would appear before the installation. Is that what you want? > > Not sure. It was just in case you had already done something like that and didn't know it was not working. Clodoaldo > Regards, > -- > Devrim GÜNDÜZ > Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com > PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer > Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr > http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz >
[GENERAL] Re: One transaction by connection - commit subdetails without release master transaction?
Dear Anybody! I replace the long question to some shorter: As I see the PGSQL supports one transaction per connection. Is this information ok? Formerly I believed that only Zeos/PGDAC supports one trs/conn, but now it seems to be based on the server, and not the components... (The pgadmin is also showing one tx state/statement per connection) So if I want to make two transactions in client am I must duplify the connections in this period? Thanks for your help: dd 2012/2/17 Durumdara : > Dear PGSQL Masters! > > > What do you thinking about this? Can I do this without doubling the > connections by users (if I duplicate the connections, the subforms can use > the second connection = another transaction)? > > > Thanks for your help: dd > -- 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] archive_cleanup_command recovery.conf Standby server error
Hi Adrian, The pg_archivecleanup.exe is already installed with postgresql 9.1, it is under bin folder. I tried manually to run the command pg_archivecleanup.exe 10.1.18.16\\DB_Stream_Share\\ %r -- it didnt recognize the path pg_archivecleanup.exe \\10.1.18.16\DB_Stream_Share\ %r --it didnt recognize the path I gave this path directly in the recovery.conf yet the same, there is something missing .. What is that ? please help me around this .. -- View this message in context: http://postgresql.1045698.n5.nabble.com/archive-cleanup-command-recovery-conf-Standby-server-error-tp5520966p5524854.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] Client encoding not the same as the db encoding
In 9.1.3 the client encoding is not defaulting to the db encoding: postgresql.conf: #client_encoding = sql_ascii => show server_encoding; server_encoding - SQL_ASCII => show client_encoding; client_encoding - UTF8 In 9.0 the client encoding defaults to the db encoding: postgresql.conf: #client_encoding = sql_ascii => show client_encoding; client_encoding - SQL_ASCII => show server_encoding; server_encoding - SQL_ASCII What am i missing? Regards, Clodoaldo
Re: [GENERAL] accumulating handles problem on machine running postgresql
I ran process explorer and looked at the handles for the System process. The
vast majority of the handles are of type "Key". I can find them in the
registry. I took two at random from process explorer and exported the registry
branch for them below.
## EXAMPLE 1: ##
Key Name:
HKEY_CLASSES_ROOT\CLSID\{9F074EE2-E6E9-4d8a-A047-EB5B5C3C55DA}
Class Name:
Last Write Time: 2/28/2012 - 1:26 AM
Value 0
Name:
Type:REG_SZ
Data:HwTextInsertion Class
Key Name:
HKEY_CLASSES_ROOT\CLSID\{9F074EE2-E6E9-4d8a-A047-EB5B5C3C55DA}\InprocServer32
Class Name:
Last Write Time: 2/29/2012 - 4:05 AM
Value 0
Name:
Type:REG_EXPAND_SZ
Data:%CommonProgramFiles%\microsoft shared\ink\tiptsf.dll
Value 1
Name:ThreadingModel
Type:REG_SZ
Data:Apartment
Key Name:
HKEY_CLASSES_ROOT\CLSID\{9F074EE2-E6E9-4d8a-A047-EB5B5C3C55DA}\ProgID
Class Name:
Last Write Time: 2/29/2012 - 4:05 AM
Key Name:
HKEY_CLASSES_ROOT\CLSID\{9F074EE2-E6E9-4d8a-A047-EB5B5C3C55DA}\Server
Class Name:
Last Write Time: 2/29/2012 - 4:05 AM
## EXAMPLE 2: ##
Key Name:
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{80FF6842-51A9-4959-B3B9-EE4DCBFD7740}
Class Name:
Last Write Time: 2/28/2012 - 12:07 AM
Key Name:
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{80FF6842-51A9-4959-B3B9-EE4DCBFD7740}\Programmable
Class Name:
Last Write Time: 12/13/2010 - 12:27 PM
Key Name:
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{80FF6842-51A9-4959-B3B9-EE4DCBFD7740}\InprocServer32
Class Name:
Last Write Time: 2/29/2012 - 3:05 AM
Key Name:
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{80FF6842-51A9-4959-B3B9-EE4DCBFD7740}\ProgID
Class Name:
Last Write Time: 2/29/2012 - 3:05 AM
Key Name:
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{80FF6842-51A9-4959-B3B9-EE4DCBFD7740}\Server
Class Name:
Last Write Time: 2/29/2012 - 3:05 AM
## END EXAMPLES ##
A common thread I notice when looking through the keys is InprocServer32.
Adam Bruss
Senior Development Engineer
AWR Corporation
11520 N. Port Washington Rd., Suite 201
Mequon, WI 53092 USA
P: 1.262.240.0291 x104
F: 1.262.240.0294
E: abr...@awrcorp.com
W: http://www.awrcorp.com
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of dennis jenkins
Sent: Tuesday, February 28, 2012 4:01 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] accumulating handles problem on machine running
postgresql
On Tue, Feb 28, 2012 at 8:48 AM, Adam Bruss wrote:
> The handles persist through restarting the postgresql service and restarting
> the IIS server. The handles are accumulating on the System process. I think
> the handles are created when the web service is accessed but that would mean
> the IIS worker processes would have responsibility and they don't seem to.
> Recycling the worker processes in IIS does nothing. And the worker processes
> have their own process w3wp.exe which never accumulate handles. It's probably
> not a postgresql thing or other people would be seeing it.
>
Use "process explorer" from sysinternals / microsoft (google for it)
to see what these handles are for (pipes, files, events, mutants,
desktops, winstations (ok, probably not those), etc...
--
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] Client encoding not the same as the db encoding
On Wednesday, February 29, 2012 6:47:21 am Clodoaldo Neto wrote: > In 9.1.3 the client encoding is not defaulting to the db encoding: > > postgresql.conf: > > #client_encoding = sql_ascii > > => show server_encoding; > server_encoding > - > SQL_ASCII > > => show client_encoding; > client_encoding > - > UTF8 > > In 9.0 the client encoding defaults to the db encoding: > > postgresql.conf: > > #client_encoding = sql_ascii > > => show client_encoding; > client_encoding > - > SQL_ASCII > > => show server_encoding; > server_encoding > - > SQL_ASCII > > What am i missing? Regards, Clodoaldo My guess this: http://www.postgresql.org/docs/9.1/interactive/release-9-1.html "E.4.3.10.1. libpq Add a libpq connection option client_encoding which behaves like he PGCLIENTENCODING environment variable (Heikki Linnakangas) The value auto sets the client encoding based on the operating system locale. " Which leads to this: http://www.postgresql.org/docs/9.1/interactive/libpq-connect.html#LIBPQ-CONNECT-CLIENT-ENCODING -- 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
[GENERAL] Easy way to corrupt a standby database
Step one: Set up streaming replication Step two: Promote replica to full database Step three: Set up the database as a replica again Now I understand this is well known but it is pretty easy to do. What I am wondering is whether there is an easy way to prevent this from occurring in the future via software checks or whether it is one of those things where the nature of the problem makes it hard to solve. Best Wishes, Chris Travers
Re: [GENERAL] Client encoding not the same as the db encoding
Adrian Klaver writes: > On Wednesday, February 29, 2012 6:47:21 am Clodoaldo Neto wrote: >> In 9.1.3 the client encoding is not defaulting to the db encoding: >> What am i missing? Regards, Clodoaldo > My guess this: > http://www.postgresql.org/docs/9.1/interactive/release-9-1.html > "E.4.3.10.1. libpq > Add a libpq connection option client_encoding which behaves like > he PGCLIENTENCODING environment variable (Heikki Linnakangas) > The value auto sets the client encoding based on the operating > system locale. > " No, probably more this (under "psql") * Have psql set the client encoding from the operating system locale by default (Heikki Linnakangas) This only happens if the PGCLIENTENCODING environment variable is not set. I think the idea was to try to match your terminal window's encoding automatically. 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] Calling Functions With OUT paramaters
I have a function definited as such: CREATE OR REPLACE FUNCTION address_pkg.parse_zip(IN cpostal character varying, OUT czip character varying, OUT czip4 character varying) LANGUAGE plpgsql; How does one call this from another plpgsql function? Currently, I am using something of the form: SELECT * INTO cZip, cZip4 FROM Address_pkg.Parse_Zip(postal_code); But, I am not a big fan of this. Is there another way?
[GENERAL] Arbitrary and mixed data types in function
Hello,
I'd like to write a generalized function that accepts arguments of all type
without producing an error if types do not match across arguments.
As an example, I've written a simple function below called 'bound' (below) that
returns a value 'x' bounded by the range [lo, hi]. The function works on any
single type with a defined inequality operator by making use of the polymorphic
ANYELEMENT type: ex: bound(10, 0, 8)=8, and bound('A'::text, 'D'::text,
'Z'::text)='D'.
Unfortunately, the function reports an error unless 'x', 'lo', and 'hi' all
share the same type, even if an inequality operation is still sensible. For
instance 'SELECT bound(1.0, 2, 3);' returns the error
'ERROR: function bound(numeric, integer, integer) does not exist'
What I would like is for the function to accept mixed types and return a value
of the same type as 'x'. For example 'SELECT bound(3::real, 0::integer,
10::smallint);' would return 3::real. For just the 9 numeric data types with
well-defined inequality comparisons, I would need to define 9^3 functions to
cover all possible type permutations. My question is, can I write a single
function that accepts arbitrary and different types for its three arguments?
I'm using PostgreSQL 9.1.
Thanks, Robert
CREATE OR REPLACE FUNCTION bound(x ANYELEMENT, lo ANYELEMENT, hi ANYELEMENT,
OUT bound ANYELEMENT) AS $$
BEGIN
IF x < lo THEN
bound := lo;
ELSEIF x > hi THEN
bound := hi;
ELSE
bound := x;
END IF;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
Robert McGehee, CFA
Geode Capital Management, LLC
One Post Office Square, 28th Floor | Boston, MA | 02109
Direct: (617)392-8396
--
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] archive_cleanup_command recovery.conf Standby server error
Try to give the full path of pg_archivecleanup.exe e.g: archive_cleanup_command = '/usr/local/pgsql/bin/pg_archivecleanup %r' > Date: Wed, 29 Feb 2012 04:40:17 -0800 > From: chinna...@gmail.com > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] archive_cleanup_command recovery.conf Standby server > error > > Hi Adrian, > > The pg_archivecleanup.exe is already installed with postgresql 9.1, it is > under bin folder. I tried manually to run the command pg_archivecleanup.exe > 10.1.18.16\\DB_Stream_Share\\ %r -- it didnt recognize the path > pg_archivecleanup.exe \\10.1.18.16\DB_Stream_Share\ %r --it didnt recognize > the path > > I gave this path directly in the recovery.conf yet the same, there is > something missing .. > > What is that ? please help me around this .. > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/archive-cleanup-command-recovery-conf-Standby-server-error-tp5520966p5524854.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
Re: [GENERAL] what Linux to run
Note that Ubuntu also comes in a GUI free server edition as well. I can definitely state that Ubuntu 10.04 LTS Server edition is rock solid stable +1 I've been running 10.04 LTS Server for over three years (on a Dell PowerEdge 2850) using Martin Pitt's PostgreSQL 9.1 PPA. -- Gary Chambers -- 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] version controlling postgresql code
Thanks all. We will stick with CVS then. Thanks again On Tue, Feb 28, 2012 at 3:36 PM, Greg Williamson wrote: > akp geek asked: > > > > > Would like to know if any one of you have used CVS or some other > version controlling tools to version the postgres code? Any > recommendations? Appreciate your help > > > > > > > I used CVS at previous jobs. > > Currently the place I work uses git and a set of rules for programmers who > create DDL changes, including rollback options (such as renaming columns to > dropme_xxx instead of simply dropping them outright in the first change > set). > > The SQL files are checked in with a timestamp which controls the order of > updates when we apply them to runtime. > > I've had unpleasant experiences with commercial tools; CVS works well for > simple environments and git can be used for more tangled development work. > > HTH, > > Greg Williamson > > > -- > 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] Client encoding not the same as the db encoding
On 02/29/2012 07:50 AM, Tom Lane wrote: Adrian Klaver writes: On Wednesday, February 29, 2012 6:47:21 am Clodoaldo Neto wrote: In 9.1.3 the client encoding is not defaulting to the db encoding: What am i missing? Regards, Clodoaldo My guess this: http://www.postgresql.org/docs/9.1/interactive/release-9-1.html "E.4.3.10.1. libpq Add a libpq connection option client_encoding which behaves like he PGCLIENTENCODING environment variable (Heikki Linnakangas) The value auto sets the client encoding based on the operating system locale. " No, probably more this (under "psql") Well I was half way there:) http://www.postgresql.org/docs/9.1/interactive/app-psql.html "If at least one of standard input or standard output are a terminal, then psql sets the client encoding to "auto", which will detect the appropriate client encoding from the locale settings (LC_CTYPE environment variable on Unix systems). If this doesn't work out as expected, the client encoding can be overridden using the environment variable PGCLIENTENCODING." * Have psql set the client encoding from the operating system locale by default (Heikki Linnakangas) This only happens if the PGCLIENTENCODING environment variable is not set. I think the idea was to try to match your terminal window's encoding automatically. regards, tom lane -- 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] archive_cleanup_command recovery.conf Standby server error
Dear Dhaval, I tried as you told as below archive_cleanup_command='c:\\Program files\\postgresql\\9.1\\pg_archivecleanup.exe 10.1.18.16\\DB_Stream_Share\\ %r' this didnt work gave a error in the log file. -- View this message in context: http://postgresql.1045698.n5.nabble.com/archive-cleanup-command-recovery-conf-Standby-server-error-tp5520966p5525452.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
Re: [GENERAL] archive_cleanup_command recovery.conf Standby server error
On 02/29/2012 04:40 AM, chinnaobi wrote: Hi Adrian, The pg_archivecleanup.exe is already installed with postgresql 9.1, it is under bin folder. I tried manually to run the command pg_archivecleanup.exe 10.1.18.16\\DB_Stream_Share\\ %r -- it didnt recognize the path When using pg_archivecleanup from the command line %r is not used. See below for more information: http://www.postgresql.org/docs/9.1/interactive/pgarchivecleanup.html pg_archivecleanup.exe \\10.1.18.16\DB_Stream_Share\ %r --it didnt recognize the path What was the exact error message? I gave this path directly in the recovery.conf yet the same, there is something missing .. What is that ? please help me around this .. I don't use Postgres on Windows so I am going to be of limited use here. If it me though I would investigate/do the following: 1) Does the user you are running pg_archivecleanup as have rights to the WAL archive directory? 2) Copy the archived WAL files to another less convoluted path and see if it is a path problem. -- View this message in context: http://postgresql.1045698.n5.nabble.com/archive-cleanup-command-recovery-conf-Standby-server-error-tp5520966p5524854.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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
[GENERAL] "invalid memory alloc request size" + "Could not open file "pg_clog/XXXX"
Hi In my Postgresql 8.4.0 server, since this morning some tables are unavailable, see example below : --> pg_dump MY_DB > bkp_MY_DB.dmp pg_dump: SQL command failed pg_dump: Error message from server: ERROR: invalid memory alloc request size 18446744073709551613 pg_dump: The command was: COPY . (col1, col2, ...). --> vacuum analyze . ; WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. --> select * from . ; ERROR: invalid memory alloc request size 18446744073709551613 --> server log file Feb 29 05:31:44 my_server postgres[6686]: [17-1] user=,db= LOG: server process (PID 3887) was terminated by signal 11: Segmentation fault Feb 29 05:31:44 my_server postgres[6686]: [18-1] user=,db= LOG: terminating any other active server processes Feb 29 05:31:44 my_server postgres[6686]: [19-1] user=,db= LOG: all server processes terminated; reinitializing Feb 29 05:31:44 my_server postgres[3892]: [20-1] user=,db= LOG: database system was interrupted; last known up at 2012-02-29 05:22:33 CET Feb 29 05:31:44 my_server postgres[3892]: [21-1] user=,db= LOG: database system was not properly shut down; automatic recovery in progress Feb 29 05:31:44 my_server postgres[3892]: [22-1] user=,db= LOG: redo starts at 10/67C2A3B8 Feb 29 05:31:45 my_server postgres[3892]: [23-1] user=,db= LOG: record with zero length at 10/68BCF990 Feb 29 05:31:45 my_server postgres[3892]: [24-1] user=,db= LOG: redo done at 10/68BCF960 Feb 29 05:31:45 my_server postgres[3892]: [25-1] user=,db= LOG: last completed transaction was at log time 2012-02-29 05:31:42.618352+01 Feb 29 05:31:45 my_server postgres[6686]: [20-1] user=,db= LOG: database system is ready to accept connections Feb 29 05:32:52 my_server postgres[4469]: [21-1] user=[unknown],db=[unknown] LOG: incomplete startup packet Feb 29 05:33:52 my_server postgres[6686]: [21-1] user=,db= LOG: server process (PID 5151) was terminated by signal 11: Segmentation fault Feb 29 05:33:52 my_server postgres[6686]: [22-1] user=,db= LOG: terminating any other active server processes Feb 29 05:33:52 my_server postgres[6686]: [23-1] user=,db= LOG: all server processes terminated; reinitializing Feb 29 05:33:52 my_server postgres[5152]: [24-1] user=,db= LOG: database system was interrupted; last known up at 2012-02-29 05:31:45 CET Feb 29 05:33:52 my_server postgres[5152]: [25-1] user=,db= LOG: database system was not properly shut down; automatic recovery in progress Feb 29 05:33:52 my_server postgres[5152]: [26-1] user=,db= LOG: record with zero length at 10/68BCF9D8 Feb 29 05:33:52 my_server postgres[5152]: [27-1] user=,db= LOG: redo is not required Feb 29 05:33:52 my_server postgres[5153]: [24-1] user=match,db=MY_DB FATAL: the database system is in recovery mode Feb 29 05:33:52 my_server postgres[6686]: [24-1] user=,db= LOG: database system is ready to accept connections Feb 29 05:37:19 my_server postgres[6686]: [25-1] user=,db= LOG: server process (PID 8065) was terminated by signal 11: Segmentation fault Feb 29 05:37:19 my_server postgres[6686]: [26-1] user=,db= LOG: terminating any other active server processes Feb 29 05:37:19 my_server postgres[6686]: [27-1] user=,db= LOG: all server processes terminated; reinitializing Feb 29 05:37:19 my_server postgres[8066]: [28-1] user=,db= LOG: database system was interrupted; last known up at 2012-02-29 05:33:52 CET Feb 29 05:37:19 my_server postgres[8066]: [29-1] user=,db= LOG: database system was not properly shut down; automatic recovery in progress Feb 29 05:37:19 my_server postgres[8066]: [30-1] user=,db= LOG: redo starts at 10/68BCFA20 Feb 29 05:37:19 my_server postgres[8066]: [31-1] user=,db= LOG: record with zero length at 10/68BD5BD0 Feb 29 05:37:19 my_server postgres[8066]: [32-1] user=,db= LOG: redo done at 10/68BD5BA0 Feb 29 05:37:19 my_server postgres[8066]: [33-1] user=,db= LOG: last completed transaction was at log time 2012-02-29 05:35:44.468968+01 Feb 29 05:37:19 my_server postgres[6686]: [28-1] user=,db= LOG: database system is ready to accept connections Feb 29 05:38:27 my_server postgres[8639]: [29-1] user=[unknown],db=[unknown] LOG: incomplete startup packet Feb 29 05:38:53 my_server postgres[6686]: [29-1] user=,db= LOG: server process (PID 8809) was terminated by signal 11: Segmentation fault I have tried to restart Postgresql but it did not solve these issues I cannot backup the full database because some tables have become unreadable I have got 7 databases on this server and only 2 have got this problem What could be the cause of the problem ? Is there a way to fix it without losing data and without dropping and recreating the db with my nightly pg_dump backup ? T
Re: [GENERAL] what Linux to run
I've been running 10.04 LTS Server for over three years (on a Dell PowerEdge 2850) using Martin Pitt's PostgreSQL 9.1 PPA. I apologize. That's over two years. -- G. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PQputCopyData buffer limit
Hello All, My CPP Application uses PQputCopyData to push the read buffer from a file into PostGreSql. According to dummy tests conducted, PQputCopyData has been able to upload Approx 9-10MB of data to PostGreSql at one shot. Can someone throw light as to the maximum buffer limit for this operation? Thanks in advance. Regards, Sujay -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-buffer-limit-tp5525514p5525514.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] Valid Input Syntax for Type DATE
I'm trying to insert rows into a table, but some date and time columns are missing values. In the INSERT INTO ... statements of the .sql file I've tried various formats: ,, and ,'', and ,' ', but they all generate the error of invalid syntax for type date. When I have missing date of the date and time types, how do I validly represent them in the input file? Rich -- 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] what Linux to run
On Wed, Feb 29, 2012 at 10:05 AM, Gary Chambers wrote: >> I've been running 10.04 LTS Server for over three years (on a Dell >> PowerEdge >> 2850) using Martin Pitt's PostgreSQL 9.1 PPA. > > > I apologize. That's over two years. Darnit! I was hoping to borrow your time machine too. :) -- 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] accumulating handles problem on machine running postgresql
On Wed, Feb 29, 2012 at 8:54 AM, Adam Bruss wrote:
> I ran process explorer and looked at the handles for the System process. The
> vast majority of the handles are of type "Key". I can find them in the
> registry. I took two at random from process explorer and exported the
> registry branch for them below.
>
> ## EXAMPLE 1: ##
>
> Key Name:
> HKEY_CLASSES_ROOT\CLSID\{9F074EE2-E6E9-4d8a-A047-EB5B5C3C55DA}
> Class Name:
> Last Write Time: 2/28/2012 - 1:26 AM
> Value 0
> Name:
> Type: REG_SZ
> Data: HwTextInsertion Class
>
>
> Key Name:
> HKEY_CLASSES_ROOT\CLSID\{9F074EE2-E6E9-4d8a-A047-EB5B5C3C55DA}\InprocServer32
> Class Name:
> Last Write Time: 2/29/2012 - 4:05 AM
> Value 0
> Name:
> Type: REG_EXPAND_SZ
> Data: %CommonProgramFiles%\microsoft shared\ink\tiptsf.dll
>
> Value 1
> Name: ThreadingModel
> Type: REG_SZ
> Data: Apartment
Seems like your web server is leaking registry keys used when loading
COM objects. The sample that you posted is for the "Tablet PC Input
Panel Text Services Framework" [1]. However, I find it strange that
a) IIS needs this and b) that it would leak it.
Are you able to obtain a large statistical sample of the leaked
registry keys? 2 out of 130,000 seems like a small sample.
Try the command line "handle.exe" tool [2]. It can dump to a text
file that you can then analyze with perl, python, grep, etc... or your
own eyeballs. :) See if the handle list is dominated by a specific
set of registry keys.
[1] http://systemexplorer.net/filereviews.php?fid=515344
[2] http://technet.microsoft.com/en-us/sysinternals/bb896655
--
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] Valid Input Syntax for Type DATE
On 2/29/2012 11:49 AM, Rich Shepard wrote: I'm trying to insert rows into a table, but some date and time columns are missing values. In the INSERT INTO ... statements of the .sql file I've tried various formats: ,, and ,'', and ,' ', but they all generate the error of invalid syntax for type date. When I have missing date of the date and time types, how do I validly represent them in the input file? Rich If the column is null'able, I think you can use the keyword: DEFAULT insert into(id, xdate) values (1, DEFAULT); Not 100%, but I think so. -Andy -- 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] Valid Input Syntax for Type DATE
On 2/29/2012 12:28 PM, Andy Colson wrote: On 2/29/2012 11:49 AM, Rich Shepard wrote: I'm trying to insert rows into a table, but some date and time columns are missing values. In the INSERT INTO ... statements of the .sql file I've tried various formats: ,, and ,'', and ,' ', but they all generate the error of invalid syntax for type date. When I have missing date of the date and time types, how do I validly represent them in the input file? Rich If the column is null'able, I think you can use the keyword: DEFAULT insert into(id, xdate) values (1, DEFAULT); Not 100%, but I think so. -Andy Or... use NULL: insert into(id, xdate) values (1, NULL); -Andy -- 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] Valid Input Syntax for Type DATE
On Wed, Feb 29, 2012 at 12:29 PM, Andy Colson wrote: > On 2/29/2012 12:28 PM, Andy Colson wrote: >> >> On 2/29/2012 11:49 AM, Rich Shepard wrote: >>> >>> I'm trying to insert rows into a table, but some date and time columns >>> are >>> missing values. In the INSERT INTO ... statements of the .sql file I've >>> tried various formats: ,, and ,'', and ,' ', but they all generate the >>> error >>> of invalid syntax for type date. >>> >>> When I have missing date of the date and time types, how do I validly >>> represent them in the input file? >>> >>> Rich >>> >>> >>> >> >> If the column is null'able, I think you can use the keyword: DEFAULT >> >> insert into(id, xdate) values (1, DEFAULT); >> >> Not 100%, but I think so. >> >> -Andy >> > > Or... use NULL: > > insert into(id, xdate) values (1, NULL); > > > -Andy NULL works, but one advantage of using DEFAULT is that you won't have to worry which columns are set to NOT NULL. This assumes that the NOT NULL columns have default values assigned to them, which is a good idea. Andrew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Privilege on schema 'public' not revokable
Hi,
after having been locked-out of the public schema by mistake (which I fixed
with "GRANT ALL ON SCHEMA public TO 'foobar'"), I wanted to update my
privilege-check script to take the schema into account, but I'm running into
the following behaviour :
> $ psql db_foo
> psql (9.1.2)
> Type "help" for help.
>
> db_foo=# REVOKE CREATE ON SCHEMA public FROM foouser;
> REVOKE
> db_foo=# SELECT has_schema_privilege('foouser', 'public', 'CREATE');
> has_schema_privilege
> --
> t
> (1 row)
>
> db_foo=# \q
> $ psql db_foo -U foouser
> psql (9.1.2)
> Type "help" for help.
>
> db_foo=> create table tokill2(a int);
> CREATE TABLE
> db_foo=>
This is observable in 9.1.2 and 8.3.18 (gentoo linux packages). Same issue
with USAGE privilege. 'foouser' is neither a superuser nor the owner of any
database object.
Either I misunderstood something, or something is going awry (I expected
has_schema_privilege() to return 'f', and not being allowed to create a table
as user 'foouser').
I'm also wondering how I managed to revoke the privilege (symptom: "no such
table 'foobar'" messages when logged-in as the problematic user) in the first
place. I was using slony 2.1 to migrate from PG8.3 to PG 9.1. Not all migrated
databases were affected by the problem.
As an aside, is there any better way to check existing privileges ? I need to
call the has_*_privilege() function for each privilege type to get an exact
view, which is cumbersome. I was using pg_class.relacl before, but it doesn't
support all object types and is not an official interface. Some of the tables
in information_schema look ideal, but again not all object types are covered.
Thanks in advance.
--
Vincent de Phily
Re: [GENERAL] Privilege on schema 'public' not revokable
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Vincent de Phily
Sent: Wednesday, February 29, 2012 1:57 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Privilege on schema 'public' not revokable
Hi,
after having been locked-out of the public schema by mistake (which I fixed
with "GRANT ALL ON SCHEMA public TO 'foobar'"), I wanted to update my
privilege-check script to take the schema into account, but I'm running into
the following behaviour :
> $ psql db_foo
> psql (9.1.2)
> Type "help" for help.
>
> db_foo=# REVOKE CREATE ON SCHEMA public FROM foouser;
> REVOKE
> db_foo=# SELECT has_schema_privilege('foouser', 'public', 'CREATE');
> has_schema_privilege
> --
> t
> (1 row)
>
> db_foo=# \q
> $ psql db_foo -U foouser
> psql (9.1.2)
> Type "help" for help.
>
> db_foo=> create table tokill2(a int);
> CREATE TABLE
> db_foo=>
This is observable in 9.1.2 and 8.3.18 (gentoo linux packages). Same issue
with USAGE privilege. 'foouser' is neither a superuser nor the owner of any
database object.
Either I misunderstood something, or something is going awry (I expected
has_schema_privilege() to return 'f', and not being allowed to create a
table as user 'foouser').
I'm also wondering how I managed to revoke the privilege (symptom: "no such
table 'foobar'" messages when logged-in as the problematic user) in the
first place. I was using slony 2.1 to migrate from PG8.3 to PG 9.1. Not all
migrated databases were affected by the problem.
As an aside, is there any better way to check existing privileges ? I need
to call the has_*_privilege() function for each privilege type to get an
exact view, which is cumbersome. I was using pg_class.relacl before, but it
doesn't support all object types and is not an official interface. Some of
the tables in information_schema look ideal, but again not all object types
are covered.
Going from memory here.
"foouser" is obtaining its permission to "CREATE" on the "public " schema
via global/PUBLIC permissions (i.e., via inheritance). Revoking only
removes an explicitly granted permission but does not institute a block for
an inherited permission. You would need to revoke the global permission to
CREATE on "public" and then only GRANT it back to those users/roles that you
wish to have it - all others will then effectively lose that ability.
David J.
Re: [GENERAL] Calling Functions With OUT paramaters
On 29/02/2012 15:51, David Greco wrote: > I have a function definited as such: > > CREATE OR REPLACE FUNCTION address_pkg.parse_zip(IN cpostal character > varying, OUT czip character varying, OUT czip4 character varying) > LANGUAGE plpgsql; > > How does one call this from another plpgsql function? Currently, I am > using something of the form: > > SELECT * INTO cZip, cZip4 FROM Address_pkg.Parse_Zip(postal_code); > > But, I am not a big fan of this. Is there another way? What's wrong with that? But to answer your question, I don't think there is... within pl/pgsql you have to SELECT INTO, or if you want to discard the result then you do PERFORM instead. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Privilege on schema 'public' not revokable
"David Johnston" writes: > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Vincent de Phily >> [ this doesn't do anything: ] >> db_foo=# REVOKE CREATE ON SCHEMA public FROM foouser; > "foouser" is obtaining its permission to "CREATE" on the "public " schema > via global/PUBLIC permissions (i.e., via inheritance). Revoking only > removes an explicitly granted permission but does not institute a block for > an inherited permission. You would need to revoke the global permission to > CREATE on "public" and then only GRANT it back to those users/roles that you > wish to have it - all others will then effectively lose that ability. Correct. Note where it says in the GRANT manual page that a user's effective rights are the sum of those granted to PUBLIC, those granted directly to him, and those granted to roles he is a member of. Rights granted to PUBLIC are available to everybody, full stop, and can't be selectively blocked. 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] Re: One transaction by connection - commit subdetails without release master transaction?
On Feb 29, 2012, at 4:11 AM, Durumdara wrote: > Dear Anybody! > > I replace the long question to some shorter: > > As I see the PGSQL supports one transaction per connection. Is this > information ok? Yes, in postgres a connection can support only one transaction. If you wish to reduce the number of connections, look into a connection pooler, such as pgBouncer. -- 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] Valid Input Syntax for Type DATE
On Wed, 29 Feb 2012, Andrew Gould wrote: If the column is null'able, I think you can use the keyword: DEFAULT insert into(id, xdate) values (1, DEFAULT); Or... use NULL: insert into(id, xdate) values (1, NULL); NULL works, but one advantage of using DEFAULT is that you won't have to worry which columns are set to NOT NULL. This assumes that the NOT NULL columns have default values assigned to them, which is a good idea. Thank you both. Rich -- 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] Re: One transaction by connection - commit subdetails without release master transaction?
On 29 Feb 2012, at 20:44, Ben Chobot wrote: >> As I see the PGSQL supports one transaction per connection. Is this >> information ok? > > Yes, in postgres a connection can support only one transaction. A small correction: a connection can support only one transaction _simultaneously_. You can serialise transactions on one connection just fine. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. -- 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] Re: One transaction by connection - commit subdetails without release master transaction?
On Feb 29, 2012, at 3:00 PM, Alban Hertroys wrote: > On 29 Feb 2012, at 20:44, Ben Chobot wrote: > >>> As I see the PGSQL supports one transaction per connection. Is this >>> information ok? >> >> Yes, in postgres a connection can support only one transaction. > > > A small correction: a connection can support only one transaction > _simultaneously_. You can serialise transactions on one connection just fine. Heh, woops, yes, that's a good correction to make. :) -- 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] PickSplit method of 2 columns ... error
Did this change to gistsplit.c to reduce the logging from LOG down to DEBUG1 ever done? -- Tim Wilson Lead DB Engineer - Telogis www.telogis.com www.telogis.co.nz +1 201 477-1448 (USA) +64 3 339-2825 (NZ) *One Platform for Location Intelligence* — This e-mail, and any attachments, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. It is the property of Telogis. If you are not the intended recipient of this e-mail, you are hereby notified that any dissemination, distribution or copying of this e-mail, any attachments thereto, and use of the information contained, is strictly prohibited. If you have received this e-mail in error, please notify the sender and permanently delete the original and any copy there of.
