[GENERAL] User defined type - analyze problem

[Carsten Kropf]

Hi *,
I have a little problem with a user defined type:
I constructed a type called "pointnd" which represents a multi-dimensional 
point. This point is subsequently used as an array type in another user defined 
type, like the following:
CREATE TYPE document AS (words tsvector, points _pointnd);

After some time, I have some problems with this type, obviously. The postgres 
process keeps on reporting the following line:
ERROR:  could not identify a comparison function for type pointnd
CONTEXT:  automatic analyze of table "test.public.documents"

I read somewhere, that this can be solved by preparing a operator class for 
btree and passing a comparison function for equality there. I did this with the 
following lines:
CREATE OPERATOR CLASS pointnd_btree_class
DEFAULT FOR TYPE pointnd USING btree AS
OPERATOR3   =(pointnd, pointnd)
;

However, the postges process still reports the same errors after having set up 
the btree operator class.
How can I solve this issue?

Best regards
Carsten Kropf

Re: [GENERAL] PostgreSQL vs. Microsoft SQL server

[Scott Marlowe]

On Mon, May 3, 2010 at 12:04 AM, Thomas Løcke  wrote:
> On Sun, May 2, 2010 at 7:57 PM, Andy  wrote:
>> Skype, perhaps the largest telephony app in the world, uses Postgresql.
>>
>> Here's some info on their postgresql usage:
>>
>> http://highscalability.com/skype-plans-postgresql-scale-1-billion-users
>> https://developer.skype.com/SkypeGarage/DbProjects/SkypePostgresqlWhitepaper
>
>
> Thank you very much for those two links. Very interesting reading indeed.
>
> And also thanks to all the other replies in this thread. You've all
> given me something to think about.

Also keep in mind that people don't have to report if they're using
PostgreSQL, so there are a LOT of companies that use it and you never
will know because it's hidden 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] PostgreSQL vs. Microsoft SQL server

[Thomas Løcke]

On Sun, May 2, 2010 at 7:57 PM, Andy  wrote:
> Skype, perhaps the largest telephony app in the world, uses Postgresql.
>
> Here's some info on their postgresql usage:
>
> http://highscalability.com/skype-plans-postgresql-scale-1-billion-users
> https://developer.skype.com/SkypeGarage/DbProjects/SkypePostgresqlWhitepaper


Thank you very much for those two links. Very interesting reading indeed.

And also thanks to all the other replies in this thread. You've all
given me something to think about.

:o)
Thomas

-- 
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] Tracking down log segment corruption

[Tom Lane]

Gordon Shannon  writes:
> In any case, I will cease and desist from ALTER SET TABLESPACE for a while!.

Here's the applied patch, if you are interested in testing it.

regards, tom lane

Index: src/backend/access/heap/heapam.c
===
RCS file: /cvsroot/pgsql/src/backend/access/heap/heapam.c,v
retrieving revision 1.277.2.1
diff -c -r1.277.2.1 heapam.c
*** src/backend/access/heap/heapam.c	24 Aug 2009 02:18:40 -	1.277.2.1
--- src/backend/access/heap/heapam.c	2 May 2010 22:19:04 -
***
*** 4195,4202 
  	 * Note: the NEWPAGE log record is used for both heaps and indexes, so do
  	 * not do anything that assumes we are touching a heap.
  	 */
! 	buffer = XLogReadBuffer(xlrec->node, xlrec->blkno, true);
  	Assert(BufferIsValid(buffer));
  	page = (Page) BufferGetPage(buffer);
  
  	Assert(record->xl_len == SizeOfHeapNewpage + BLCKSZ);
--- 4195,4204 
  	 * Note: the NEWPAGE log record is used for both heaps and indexes, so do
  	 * not do anything that assumes we are touching a heap.
  	 */
! 	buffer = XLogReadBufferExtended(xlrec->node, xlrec->forknum, xlrec->blkno,
! 	RBM_ZERO);
  	Assert(BufferIsValid(buffer));
+ 	LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE);
  	page = (Page) BufferGetPage(buffer);
  
  	Assert(record->xl_len == SizeOfHeapNewpage + BLCKSZ);

-- 
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] Tracking down log segment corruption

[Gordon Shannon]

Sounds like you're on it.  Just wanted to share one additional piece, in
case it helps.

Just before the ALTER INDEX SET TABLESPACE was issued, there were some
writes to the table in question inside a serializable transaction. The
transaction committed at 11:11:58 EDT, and consisted of, among a couple
thousand writes to sibling tables, 4 writes (unknown combination of inserts
and updates) to cts_20100501, which definitely effected the index in
question.

In any case, I will cease and desist from ALTER SET TABLESPACE for a while!.

Thanks!
Gordon

Between 11:11:56 and 11:11:58 EDT (11 sec before the crash), there were

On Sun, May 2, 2010 at 3:16 PM, Tom Lane  wrote:

