Re: [GENERAL] problem with pg_standby
thanks Greg, yes, we want SELinux in enforcing mode. Thereby (and to ensure persistence) just chcon is the wrong way and * semanage fcontext -a -t postgresql_t '' * restorecon -vvFR is much better ;-) regards--GERD-- On Wednesday, August 04, 2010 07:56:56 am Greg Smith wrote: > Gerd Koenig wrote: > > thanks for the hint, yes, SELinux caused the troubles. It complained > > about wrong filecontext while starting postgres via init-script. > > > > Filecontext was: var_lib_t and it should be: postgresql_t > > If you want to keep SELinux on, basically you have to relabel the > directory you are putting those into so it can access them. You might > find some useful hints on that topic at > http://serverfault.com/questions/32333/how-does-selinux-affect-the-home-dir > ectory (including the observation that /home is a bad place for them). You > might think you can just run the chcon command to reset the labels, but it > doesn't quite work like that; you have to change the policy and then use > restorecon to correct them. -- 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] problem with pg_standby
Gerd Koenig wrote: thanks for the hint, yes, SELinux caused the troubles. It complained about wrong filecontext while starting postgres via init-script. Filecontext was: var_lib_t and it should be: postgresql_t If you want to keep SELinux on, basically you have to relabel the directory you are putting those into so it can access them. You might find some useful hints on that topic at http://serverfault.com/questions/32333/how-does-selinux-affect-the-home-directory (including the observation that /home is a bad place for them). You might think you can just run the chcon command to reset the labels, but it doesn't quite work like that; you have to change the policy and then use restorecon to correct them. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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 8.4 won't start on Windows Server 2008 64-bit
On 04/08/10 13:22, Tom Lane wrote: > Craig Ringer writes: >> On 03/08/10 23:37, David R Robison wrote: >>> 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, >>> .\src\backend\postmaster\postmaster.c:743 >>> 2010-08-03 15:34:01 GMT DEBUG: 0: TZ "US/Eastern" matches Windows >>> timezone "Eastern Daylight Time" >>> 2010-08-03 15:34:01 GMT LOCATION: identify_system_timezone, >>> .\src\timezone\pgtz.c:1088 >>> 2010-08-03 11:34:01 EDT DEBUG: 0: invoking >>> IpcMemoryCreate(size=37044224) >>> 2010-08-03 11:34:01 EDT LOCATION: CreateSharedMemoryAndSemaphores, >>> .\src\backend\storage\ipc\ipci.c:130 >>> 2010-08-03 11:34:01 EDT DEBUG: 0: max_safe_fds = 987, usable_fds = >>> 1000, already_open = 3 >>> 2010-08-03 11:34:01 EDT LOCATION: set_max_safe_fds, >>> .\src\backend\storage\file\fd.c:479 >>> 2010-08-03 11:34:02 EDT DEBUG: 0: logger shutting down >>> 2010-08-03 11:34:02 EDT LOCATION: SysLoggerMain, >>> .\src\backend\postmaster\syslogger.c:446 > >> This is pretty odd. It seems to init shared memory OK, set up the fd >> limits, then exit. > > Actually, the logger is launched after those two steps, so the fact that > we see anything at all from the logger is interesting. What it looks > like to me is that the postmaster crashed at some point after launching > syslogger. There is (not supposed to be) any exit path that wouldn't > have logged a complaint message, ergo it was a crash not intentional > exit. But just where it crashed is hard to tell from this. On Windows, there should've been an error from the runtime, like "This application has requested the Runtime to terminate it in an unusual way" ... I'm uncertain if such an error would get logged to the postgresql logs if the *postmaster* crashes, though. I've seen such crashes reported in Pg logs before, but they may have been from crashing backends rather than the postmaster its self. One would expect to see something in the pg logs or the service manager / event log history though... If the postmaster is crashing in startup, a different approach will be required to debug it. This article may be helpful in explaining how to set things up if the OP wants to try their hand at hooking up a debugger. http://www.debuginfo.com/articles/debugstartup.html The discussion of using Visual Studio's JIT debugger probably won't help though, as Visual Studio Express (the free version) doesn't, AFAIK, include the JIT debugger. You'd have to use windbg from Debugging Tools for Windows (ugh). And anyway, the postgres account isn't privelged enough to launch the JIT debugger. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.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] Question about Idle in TX
On 04/08/10 03:17, John R Pierce wrote: > On 08/03/10 12:13 PM, David Kerr wrote: >> I know that Idle in Transactions are a problem, however I'm trying to >> assess how much of a problem. >> >> for example: If a java program connects to the DB and does "begin;" >> and then internally does a "sleep 6 days" >> >> Does that cauz any issues other than eating a connection to the database? >> >> (note, nothing i have does this, i'm just trying to understand) >> >> I know that "Idle in TXs" can interfere with Vaccums for example, but >> I'm not sure if that's due to them usually having some form of lock on a >> table. > > no dead tuples created after the oldest active transaction (including > said ) can be vacuumed, from anywhere in the database. Is that still true for READ COMMITTED transactions? Because it need not be. I seem to remember a previous discussion in which it emerged that as of 8.3 or 8.4 Pg is smart enough to realize that an open READ COMMITTED transaction can't ever refer to tuples from snapshots older than the currently running statement (if any), so it shouldn't impede vacuum. I can't seem to find any references for that, though. For that matter, a SERIALIZABLE transaction only acquires its snapshot on the first _real_ command (SELECT, etc) so it shouldn't impede VACUUM if it's just issued a BEGIN and a few SETs. However, I'm not totally sure it *doesn't* impede vacuum, it just doesn't have to. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.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] PG 8.4 won't start on Windows Server 2008 64-bit
Craig Ringer writes: > On 03/08/10 23:37, David R Robison wrote: >> 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, >> .\src\backend\postmaster\postmaster.c:743 >> 2010-08-03 15:34:01 GMT DEBUG: 0: TZ "US/Eastern" matches Windows >> timezone "Eastern Daylight Time" >> 2010-08-03 15:34:01 GMT LOCATION: identify_system_timezone, >> .\src\timezone\pgtz.c:1088 >> 2010-08-03 11:34:01 EDT DEBUG: 0: invoking >> IpcMemoryCreate(size=37044224) >> 2010-08-03 11:34:01 EDT LOCATION: CreateSharedMemoryAndSemaphores, >> .\src\backend\storage\ipc\ipci.c:130 >> 2010-08-03 11:34:01 EDT DEBUG: 0: max_safe_fds = 987, usable_fds = >> 1000, already_open = 3 >> 2010-08-03 11:34:01 EDT LOCATION: set_max_safe_fds, >> .\src\backend\storage\file\fd.c:479 >> 2010-08-03 11:34:02 EDT DEBUG: 0: logger shutting down >> 2010-08-03 11:34:02 EDT LOCATION: SysLoggerMain, >> .\src\backend\postmaster\syslogger.c:446 > This is pretty odd. It seems to init shared memory OK, set up the fd > limits, then exit. Actually, the logger is launched after those two steps, so the fact that we see anything at all from the logger is interesting. What it looks like to me is that the postmaster crashed at some point after launching syslogger. There is (not supposed to be) any exit path that wouldn't have logged a complaint message, ergo it was a crash not intentional exit. But just where it crashed is hard to tell from this. 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] problem with pg_standby
Hello Greg, thanks for the hint, yes, SELinux caused the troubles. It complained about wrong filecontext while starting postgres via init-script. Filecontext was: var_lib_t and it should be: postgresql_t regards...GERD On Tuesday, August 03, 2010 11:54:45 pm Greg Smith wrote: > Gerd Koenig wrote: > > Since even the init-script starts pg as user postgres I have no idea what > > differs from init-script to direct call of pg_ctl as user postgres...?!?! > > Do you have SELinux turned on? That can do weird stuff like this--the > init script will be running with restrictions the manual pg_ctl launch > will not have. Should be information in /var/log/ somewhere if that's > the case. -- 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 8.4 won't start on Windows Server 2008 64-bit
On 03/08/10 23:37, David R Robison wrote: > > 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, > .\src\backend\postmaster\postmaster.c:743 > 2010-08-03 15:34:01 GMT DEBUG: 0: TZ "US/Eastern" matches Windows > timezone "Eastern Daylight Time" > 2010-08-03 15:34:01 GMT LOCATION: identify_system_timezone, > .\src\timezone\pgtz.c:1088 > 2010-08-03 11:34:01 EDT DEBUG: 0: invoking > IpcMemoryCreate(size=37044224) > 2010-08-03 11:34:01 EDT LOCATION: CreateSharedMemoryAndSemaphores, > .\src\backend\storage\ipc\ipci.c:130 > 2010-08-03 11:34:01 EDT DEBUG: 0: max_safe_fds = 987, usable_fds = > 1000, already_open = 3 > 2010-08-03 11:34:01 EDT LOCATION: set_max_safe_fds, > .\src\backend\storage\file\fd.c:479 > 2010-08-03 11:34:02 EDT DEBUG: 0: logger shutting down > 2010-08-03 11:34:02 EDT LOCATION: SysLoggerMain, > .\src\backend\postmaster\syslogger.c:446 This is pretty odd. It seems to init shared memory OK, set up the fd limits, then exit. What's your log level set to in postgresql.conf ? If "log_min_messages" isn't set to "debug5", set it to debug5 and try again. log_min_messages = debug5 It'd also be helpful to set log_line_prefix: log_line_prefix = '%t %p ' ... then try to start the service again. If you're game, you can potentially run PostgreSQL from Visual Studio after configuring your symbol path and installing the source code, so you can set breakpoints and step through startup. This will help you isolate where things go wrong, but will require some knowledge of C programming to be practical given the difficulty of doing any kind of remote support. If you're interested in giving this a try, see: http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows ... which covers setup of the symbol path and Visual Studio. I haven't written anything for debugging PostgreSQL startup problems there, but it should be pretty simple to get Visual Studio to fire up a postgresql backend in single-user mode: http://www.postgresql.org/docs/current/static/app-postgres.html postgres.exe --single -D "\path\to\data\dir" ...other args... ... though on Windows the need to run postgres.exe as the "postgres" user will complicate things. I'm not too sure if Visual Studio will debug a process invoked via runas.exe. I don't have a Windows machine with Visual Studio to hand so I can't test this right now, but you might want to have a play and see if you have any luck. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.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] "package org.postgresql.util does not exist" compilation problem
On 03/08/10 23:18, Santiago Álvarez Martínez wrote: > > Hi: > > I'm developing a Java application, using Maven, Spring and Hibernate, > and Postgre (with Postgis) as DBMS. > > Everything went OK, until I had to import the org.postgresql.util > package, to use the PGobject class, in a UserType Hibernate class. > > I got the following errors: > > [...] > [loading org/postgis/Geometry.class(org/postgis:Geometry.class)] > [loading > org/postgis/binary/BinaryParser.class(org/postgis/binary:BinaryParser.class)] > [loading > org/postgis/binary/BinaryWriter.class(org/postgis/binary:BinaryWriter.class)] > /home/perseo/PFC/maven.1264475335794/src/main/java/es/udc/fiestas/model/util/types/PointType.java:15: > package org.postgresql.util does not exist I can import and use org.postgresql.util.PGobject in a Maven project with the same version of PgJDBC. See example project: http://www.postnewspapers.com.au/~craig/pgdemo.zip Is it possible there's an elderly version of the PostgreSQL JDBC driver on the classpath that lacks the org.postgresql.util package? -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.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] Danger of idiomatic plpgsql loop for merging data
Hi fellow PostgreSQL hackers, First, a thank you to Merlin for commenting on my earlier post! I've run into another dangerous problem since the earlier post. I began converting from the plpgsql loop idiom for merging data into a COALESCE(find(), create(), find()) idiom and ran into a problem with the latter. I'll hold the full code to the end of this post and summarize the situation first. I'm using this idiom in 48 get_ functions associated with 48 different tables! In all cases, the functions should be idempotent with monotonic side effects, i.e. when the data desired is not present (the first time they are called), they create the data; in all cases, they return a reference (primary key value) to the data. Here's the new problem: Depending on the idiom I use, when I nest a call to one of these get_ functions as an argument to another function, the outer function does not see the new data! To be more specific, in the call: SELECT access_foo( get_foo(foo_data) ) where access_foo expects a reference to a row of TABLE foos and get_foo returns such a reference, possibly creating the desired row, when I write get_foo() with the plpgsql loop idiom, it seems to always return a reference which access_foo can use immediately. On the other hand, when I use the COALESCE(find(), create(), find()) idiom and the get_foo() function created a new row, access_foo fails to find it! In all cases saying: SELECT get_foo(foo_data); SELECT access_foo( get_foo(foo_data) ); works fine since if the data needed to be added, it was done in the separate earlier transaction. Because of this problem, I'm abandoning my original preference for the COALESCE(find(), create(), find()) idiom and I'm adding a check to the plpgsql LOOP idiom to prevent it going infinite. For those who'd like to see the gory details, here is the code, simplified as much as possible (and with a suffix on the type name to please Merlin:): -- (0) The table in question: -- The trailing underscores can be read as "field" or "slot" -- which is sometimes useful to avoid clashes with reserved -- words, local variables, etc. CREATE TABLE foos ( ref_ foo_reftype PRIMARY KEY DEFAULT next_foo_ref(); name_ text UNIQUE NOT NULL; ); -- (1) The idiom from the PostgreSQL reference manual, which -- unfortunately can go into an infinite loop if a trigger -- should raise a unique_violation exception. -- The underscore prefix can be read as "local" -- and is sometimes useful to avoid name clashes, etc. CREATE OR REPLACE FUNCTION get_foo(text) RETURNS foo_reftype AS $$ DECLARE _ref foo_reftype; BEGIN LOOP SELECT ref_ INTO _ref FROM foos WHERE name_ = $1; IF FOUND THEN RETURN _ref; END IF; BEGIN INSERT INTO foos(name_) VALUES($1); EXCEPTION WHEN unique_violation THEN -- maybe another thread? END; END LOOP; END; $$ LANGUAGE plpgsql STRICT; -- (2) Where I was originally going -- to avoid the infinite loop problem, -- and also hoping to get better performance -- on the most common case where the -- first call to old_foo() finds the row -- (since SQL functions are inlined into -- the execution plan): CREATE OR REPLACE FUNCTION old_foo(text) RETURNS foo_reftype AS $$ SELECT ref_ FROM foos WHERE name_ = $1 $$ LANGUAGE SQL STRICT; CREATE OR REPLACE FUNCTION new_foo(text) RETURNS foo_reftype AS $$ DECLARE this regprocedure := 'new_foo(text)'; _ref foo_reftype; BEGIN INSERT INTO foos(name_) VALUES ($1) RETURNING ref_ INTO _ref; RETURN _ref; EXCEPTION WHEN unique_violation THEN -- maybe another thread? RAISE NOTICE '% "%" unique_violation', this, $1; RETURN NULL; END; $$ LANGUAGE plpgsql STRICT; CREATE OR REPLACE FUNCTION get_foo(text) RETURNS foo_reftype AS $$ SELECT COALESCE( old_foo($1), new_foo($1), old_foo($1) ) $$ LANGUAGE sql STRICT; -- (3) Where I'm going now, although it feels -- like a patch (I hate :-( patches!): CREATE OR REPLACE FUNCTION get_foo(text) RETURNS foo_reftype AS $$ DECLARE _ref foo_reftype; killroy_was_here boolean := false; this regprocedure := 'get_foo(text)'; BEGIN LOOP SELECT ref_ INTO _ref FROM foos WHERE name_ = $1; IF FOUND THEN RETURN _foo; END IF; IF killroy_was_here THEN RAISE EXCEPTION '% "%" loops!', this, $1; END IF; killroy_was_here := true; BEGIN INSERT INTO foos(name_) VALUES($1); EXCEPTION WHEN unique_violation THEN -- another thread? RAISE NOTICE '% "%" unique_violation', this, $1; END; END LOOP; END; $$ LANGUAGE plpgsql; -- end code _Greg P.S. Sorry about the excessive underscores! I've been burned by having local variables confused with fields, so when I need to clearly differentiate such things, I use this convention. J. Greg Davidson -- 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] When can postgresql use a partial (NOT NULL) index? Seems to depend on size of IN clause (even with enable seqscan = off)
Adding the is not null clause does allow the query to use the index again
(and is a much cleaner workaround in that I don't have to change the indexes
or rely on any magic number for splitting the in clauses). Also makes sense
since it more exactly matches the partial indexing condition.
Thanks Tom!
Tim
=> SELECT * FROM scm WHERE ((bid in
(171082,1558141,1261493,1558137,1558166,1622957,1261535,1558191,1885437,2025548,1558144,1261485,1261536,1261539,1261541,167964,1558183,1789348,190512,1558150,196731,1261533,2056107,1875527,1177541,1535923,1558184,1558155,1261538,1261537,1558140,1159311,1558188,1558185,1261529,1558158,121460,1558517,190515,1558194,1558143,1558153,1261484,1261542,1558156,1201225,1261481,1558157,1891458,1200735,1285621,1702779,1558135,1261540,1579615,1558189,1558154,2053227,1261531,1261488,1558139,1261527,1558192,1261530,1261528,1159310,1558136,1558138,1558164,1261543,115605,2053214,1558187,1183258,1184576,1558145,1558159,1208646,1622955,1558161,1558160,1208046,160938,167963,167965,1261487,1828875,1541699,1261491,1210589,1558162,1558151,1558152,1558163,1181201,1186001,1197776,1200734,1208043,1208044,1208045,1261486,1558142,1558146,196733,191036,1190958,1261532,1178300,1544212,115606,1637671,1261482,1261489,1261483,1875455,115596,1558165,1000152148,1000152147,1000152146,1000141594,1000141133,1000172483,1000191484,1000191485,1000196236,1000236337,1000241756,1000242921,1000256842,1000257993,1000270323,1000272820,1000281535,1000297033,1000297039,1000297446,1000301868,1000307196,1000316101,1000331822,1000334293,1000342550,1000352078,1000367699,1000372920,1000373959,1000383317,1000400498,1000405863,1000412281,1000420780,1000430861)))
AND bid IS NOT NULL ORDER BY m DESC LIMIT 100 OFFSET 0;
Limit (cost=80925.25..80925.50 rows=100 width=229)
-> Sort (cost=80925.25..80979.66 rows=21765 width=229)
Sort Key: m
-> Bitmap Heap Scan on scm (cost=825.19..80093.41 rows=21765
width=229)
Recheck Cond: ((bid = ANY
('{171082,1558141,1261493,1558137,1558166,1622957,1261535,1558191,1885437,2025548,1558144,1261485,1261536,1261539,1261541,167964,1558183,1789348,190512,1558150,196731,1261533,2056107,1875527,117541,1535923,1558184,1558155,1261538,1261537,1558140,1159311,1558188,1558185,1261529,1558158,121460,1558517,190515,1558194,1558143,1558153,1261484,1261542,1558156,1201225,1261481,1558157,1891458,1200735,1285621,1702779,1558135,1261540,1579615,155889,1558154,2053227,1261531,1261488,1558139,1261527,1558192,1261530,1261528,1159310,1558136,1558138,1558164,1261543,115605,2053214,1558187,1183258,1184576,1558145,1558159,1208646,1622955,1558161,1558160,1208046,160938,167963,167965,1261487,828875,1541699,1261491,1210589,1558162,1558151,1558152,1558163,1181201,1186001,1197776,1200734,1208043,1208044,1208045,1261486,1558142,1558146,196733,191036,1190958,1261532,1178300,1544212,115606,1637671,1261482,1261489,1261483,1875455,11596,1558165,1000152148,1000152147,1000152146,1000141594,1000141133,1000172483,1000191484,1000191485,1000196236,1000236337,1000241756,1000242921,1000256842,1000257993,1000270323,1000272820,1000281535,1000297033,1000297039,1000297446,1000301868,1000307196,100316101,1000331822,1000334293,1000342550,1000352078,1000367699,1000372920,1000373959,1000383317,1000400498,1000405863,1000412281,1000420780,1000430861}'::integer[]))
AND (bid IS NOT NULL))
-> Bitmap Index Scan on index_scm_on_bid (cost=0.00..819.75
rows=21765 width=0)
Index Cond: (bid = ANY
('{171082,1558141,1261493,1558137,1558166,1622957,1261535,1558191,1885437,2025548,1558144,1261485,1261536,1261539,1261541,167964,1558183,1789348,190512,1558150,196731,1261533,2056107,18755271177541,1535923,1558184,1558155,1261538,1261537,1558140,1159311,1558188,1558185,1261529,1558158,121460,1558517,190515,1558194,1558143,1558153,1261484,1261542,1558156,1201225,1261481,1558157,1891458,1200735,1285621,1702779,1558135,1261540,1579615,158189,1558154,2053227,1261531,1261488,1558139,1261527,1558192,1261530,1261528,1159310,1558136,1558138,1558164,1261543,115605,2053214,1558187,1183258,1184576,1558145,1558159,1208646,1622955,1558161,1558160,1208046,160938,167963,167965,126147,1828875,1541699,1261491,1210589,1558162,1558151,1558152,1558163,1181201,1186001,1197776,1200734,1208043,1208044,1208045,1261486,1558142,1558146,196733,191036,1190958,1261532,1178300,1544212,115606,1637671,1261482,1261489,1261483,1875455,100015596,1558165,1000152148,1000152147,1000152146,1000141594,1000141133,1000172483,1000191484,1000191485,1000196236,1000236337,1000241756,1000242921,1000256842,1000257993,1000270323,1000272820,1000281535,1000297033,1000297039,1000297446,1000301868,10003071961000316101,1000331822,1000334293,1000342550,1000352078,1000367699,1000372920,1000373959,1000383317,1000400498,1000405863,1000412281,1000420780,1000430861}'::integer[]))
Re: [GENERAL] problem with pg_standby
Gerd Koenig wrote: Since even the init-script starts pg as user postgres I have no idea what differs from init-script to direct call of pg_ctl as user postgres...?!?! Do you have SELinux turned on? That can do weird stuff like this--the init script will be running with restrictions the manual pg_ctl launch will not have. Should be information in /var/log/ somewhere if that's the case. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] problem with pg_standby
Hi again, I just want to drop you an additional note. After several attempts of debugging pg_standby is restoring the WAL files as expected, but I cannot explain why... First startup of postgres was with init-script provided by the rpm installation (/etc/init.d/postgresql start as user root). The pg processes have been started thereby as user postgres. An additional stop/start by init-script ended up in the same problem situation, where pg_standby obviously wasn't able to get the file 0001001E001D.0020.backup, even if it was there (and readable by user postgres ;-) ). Afterwards (3rd attempt) I started postgres not by init-script, but by pg_ctl as user postgres, and..hejheureka..pg_standby finds all needed files and restores them. Since even the init-script starts pg as user postgres I have no idea what differs from init-script to direct call of pg_ctl as user postgres...?!?! Somebody an explanation ? kind regardsGERD On Tuesday, August 03, 2010 10:37:12 pm Gerd Koenig wrote: > Hello, > > we currently setup a standby database with archive_command sending the WALs > from master to standby. > This works as expected, but the standby database doesn't restore the WALs > from the given directory in recovery.conf and I have no idea why... > > recovery.conf: > > restore_command = '/usr/bin/pg_standby -d -s 20 -t /tmp/pgsql.trigger.5432 > /var/lib/pgsql/wal_exchange %f %p %r 2>> > /var/lib/pgsql/data/pg_log/standby.log' > > The file standby.log contains: > > Trigger file: /tmp/pgsql.trigger.5432 > Waiting for WAL file: 0001.history > WAL file path : /var/lib/pgsql/wal_exchange/0001.history > Restoring to... : pg_xlog/RECOVERYHISTORY > Sleep interval : 20 seconds > Max wait interval : 0 forever > Command for restore : cp "/var/lib/pgsql/wal_exchange/0001.history" > "pg_xlog/RECOVERYHISTORY" > Keep archive history: and later > running restore :cp: cannot stat > `/var/lib/pgsql/wal_exchange/0001.history': No such file or directory > cp: cannot stat `/var/lib/pgsql/wal_exchange/0001.history': No such > file or directory > cp: cannot stat `/var/lib/pgsql/wal_exchange/0001.history': No such > file or directory > cp: cannot stat `/var/lib/pgsql/wal_exchange/0001.history': No such > file or directory > not restored: history file not found > > Trigger file: /tmp/pgsql.trigger.5432 > Waiting for WAL file: 0001001E001D.0020.backup > WAL file path : > /var/lib/pgsql/wal_exchange/0001001E001D.0020.backup > Restoring to... : pg_xlog/RECOVERYHISTORY > Sleep interval : 20 seconds > Max wait interval : 0 forever > Command for restore : cp > "/var/lib/pgsql/wal_exchange/0001001E001D.0020.backup" > "pg_xlog/RECOVERYHISTORY" > Keep archive history: and later > WAL file not present yet. Checking for trigger file... > WAL file not present yet. Checking for trigger file... > WAL file not present yet. Checking for trigger file... > WAL file not present yet. Checking for trigger file... > WAL file not present yet. Checking for trigger file... > --- > > And in the directory "/var/lib/pgsql/wal_exchange" I have dozens of WALs in > the meantime, but they didn't get restored, and in the logs I receive "WAL > file not present" > The file "pg_xlog/RECOVERYHISTORY" doesn't exist, btw..., and the file > xx0020.backup contains: > "" > START WAL LOCATION: 1E/1D20 (file 0001001E001D) > STOP WAL LOCATION: 1E/1F00 (file 0001001E001E) > CHECKPOINT LOCATION: 1E/1D20 > START TIME: 2010-08-03 21:11:23 CEST > LABEL: initial_backup > STOP TIME: 2010-08-03 21:44:06 CEST > "" > > I can stop and restart the standby db as often as I want ending up in a > working sync-mechanism, as long as the WALs being copied from master to > standby, right ? > > Any help appreciated, many thanks .GERD. -- 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] problem with pg_standby
On Tue, 2010-08-03 at 22:37 +0200, Gerd Koenig wrote: > Hello, > > we currently setup a standby database with archive_command sending the WALs > from master to standby. > This works as expected, but the standby database doesn't restore the WALs > from > the given directory in recovery.conf and I have no idea why... What are you using to manage this? I would strongly suggest Walmgr or PITRTools. They take a lot of the guess work out of all this stuff. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] When can postgresql use a partial (NOT NULL) index? Seems to depend on size of IN clause (even with enable seqscan = off)
On Tue, Aug 3, 2010 at 2:03 PM, Timothy Garnett wrote: > Hi all, > > I'm debugging a performance issue that looks like it might actually be an > issue/limitation/parameter/bug in the query planner, but since I couldn't > find anything authoritative on when exactly postgresql is able to use > partial not null indexes I'm not sure that that's the case and I was hopping > someone could give some clarity around that or point to an option I could > tweak that would change this behavior. Anyways the table in question (with > names changed) is below. I'm running postgres 8.4.1 8.4.1 has some pretty nasty bugs that have since been fixed, and some work was done on the planner as well since then. First upgrade and see if your problem goes away. -- 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] When can postgresql use a partial (NOT NULL) index? Seems to depend on size of IN clause (even with enable seqscan = off)
Timothy Garnett writes: > ... My first thought was that there was a problem with the > statistics/estimation in the planner, but using "set enable seq_scan=off;" > still does not use the index when there's over 100 bid's in the IN clause. > Breaking the IN clause into 2 < 100 element groups does however rescue the > use of the index and the fast performance as does creating a new non-partial > index on bid (i.e. an index "index_scm_on_bid2" btree (bid) WITH > (fillfactor=100) will be used with over 100 bid's). I think you're hitting the code that abandons attempts to prove constraints true when the expressions get too large (to avoid O(N^2) or worse behavior). Could you just add an explicit AND bid IS NOT NULL when you know none of the items in the IN clause will be null? 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] problem with pg_standby
Hello, we currently setup a standby database with archive_command sending the WALs from master to standby. This works as expected, but the standby database doesn't restore the WALs from the given directory in recovery.conf and I have no idea why... recovery.conf: restore_command = '/usr/bin/pg_standby -d -s 20 -t /tmp/pgsql.trigger.5432 /var/lib/pgsql/wal_exchange %f %p %r 2>> /var/lib/pgsql/data/pg_log/standby.log' The file standby.log contains: Trigger file: /tmp/pgsql.trigger.5432 Waiting for WAL file: 0001.history WAL file path : /var/lib/pgsql/wal_exchange/0001.history Restoring to... : pg_xlog/RECOVERYHISTORY Sleep interval : 20 seconds Max wait interval : 0 forever Command for restore : cp "/var/lib/pgsql/wal_exchange/0001.history" "pg_xlog/RECOVERYHISTORY" Keep archive history: and later running restore :cp: cannot stat `/var/lib/pgsql/wal_exchange/0001.history': No such file or directory cp: cannot stat `/var/lib/pgsql/wal_exchange/0001.history': No such file or directory cp: cannot stat `/var/lib/pgsql/wal_exchange/0001.history': No such file or directory cp: cannot stat `/var/lib/pgsql/wal_exchange/0001.history': No such file or directory not restored: history file not found Trigger file: /tmp/pgsql.trigger.5432 Waiting for WAL file: 0001001E001D.0020.backup WAL file path : /var/lib/pgsql/wal_exchange/0001001E001D.0020.backup Restoring to... : pg_xlog/RECOVERYHISTORY Sleep interval : 20 seconds Max wait interval : 0 forever Command for restore : cp "/var/lib/pgsql/wal_exchange/0001001E001D.0020.backup" "pg_xlog/RECOVERYHISTORY" Keep archive history: and later WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... --- And in the directory "/var/lib/pgsql/wal_exchange" I have dozens of WALs in the meantime, but they didn't get restored, and in the logs I receive "WAL file not present" The file "pg_xlog/RECOVERYHISTORY" doesn't exist, btw..., and the file xx0020.backup contains: "" START WAL LOCATION: 1E/1D20 (file 0001001E001D) STOP WAL LOCATION: 1E/1F00 (file 0001001E001E) CHECKPOINT LOCATION: 1E/1D20 START TIME: 2010-08-03 21:11:23 CEST LABEL: initial_backup STOP TIME: 2010-08-03 21:44:06 CEST "" I can stop and restart the standby db as often as I want ending up in a working sync-mechanism, as long as the WALs being copied from master to standby, right ? Any help appreciated, many thanks .GERD. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] When can postgresql use a partial (NOT NULL) index? Seems to depend on size of IN clause (even with enable seqscan = off)
Hi all,
I'm debugging a performance issue that looks like it might actually be an
issue/limitation/parameter/bug in the query planner, but since I couldn't
find anything authoritative on when exactly postgresql is able to use
partial not null indexes I'm not sure that that's the case and I was hopping
someone could give some clarity around that or point to an option I could
tweak that would change this behavior. Anyways the table in question (with
names changed) is below. I'm running postgres 8.4.1
\d
Column | Type | Modifiers
--+--+---
id | integer | not null
sid | integer |
bid | integer |
m| date | not null
k| integer |
cc | text |
f| integer |
d| smallint |
u| smallint |
f2 | integer |
cm | text |
Indexes:
"scm_pkey" PRIMARY KEY, btree (id) WITH (fillfactor=100)
"index_scm_on_bid" btree (bid) WITH (fillfactor=100) WHERE bid IS NOT
NULL
~35 million rows (about 15 million of which have null bid). There are about
1 million distinct bids (with selectivity ranging from 1 to ~100,000 rows).
The end user selects an arbitrary number of bid's then we run several
queries one of which I include explain analyze output below. For <= 100
bids the planner uses the index and completes in ~35ms, for 101+ bid's the
planner uses a sequence scan and completes in ~45 seconds (3 orders of
magnitude slower). My first thought was that there was a problem with the
statistics/estimation in the planner, but using "set enable seq_scan=off;"
still does not use the index when there's over 100 bid's in the IN clause.
Breaking the IN clause into 2 < 100 element groups does however rescue the
use of the index and the fast performance as does creating a new non-partial
index on bid (i.e. an index "index_scm_on_bid2" btree (bid) WITH
(fillfactor=100) will be used with over 100 bid's). My best guess is that
this is do to some limit on the number of in clause elements the query
planner will check to see if match a partial index before giving up and
assuming it doesn't (if there is such a limit I'd definitely like to make it
larger, at least for this big of a table...). Is this 100 limit documented
anywhere?
Anyways my workarounds are to either split the IN clause into multiple < 100
element groups or switch the index to be across all values rather then not
null. The reason I tend to use not null partial indexes is that I have
another similarly sized table with 20 separately indexed columns each of
which only has about 1 non-null rows and the disk space savings from the
not null partial index there are huge.
# <= 100 bids
=>explain analyze SELECT * FROM "scm" WHERE ((bid in
(171082,1558141,1261493,1558137,1558166,1622957,1261535,1558191,1885437,2025548,1558144,1261485,1261536,1261539,1261541,167964,1558183,1789348,190512,1558150,196731,1261533,2056107,1875527,1177541,1535923,1558184,1558155,1261538,1261537,1558140,1159311,1558188,1558185,1261529,1558158,121460,1558517,190515,1558194,1558143,1558153,1261484,1261542,1558156,1201225,1261481,1558157,1891458,1200735,1285621,1702779,1558135,1261540,1579615,1558189,1558154,2053227,1261531,1261488,1558139,1261527,1558192,1261530,1261528,1159310,1558136,1558138,1558164,1261543,115605,2053214,1558187,1183258,1184576,1558145,1558159,1208646,1622955,1558161,1558160,1208046,160938,167963,167965,1261487,1828875,1541699,1261491,1210589,1558162,1558151,1558152,1558163,1181201,1186001,1197776,1200734,1208043,1208044)))
order by m desc limit 100;
Limit (cost=66518.18..66518.43 rows=100 width=229) (actual
time=24.665..24.821 rows=100 loops=1)
-> Sort (cost=66518.18..66563.14 rows=17987 width=229) (actual
time=24.658..24.698 rows=100 loops=1)
Sort Key: m
Sort Method: top-N heapsort Memory: 85kB
-> Bitmap Heap Scan on scm (cost=566.83..65830.73 rows=17987
width=229) (actual time=1.863..12.850 rows=16430 loops=1)
Recheck Cond: (bid = ANY
('{171082,1558141,1261493,1558137,1558166,1622957,1261535,1558191,1885437,2025548,1558144,1261485,1261536,1261539,1261541,167964,1558183,1789348,190512,1558150,196731,1261533,2056107,1875527,1177541,1535923,1558184,1558155,1261538,1261537,1558140,1159311,1558188,1558185,1261529,1558158,121460,1558517,190515,1558194,1558143,1558153,1261484,1261542,1558156,1201225,1261481,1558157,1891458,1200735,1285621,1702779,1558135,1261540,1579615,1558189,1558154,2053227,1261531,1261488,1558139,1261527,1558192,1261530,1261528,1159310,1558136,1558138,1558164,1261543,115605,2053214,1558187,1183258,1184576,1558145,1558159,1208646,1622955,1558161,1558160,1208046,160938,167963,167965,1261487,1828875,1541699
Re: [GENERAL] optimal memory
On Tue, Aug 3, 2010 at 6:27 AM, Sim Zacks wrote: > >> So, about how big is your db? How many users are likely to be running >> queries at once? How big of a chunk of data are those users likely to >> each need for sorts etc? >> > The database is 400MB (using du on the base folder), I have 10 active > users who run queries and functions that generally take less then 1 > second each to run. Then a couple gigabytes should be enough to run it smoothly, assuming that those queries aren't doing things that need 100s of megabytes for sorting for each query. I'd start with a 2 or 3Gig VM, give 400 or so to shared_buffers, and set work_mem to something like 8 or 16Meg and see how it runs. -- 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] Question about Idle in TX
On Tue, Aug 03, 2010 at 03:57:27PM -0400, Tom Lane wrote: - David Kerr writes: - > On Tue, Aug 03, 2010 at 03:49:57PM -0400, Tom Lane wrote: - > - In recent versions of PG, no. Before about 8.3 it was a Really Bad Idea, - > - because the open transaction would prevent VACUUM from reclaiming storage. - - > We're on 8.3.9, so hopefully it's fairly safe then? - - Should be. You might want to test it just to make sure I'm recalling - correctly when that got fixed. Do a BEGIN in one session, then in - another session insert and delete some rows in a table, then VACUUM - VERBOSE and see if they get cleaned up. - - regards, tom lane - Ah yeah, good idea. I'll give it a shot. thanks! 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] Question about Idle in TX
David Kerr writes: > On Tue, Aug 03, 2010 at 03:49:57PM -0400, Tom Lane wrote: > - In recent versions of PG, no. Before about 8.3 it was a Really Bad Idea, > - because the open transaction would prevent VACUUM from reclaiming storage. > We're on 8.3.9, so hopefully it's fairly safe then? Should be. You might want to test it just to make sure I'm recalling correctly when that got fixed. Do a BEGIN in one session, then in another session insert and delete some rows in a table, then VACUUM VERBOSE and see if they get cleaned up. 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] Question about Idle in TX
On Tue, Aug 03, 2010 at 03:49:57PM -0400, Tom Lane wrote: - David Kerr writes: - > for example: If a java program connects to the DB and does "begin;" - > and then internally does a "sleep 6 days" - - > Does that cauz any issues other than eating a connection to the database? - - In recent versions of PG, no. Before about 8.3 it was a Really Bad Idea, - because the open transaction would prevent VACUUM from reclaiming storage. - - It's *still* a Really Bad Idea to begin a transaction, do something, - and then sleep 6 days. But "BEGIN" without any following commands - has been fixed to be harmless, mainly because there are so many - badly designed clients that do exactly that ... - - regards, tom lane - ah ok, I think i'm in that group. We're using Talend and i think it might be doing that, and it might be a bit of an effort to stop that from happening. We're on 8.3.9, so hopefully it's fairly safe then? Thanks 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] Finding the primary key of tables
Thanks a million. Rusty SQL :P 2010/8/3 Merlin Moncure > 2010/8/3 George Silva : > > I'm going for Merlin's solution. Its the easiest one :P > > > > But I'm also having a problem: > > > > SELECT column_name FROM information_schema.key_column_usage k > > LEFT OUTER JOIN information_schema.table_constraints ON (k.table_name > = > > table_constraints.table_name) > > WHERE > > table_constraints.constraint_type = 'PRIMARY KEY' > > AND k.table_name = 'acidentes' > > AND k.table_schema = 'public' > > > > this still returns me multiple columns. Did I forgot something? > > yup -- you are supposed be matching on constraint_name, not just > table_name. try: > SELECT column_name FROM information_schema.key_column_usage k > LEFT OUTER JOIN information_schema.table_constraints USING > (table_schema, table_name, constraint_name) > WHERE > table_constraints.constraint_type = 'PRIMARY KEY' > AND k.table_name = 'acidentes' > AND k.table_schema = 'public' > > > merlin > -- George R. C. Silva Desenvolvimento em GIS http://blog.geoprocessamento.net
Re: [GENERAL] Question about Idle in TX
David Kerr writes: > for example: If a java program connects to the DB and does "begin;" > and then internally does a "sleep 6 days" > Does that cauz any issues other than eating a connection to the database? In recent versions of PG, no. Before about 8.3 it was a Really Bad Idea, because the open transaction would prevent VACUUM from reclaiming storage. It's *still* a Really Bad Idea to begin a transaction, do something, and then sleep 6 days. But "BEGIN" without any following commands has been fixed to be harmless, mainly because there are so many badly designed clients that do exactly that ... 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] Finding the primary key of tables
2010/8/3 George Silva : > I'm going for Merlin's solution. Its the easiest one :P > > But I'm also having a problem: > > SELECT column_name FROM information_schema.key_column_usage k > LEFT OUTER JOIN information_schema.table_constraints ON (k.table_name = > table_constraints.table_name) > WHERE > table_constraints.constraint_type = 'PRIMARY KEY' > AND k.table_name = 'acidentes' > AND k.table_schema = 'public' > > this still returns me multiple columns. Did I forgot something? yup -- you are supposed be matching on constraint_name, not just table_name. try: SELECT column_name FROM information_schema.key_column_usage k LEFT OUTER JOIN information_schema.table_constraints USING (table_schema, table_name, constraint_name) WHERE table_constraints.constraint_type = 'PRIMARY KEY' AND k.table_name = 'acidentes' AND k.table_schema = 'public' merlin -- 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] Finding the primary key of tables
I'm going for Merlin's solution. Its the easiest one :P But I'm also having a problem: SELECT column_name FROM information_schema.key_column_usage k LEFT OUTER JOIN information_schema.table_constraints ON (k.table_name = table_constraints.table_name) WHERE table_constraints.constraint_type = 'PRIMARY KEY' AND k.table_name = 'acidentes' AND k.table_schema = 'public' this still returns me multiple columns. Did I forgot something? 2010/8/3 Devrim GÜNDÜZ > On Tue, 2010-08-03 at 16:13 -0300, George Silva wrote: > > I'm building a function which needs to know what is the primary key of > > a > > certain table (all in pgplsql). > > > > I was using select * from information_schema.key_column_usage where > > table_schema='foo' and table_name = 'aaa'; but that will give me > > multiple > > results in case of additional keys in the table. > > > > Any suggestions? > > See pg_index.indisprimary column. If it is true, then the it is the PK > of given table. > > Regards, > -- > Devrim GÜNDÜZ > PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer > PostgreSQL RPM Repository: http://yum.pgrpms.org > Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr > http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz > -- George R. C. Silva Desenvolvimento em GIS http://blog.geoprocessamento.net
Re: [GENERAL] Finding the primary key of tables
On Tue, 2010-08-03 at 16:13 -0300, George Silva wrote: > I'm building a function which needs to know what is the primary key of > a > certain table (all in pgplsql). > > I was using select * from information_schema.key_column_usage where > table_schema='foo' and table_name = 'aaa'; but that will give me > multiple > results in case of additional keys in the table. > > Any suggestions? See pg_index.indisprimary column. If it is true, then the it is the PK of given table. Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Question about Idle in TX
On Tue, Aug 03, 2010 at 03:30:46PM -0400, Greg Smith wrote: - David Kerr wrote: - >I know that "Idle in TXs" can interfere with Vaccums for example, but - >I'm not sure if that's due to them usually having some form of lock on a - >table. - > - - Locks aren't the issue. When you have a transaction open, the database - makes sure it can deliver a consistent view of the database for the Thanks guys! I thought that was the case but wanted to be 100% sure before i kicked up a fuss to my developers =) 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] Finding the primary key of tables
On Tue, Aug 3, 2010 at 3:13 PM, George Silva wrote: > Hello guys, > > I'm building a function which needs to know what is the primary key of a > certain table (all in pgplsql). > > I was using select * from information_schema.key_column_usage where > table_schema='foo' and table_name = 'aaa'; but that will give me multiple > results in case of additional keys in the table. > > Any suggestions? take a look at information_schema.table_constraint and match on constraint_name. merlin -- 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] Question about Idle in TX
David Kerr wrote: I know that "Idle in TXs" can interfere with Vaccums for example, but I'm not sure if that's due to them usually having some form of lock on a table. Locks aren't the issue. When you have a transaction open, the database makes sure it can deliver a consistent view of the database for the lifetime of that transaction, using MVCC: http://wiki.postgresql.org/wiki/MVCC What this means in practice is that VACUUM may stop cleaning up old data because your open transaction might still need to look at it. Table maintenance can grind to a halt when you have one of these long running transactions. Dead rows (ones left behind by DELETE or UPDATE) will stop being recycled, tables will grow, queries will slow down. If you're running 8.4 or later, there is a significant improvement to how pessimistic that gets in a typical case. To quote Alvaro, the author of that patch: "I expect to be able to remove dead rows created by transactions that are no longer in progress, but which started more recently than some currently-open long-running transaction." It's still something to be wary of. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] Finding the primary key of tables
On 08/03/10 12:13 PM, George Silva wrote: Hello guys, I'm building a function which needs to know what is the primary key of a certain table (all in pgplsql). I was using select * from information_schema.key_column_usage where table_schema='foo' and table_name = 'aaa'; but that will give me multiple results in case of additional keys in the table. the primary key index is called tablename_pkey ... afaik, this is the only thing that makes it special, otherwise, its just another index with a unique constraint. -- 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] Question about Idle in TX
On 08/03/10 12:13 PM, David Kerr wrote: I know that Idle in Transactions are a problem, however I'm trying to assess how much of a problem. for example: If a java program connects to the DB and does "begin;" and then internally does a "sleep 6 days" Does that cauz any issues other than eating a connection to the database? (note, nothing i have does this, i'm just trying to understand) I know that "Idle in TXs" can interfere with Vaccums for example, but I'm not sure if that's due to them usually having some form of lock on a table. no dead tuples created after the oldest active transaction (including said ) can be vacuumed, from anywhere in the database. so, nothing deleted/updated since that BEGIN; you describe will get vacuumed. this will likely lead to a high amount of database bloat if you have a lot of update transactions. and when you finally terminate that idle transaction vacuum wil have a LOT of work to do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Finding the primary key of tables
Hello guys, I'm building a function which needs to know what is the primary key of a certain table (all in pgplsql). I was using select * from information_schema.key_column_usage where table_schema='foo' and table_name = 'aaa'; but that will give me multiple results in case of additional keys in the table. Any suggestions? -- George R. C. Silva Desenvolvimento em GIS http://blog.geoprocessamento.net
[GENERAL] Question about Idle in TX
I know that Idle in Transactions are a problem, however I'm trying to assess how much of a problem. for example: If a java program connects to the DB and does "begin;" and then internally does a "sleep 6 days" Does that cauz any issues other than eating a connection to the database? (note, nothing i have does this, i'm just trying to understand) I know that "Idle in TXs" can interfere with Vaccums for example, but I'm not sure if that's due to them usually having some form of lock on a table. Thanks, 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] Nodes and trees...
On Tue, Aug 03, 2010 at 02:01:58PM +0200, Jason Schauberger wrote: > Dear fellow Postgres users, :-) > > please consider this table: > > CREATE TABLE nodes ( > > id int PRIMARY KEY, > > parent int REFERENCES nodes(id) > > ); Generally, you'll want to separate the nodes table from the edges table, as in: CREATE TABLE nodes (id INTEGER PRIMARY KEY); CREATE TABLE edges ( tail INTEGER NOT NULL REFERENCES nodes(id), head INTEGER NOT NULL REFERENCES nodes(id), PRIMARY KEY(tail, head), CHECK (tail <> head) ); Then you might want to prevent other kinds of issues (more uniqueness, "must be forest," etc.) with other constraints, but let's not go there for now. > In this table, each node *can* have a parent node. You can picture > the whole set of rows of this table as one or more trees with nodes > and the root of the tree is the node which has no parent node (that > is, parent is NULL). > > Now here's my objective: I want to *quickly* find all nodes that > have the same root node. Given a "root" node, i.e. one which appears only as a tail in the edges table, you'd do something like this: WITH descendants AS ( SELECT head FROM edges WHERE tail=1 /* the root node */ UNION SELECT e.head FROM edges e JOIN descendants d ON (e.tail = d.head) ) SELECT * FROM descendants; You might want to index edges.tail and edges.head. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Nodes and trees...
> -Original Message- > From: Jason Schauberger [mailto:crossroads0...@googlemail.com] > Sent: Tuesday, August 03, 2010 8:02 AM > To: pgsql-general@postgresql.org > Subject: Nodes and trees... > > Dear fellow Postgres users, :-) > > please consider this table: > > CREATE TABLE nodes ( > > id int PRIMARY KEY, > > parent int REFERENCES nodes(id) > > ); > > In this table, each node *can* have a parent node. You can > picture the whole set of rows of this table as one or more > trees with nodes and the root of the tree is the node which > has no parent node (that is, parent is NULL). > > Now here's my objective: I want to *quickly* find all nodes > that have the same root node. > > I could iterate through the complete tree in question > starting from the root node and going all the way through to > the terminal/leaf nodes (those without a child), but that > could take quite long depending on how large the tree is. > > So, what I could do is this: > > CREATE TABLE nodes ( > > id int PRIMARY KEY, > > parent int REFERENCES nodes(id), > > rootint NOT NULLREFERENCES nodes(id) > > ); > > and fill out the root column every time I insert a node. But > then there is the problem of anomalies, where the root column > could have an incorrect value (that is, the id of a node > which is actually NOT the root of the same tree). I guess I > could check for correctness using triggers. > > I also thought that using views might make adding a root > column to the table completely unnecessary and at the same > time allow for quickly finding nodes which have the same root. > > So, what's the best method to do this? It must be fast, it > must prevent anomalies, and must also be either SQL standard > or if it is not, at least it must be easily portable across > the most popular SQL databases. I also explicitly don't want > to create an extra tree ID or something like that, because it > only mitigates the problem of anomalies, but does not solve it. > > Thanks in advance, > > Jason. > Look up connectby() in tablefuncs contrib module. Regards, Igor Neyman -- 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] Dynamic data model, locks and performance
This example is certainly a workable situation. However it does require understanding the constraints of an ALTER TABLE statement and manually developing appropriate scripts. The update model offered my ChronicDB accounts for schema changes of considerable complexity, such as merging fields, partitioning, renaming, or moving fields from one table to another, as well as for reversing schema changes live if a bug is discovered in the application. On Thu, 2010-07-29 at 23:22 +0800, Craig Ringer wrote: > On 29/07/10 22:36, Pierre Thibault wrote: > > > Why so? This is something expected by a database used in a constant > > integration environment. Maybe I did not expressed myself very well. Users > > are not changing their models all the time. They create new models which > > mean create new tables and from time to time they add new fields to existing > > tables just like developers do when they update a schema for new application > > functionalities. > > Ah, OK. I read your initial mail as implying much more frequent changes, > especially combined with "millions" of tables. > > > In my last job, I was working with constant database integration. We were > > created DDL scripts to add new fields and tables live on a SQLSever database > > in production. Most scripts were executed during to night to reduce the > > impact on the db. In practice, this may means that a running query will have > > to wait maybe half a second to get a lock because of such update. Usually, > > not really more than that. Can I expect similar performance with > > Postgressql? > > With a few caveats, yes. > > The main one: For columns you want to be NOT NULL, you should add new > columns as nullable. Then UPDATE the new column to hold any desired > default, before issuing an > > ALTER TABLE ... ALTER COLUMN ... SET NOT NULL. > > That's because an ALTER TABLE to add a nullable column doesn't have to > rewrite the table. An ALTER TABLE to add a NOT NULL column has to > immediately rewrite the table to add the default to every record. This > is slow, and during this operation ALTER TABLE holds an exclusive lock. > > By contrast, if you ALTER TABLE to add a nullable column (brief > exclusive lock), UPDATE (long much lower-order lock that doesn't > conflict with SELECT, INSERT, or UPDATE to unaffected rows) and then > finally ALTER TABLE again to add the constraint (a further brief lock) > you have greatly reduced lock times. > > >> Really, I'd be researching dynamic schema databases, object stores, > >> key/value set stores, etc. Such things are outside my personal > >> experience, though, and I can't really advise you much on technologies. > >> Beware of the latest No-SQL fad platforms, though; you might land up > >> being better off with something older and more stable even if it's less > >> fashionable than CouchDB or whatever is currently "in" today. > >> > > Maybe, but, as I said, using a SQL database with the one table by class > > hierarchy strategy seems to be the way to go for me. I'll take a lot a these > > options too. > > Just beware of huge table counts. People have reported issues on the > list with truly huge numbers of tables. It's not something that turns up > in most regular relational designs, and there are a few things in Pg > (like, AFAIK, autovacuum's scanning of tables to vacuum) that scale > linearly with table counts. > > I'm sure it's workable, it just might not be ideal. > > -- > Craig Ringer > -- 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] Two problems when using Postgresql8.3.7, Please help me!
"Richard" writes: > 1.To add live HA to PG, I transfer WAL of a database instance(Primary node) > to another database instance (standby node) at real time, and keep startup > alive in standby node to recovery WAL online,so that standby node can be a > hot standby. > But I got some trouble. When standby node switch to primary mode to accept > connections, of course after startup initilize the WAL and exit, the postgres > process ereportERROR > when mdread function enter ERROR branch, and I got message like this "could > not read block X of relation X/X/X: read only Xof X bytes". I spent two days > to figure out what happened, but it is too hard.Please help me. I wonder whether your problem is related to this 8.3.8 bug fix: * Force WAL segment switch during pg_start_backup() (Heikki) This avoids corner cases that could render a base backup unusable. I'd suggest updating to 8.3.latest and making sure that you are taking the base backup according to the documented procedure. The symptoms you are mentioning could definitely be caused by starting from an inconsistent backup. regards, tom lane PS: Please do not post the same question to multiple mailing lists. -- 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] alter table set tablespace
Jeff, One way to address the indefinite locking due to an ALTER TABLE statement for PostgreSQL is to use ChronicDB. It allows you to apply such a schema change live, without bringing down the database. The space requirements for applying the live schema change would be to have at least twice as much space available on disk as the existing database. On Tue, 2010-07-27 at 14:19 -0700, Jeff Davis wrote: > On Tue, 2010-07-27 at 20:38 +, Leif Gunnar Erlandsen wrote: > > I want to move one table from one disk to another. > > > > In order to do this I wanted to create a new tablespace on the new disks and > > issue the command alter table tablename set tablespace tablespacename; > > > > The question is, will this in any way affect the database which is in > > heavily > > use? > > > > The table in question is the largest one approximately 90GB. > > Yes, it will have a performance impact. The table will be locked while > it's being moved to the new tablespace, preventing both reads and writes > until the operation is complete. > > Regards, > Jeff Davis > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Two problems when using Postgresql8.3.7, Please help me!
1.To add live HA to PG, I transfer WAL of a database instance(Primary node) to another database instance (standby node) at real time, and keep startup alive in standby node to recovery WAL online,so that standby node can be a hot standby. But I got some trouble. When standby node switch to primary mode to accept connections, of course after startup initilize the WAL and exit, the postgres process ereportERROR when mdread function enter ERROR branch, and I got message like this "could not read block X of relation X/X/X: read only Xof X bytes". I spent two days to figure out what happened, but it is too hard.Please help me. 2. When restore data from a LIVE backup , I got message like "unexpected pageaddr %X/%X in log file %u, segment %u, offset %u" "WAL ends before end time of backup dump". It seems the WAL was corrupted. I found the LSN where the error occured contained the wrong pageaddr, the pageaddr was 8K before it's real address.What was wrong? -- Richard 2010-08-04 -- 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 8.4 won't start on Windows Server 2008 64-bit
I uninstalled 8.4 and installed 8.3 with the same results. Any thoughts? David On 8/3/2010 11:37 AM, David R Robison wrote: I ran postgres manually and got some additional information. The full log is 2010-08-03 15:34:01 GMT DEBUG: 0: postgres: PostmasterMain: initial environ dump: 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:736 2010-08-03 15:34:01 GMT DEBUG: 0: - 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:738 2010-08-03 15:34:01 GMT DEBUG: 0: ALLUSERSPROFILE=C:\ProgramData 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: APPDATA=C:\Users\postgres\AppData\Roaming 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: CLIENTNAME=ORC-LPT-053 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: CommonProgramFiles=c:\Program Files (x86)\Common Files 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: CommonProgramFiles(x86)=c:\Program Files (x86)\Common Files 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: CommonProgramW6432=c:\Program Files\Common Files 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: COMPUTERNAME=TRAVELTIMEDEV 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: ComSpec=C:\Windows\system32\cmd.exe 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: FP_NO_HOST_CHECK=NO 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: HOMEDRIVE=C: 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: HOMEPATH=\Users\postgres 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: LOCALAPPDATA=C:\Users\postgres\AppData\Local 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: LOGONSERVER=\\TRAVELTIMEDEV 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: OS=Windows_NT 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: Path=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PROCESSOR_ARCHITECTURE=x86 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PROCESSOR_ARCHITEW6432=AMD64 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 26 Stepping 5, GenuineIntel 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PROCESSOR_LEVEL=6 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PROCESSOR_REVISION=1a05 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: ProgramData=C:\ProgramData 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: ProgramFiles=c:\Program Files (x86) 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: ProgramFiles(x86)=c:\Program Files (x86) 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: ProgramW6432=c:\Program Files 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PROMPT=$P$G 2010-08-03 15:34:01 GMT LOCATIO
Re: [GENERAL] libpq logging redirection
dev...@gmx-topmail.de writes: > ist there any possibility for libpq clients to redirect the messages that are > logged to stderr by libpq e.g. by registering a callback function to handle > error logging by oneself? http://www.postgresql.org/docs/8.4/static/libpq-notice-processing.html 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] libpq logging redirection
Hello Postgres Community, ist there any possibility for libpq clients to redirect the messages that are logged to stderr by libpq e.g. by registering a callback function to handle error logging by oneself? - Regards -- GMX DSL: Internet-, Telefon- und Handy-Flat ab 19,99 EUR/mtl. Bis zu 150 EUR Startguthaben inklusive! http://portal.gmx.net/de/go/dsl -- 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 8.4 won't start on Windows Server 2008 64-bit
I ran postgres manually and got some additional information. The full log is 2010-08-03 15:34:01 GMT DEBUG: 0: postgres: PostmasterMain: initial environ dump: 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:736 2010-08-03 15:34:01 GMT DEBUG: 0: - 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:738 2010-08-03 15:34:01 GMT DEBUG: 0: ALLUSERSPROFILE=C:\ProgramData 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: APPDATA=C:\Users\postgres\AppData\Roaming 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: CLIENTNAME=ORC-LPT-053 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: CommonProgramFiles=c:\Program Files (x86)\Common Files 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: CommonProgramFiles(x86)=c:\Program Files (x86)\Common Files 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: CommonProgramW6432=c:\Program Files\Common Files 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: COMPUTERNAME=TRAVELTIMEDEV 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: ComSpec=C:\Windows\system32\cmd.exe 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: FP_NO_HOST_CHECK=NO 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: HOMEDRIVE=C: 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: HOMEPATH=\Users\postgres 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: LOCALAPPDATA=C:\Users\postgres\AppData\Local 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: LOGONSERVER=\\TRAVELTIMEDEV 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: OS=Windows_NT 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: Path=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PROCESSOR_ARCHITECTURE=x86 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PROCESSOR_ARCHITEW6432=AMD64 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 26 Stepping 5, GenuineIntel 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PROCESSOR_LEVEL=6 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PROCESSOR_REVISION=1a05 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: ProgramData=C:\ProgramData 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: ProgramFiles=c:\Program Files (x86) 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: ProgramFiles(x86)=c:\Program Files (x86) 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: ProgramW6432=c:\Program Files 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PROMPT=$P$G 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:741 2010-08-03 15:34:01 GMT DEBUG: 0: PUBLIC=C:\Users\Publi
[GENERAL] PG 8.4 won't start on Windows Server 2008 64-bit
I installed PostgreSQL 8.4 using the one-click installer. However, the postgres-8.4 windows service will not start. I have checked the Windows Event Log but there are no entries except one saying that the service start timed out. I checked the pg_log directory and all that is logged is 2010-08-03 10:42:26 EDT DEBUG: 0: logger shutting down 2010-08-03 10:42:26 EDT LOCATION: SysLoggerMain, .\src\backend\postmaster\syslogger.c:446 2010-08-03 10:42:26 EDT DEBUG: 0: shmem_exit(0): 0 callbacks to make 2010-08-03 10:42:26 EDT LOCATION: shmem_exit, .\src\backend\storage\ipc\ipc.c:211 2010-08-03 10:42:26 EDT DEBUG: 0: proc_exit(0): 0 callbacks to make 2010-08-03 10:42:26 EDT LOCATION: proc_exit_prepare, .\src\backend\storage\ipc\ipc.c:183 2010-08-03 10:42:26 EDT DEBUG: 0: exit(0) 2010-08-03 10:42:26 EDT LOCATION: proc_exit, .\src\backend\storage\ipc\ipc.c:135 Any ideas how to get this to work? Thanks in advance, David -- David R Robison Open Roads Consulting, Inc. 103 Watson Road, Chesapeake, VA 23320 phone: (757) 546-3401 e-mail: drrobi...@openroadsconsulting.com web: http://openroadsconsulting.com blog: http://therobe.blogspot.com book: http://www.xulonpress.com/bookstore/bookdetail.php?PB_ISBN=9781597816526 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "package org.postgresql.util does not exist" compilation problem
Hi:
I'm developing a Java application, using Maven, Spring and Hibernate, and
Postgre (with Postgis) as DBMS.
Everything went OK, until I had to import the org.postgresql.util package, to
use the PGobject class, in a UserType Hibernate class.
I got the following errors:
[...]
[loading org/postgis/Geometry.class(org/postgis:Geometry.class)]
[loading
org/postgis/binary/BinaryParser.class(org/postgis/binary:BinaryParser.class)]
[loading
org/postgis/binary/BinaryWriter.class(org/postgis/binary:BinaryWriter.class)]
/home/perseo/PFC/maven.1264475335794/src/main/java/es/udc/fiestas/model/util/types/PointType.java:15:
package org.postgresql.util does not exist
import org.postgresql.util.PGobject;
^
[...]
[checking es.udc.fiestas.web.pages.usuario.CambiarUbicacion]
[checking es.udc.fiestas.model.util.types.PointType]
/home/perseo/PFC/maven.1264475335794/src/main/java/es/udc/fiestas/model/util/types/PointType.java:56:
cannot find symbol
symbol : class PGobject
location: class es.udc.fiestas.model.util.types.PointType
PGobject pgo = (PGobject) rs.getObject(names[0]);
^
/home/perseo/PFC/maven.1264475335794/src/main/java/es/udc/fiestas/model/util/types/PointType.java:[56,24]
cannot find symbol
symbol : class PGobject
location: class es.udc.fiestas.model.util.types.PointType
In my pom.xml file, I've included the dependencies for both Postgre and
Postgis, and Eclipse doesn't complain about them (neither in the pom.xml file,
nor in the PointType.java):
postgresql
postgresql
8.4-701.jdbc4
org.postgis
postgis-jdbc
1.3.3
Is there any issue with Postgre JDBC that doesn't allow to use their classes
like any other package?
Note: I have to import that class, to be used here:
public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
throws HibernateException, SQLException {
PGobject pgo = (PGobject) rs.getObject(names[0]);
BinaryParser bp = new BinaryParser();
return (Point) bp.parse(pgo.getValue());
}
As the returned object is a PGobject (from a Geometry Postgis column), I use
the PGobject getValue() method to access its content, so I can't generalize
that.
Thank you in advance.
Nice regards.
Santiago.
Re: [GENERAL] deleting db cluster
On Tue, 2010-08-03 at 16:15 +0200, Ulas Albayrak wrote: > Hi, > > I have a Linux/Debian machine running postgres 8.3 and I need to > remove a database cluster that I created with the "initdb" command. Is > it enough to just delete the folder in which the cluster resides on > the filesystem, or does it require some additional actions? If you remove the data directory (the folder you specifified with initdb) then the database is gone. JD > > Regards > -- > Ulas Albayrak > ulas.albay...@gmail.com > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] Application user name attribute on connection pool
Usually bighouse financial systems use BIGINT and a field to store position-of-decimal point to track arbitrary precision currency values... That's the "right way" to do it. I believe for mom-and-pop stuff, you can satisfy the auditors if you use NUMERIC(,2) and implement round-to-even (banker's rounding), though... On 2010-08-03 08:01:34AM +0200, Alban Hertroys wrote: > On 2 Aug 2010, at 23:43, Radosław Smogura wrote: > > >> PostgreSQL already has BIGINT aka INT8, which are 8 bytes, and can > >> represent integers up to like 9 billion billion (eg, 9 * 10^18). > > But I think about numbers with precision - you can use float for moneys, > > etc > > (rounding problems), and dividing each value in application by some scale > > isn't nice, too. > > > Most people don't use float for monetary values. > Have a look at the NUMERIC type: > http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html > > Alban Hertroys > > -- > If you can't see the forest for the trees, > cut the trees and you'll see there is no forest. > > > !DSPAM:737,4c57b0dc286217280628589! > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- === Peter C. Lai | Bard College at Simon's Rock Systems Administrator| 84 Alford Rd. Information Technology Svcs. | Gt. Barrington, MA 01230 USA peter AT simons-rock.edu | (413) 528-7428 === -- 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] Nodes and trees...
On Tue, Aug 3, 2010 at 8:01 AM, Jason Schauberger wrote: > Dear fellow Postgres users, :-) > > please consider this table: > > CREATE TABLE nodes ( > > id int PRIMARY KEY, > > parent int REFERENCES nodes(id) > > ); > > In this table, each node *can* have a parent node. You can picture the > whole set of rows of this table as one or more trees with nodes and > the root of the tree is the node which has no parent node (that is, > parent is NULL). > > Now here's my objective: I want to *quickly* find all nodes that have > the same root node. > > I could iterate through the complete tree in question starting from > the root node and going all the way through to the terminal/leaf nodes > (those without a child), but that could take quite long depending on > how large the tree is. > > So, what I could do is this: > > CREATE TABLE nodes ( > > id int PRIMARY KEY, > > parent int REFERENCES nodes(id), > > root int NOT NULL REFERENCES nodes(id) > > ); > > and fill out the root column every time I insert a node. But then > there is the problem of anomalies, where the root column could have an > incorrect value (that is, the id of a node which is actually NOT the > root of the same tree). I guess I could check for correctness using > triggers. yes, you should absolutely do that -- the role of the database is to (as much as possible) make anomalies in your data impossible. If you go this route, set the root_id node in the trigger. Postgres triggers aren't strictly portable, but should be able to be migrated with minimal effort. Looking it up in the application is possible, but undesirable IMO. If you are only specifically interested in the root node, you can materialize it in the application and index it. This isn't a general solution for investigating familial relationships of nodes, but it doesn't sound like you need something general. The general solution that is going to be most portable is going probably going to be a materialized path approach. You encode the parents of a node in a string (in postgres you can use an array) in such away that you can pull out specific parents_ids. Materialized approaches have at least one big downside: updates to the tree are extremely expensive. Here is a pretty neat way of doing nested data with farey fractions with SQL examples designed for Oracle -- they should be able to be ported to postgres without too much effort: http://arxiv.org/html/cs/0401014. I've been looking for an excuse to play with it! merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Nodes and trees...
Dear fellow Postgres users, :-) please consider this table: CREATE TABLE nodes ( id int PRIMARY KEY, parent int REFERENCES nodes(id) ); In this table, each node *can* have a parent node. You can picture the whole set of rows of this table as one or more trees with nodes and the root of the tree is the node which has no parent node (that is, parent is NULL). Now here's my objective: I want to *quickly* find all nodes that have the same root node. I could iterate through the complete tree in question starting from the root node and going all the way through to the terminal/leaf nodes (those without a child), but that could take quite long depending on how large the tree is. So, what I could do is this: CREATE TABLE nodes ( id int PRIMARY KEY, parent int REFERENCES nodes(id), rootint NOT NULLREFERENCES nodes(id) ); and fill out the root column every time I insert a node. But then there is the problem of anomalies, where the root column could have an incorrect value (that is, the id of a node which is actually NOT the root of the same tree). I guess I could check for correctness using triggers. I also thought that using views might make adding a root column to the table completely unnecessary and at the same time allow for quickly finding nodes which have the same root. So, what's the best method to do this? It must be fast, it must prevent anomalies, and must also be either SQL standard or if it is not, at least it must be easily portable across the most popular SQL databases. I also explicitly don't want to create an extra tree ID or something like that, because it only mitigates the problem of anomalies, but does not solve it. Thanks in advance, Jason. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] deleting db cluster
Hi, I have a Linux/Debian machine running postgres 8.3 and I need to remove a database cluster that I created with the "initdb" command. Is it enough to just delete the folder in which the cluster resides on the filesystem, or does it require some additional actions? Regards -- Ulas Albayrak ulas.albay...@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] optimal memory
> So, about how big is your db? How many users are likely to be running > queries at once? How big of a chunk of data are those users likely to > each need for sorts etc? > The database is 400MB (using du on the base folder), I have 10 active users who run queries and functions that generally take less then 1 second each to run. -- 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] optimal memory
2010/8/3 Sim Zacks : > Is there a way to tell what the optimal memory is for a specific > postgresql instance? > > I am configuring Xen virtual machines and I don't want to give it more > then it needs. > > Would looking at the swap be an indication? As soon as it starts to use > swap, that means I need more, but until that point, I have enough? Once you start using swap space it's WAY too small. If your db is say 1GB, and you have 10 or so connections running at once, and each might need 20MB of work_mem, then it would be good to at least 2Gig or so so that the db can be cached by the cache and you've got enough memory left over for the db to allocate enough shared_buffers to hold a decent chunk of it (say 256 to 512Meg). OTOH, if your database is 1TB in size, then you can't really have too much memory, as every bit you throw at the machine will help. So, about how big is your db? How many users are likely to be running queries at once? How big of a chunk of data are those users likely to each need for sorts etc? -- 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] optimal memory
In response to Sim Zacks : > > > On 03-Aug-2010 11:18 AM, A. Kretschmer wrote: > > In response to Sim Zacks : > > > >> Is there a way to tell what the optimal memory is for a specific > >> postgresql instance? > >> > >> I am configuring Xen virtual machines and I don't want to give it more > >> then it needs. > >> > >> Would looking at the swap be an indication? As soon as it starts to use > >> swap, that means I need more, but until that point, I have enough? > >> > > You can't have enough ;-) > > > > Fits your DB into the RAM? > > > > If you don't have enough, for instance, work_mem, sort-operations > > performed on disk and not in the ram. That's much slower. So, as i said, > > you can't have enough ram ;-) > > > In theory that's a great answer. ;-) > If my database is 400MB (du on the base directory) and there are 10 > active users running functions and queries, that for the most part take > less then 1 sec each. > I would assume that 10GB of RAM is overkill. Maybe. > Is 2 GB RAM also overkill? Is there a way to know when you have reached > the overkill level? I think, you should try it. Set your virtual machine to 2 GByte, set shared_buffers to 512 MByte, effective_cache_size to 1.5 gbyte and work_mem to, for instance, 20 mbyte. Monitor the machine, watch the logfile (set log_min_duration_statement properly). Reduce all parameters to 50% and compare the results. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] optimal memory
In response to Sim Zacks : > Is there a way to tell what the optimal memory is for a specific > postgresql instance? > > I am configuring Xen virtual machines and I don't want to give it more > then it needs. > > Would looking at the swap be an indication? As soon as it starts to use > swap, that means I need more, but until that point, I have enough? You can't have enough ;-) Fits your DB into the RAM? If you don't have enough, for instance, work_mem, sort-operations performed on disk and not in the ram. That's much slower. So, as i said, you can't have enough ram ;-) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] optimal memory
Is there a way to tell what the optimal memory is for a specific postgresql instance? I am configuring Xen virtual machines and I don't want to give it more then it needs. Would looking at the swap be an indication? As soon as it starts to use swap, that means I need more, but until that point, I have enough? Thanks Sim -- 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_hba.conf
In response to quickinfo quickinfo : > Dear all, > > I am using postgres. when I try to connect to the database it is showing me > following error. Please look into that and help me out. > > an error occurred: > > FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres", database > "template1", SSL off. > > How do I proceed with this error. What are the things I need to change. You have to read the doc about the hba-file first! http://www.postgresql.org/docs/current/static/client-authentication.html Usually you should not work as user postgres... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
