Probably temp table related ...but catalog bloat on one of my databases
appears to be pretty bad.
Is the below bloat (table and index) something to worry about?
pg_stat_all_tables show the relations ARE getting successfully vacuumed...
Any suggestions on eliminating? Not sure if tools like pg_re
On Thursday, January 9, 2014 4:03 PM, Jeff Amiel
wrote:
I am familiar with pg_reorg and it’s sibling pg_repack - but they call the base
postgresql reindex functions underneath - and I have learned by using ‘verbose’
that the actual clustering of the table is quick - it’s the reindexing
I have a maintenance window coming up and using pg_upgrade to upgrade from
9.2.X to 9.3.X.
As part of the window, I’d like to ‘cluster’ each table by its primary key.
After doing so, I see amazing performance improvements (probably mostly because
of index bloat - but possibly due to table fragm
Trying to follow the threads and other references - but I can't determine where
this patch ended up.
(http://www.postgresql.org/message-id/ca+tgmobrrrxco+t6gcqrw_djw+uf9zedwf9bejnu+rb5teb...@mail.gmail.com)
I'm trying to experiment with some new hardware - and the functionality to add
specific t
On Monday, November 4, 2013 3:23 PM, Adrian Klaver
wrote:
>Probably poor choice of words:). So then, what we are looking at is
>other clients trying to update user_profile but not succeeding because
>pid 4899 is blocking. At this point all I can see is that the offending
>query is updat
On Monday, November 4, 2013 2:25 PM, Adrian Klaver
wrote:
>
> I grepped the schema (just to be sure) - no foreign keys on columns or table
> at all.
> I do have an audit trigger on the table for updates - inserts into an audit
> table when changes are made and it DOES do a separate select
On Monday, November 4, 2013 1:48 PM, Adrian Klaver
wrote:
>Any triggers on user_profile?
>Any FK relationship in either direction?
I grepped the schema (just to be sure) - no foreign keys on columns or table at
all.
I do have an audit trigger on the table for updates - inserts into an a
to: Rob Sargent
The login references have nothing to do with postgres - is simply table/column
names being used.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
PostgreSQL 9.2.4 on x86_64-pc-solaris2.10, compiled by gcc (GCC) 3.4.3
(csl-sol210-3_4-branch+sol_rpath), 64-bit
Have got an annoying scenario that has been creating issues for us for years….
Time to try to figure it out.
Essentially, we have a user table where we maintain username, id number,
en
Thanks much!
(sorry for top-posting, yahoo email sucks)
- Original Message -
From: Amit Langote
To: Jeff Amiel
Cc: "dep...@depesz.com" ; "pgsql-general@postgresql.org"
Sent: Monday, May 20, 2013 9:51 AM
Subject: Re: [GENERAL] Why does row estimation on nested l
Ok - I agree -
Can somebody help me understand where the row estimates come from on a
nested-loop operation in postgres then?
- Original Message -
From: hubert depesz lubaczewski
To: Jeff Amiel
Cc: "pgsql-general@postgresql.org"
Sent: Saturday, May 18, 2013 3:39 AM
S
- Original Message -
From: Amit Langote
To: Jeff Amiel
Cc: "pgsql-general@postgresql.org"
Sent: Friday, May 17, 2013 2:21 PM
Subject: Re: [GENERAL] Why does row estimation on nested loop make no sense to
me
>> explain analyze
>> select era.child_entity fro
> explain analyze
> select era.child_entity from entity_rel era join user_entity ue on
> ue.entity_id = era.parent_entity and ue.user_id=12345
>
> Nested Loop (cost=0.00..2903.37 rows=29107 width=4) (actual
> time=0.028..0.274 rows=201 loops=1)
> -> Index Only Scan using entity_pk on u
- Original Message -
From: Amit Langote
To: Jeff Amiel
Cc: "pgsql-general@postgresql.org"
Sent: Friday, May 17, 2013 11:37 AM
Subject: Re: [GENERAL] Why does row estimation on nested loop make no sense to
me
On Sat, May 18, 2013 at 1:25 AM, Jeff Amiel wrote:
> O
> Can you provide a self-contained test case that does this?
That response scares me.
:)
I can try - Every other table set (small, easy to experiment with) returns
results as expected -
Is the implication that this looks 'unusual'?
--
Sent via pgsql-general mailing list (pgsql-general@po
On most nested loops that I do explain/explain analyze on, the row estimation
for the nested-loop itself is a product of the inner nodes of the nested loop.
However in this case, I am stumped!
explain
select era.child_entity from entity_rel era join user_entity ue on
ue.entity_id = era.paren
In prepping for an upgrade to 9.2.3, I stumbled across this:
CREATE TABLE foo
(
myint integer,
string1 text,
string2 text
)
WITH (
OIDS=FALSE
);
insert into foo values (12345,'Y','N');
select * from foo f where f.myint = 12345 or f.name='Y'
In 9.2.3, this returns:
ERROR: column f.name
Ive got a scenario where I've got a 2 million row table. Data from inbound
files gets processed into it.
A new file might have 10 resulting rows in this table...might have
40K...depends on the source, day of month, etc.
I've got a process that parses the file and loads the records into the
t
--- On Fri, 12/2/11, Tom Lane wrote:
> The only real fix for that will require cross-column
> statistics, which
> we don't have yet --- without such, there's no way for the
> planner to
> know that distributors have an atypical number of child
> customers.
The only caveat that I can think of
--- On Fri, 12/2/11, Tom Lane wrote:
> The only real fix for that will require cross-column
> statistics, which
> we don't have yet --- without such, there's no way for the
> planner to
> know that distributors have an atypical number of child
> customers.
>
I suspected as such.
> At the mo
--- On Fri, 12/2/11, David Johnston wrote:
>
>
> Can you wrap the query into an SQL or PL/pgSQL function so
> that, at least,
> then planner will not be able to see the embedded plan info
> in the outer
> queries? You use-case may allow
--- On Fri, 12/2/11, David Johnston wrote:
> From: David Johnston
> -
> My, possibly naïve, observation:
>
> So aside from the fact the estimates seem to be off the
> planner has still
> chosen the most effective plan? In that situati
--- On Fri, 12/2/11, David Johnston wrote:
> What happens if you disable, say, nested loops and/or index
> scans?
planner selects different join/indexing techniques (query is slower) but row
estimates (bad) remain identical.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.o
--- On Fri, 12/2/11, David Johnston wrote:
> From: David Johnston
> What kind of plan does the following give?
>
> EXPLAIN ANALYZE
> SELECT *
> FROM customer_rel p
> JOIN customer c ON (p.parent_customer = c.customer_id)
> WHERE c.customer_type = 'DISTRIBUTOR'
Nearly identical output
"Nested
Oddball data distribution giving me headaches.
We have a distinct 'customer' table with customer_id, type and name/demographic
information.
Assume some 1 million rows in the customer table.
We then have a customer 'relationship' table which simply contains 2
columns…designating parent and chil
It's come time to bite the bullet and convert a half-terraybyte database from
ASCII to UTF8. Have gone through a bit of effort to track down the unclean
ascii text and repair it but would like to avoid the outage of a many-many hour
dump-restore.
Using Postgres 8.4.X.
Are there any other magi
PostgreSQL 8.4.4 on x86_64-pc-solaris2.10, compiled by GCC gcc (GCC) 3.4.3
(csl-sol210-3_4-branch+sol_rpath), 64-bit
2 large tables (around a billion rows each) recently received attention from
autovacuum. They both currently show up in my currently running queries as
"autovacuum: VACUUM table
On 6/14/10 8:05 AM, "Jeff Amiel" wrote:
> What is recommended in terms of prep/switchover in this instance? Should we
> be rsyncing or using built-in wal-log shipping of these transaction logs to
> our stand-by server? Simply pop out these drives and hand-move them to t
Recently migrated to a shiny new 8.4.4 postgres instancedata stored on
attached storage array. Transaction logs stored on 2 local mirrored drives
(local to the database server itself) for best performance.
While we are replicating (using slony) to our DR site, our first-choice plan
(in the
On 6/8/10 2:03 PM, "Alvaro Herrera" wrote:
>
> I've seen this problem (and others) in a high-load environment. Not
> Slony related though.
>
> I wrote a small tool to check btree index files for consistency problems
> such as this one, by parsing pg_filedump output. I've seen strange
> thin
On 6/8/10 1:15 PM, "Jaime Casanova" wrote:
> On Tue, Jun 8, 2010 at 12:49 PM, Jeff Amiel wrote:
>>
>> Does Slony manage it's own vacuuming separate from postgres' autovacuum?
>>
>
> Yes it does: http://www.slony.info/documentation/mainte
On 6/8/10 12:56 PM, "Tom Lane" wrote:
> Jeff Amiel writes:
>> On a side note, I am 100% sure that autovacuum was disabled when I brought
>> the database back up after the core dump(s). However, minutes after
>> restarting, some of my larger tables started
On 6/8/10 11:23 AM, "Tom Lane" wrote:
> In your original report you mentioned that the next autovacuum attempt
> on the same table succeeded without incident. Has that been true each
> time? I wonder whether this is some transient state, rather than actual
> corruption that you need to REINDEX
On 6/8/10 10:47 AM, "Stephen Frost" wrote:
> Then you've been through this before.. Perhaps you should go check out
> what you did then. Back before 8.1, we didn't use OIDs for
> users/groups. :) Changing to OIDs was part of the work that I did to
> add role support.
Hmmm...this code has b
On 6/8/10 10:39 AM, "Stephen Frost" wrote:
> I'm afriad you're not going to have a choice.. I would recommend
> creating a mapping from the old IDs to the new ones as part of this
> upgrade, to keep the historical information. Guess it's not nice to
> point this out- but you really shouldn't h
On 6/8/10 10:30 AM, "Thom Brown" wrote:
> Can't you switch to using role names? I don't think oids are intended
> to be used by anything other than PostgreSQL.
:( If only I couldmassive audit tables contain these IDs with years of
data
We have a plan to change to sequence values store
We currently use the 'usesysid' column from pg_shadow (which is really
pg_authid.oid I assume) for a trigger-based auditing mechanism.
We are about to do a dump from an 8.2 database into 8.4 and would like to
preserve the usesysid/oid when restoring.
No matter what options I throw ad pg_dumpall,
Not looking for help...just putting some data out there.
2 previous crashes caused by corrupt slony indexes
http://archives.postgresql.org/pgsql-general/2010-02/msg00022.php
http://archives.postgresql.org/pgsql-general/2009-12/msg01172.php
New one yesterday.
Jun 7 15:05:01 db-1 postgres[9334]
About a month ago I posted about a database crash possibly caused by corrupt
index..
Dec 30 17:41:57 db-1 postgres[28957]: [ID 748848 local0.crit] [34004622-1]
2009-12-30 17:41:57.825 CST28957PANIC: right sibling 2019 of block 2018 is
not next child of 1937 in index "sl_log_2_idx1"
Has si
--- On Wed, 12/30/09, Jeff Amiel wrote:
> Subject: PANIC: right sibling 2019 of block 2018 is not next child of 1937
> in index "sl_log_2_idx1"
I am assuming a re-index for that particular index will rebuild/fix the index
(if it happens again). Any other thoughts?
I know I don't have a lot of data right now (still looking for core dump)
Any obvious thoughts or advice until I can get more info?
Dec 30 17:41:57 db-1 postgres[28957]: [ID 748848 local0.crit] [34004622-1]
2009-12-30 17:41:57.825 CST28957PANIC: right sibling 2019 of block 2018 is
not next
--- On Fri, 11/27/09, Tom Lane wrote:
> You didn't show us any evidence of that, either. Both
> of your test
> cases are using the index.
Ok...third try. The cost when passing in an empty string is SIGNIFICANTLY
higher than when not. Wouldn't seem that the planner is using the index
effecti
hmm...ok...planner is not using the index effectively (as effectively as when a
non-empty value is passed in)
--- On Fri, 11/27/09, Tom Lane wrote:
> From: Tom Lane
> Subject: Re: [GENERAL] empty string causes planner to avoid index. Makes me
> sad.
> To: "Jeff Amiel"
PostgreSQL 8.2.12 on i386-pc-solaris2.10, compiled by GCC gcc (GCC) 3.4.3
(csl-sol210-3_4-branch+sol_rpath)
CREATE TABLE items
(
field1 character(9) NOT NULL,
field2 character varying(17) NOT NULL
};
CREATE INDEX "field1-field2"
ON items
USING btree
(field1, field2);
About 15 million
--- On Mon, 10/6/08, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> I'm wondering if the OP has some line breaks in his
> data that are
> getting misinterpreted, or maybe his encoding on the two
> dbs is
> different and he's not taking care of that.
Ahhh
*looks at encoding*
Well..they are both
What is the difference between:
select foo();
and
select * from foo();
Foo is defined as:
CREATE OR REPLACE FUNCTION foo()
RETURNS SETOF integer AS
'SELECT column from foo_table;'
LANGUAGE 'sql' STABLE;
Explain shows difference...
explain select * from foo()
"Function Scan on foo (cos
I performed a pg_dump on a database and created a new schema-only database to
copy that data into.
However trying to use psql -f to load the data in, I get a plethora of syntax
errors including the dreaded "invalid command \N".
I even tried to pipe the pg_dump results directly into the psql co
What is the difference between:
select foo();
and
select * from foo();
Foo is defined as:
CREATE OR REPLACE FUNCTION foo()
RETURNS SETOF integer AS
'SELECT column from foo_table;'
LANGUAGE 'sql' STABLE;
Explain shows difference...
explain select * from foo()
"Function Scan on foo (cos
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
The Right Way (tm) to do this would be something like
create temp table dates as select * from get_dates(...);
analyze dates;
... original select, but join against temp table ...
which would leave the
-Original Message-
From: Hoover, Jeffrey [mailto:[EMAIL PROTECTED]
change t.date2< dates.date to t.date2+0= dates.date) and t.date2+0http://www.postgresql.org/mailpref/pgsql-general
"PostgreSQL 8.2.4 on i386-pc-solaris2.10, compiled by GCC gcc (GCC)
3.4.3 (csl-sol210-3_4-branch+sol_rpath)"
(test environment)
Picture a table called 'transaction' with 1 million rows.
most (> 99% of the records have date1 and date2 values in the past
(spread over 4 years)
99.99% of the records h
"PostgreSQL 8.2.4 on i386-pc-solaris2.10, compiled by GCC gcc (GCC) 3.4.3
(csl-sol210-3_4-branch+sol_rpath)"
As the proud author of this previous post:
http://archives.postgresql.org/pgsql-general/2007-08/msg01911.php
I never found a real answer except to disable SSL on the connections between my
Tom Lane wrote:
One idea: you are linking to /usr/local/ssl/lib/libssl.so, but is
it possible that when you compile PG it is finding the header files
for some other version?
Sure
enough...I put a #ERROR at the top of the 'old/incorrect' ssl..h and
did a make clean/make and errored out.
So
Tom Lane wrote:
One idea: you are linking to /usr/local/ssl/lib/libssl.so, but is
it possible that when you compile PG it is finding the header files
for some other version?
yes...if I could figure out how the include path is being set on the
postgresql build.
I'm looking at the config.log
Jeff Amiel wrote:
Now their are 2 subscriber nodes that connect to this node for slony
replication...
One is running the same version (libssl 0.9.8e) but one is running
0.9.7e-p1 2.
could this be an issue?
Note that both nodes are set to 'hostnossl' in the pg_hba.conf
--
Sent
Tom Lane wrote:
The previous thread suggested that you might have a problem with
different bits of code being linked to different versions of libssl.
Did you ever resolve that? Given the lack of other reports, I'm
pretty suspicious that it's something like that, rather than a real
bug in either
"PostgreSQL 8.2.4 on i386-pc-solaris2.10, compiled by GCC gcc (GCC) 3.4.3
(csl-sol210-3_4-branch+sol_rpath)"
As the proud author of this previous post:
http://archives.postgresql.org/pgsql-general/2007-08/msg01911.php
I never found a real answer except to disable SSL on the connections betwee
Sam Mason wrote:
Looks like you've "disabled" seqscans.
Sure enough. Thanks.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
And yes...I vacuumed...analyzed...vacuum analyzed the table to no avail.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Only 19 rows in the table.
explain analyze select * from table_a;
"Seq Scan on table_a (cost=1.00..10001.02 rows=19
width=103) (actual time=0.007..0.012 rows=19 loops=1)"
"Total runtime: 0.040 ms"
If I run the same query on the same table in a different database that I
pg_res
Bruce Momjian wrote:
No, it isn't. Please add a TODO item about it:
* Prevent long-lived temp tables from causing frozen-Xid advancement
starvation
Can somebody explain this one to me? because of our auditing technique,
we have many LONG lived temp tables.(one per pooled
--- Scott Marlowe <[EMAIL PROTECTED]> wrote:
>
> Interesting. Is it possible that either you've got 2 versions of
> openssl? Maybe slony is being compiled against one, then using the
> other lib, etc.?
yes...I suppose it is.Solaris came with one...we installed another.
hm...
#
A couple of years back (2005) we were setting up replication for the first time
(using slony) from
our production database server to a stand-by box sitting next to it and a
remote box in a DR
site. We were running FreeBSD 5.X/6.X on all systems on Dell servers and
postgres 7.4.X and then
8.0.X
--- Alvaro Herrera <[EMAIL PROTECTED]> wrote:
>2.168.20.44 28785LOG: duration: 22606.146 ms execute : select
>
> Interesting. What's your log_line_prefix? Does it have "%q" somewhere?
No, no %q...not quite sure what it means: "stop here in non-session processes"
__
I notice in the log entries for the out of memory events have no username,
database name or host
identifier (while regular logged events do) Does that mean anything to anybody?
Aug 28 08:25:50 db-1 postgres[29019]: [ID 748848 local0.warning] [111900-1]
2007-08-28
08:25:50.081 CDT29019ERROR:
Kamil Srot wrote:
Heh, is the pool still open? Maybe I can make at least something from
it :-D
Current odds
Application bug: even money
Application configuration issue: 2-1
Rogue cron job or other maintenance process: 4-1
Somebody messing with you (or SQL injection): 8-1
XID wrap
My entire shop has set up a betting pool on the outcome of this...so I
hope you post results regardless of the outcome, Kamil.
(in all seriousness, we hope you find/fix the problem before things get
really ugly)
Kamil Srot wrote:
Richard Huxton wrote:
Kamil Srot wrote:
In the version used o
--- "Joshua D. Drake" <[EMAIL PROTECTED]> wrote:
>
> The machine we are tracking this problem on is also 64bit.
H.looks like 3 different people are tracking a similar issue on 64 bit
platforms.you,
Erik and myself.
_
--- "Joshua D. Drake" <[EMAIL PROTECTED]> wrote:
> We are actually diagnosing a similar problem on this
> end, where we get a
> failure at 1920... I am currently trying to get some
> DEBUG output.
Tracking for last few days.
Does not appear to happen when little or no user
activity (like Saturday
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> Jeff Amiel <[EMAIL PROTECTED]> writes:
> > is log_min_messages one of them that requires a
> > restart?
>
> No, SIGHUP (pg_ctl reload) should be sufficient.
Weird
looks like some items are going to syslog and so
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> Jeff Amiel <[EMAIL PROTECTED]> writes:
> > is log_min_messages one of them that requires a
> > restart?
>
> No, SIGHUP (pg_ctl reload) should be sufficient.
Weird
looks like some items are going to syslog and so
--- "Joshua D. Drake" <[EMAIL PROTECTED]> wrote:
> Having log_line_prefix with at least %p and %m (or
> %t) plus a
> log_min_messages of DEBUG2 would be great.
i am getting the additional timestampt/pid on my log
lines nowbut no additional debug output...
is log_min_messages one of them tha
--- "Joshua D. Drake" <[EMAIL PROTECTED]> wrote:
> We are actually diagnosing a similar problem on this
> end, where we get a
> failure at 1920... I am currently trying to get some
> DEBUG output.
We are actually getting it semi-regularly today (3
times already)I would be happy to provide s
"PostgreSQL 8.2.4 on i386-pc-solaris2.10, compiled by
GCC gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath)"
Week-old installstill tuning and tweaking this
thing.
Over last 2 days, have spotted 10 "Out of Memory"
errors in postgres logs (never saw before with same
app/usage patterns on tuned
--- Tom Lane <[EMAIL PROTECTED]> wrote:
>
> I can't help thinking you are looking at generalized
> system
> instability. Maybe someone knocked a few cables
> loose while
> installing new network hardware?
Database server/storage instability or network
instability?
There is no doubt that there
>From the logs tonight when the second crash occurred..
Aug 22 20:45:12 db-1 postgres[5805]: [ID 748848
local0.info] [6-1] 2007-08-22 20:45:12 CDT LOG:
received smart shutdown request
Aug 22 20:45:12 db-1 postgres[5805]: [ID 748848
local0.info] [7-1] 2007-08-22 20:45:12 CDT LOG:
server proce
Fairly new (less than a week) install.
"PostgreSQL 8.2.4 on i386-pc-solaris2.10, compiled by
GCC gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath)"
database size around 43 gigabytes.
2 attempts at a pg_dump across the network caused the
database to go down...
The first time I thought it was beca
http://svr5.postgresql.org/pgsql-hackers/2006-03/msg01198.php
http://www.mail-archive.com/pgsql-general@postgresql.org/msg90845.html
This is related to an autovacuum bug and the freeze logic with
template0and probably a corrupted pg_statistics table in template0
you should upgrade AND re
A 'bad' thing happened yesterday.
Postgresql 8.1.X FreeBSD 6.0
At some point in the day, ran out of space on the root
filesystem. (db is elsewhere)
Took about 10 minutes to clear enough space to make
processes stop freaking out and to slow my heart-rate
down to below 200 beats per minute.
Everyt
Got a bit of a rude surprise when I rebuilt a slony subscriber node from
scratch and noticed the indexes for some tables using significantly less
space than on their master-node counterpart.
In once case, it was 2 gigabytes versus 9 gigabytes for the same table
indexs on the master node. I'm as
Whenever I read the documentation on the transaction wraparound stuff,
my head spins with all the references to frozen xids and min/max ages.
When it comes down to it, my interpretation of the whole kaboodle is
"run postgresql v8.1 or later and autovacuum, and you will not have
to deal wit
Tom Lane <[EMAIL PROTECTED]> wrote:I think that would have excluded anything
that didn't demonstrably
belong to schema public, such as procedural languages. Is it possible
that *all* your functions failed to load, and you only noted the ensuing
GRANT/REVOKE failures?
yes...ALL my functions did i
The original pg_dump used --schema="public" .
Could the fact that pg_catalog or information_schema weren't included cause
these kinds of issues? (I can't imagine why)
<[EMAIL PROTECTED]> wrote: did a pg_dump --format=c for a production database
(on a 8.1.2 server) and attempted to pg_restore
did a pg_dump --format=c for a production database (on a 8.1.2 server) and
attempted to pg_restore on a 8.2.0 server.
Things seemed to go fine with the exception of functions, triggers and trigger
functions.
It was apparently doing a bunch of ACL work towards the end and spewed a slew
of error
"PostgreSQL 8.2.0 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC) 3.4.4
[FreeBSD] 20050518"
We just switched from 'pg_dumpall" to "pg_dump -format=c" for our nightly
backups.
I wanted to experiment with restoring a single table (if the need should ever
arise) from the dump file.
I use the
Tom Lane wrote:
Yech. So much for RAID reliability ... maybe you need to reconfigure
the array for more redundancy?
Yeah...I'm not sure if I screwed the pooch by trying the bring the drive
back 'online'.in the past we just try re-seating it and the raid
card 'does its thing' and rebui
order by oid;
ERROR: could not access status of transaction 2485385834
DETAIL: could not open file "pg_clog/0942": No such file or directory
Soam I screwed here...just I just re-init-db and restore the entire kit and
kaboodle from scratch?
Jeff Amiel <[EMAIL PROTECTED]> wr
Had a drive failure on a raid 5 array of a backup box that a couple of postgres
databases sit on. One of the databases is a slony subscriber to a production
database and the other is a test-environment database.
The drive was offline...brought it back online, hoping it would start a
rebuild.
Tom Lane <[EMAIL PROTECTED]> wrote:
Really? Wow, *that's* an interesting thought. Is it likely that that
temp table could contain many-hour-old data?
Certainly...our connection pool used by jboss can have connections to postgres
persisting for multiple days. (We're still looking for a way to
Tom Lane <[EMAIL PROTECTED]> wrote:
BTW, please don't do anything to try to correct the problem until we're
pretty sure we understand how this happened --- we might ask you for
more info. AFAICS this isn't having any bad effects except for bleats
in your log file, so you can wait.
Happened agai
Tom Lane <[EMAIL PROTECTED]> wrote:
Well, if the problem is indeed in pg_statistic, it'll be easy to repair
(think TRUNCATE...). Have you turned up the logging level to find out?
BTW, please don't do anything to try to correct the problem until we're
pretty sure we understand how this happened
Alvaro Herrera <[EMAIL PROTECTED]> wrote:
Did you perchance connect to template0 (probably marking it as
connectable in the process), made some modification there, and then
mark it as non-connectable again, without executing VACUUM FREEZE on it?
AFAICS we only execute VACUUM FREEZE on it, so we s
Tom Lane <[EMAIL PROTECTED]> wrote:So apparently there's some row in template0
that didn't get marked
committed before the pg_clog segment for it went away. Given 8.1's
rather schizophrenic view of whether it can modify template0 or not,
this is not too surprising, but I thought we'd put in some
late0"
Jan 2 03:05:05 prod-app-1 postgres[8524]: [5-1] 8524 ERROR: could not access
status of transaction 1924023481
Jan 2 03:05:05 prod-app-1 postgres[8524]: [5-2] 8524 DETAIL: could not open
file "pg_clog/072A": No such file or
directory
Jeff Amiel <[EMAIL PROTECTED]>
"PostgreSQL 8.1.2 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC) 3.4.4
[FreeBSD] 20050518"
Started seeing this in the logs this afternoon...scaring the begeezus out of me.
Jan 11 19:20:19 prod-app-1 postgres[1752]: [5-1] 1752 ERROR: could not access
status of transaction 1924023481
Jan
2006 at 08:20:23AM -0600, Jeff Amiel wrote:
ERROR: incompatible library "/lib/libc.so.6": missing magic block
SQL state: XX000
Hint: Extension libraries are required to use the PG_MODULE_MAGIC macro.
I've added the appropriate macro to all my actual C code...but in this
case su
I just upgraded to v8.2 and have an issue with one of my stored
proc/functions that worked under 8.1:
CREATE OR REPLACE FUNCTION getpid()
RETURNS integer AS
'/lib/libc.so.6', 'getpid'
LANGUAGE 'c' VOLATILE;
ERROR: incompatible library "/lib/libc.so.6": missing magic block
SQL state: XX000
Hint
We have a production FreeBSD 6.0 system with Postgresql 8.1 where we
have avoided upgrading/updating the ports to avoid compatability and
other unknown issues.
We have our supfile default date set on our production, test and
development environments to ensure that they all have the same
versions.
it is done using now()
But what I don't understand is how the transaction that started first
could 'see' the record that hadn't been changed yet by the initial
update to 'COMPLETE'?
I thought:
"Each transaction sees a snapshot (database version) as of its
starttime, no matter what other tr
PostgreSQL 8.1.2 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC)
3.4.4 [FreeBSD] 20050518
We have triggers on each of our tables that create audit table entries
on each insert/update/delete.
The audit table (in addition to containing information about the change
that was made) contains a
1 - 100 of 130 matches
Mail list logo