> Found it, I think.  ATExecSetTableSpace transfers the copied data to the
> slave by means of XLOG_HEAP_NEWPAGE WAL records.  The replay function
> for this (heap_xlog_newpage) is failing to pay any attention to the
> forkNum field of the WAL record.  This means it will happily write FSM
> and visibility-map pages into the main fork of the relation.  So if the
> index had any such pages on the master, it would immediately become
> corrupted on the slave.  Now indexes don't have a visibility-map fork,
> but they could have FSM pages.  And an FSM page would have the right
> header information to look like an empty index page.  So dropping an
> index FSM page into the main fork of the index would produce the
> observed symptom.
>
> I'm not 100% sure that this is what bit you, but it's clearly a bug and
> AFAICS it could produce the observed symptoms.
>
> This is a seriously, seriously nasty data corruption bug.  The only bit
> of good news is that ALTER SET TABLESPACE seems to be the only operation
> that can emit XLOG_HEAP_NEWPAGE records with forkNum different from
> MAIN_FORKNUM, so that's the only operation that's at risk.  But if you
> do do that, not only are standby slaves going to get clobbered, but the
> master could get corrupted too if you were unlucky enough to have a
> crash and replay from WAL shortly after completing the ALTER.  And it's
> not only indexes that are at risk --- tables could get clobbered the
> same way.
>
> My crystal ball says there will be update releases in the very near
> future.
>
>regards, tom lane
>

Re: [GENERAL] Tracking down log segment corruption

[Tom Lane]

Gordon Shannon  writes:
> [ corruption on a standby slave after an ALTER SET TABLESPACE operation ]

Found it, I think.  ATExecSetTableSpace transfers the copied data to the
slave by means of XLOG_HEAP_NEWPAGE WAL records.  The replay function
for this (heap_xlog_newpage) is failing to pay any attention to the
forkNum field of the WAL record.  This means it will happily write FSM
and visibility-map pages into the main fork of the relation.  So if the
index had any such pages on the master, it would immediately become
corrupted on the slave.  Now indexes don't have a visibility-map fork,
but they could have FSM pages.  And an FSM page would have the right
header information to look like an empty index page.  So dropping an
index FSM page into the main fork of the index would produce the
observed symptom.

I'm not 100% sure that this is what bit you, but it's clearly a bug and
AFAICS it could produce the observed symptoms.

This is a seriously, seriously nasty data corruption bug.  The only bit
of good news is that ALTER SET TABLESPACE seems to be the only operation
that can emit XLOG_HEAP_NEWPAGE records with forkNum different from
MAIN_FORKNUM, so that's the only operation that's at risk.  But if you
do do that, not only are standby slaves going to get clobbered, but the
master could get corrupted too if you were unlucky enough to have a
crash and replay from WAL shortly after completing the ALTER.  And it's
not only indexes that are at risk --- tables could get clobbered the
same way.

My crystal ball says there will be update releases in the very near
future.

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] Tracking down log segment corruption

[Gordon Shannon]

On Sun, May 2, 2010 at 12:52 PM, Tom Lane  wrote:

> Gordon Shannon  writes:
> > Bingo.  Yes it is reasonable.  It was 25 seconds between my altering the
> > index in question and the server crash.
>
> Sounds like we have a smoking gun.  Could you show all your non-default
> postgresql.conf settings on the master?  I'm wondering about
> full_page_writes in particular, but might as well gather all the
> relevant data while we're at it.  Easiest way is:
>
> select name,setting from pg_settings where source not in
> ('default','override');
>
>regards, tom lane
>

 archive_command | cp %p /var/lib/pgsql/wal/%f.wrk; mv
/var/lib/pgsql/wal/%f.wrk /var/lib/pgsql/wal/%f
 archive_mode| on
 authentication_timeout  | 20
 autovacuum_analyze_scale_factor | 0.05
 autovacuum_freeze_max_age   | 15
 autovacuum_vacuum_cost_delay| -1
 autovacuum_vacuum_scale_factor  | 0.1
 checkpoint_segments | 128
 checkpoint_warning  | 300
 commit_siblings | 1
 cpu_index_tuple_cost| 0.001
 cpu_operator_cost   | 0.0005
 cpu_tuple_cost  | 0.003
 DateStyle   | ISO, MDY
 deadlock_timeout| 5000
 default_text_search_config  | pg_catalog.english
 effective_cache_size| 6291456
 lc_messages | en_US.UTF-8
 lc_monetary | en_US.UTF-8
 lc_numeric  | en_US.UTF-8
 lc_time | en_US.UTF-8
 listen_addresses| *
 log_autovacuum_min_duration | 0
 log_destination | stderr
 log_directory   | /var/log/postgres
 log_filename| pg%d.log
 log_line_prefix | %p %u %r %t [%l]
 log_min_duration_statement  | 18
 log_rotation_age| 1440
 log_rotation_size   | 0
 log_temp_files  | 0
 log_timezone| US/Eastern
 log_truncate_on_rotation| on
 logging_collector   | on
 maintenance_work_mem| 8388608
 max_connections | 500
 max_stack_depth | 2048
 port| 5432
 search_path | public, archive, _slony_cluster
 shared_buffers  | 2359296
 standard_conforming_strings | on
 synchronous_commit  | off
 temp_tablespaces|
ts27,ts28,ts29,ts30,ts31,ts32,ts33,ts34,ts35,ts36,ts37
 TimeZone| US/Eastern
 timezone_abbreviations  | Default
 track_functions | pl
 vacuum_freeze_min_age   | 5
 vacuum_freeze_table_age | 13
 wal_buffers | 1024
 work_mem| 204800

Re: [GENERAL] postgres crashes - could not reattach to shared memory

[Dave Page]

On Sun, May 2, 2010 at 7:41 PM, Tom Lane  wrote:
> Dave Page  writes:
>> On 5/2/10, Tom Lane  wrote:
>> I'm not so sure it's fair to the users though.
>
> Well, we did promise that Windows 8.2 would have the same lifespan as
> 8.2 on other platforms:
> http://www.postgresql.org/about/news.865

Right.

> The planned EOL is only a year and a half away anyway.  OTOH, if it's
> doubling your effort to build Windows binary distributions, maybe
> it's not worth continuing to support it.

Probably 66% of the effort in a back branch release is the 8.2
installer for me. The 8.3 MSI installer build automates (or
eliminates) much of the harder manual work, and the one-clicks are
100% automated - the effort there has been put in over the longer term
to develop them in a maintainable way.

But... unless there are other good reasons (like we actually can't fix
things without serious effort, rather than we just can't be bothered),
I don't want my time to be the cause of us dropping it early.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] File compression in WinXP

[Ivan Voras]

On 2.5.2010 16:48, pasman pasmański wrote:
> Hello.
> I'm install postgresql 8.4.3 on WinXPsp3. 
> Because of small disk i create tablespace
> pointing to commpressed folder and move
> some tables to it. 
> Compression is good: 10GB to 3-4GB
> speed acceptable (small activity,10 users)
> 
> But is this safe ?

Microsoft "guarantees" your safety here, there should be no difference
in behaviour.

Of course, memory and other resource requirements are still dependent on
the uncompressed size. Your performance will probably be less than
without compression.


-- 
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] Tracking down log segment corruption

[Tom Lane]

Gordon Shannon  writes:
> Bingo.  Yes it is reasonable.  It was 25 seconds between my altering the
> index in question and the server crash.

Sounds like we have a smoking gun.  Could you show all your non-default
postgresql.conf settings on the master?  I'm wondering about
full_page_writes in particular, but might as well gather all the
relevant data while we're at it.  Easiest way is:

select name,setting from pg_settings where source not in ('default','override');

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] postgres crashes - could not reattach to shared memory

[Tom Lane]

Dave Page  writes:
> On 5/2/10, Tom Lane  wrote:
>> If we take seriously the proposition that we're still supporting 8.2
>> on Windows, we probably ought to do something about that.  If we don't,
>> we need to officially deprecate that version.

> That's the last non-msvc++ version. Personally I'd love to drop it so
> i can get rid of mingw/msys and move entirely to vc++ for Win32 and
> Win64.

> I'm not so sure it's fair to the users though.

Well, we did promise that Windows 8.2 would have the same lifespan as
8.2 on other platforms:
http://www.postgresql.org/about/news.865

The planned EOL is only a year and a half away anyway.  OTOH, if it's
doubling your effort to build Windows binary distributions, maybe
it's not worth continuing to support it.

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] Tracking down log segment corruption

[Gordon Shannon]

On Sun, May 2, 2010 at 12:10 PM, Tom Lane  wrote:

> No, this would be a pg_database row with that OID.  But it looks like
> you found the relevant index anyway.
>
> Yup, realized that on second reading.


> > These commands worked fine on the master, yet this seems suspiciously
> > relevant.
>
>
> Yeah, perhaps so.  What time did the failure on the standby occur (how
> long after you did those moves)?  Is it reasonable to assume that this
> was the first subsequent access to the index?
>
>
Bingo.  Yes it is reasonable.  It was 25 seconds between my altering the
index in question and the server crash.

My local commands (in MDT, plus my machine is 15 sec ahead of the server):

09:10:52> alter index cts_20100501_natural_uk set tablespace ts30;
ALTER INDEX
Time: 787.790 ms
09:11:41> alter index cts_20100501_pkey set tablespace ts30;
ALTER INDEX
Time: 468.526 ms
09:11:51> alter index cts_20100501_topic_date_nk set tablespace ts30;
ALTER INDEX
Time: 385.322 ms
09:11:59> alter index cts_20100501_updated_nk set tablespace ts30;
ALTER INDEX
Time: 963.150 ms
09:12:10> alter table cts_20100501 set tablespace ts29;
ALTER TABLE

And from the wsb log (times in EDT):

4158   2010-05-02 11:12:09 EDT [26446]LOG:  restored log file
"00013C7700C4" from archive
4158   2010-05-02 11:12:09 EDT [26447]WARNING:  specified item offset is too
large
4158   2010-05-02 11:12:09 EDT [26448]CONTEXT:  xlog redo insert: rel
48777166/22362/48778276; tid 2/2
4158   2010-05-02 11:12:09 EDT [26449]PANIC:  btree_insert_redo: failed to
add item

Re: [GENERAL] postgres crashes - could not reattach to shared memory

[Dave Page]

On 5/2/10, Tom Lane  wrote:
> Dave Page  writes:
>> On Sun, May 2, 2010 at 8:27 AM, Sofer, Yuval  wrote:
>>> PG "FATAL:  could not reattach to shared memory (key=5432001,
>>> addr=0210): Invalid argument.
>>>
>>> The version is 8.2.4, the platform is win32
>
>> This was fixed in 8.3.8 and 8.4.1. I'm not entirely sure why it didn't
>> get backpatched to 8.2 - it was mentioned in the discussion about the
>> patch, but I don't see any reason for not back patching it stated.
>> http://archives.postgresql.org/pgsql-hackers/2009-07/thrd5.php#00782
>
> The patch that was being worked on at the time wouldn't have come close
> to applying to 8.2, because the win32 shmem code got rearranged very
> substantially for 8.3.  However, AIUI it's the same underlying
> technology, so in principle it could be fixed the same way.

Right - thats what i was expecting to see.

> If we take seriously the proposition that we're still supporting 8.2
> on Windows, we probably ought to do something about that.  If we don't,
> we need to officially deprecate that version.

That's the last non-msvc++ version. Personally I'd love to drop it so
i can get rid of mingw/msys and move entirely to vc++ for Win32 and
Win64.

I'm not so sure it's fair to the users though.


-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] Tracking down log segment corruption

[Tom Lane]

Gordon Shannon  writes:
> Interesting. There is no pg_class entry for 22362.

No, this would be a pg_database row with that OID.  But it looks like
you found the relevant index anyway.

> ... There is, however, an
> entry for that filenode. It's an index I created Sat AM, about 6AM.
> ...
> - This morning, I was doing some table maintenance on the master and
> discovered I had created this table and its indexes in the wrong tablespace.
> I wanted the table in ts29, but had it in ts30.  Vice versa for the
> indexes.  So I moved them. This is from my command history:

> alter index cts_20100501_natural_uk set tablespace ts30;
> alter index cts_20100501_pkey set tablespace ts30;
> alter index cts_20100501_topic_date_nk set tablespace ts30;
> alter index cts_20100501_updated_nk set tablespace ts30;
> alter table cts_20100501 set tablespace ts29;

> These commands worked fine on the master, yet this seems suspiciously
> relevant.

Yeah, perhaps so.  What time did the failure on the standby occur (how
long after you did those moves)?  Is it reasonable to assume that this
was the first subsequent access to the index?

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] PostgreSQL vs. Microsoft SQL server

[Andy]

Skype, perhaps the largest telephony app in the world, uses Postgresql.

Here's some info on their postgresql usage:

http://highscalability.com/skype-plans-postgresql-scale-1-billion-users
https://developer.skype.com/SkypeGarage/DbProjects/SkypePostgresqlWhitepaper



--- On Sat, 5/1/10, Thomas Løcke  wrote:

> From: Thomas Løcke 
> Subject: [GENERAL] PostgreSQL vs. Microsoft SQL server
> To: pgsql-general@postgresql.org
> Date: Saturday, May 1, 2010, 2:47 PM
> Anybody know of any recent
> comparisons made between the two?
> 
> I'm in the process of buying a new telephony related
> software suite,
> and I'm getting mixed advice. Some say that MSSQL is
> _much_
> better/faster than PostgreSQL, and others say the
> opposite.
> 
> The vendor is more or less indifferent, with a small plus
> to the
> Microsoft solution because, well, they are a Microsoft
> shop. The
> sales-people all bang on about MSSQL being the superior
> choice, and
> PostgreSQL being a "toy compared to the Microsoft RDBMS".
> The tech
> people though are divided into three groups: One group says
> the two
> systems are more or less equal, another group who says the
> Microsoft
> database is superior and finally a group who speaks highly
> of
> PostgreSQL.
> 
> I've not been able to convince them to send me some actual
> benchmark
> numbers, which actually should turn on quite a few alarms,
> come to
> think about it.  :o)
> 
> Maybe you guys are aware of some recent generic
> tests/comparisons
> between the two systems?
> 
> Regards,
> Thomas
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Tracking down log segment corruption

[Gordon Shannon]

On Sun, May 2, 2010 at 11:02 AM, Tom Lane  wrote:

>
>
> Hmm ... AFAICS the only way to get that message when the incoming TID's
> offsetNumber is only 2 is for the index page to be completely empty
> (not zeroes, else PageAddItem's sanity check would have triggered,
> but valid and empty).  What that smells like is a software bug, like
> failing to emit a WAL record in a case where it was necessary.  Can you
> identify which index this was?  (Look for relfilenode 48778276 in the
> database with OID 22362.)  If so, can you give us any hints about
> unusual things that might have been done with that index?
>

Interesting. There is no pg_class entry for 22362.  There is, however, an
entry for that filenode. It's an index I created Sat AM, about 6AM.

select oid, * from pg_class where relfilenode=48778276;
-[ RECORD 1 ]--+---
oid| 48777488
relname| cts_20100501_topic_date_nk
relnamespace   | 2200
reltype| 0
relowner   | 16412
relam  | 403
relfilenode| 48778276
reltablespace  | 48777166
relpages   | 2476
reltuples  | 58879
reltoastrelid  | 0
reltoastidxid  | 0
relhasindex| f
relisshared| f
relistemp  | f
relkind| i
relnatts   | 2
relchecks  | 0
relhasoids | f
relhaspkey | f
relhasrules| f
relhastriggers | f
relhassubclass | f
relfrozenxid   | 0
relacl | null
reloptions | null

Possibly relevant facts:

- The WSB server went active on Friday around 3:30PM
- On Friday evening, I added about 11 tablespaces.  I noted the new files on
the WSB, no problems.
- On Sat morning, I created a partitioned table cts_20100501 (inherits from
another table) and 4 indexes.
- This morning, I was doing some table maintenance on the master and
discovered I had created this table and its indexes in the wrong tablespace.
I wanted the table in ts29, but had it in ts30.  Vice versa for the
indexes.  So I moved them. This is from my command history:

alter index cts_20100501_natural_uk set tablespace ts30;
alter index cts_20100501_pkey set tablespace ts30;
alter index cts_20100501_topic_date_nk set tablespace ts30;
alter index cts_20100501_updated_nk set tablespace ts30;
alter table cts_20100501 set tablespace ts29;

These commands worked fine on the master, yet this seems suspiciously
relevant.


> > Any suggestions?
>
> As far as recovering goes, there's probably not much you can do except
> resync the standby from scratch.  But it would be nice to get to the
> bottom of the problem, so that we can fix the bug.  Have you got an
> archive of this xlog segment and the ones before it, and would you be
> willing to let a developer look at them?
>
>
Before I received your reply, I had already started the re-sync, and
unfortunately already deleted the wal logs in question.
If it happens again, I will certainly keep them, and would be happy to share
them.

Regards,
Gordon

Re: [GENERAL] Tracking down log segment corruption

[Tom Lane]

Gordon Shannon  writes:
> I just got ran into the same problem.  Both servers are running 8.4.3, and
> the standby server had been running for 2 days, processing many thousands of
> logs successfully.  Here's my error:

> 4158   2010-05-02 11:12:09 EDT [26445]LOG:  restored log file
> "00013C7700C3" from archive
> 4158   2010-05-02 11:12:09 EDT [26446]LOG:  restored log file
> "00013C7700C4" from archive
> 4158   2010-05-02 11:12:09 EDT [26447]WARNING:  specified item offset is too
> large
> 4158   2010-05-02 11:12:09 EDT [26448]CONTEXT:  xlog redo insert: rel
> 48777166/22362/48778276; tid 2/2
> 4158   2010-05-02 11:12:09 EDT [26449]PANIC:  btree_insert_redo: failed to
> add item
> 4158   2010-05-02 11:12:09 EDT [26450]CONTEXT:  xlog redo insert: rel
> 48777166/22362/48778276; tid 2/2
> 4151   2010-05-02 11:12:09 EDT [1]LOG:  startup process (PID 4158) was
> terminated by signal 6: Aborted
> 4151   2010-05-02 11:12:09 EDT [2]LOG:  terminating any other active server
> processes

Hmm ... AFAICS the only way to get that message when the incoming TID's
offsetNumber is only 2 is for the index page to be completely empty
(not zeroes, else PageAddItem's sanity check would have triggered,
but valid and empty).  What that smells like is a software bug, like
failing to emit a WAL record in a case where it was necessary.  Can you
identify which index this was?  (Look for relfilenode 48778276 in the
database with OID 22362.)  If so, can you give us any hints about
unusual things that might have been done with that index?

> Any suggestions?

As far as recovering goes, there's probably not much you can do except
resync the standby from scratch.  But it would be nice to get to the
bottom of the problem, so that we can fix the bug.  Have you got an
archive of this xlog segment and the ones before it, and would you be
willing to let a developer look at them?

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] Tracking down log segment corruption

[Gordon Shannon]

I just got ran into the same problem.  Both servers are running 8.4.3, and
the standby server had been running for 2 days, processing many thousands of
logs successfully.  Here's my error:

4158   2010-05-02 11:12:09 EDT [26445]LOG:  restored log file
"00013C7700C3" from archive
4158   2010-05-02 11:12:09 EDT [26446]LOG:  restored log file
"00013C7700C4" from archive
4158   2010-05-02 11:12:09 EDT [26447]WARNING:  specified item offset is too
large
4158   2010-05-02 11:12:09 EDT [26448]CONTEXT:  xlog redo insert: rel
48777166/22362/48778276; tid 2/2
4158   2010-05-02 11:12:09 EDT [26449]PANIC:  btree_insert_redo: failed to
add item
4158   2010-05-02 11:12:09 EDT [26450]CONTEXT:  xlog redo insert: rel
48777166/22362/48778276; tid 2/2
4151   2010-05-02 11:12:09 EDT [1]LOG:  startup process (PID 4158) was
terminated by signal 6: Aborted
4151   2010-05-02 11:12:09 EDT [2]LOG:  terminating any other active server
processes

Any suggestions?


Charles Duffy-5 wrote:
> 
> 
> [14-1] LOG:  restored log file "000100140010" from archive
> [15-1] WARNING:  specified item offset is too large
> [15-2] CONTEXT:  xlog redo insert_upper: rel 1663/16384/17763; tid 2960/89
> [16-1] PANIC:  btree_insert_redo: failed to add item
> [16-2] CONTEXT:  xlog redo insert_upper: rel 1663/16384/17763; tid 2960/89
> [1-1] LOG:  startup process (PID 17310) was terminated by signal 6:
> Aborted
> [2-1] LOG:  aborting startup due to startup process failure
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Tracking-down-log-segment-corruption-tp21121136p28427922.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] About the limit of storage

[Cédric Villemain]

2010/5/2 Sean :
> I need to have a PostgreSQL server with 10TB disk. I created a DB which
> contains three tables.
>
> I understand that PostgreSQL has the following hard limit:
> size per table: 32TB
> size per DB: none
> I wonder what I should do if the sum of my data exceed 10TB. It does not
> exceed any limit that PostgreSQL system imposes, but it does exceed my
> server's storage. Can anyone give a hint?

Then you can add other storage, and make a 'tablespace' wich point to it.

http://www.postgresql.org/docs/current/static/manage-ag-tablespaces.html

> Thanks,
> Sean
> 
> Hotmail has tools for the New Busy. Search, chat and e-mail from your inbox.
> Learn more.



-- 
Cédric Villemain

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] About the limit of storage

[Sean]

I need to have a PostgreSQL server with 10TB disk. I created a DB which 
contains three tables.

I understand that PostgreSQL has the following hard limit:
size per table: 32TBsize per DB: none
I wonder what I should do if the sum of my data exceed 10TB. It does not exceed 
any limit that PostgreSQL system imposes, but it does exceed my server's 
storage. Can anyone give a hint?
Thanks,Sean   
_
Hotmail has tools for the New Busy. Search, chat and e-mail from your inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_1

Re: [GENERAL] Indexing queries with bit masks

[Mike Christensen]

Hey thanks..  I thought I'd share the method I came up with for updating
subscriptions.  Basically, as far as my code is concerned the DB uses a
bitmask (at least for updates) but I abstract it through a function.  First
off, I have a little helper function so I don't repeat the same code a bunch
of times:

CREATE OR REPLACE FUNCTION KPC_UpdateEmailPreferenceHelper(_enable boolean,
_userid uuid, _type smallint)
   RETURNS void AS
   $BODY$
  BEGIN
 IF _enable THEN
INSERT INTO EmailPreferences (UserId, NotificationType) SELECT
_userid, _type
WHERE NOT EXISTS (SELECT 1 FROM EmailPreferences WHERE UserId =
_userid AND NotificationType = _type);
 ELSE
DELETE FROM EmailPreferences WHERE UserId = _userid AND
NotificationType = _type;
 END IF;
  END;
   $BODY$
   LANGUAGE 'plpgsql' VOLATILE
   COST 100;

Then through my code I call this one with a bitmask of which notifications
the user wants to subscribe to:

CREATE OR REPLACE FUNCTION KPC_UpdateEmailPreference(_userid uuid, _prefs
smallint)
  RETURNS void AS
$BODY$
  BEGIN
 PERFORM KPC_UpdateEmailPreferenceHelper (_prefs & 1 > 0, _userid,
1::smallint);
 PERFORM KPC_UpdateEmailPreferenceHelper (_prefs & 2 > 0, _userid,
2::smallint);
 PERFORM KPC_UpdateEmailPreferenceHelper (_prefs & 4 > 0, _userid,
3::smallint);
 PERFORM KPC_UpdateEmailPreferenceHelper (_prefs & 8 > 0, _userid,
4::smallint);
 PERFORM KPC_UpdateEmailPreferenceHelper (_prefs & 16 > 0, _userid,
5::smallint);
 PERFORM KPC_UpdateEmailPreferenceHelper (_prefs & 32 > 0, _userid,
6::smallint);
 PERFORM KPC_UpdateEmailPreferenceHelper (_prefs & 64 > 0, _userid,
7::smallint);
 PERFORM KPC_UpdateEmailPreferenceHelper (_prefs & 128 > 0, _userid,
8::smallint);
 PERFORM KPC_UpdateEmailPreferenceHelper (_prefs & 256 > 0, _userid,
9::smallint);
 PERFORM KPC_UpdateEmailPreferenceHelper (_prefs & 512 > 0, _userid,
10::smallint);
 PERFORM KPC_UpdateEmailPreferenceHelper (_prefs & 1024 > 0,
_userid, 11::smallint);
 PERFORM KPC_UpdateEmailPreferenceHelper (_prefs & 2048 > 0,
_userid, 12::smallint);
 PERFORM KPC_UpdateEmailPreferenceHelper (_prefs & 4096 > 0,
_userid, 13::smallint);
 PERFORM KPC_UpdateEmailPreferenceHelper (_prefs & 8192 > 0,
_userid, 14::smallint);
  END;
   $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Seems to work pretty well, anyone have any feedback?

Mike

2010/5/1 Filip Rembiałkowski 

> 2010/4/30 Mike Christensen :
> > Ok I've been blatantly lying, err, purposely simplifying the problem for
> the
> > sake of the original email :)
> >
> > I've read over the responses, and am actually now considering just not
> using
> > any index at all.  Here's why:
> >
> > First, this actually isn't the only thing on the WHERE clause.  It will
> only
> > query for users who are "friends" with you so it can notify them of your
> > activities.  That's done via a weird JOIN on a table that holds all the
> > friend relationships.  So in reality, it will only load maybe a hundred
> > rows, or maybe a thousand every once in a while if you're way popular.
> If
> > I'm not mistaken, it should use the index to narrow it down to the list
> of
> > friends, and then use a sequential scan to weed out the ones who
> subscribe
> > to that type of notification.
> >
> > Second, the only thing /ever/ that will do this query is the queue
> service
> > whose job it is to process notifications (which are files dropped on the
> > file system) and email people all day long.  This service handles one job
> at
> > a time, and could potentially run on its own machine with its own
> read-only
> > copy of the database.  Thus, even if it was a fairly slow query, it's not
> > gonna bring down the rest of the site.
> >
> > Regarding the idea of putting an index on each bit, I thought about this
> > earlier as well as just kinda cringed.  The users table gets updated
> quite a
> > bit (last logon, session id, any time they change their profile info,
> > etc)..  Too many indexes is bad.  I could just put the data in another
> table
> > of course, which lead me to another idea.  Have a table called
> Subscriptions
> > and have each row hold a user id and a notification type.  I could index
> > both, and join on (Subscriptions.UserId = Users.UserId AND
> > Subscriptions.Type = 8).  This would be pretty dang fast, however updates
> > are kinda a royal pain.  When the user changes which types of
> subscriptions
> > they want (via a list of checkboxes), I'd have to figure out which rows
> to
> > delete and which new ones to insert.  However, I think I have an idea in
> > mind for a PgSQL function you pass in the bitmask to and then it
> > "translates" it to conditional deletes and inserts.
> >
> > A third idea I'm tossing around is just not worry about it.  Put the
> bitmask
> > in the DB, but not filter on it.  Every "friend" would be loaded into the
> > 

Re: [GENERAL] Order of execution in shell echo to psql

[John Gage]

Thanks very much for elucidating this.  \g is going to help me in this  
situation more than the ;


John


On May 2, 2010, at 4:25 PM, Tom Lane wrote:


David W Noon  writes:

On Sun, 2 May 2010 14:13:52 +0200, John Gage wrote abour [GENERAL]

I issue the following command to the shell:

echo '\o file.txt \\ select * from table_name limit 10  \o ' |  
psql --

host 'localhost' --port 5432 --username 'johngage' 'database_name'



Try putting a semi-colon at the end of your SQL query.


Or put a \g there.  As it stands, execution of the SQL query is
triggered by the EOF at the end of the string.  So the fact that the
second \o got executed before that is unsurprising.

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] File compression in WinXP

[pasman pasmański]

Hello.
I'm install postgresql 8.4.3 on WinXPsp3.
Because of small disk i create tablespace
pointing to commpressed folder and move
some tables to it.
Compression is good: 10GB to 3-4GB
speed acceptable (small activity,10 users)

But is this safe ?



pasman

Re: [GENERAL] postgres crashes - could not reattach to shared memory

[Tom Lane]

Dave Page  writes:
> On Sun, May 2, 2010 at 8:27 AM, Sofer, Yuval  wrote:
>> PG "FATAL:  could not reattach to shared memory (key=5432001,
>> addr=0210): Invalid argument.
>> 
>> The version is 8.2.4, the platform is win32

> This was fixed in 8.3.8 and 8.4.1. I'm not entirely sure why it didn't
> get backpatched to 8.2 - it was mentioned in the discussion about the
> patch, but I don't see any reason for not back patching it stated.
> http://archives.postgresql.org/pgsql-hackers/2009-07/thrd5.php#00782

The patch that was being worked on at the time wouldn't have come close
to applying to 8.2, because the win32 shmem code got rearranged very
substantially for 8.3.  However, AIUI it's the same underlying
technology, so in principle it could be fixed the same way.

If we take seriously the proposition that we're still supporting 8.2
on Windows, we probably ought to do something about that.  If we don't,
we need to officially deprecate that version.

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] Order of execution in shell echo to psql

[Tom Lane]

David W Noon  writes:
> On Sun, 2 May 2010 14:13:52 +0200, John Gage wrote abour [GENERAL]
>> I issue the following command to the shell:
>> 
>> echo '\o file.txt \\ select * from table_name limit 10  \o ' | psql -- 
>> host 'localhost' --port 5432 --username 'johngage' 'database_name'

> Try putting a semi-colon at the end of your SQL query.

Or put a \g there.  As it stands, execution of the SQL query is
triggered by the EOF at the end of the string.  So the fact that the
second \o got executed before that is unsurprising.

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] int2vector

[Tom Lane]

AI Rumman  writes:
> What is int2vector data type? Is it possible to use it as an array?

It's a restricted form of regular smallint[].  But having said that,
using it in user tables is strongly discouraged --- if we change it
and that breaks your code, you will get zero sympathy.

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] Order of execution in shell echo to psql

[David W Noon]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Sun, 2 May 2010 14:13:52 +0200, John Gage wrote abour [GENERAL]
Order of execution in shell echo to psql:

>I issue the following command to the shell:
>
>echo '\o file.txt \\ select * from table_name limit 10  \o ' | psql -- 
>host 'localhost' --port 5432 --username 'johngage' 'database_name'

Try putting a semi-colon at the end of your SQL query.  [Or try using
proper copy-and-paste when writing your messages.]
- -- 
Regards,

Dave  [RLU #314465]
==
dwn...@ntlworld.com (David W Noon)
==
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAkvdeiwACgkQRQ2Fs59Psv/1FQCgw+m+tBm+5VtnMPFrMn+q6xnV
gwMAoNIHaCb3vVSyu35w8aJ9IZfmKV6g
=vnpA
-END PGP SIGNATURE-

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Order of execution in shell echo to psql

[John Gage]

I issue the following command to the shell:

echo '\o file.txt \\ select * from table_name limit 10  \o ' | psql -- 
host 'localhost' --port 5432 --username 'johngage' 'database_name'


I expect the results to be redirected to file.txt because that is the  
meta-command immediately preceeding the query.


In fact, the results go to stdout, obviously on account of the \o at  
the end of the query.  And, equally obviously, I can leave that out  
(which "works").


But I am surprised that the \o is the controlling meta-command.

Thanks,

John

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] int2vector

[AI Rumman]

What is int2vector data type? Is it possible to use it as an array?

Any one please.

Re: [GENERAL] Large index operation crashes postgres

[strk]

On Mon, Mar 29, 2010 at 09:39:35AM +0200, Frans Hals wrote:
> Paul,
> 
> took your advice and installed Geos 3.2.0.
> Index is now running for 14 hrs, postmaster is taking all the RAM.
> Sadly it looks like the Geos update didn't save me.

Frans, could you try installing GEOS 3.2.2 ?
A leak was fixed there. You should need no action
on the postgis side to get the fix.

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html

-- 
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] PostgreSQL vs. Microsoft SQL server

[Thomas Kellerer]

Greg Smith wrote on 02.05.2010 01:16:

Scott Ribe wrote:

PG's locking scheme, MVCC, basically precludes certain specific
optimizations that means a small number of very specific queries don't
perform as well, while at the same time it means that throughput with
multiple simultaneous connections scales extremely well with multiple
processors.


SQL Server uses MVCC too as of their 2005 release, implemented with row
versioning similarly to Postgres. The main non-MVCC holdout at this
point is DB2.


AFAIK even in a fresh install of SQL Server 2008 the row versioning is turned 
off by default (at least this is true for 2005)
I don't know if this is for compatibility reason or because of the performance 
penalty that comes with it

And DB2 9.7 introduced MVCC as part of their Oracle compatibility.

Thomas



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres crashes - could not reattach to shared memory

[Thomas Kellerer]

Sofer, Yuval wrote on 02.05.2010 09:27:

Hi

Postgres crashes with -

PG "FATAL: could not reattach to shared memory (key=5432001,
addr=0210): Invalid argument.

The version is 8.2.4, the platform is win32

Does someone know the reason/workaround ?


I think this is supposed to be fixed with 8.4

Regards
Thomas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres crashes - could not reattach to shared memory

[Dave Page]

On Sun, May 2, 2010 at 8:27 AM, Sofer, Yuval  wrote:
> Hi
>
>
>
> Postgres crashes with -
>
>
>
> PG "FATAL:  could not reattach to shared memory (key=5432001,
> addr=0210): Invalid argument.
>
> The version is 8.2.4, the platform is win32
>
> Does someone know the reason/workaround ?

This was fixed in 8.3.8 and 8.4.1. I'm not entirely sure why it didn't
get backpatched to 8.2 - it was mentioned in the discussion about the
patch, but I don't see any reason for not back patching it stated.

http://archives.postgresql.org/pgsql-hackers/2009-07/thrd5.php#00782

Even if it were fixed, you would need a much newer build than 8.2.4.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres crashes - could not reattach to shared memory

[Vibhor Kumar]

On 02/05/10 12:57 PM, Sofer, Yuval wrote:

  
  
  

  
  PG "FATAL:  could not reattach to shared
memory
(key=5432001, addr=0210): Invalid argument.
   
  
  

On windows this kind of
issue, generally happens due to Firewall/Antivirus Software.

Please try with disabling the firewall/antivirus software and restart
the PostgreSQL.

Following is a thread, which has good discussion on it:
http://www.mail-archive.com/pgsql-general@postgresql.org/msg132613.html


-- 
Thanks & Regards,
Vibhor Kumar.

Re: [GENERAL] postgres crashes - could not reattach to shared memory

[Shoaib Mir]

On Sun, May 2, 2010 at 5:27 PM, Sofer, Yuval  wrote:
>
> PG "FATAL:  could not reattach to shared memory (key=5432001,
> addr=0210): Invalid argument.
>
>
>

In my previous experiences with PostgreSQL on Windows I have seen such
similar type of problems when Anti-virus software was running on the system
as well. If you have such installed can you try removing it and then see how
it goes?

-- 
Shoaib Mir
http://shoaibmir.wordpress.com/

[GENERAL] postgres crashes - could not reattach to shared memory

[Sofer, Yuval]

Hi



Postgres crashes with -



PG "FATAL:  could not reattach to shared memory (key=5432001, addr=0210): 
Invalid argument.



The version is 8.2.4, the platform is win32



Does someone know the reason/workaround ?



Thanks,

Yuval Sofer

BMC Software

CTM&D Business Unit

DBA Team

972-52-4286-282

yuval_so...@bmc.com

Re: [GENERAL] PostgreSQL vs. Microsoft SQL server

[Dimitri Fontaine]

Merlin Moncure  writes:

> On Sat, May 1, 2010 at 2:47 PM, Thomas Løcke  
> wrote:
>> Anybody know of any recent comparisons made between the two?
>>
>> I'm in the process of buying a new telephony related software suite,
>
> if you are writing stuff in C/C++, doing significant coding INSIDE the
> database (pl/pgsql, C, etc) and/or think of the database as a self
> contained development environment, then postgresql is definitely the
> right choice.  our C interface (both libpq and internal) is excellent,
> variety of server side programming options is second to none, and
> postgres's flexible type system is fantastic and capable of doing all
> kinds of things that bend the rules of what a database is 'supposed to
> do'.

An specific example here would be the prefix_range data type that handle
indexing telephony routing queries. See it here:

  http://prefix.projects.postgresql.org/

Keep in mind you'll find other side projects along with PostgreSQL to
help you answer to your business needs. It's not just about which is
faster on some random benchmark but which helps you best doing your
work.

I know nothing about MSSQL, but another axe of analysis that I tend to
follow is the error management. PostgreSQL is very good on this point,
you really can trust your data to it.

Regards,
-- 
dim

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general