From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-hackers@postgresql.org
Subject: Re: GIN improvements part 1: additional information
Date: Sun, 06 Oct 2013 01:58:25 +0200
Message-ID: <5250A7A1.2010701@fuzzy.cz> (raw)
In-Reply-To: <524E7C3B.7060402@vmware.com>
References: <CAPpHfduxv-iL7aedwPW0W5fXrWGAKfxijWM63_hZujaCRxnmFQ@mail.gmail.com>
<CAPpHfduQrtC4B+MC9_z3QB8oJsK1Ws-H8ivEsy_cmDnfoJqkeQ@mail.gmail.com>
<CAPpHfdszEJhSynTPX6=tB3aKq1EA1JXpwx4xiFYM097K7PhQLA@mail.gmail.com>
<CAPpHfdu8=5spaS7c9mxwH7bNMDR6n378fZCv6c2Y3YM7iE5KiQ@mail.gmail.com>
<51CEA13C.8040103@vmware.com>
<CAPpHfdutJEC9+6vWX579afZKzdHKCT2wzDkAEom1XmiXEwF3pw@mail.gmail.com>
<524E7C3B.7060402@vmware.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 4.10.2013 10:28, Heikki Linnakangas wrote:
> Aside from the pg_upgrade discussion, here's an updated version of the
> patch, rebased over master. It also contains some other misc refactoring
> I've done while reading through the patch. I haven't tested this much, I
> may well have also broken something, but I wanted to post an update
> before the weekend.
>
> Thinking about the page format, I think we should start using the
> pd_lower/upper pointers in the data page format. For a non-leaf page,
> pd_upper would always point to the beginning of the special area, and
> pd_lower would indicate the end of PostingItems. For a leaf page,
> pd_lower would indicate the end of the compressed posting list, and
> pd_upper would point to the "leaf-index" at the end of the page. That
> matches the standard page layout in the sense that the space between
> pd_lower and pd_upper is free, although the data stored in the non-free
> areas would be quite different. That would allow us to mark full-page
> images with buffer_std, allowing the "gap" to be left out. I think that
> would be a more natural way to keep track of the used/unused space on
> the page, anyway, compared to the current maxoff/endoffset field in the
> special area.
>
> In the attached patch, I in fact already did that for data leaf pages,
> but didn't change the format of non-leaf pages yet. If we want to
> support pg_upgrade, we might want to refrain from changing the non-leaf
> format.
>
> - Heikki
Hi,
I've attempted to rerun the benchmarks tests I did a few weeks ago, but
I got repeated crashes when loading the data (into a table with
tsvector+gin index).
Right before a crash, theres this message in the log:
PANIC: not enough space in leaf page!
The exact crash varies though - for example this is the backtrace on the
first gdb attempt (second crash):
Program received signal SIGABRT, Aborted.
0x00007fb3c0b40b15 in raise () from /lib64/libc.so.6
(gdb) bt
#0 0x00007fb3c0b40b15 in raise () from /lib64/libc.so.6
#1 0x00007fb3c0b41f96 in abort () from /lib64/libc.so.6
#2 0x000000000072753b in errfinish ()
#3 0x0000000000729c1a in elog_finish ()
#4 0x00000000004721d6 in dataPlaceToPage ()
#5 0x0000000000473d51 in ginInsertValue ()
#6 0x0000000000473262 in ginInsertItemPointers ()
#7 0x000000000046d915 in ginEntryInsert ()
#8 0x00000000004792bf in ginInsertCleanup ()
#9 0x0000000000479b32 in ginHeapTupleFastInsert ()
#10 0x000000000046e0b4 in gininsert ()
#11 0x000000000072d733 in FunctionCall6Coll ()
#12 0x00000000004977bc in index_insert ()
#13 0x00000000005952ad in ExecInsertIndexTuples ()
#14 0x00000000005a235d in ExecModifyTable ()
#15 0x000000000058c4e8 in ExecProcNode ()
#16 0x0000000000589ab0 in standard_ExecutorRun ()
#17 0x00000000006603cf in ProcessQuery ()
#18 0x00000000006605fc in PortalRunMulti ()
#19 0x0000000000661142 in PortalRun ()
#20 0x000000000065e4c3 in PostgresMain ()
#21 0x0000000000465446 in ServerLoop ()
#22 0x000000000061f553 in PostmasterMain ()
#23 0x0000000000465cd5 in main ()
Then I recompiled the sources with "-ggdb" and I got this:
Program received signal SIGQUIT, Quit.
ResourceOwnerEnlargeSnapshots (owner=0x200000001) at resowner.c:1077
1077 {
(gdb) bt
#0 ResourceOwnerEnlargeSnapshots (owner=0x200000001) at resowner.c:1077
#1 0x0000000000872228 in RegisterSnapshotOnOwner (snapshot=0x1f9ac60,
snapshot@entry=<error reading variable: Cannot access memory at address
0x7fff6b6151c8>, owner=0x1e81960) at snapmgr.c:588
and then this:
Program received signal SIGQUIT, Quit.
0x00007f48d298b8f2 in recv () from /lib64/libc.so.6
(gdb) bt
#0 0x00007f48d298b8f2 in recv () from /lib64/libc.so.6
#1 0x000000000062ec6b in secure_read (port=0x1a110b0, ptr=0xc5f840
<PqRecvBuffer>, len=8192) at be-secure.c:304
#2 0x0000000000639e31 in pq_recvbuf () at pqcomm.c:854
#3 0x0000000000639ec9 in pq_getbyte () at pqcomm.c:895
#4 0x0000000000723743 in SocketBackend (inBuf=0x7fff3522f9f0) at
postgres.c:344
#5 0x0000000000723b22 in ReadCommand (inBuf=0x7fff3522f9f0) at
postgres.c:492
#6 0x0000000000728218 in PostgresMain (argc=1, argv=0x19f4b50,
dbname=0x19f4b38 "archie", username=0x19f4b20 "tomas") at postgres.c:3953
#7 0x00000000006d27ae in BackendRun (port=0x1a110b0) at postmaster.c:4083
#8 0x00000000006d1f84 in BackendStartup (port=0x1a110b0) at
postmaster.c:3772
#9 0x00000000006cea47 in ServerLoop () at postmaster.c:1583
#10 0x00000000006ce150 in PostmasterMain (argc=3, argv=0x19f2a10) at
postmaster.c:1239
#11 0x000000000063c0a8 in main (argc=3, argv=0x19f2a10) at main.c:196
So it crashes at various times, although now that I'm looking into the
log I see this:
LOG: server process (PID 12326) was terminated by signal 6: Aborted
DETAIL: Failed process was running: autovacuum: ANALYZE public.messages
LOG: terminating any other active server processes
So in some cases it was autovacuum that crashed, and the other processes
were killed because of corrupted shared memory.
But in all cases there's 'not enough space in leaf page!' right before
the crash.
The behavior after the crash is pretty consistent too - I do get this:
LOG: startup process (PID 26259) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
LOG: database system was interrupted while in recovery at 2013-10-06
01:26:48 CEST
HINT: This probably means that some data is corrupted and you will have
to use the last backup for recovery.
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 0/2B0094B0
LOG: startup process (PID 12237) was terminated by signal 11:
Segmentation fault
or this:
LOG: select() failed in postmaster: Nepřípustný argument
LOG: database system was interrupted; last known up at 2013-10-06
01:39:38 CEST
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 0/A013300
LOG: startup process (PID 12441) was terminated by signal 11:
Segmentation fault
LOG: aborting startup due to startup process failure
and then I have to reinit the whole cluster because the xlog is corrupted.
Attached is a log from the multiple runs (and crashes). The test
basically parses mailing list archives and inserts them into a table.
The unique constraint violations are OK (i.e. expected).
Tomas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackersAttachments:
[text/x-log] pg-master.log (60.5K, ../5250A7A1.2010701@fuzzy.cz/2-pg-master.log)
download | inline:
LOG: database system was shut down at 2013-10-06 01:18:56 CEST
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
ERROR: duplicate key value violates unique constraint "messages_hash_id_key"
DETAIL: Key (hash_id)=(2acbc450260904fa1a1cbb23e8d1488f) already exists.
STATEMENT: INSERT INTO messages (list, message_id, in_reply_to, refs, sent, subject, author, body_plain, headers, raw_message) VALUES ('pgsql-hackers', NULL, NULL, NULL, 'Jan 6 14:52:38 1998', 'Re: [PATCHES] [HACKERS] Patch for psql.c', 'Igor <igor@sba.miami.edu>', 'Yea..supposedly free''ing nulls is ok...but PQclear sends a nasty error
message if a value sent to it is null...I would agree with you that it
shouldn''t be that big of a deal (unless to shut up Purify) to free
nulls..In fact, we should probably patch PQclear to simply return
if result passed to it is null...
Actually, free(NULL) is not supported by all compilers so it might be
safer to make sure we''re not freeing any nulls....One interesting thing to
do would be to write a C program that executes every function in libpq
repeatedly, and under Purify''s watch....Sort of purity test for libpq..:)
=+=------------------------/\---------------------------------=+=
Igor Natanzon |**| E-mail: igor@sba.miami.edu
=+=------------------------\/---------------------------------=+=
On Mon, 2 Jun 1997, James A. Hillyerd wrote:
> On Mon, 2 Jun 1997, Igor wrote:
>
> > Well...I put these PQclear''s in because there were memory leaks in that
> > area. If you put it outside, of the case statement, you start clearing
> > already freed tuples causing a mess (Marc.. that was the reason why
> > regression screwed up on boolean). Now, there are a few more memory leaks
> > (particularly with PGsettings *settings variable) but it doesn''t leak much
> > and will be left alone until 6.2 probably.
> > I guess if you do if(results) PQclear (results)...oh well...There are no
> > memory leaks from PQexec anymore so might just as well leave it alone for
> > now.
> >
>
> I don''t know if everything knows this or not, but free()ing a null pointer
> is valid, so it may be easier to set pointers to null after freeing them,
> then there wouldn''t be much penalty in free()ing them again.
>
> I guess that is sloppy coding though. =)
>
> -james
>
> [ James A. Hillyerd (JH2162) james@blarg.net - Perl Programmer ]
> [ http://www.blarg.net/~james/ http://www.pimedia.com/ ]
> [ 1024/B11C3751 CA 1C B3 A9 07 2F 57 C9 91 F4 73 F2 19 A4 C5 88 ]
>
------------------------------', hstore(ARRAY['date', 'from', 'subject'], ARRAY['Mon, 2 Jun 1997 19:22:24 -0400 (EDT)', 'Igor <igor@sba.miami.edu>', 'Re: [PATCHES] [HACKERS] Patch for psql.c']), 'From igor@sba.miami.edu Tue Jan 6 14:52:38 1998
Date: Mon, 2 Jun 1997 19:22:24 -0400 (EDT)
From: Igor <igor@sba.miami.edu>
Subject: Re: [PATCHES] [HACKERS] Patch for psql.c
Yea..supposedly free''ing nulls is ok...but PQclear sends a nasty error
message if a value sent to it is null...I would agree with you that it
shouldn''t be that big of a deal (unless to shut up Purify) to free
nulls..In fact, we should probably patch PQclear to simply return
if result passed to it is null...
Actually, free(NULL) is not supported by all compilers so it might be
safer to make sure we''re not freeing any nulls....One interesting thing to
do would be to write a C program that executes every function in libpq
repeatedly, and under Purify''s watch....Sort of purity test for libpq..:)
=+=------------------------/\---------------------------------=+=
Igor Natanzon |**| E-mail: igor@sba.miami.edu
=+=------------------------\/---------------------------------=+=
On Mon, 2 Jun 1997, James A. Hillyerd wrote:
> On Mon, 2 Jun 1997, Igor wrote:
>
> > Well...I put these PQclear''s in because there were memory leaks in that
> > area. If you put it outside, of the case statement, you start clearing
> > already freed tuples causing a mess (Marc.. that was the reason why
> > regression screwed up on boolean). Now, there are a few more memory leaks
> > (particularly with PGsettings *settings variable) but it doesn''t leak much
> > and will be left alone until 6.2 probably.
> > I guess if you do if(results) PQclear (results)...oh well...There are no
> > memory leaks from PQexec anymore so might just as well leave it alone for
> > now.
> >
>
> I don''t know if everything knows this or not, but free()ing a null pointer
> is valid, so it may be easier to set pointers to null after freeing them,
> then there wouldn''t be much penalty in free()ing them again.
>
> I guess that is sloppy coding though. =)
>
> -james
>
> [ James A. Hillyerd (JH2162) james@blarg.net - Perl Programmer ]
> [ http://www.blarg.net/~james/ http://www.pimedia.com/ ]
> [ 1024/B11C3751 CA 1C B3 A9 07 2F 57 C9 91 F4 73 F2 19 A4 C5 88 ]
>
------------------------------
')
LOG: checkpoints are occurring too frequently (7 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: checkpoints are occurring too frequently (4 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
FATAL: database "tomas" does not exist
LOG: checkpoints are occurring too frequently (6 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: checkpoints are occurring too frequently (5 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: checkpoints are occurring too frequently (5 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: sending cancel to blocking autovacuum PID 26182
DETAIL: Process 26175 waits for ShareUpdateExclusiveLock on relation 16515 of database 16384.
STATEMENT: ANALYZE messages
ERROR: canceling autovacuum task
CONTEXT: automatic analyze of table "archie.public.messages"
LOG: checkpoints are occurring too frequently (4 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
PANIC: not enough space in leaf page!
STATEMENT: INSERT INTO messages (list, message_id, in_reply_to, refs, sent, subject, author, body_plain, headers, raw_message) VALUES ('pgsql-performance', '20040702130710.GA25007@wolff.to', ARRAY['000001c46009$062e8260$5be0d089@ekelhardt'], ARRAY['000001c46009$062e8260$5be0d089@ekelhardt'], 'Jul 2 10:00:36 2004', 'Re: Mysterious performance of query because of plsql function in where condition', 'Bruno Wolff III <bruno@wolff.to>', 'On Fri, Jul 02, 2004 at 09:48:48 +0200,
Peter Alberer <h9351252@obelix.wu-wien.ac.at> wrote:
>
> Postgres seems to execute the function "submission_status" for every row
> of
> the submissions table (~1500 rows). The query therefore takes quite a
> lot
> time, although in fact no row is returned from the assignments table
> when
> the condition package_id=949589 is used.
If submission_status is invertable you might want to create the
inverse function, mark it immutable and call it with ''closed''.
That would allow the optimizer to compare submissions.submission_id
to a constant.
Another option would be be to create an index on
submission_status(submissions.submission_id).', hstore(ARRAY['received', 'from', 'x-sequence-number', 'content-disposition', 'x-spam-level', 'mail-followup-to', 'x-archive-number', 'in-reply-to', 'x-virus-scanned', 'mime-version', 'user-agent', 'to', 'references', 'x-original-to', 'date', 'x-spam-status', 'message-id', 'content-type', 'cc', 'subject'], ARRAY['from localhost (unknown [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id 0A6B2D1B26C
for <pgsql-performance-postgresql.org@localhost.postgresql.org>; Fri, 2 Jul 2004 10:00:35 -0300 (ADT),from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 18639-05
for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
Fri, 2 Jul 2004 13:00:33 +0000 (GMT),from wolff.to (wolff.to [66.93.249.74])
by svr1.postgresql.org (Postfix) with SMTP id 0D84DD1B1D5
for <pgsql-performance@postgresql.org>; Fri, 2 Jul 2004 10:00:27 -0300 (ADT),(qmail 25503 invoked by uid 500); 2 Jul 2004 13:07:10 -0000', 'Bruno Wolff III <bruno@wolff.to>', '7398', 'inline', '*', 'Peter Alberer <h9351252@obelix.wu-wien.ac.at>,
pgsql-performance@postgresql.org', '200407/7', '<000001c46009$062e8260$5be0d089@ekelhardt>', 'by amavisd-new at hub.org', '1.0', 'Mutt/1.5.6i', 'Peter Alberer <h9351252@obelix.wu-wien.ac.at>', '<000001c46009$062e8260$5be0d089@ekelhardt>', 'pgsql-performance-postgresql.org@localhost.postgresql.org', 'Fri, 2 Jul 2004 08:07:10 -0500', 'No, hits=1.1 tagged_above=0.0 required=5.0
tests=MAILTO_TO_SPAM_ADDR', '<20040702130710.GA25007@wolff.to>', 'text/plain; charset=us-ascii', 'pgsql-performance@postgresql.org', 'Re: Mysterious performance of query because of plsql function in where condition']), 'From pgsql-performance-owner@postgresql.org Fri Jul 2 10:00:36 2004
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id 0A6B2D1B26C
for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
Fri, 2 Jul 2004 10:00:35 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 18639-05
for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
Fri, 2 Jul 2004 13:00:33 +0000 (GMT)
Received: from wolff.to (wolff.to [66.93.249.74])
by svr1.postgresql.org (Postfix) with SMTP id 0D84DD1B1D5
for <pgsql-performance@postgresql.org>; Fri, 2 Jul 2004 10:00:27 -0300 (ADT)
Received: (qmail 25503 invoked by uid 500); 2 Jul 2004 13:07:10 -0000
Date: Fri, 2 Jul 2004 08:07:10 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Peter Alberer <h9351252@obelix.wu-wien.ac.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: Mysterious performance of query because of plsql function in
where condition
Message-ID: <20040702130710.GA25007@wolff.to>
Mail-Followup-To: Peter Alberer <h9351252@obelix.wu-wien.ac.at>,
pgsql-performance@postgresql.org
References: <000001c46009$062e8260$5be0d089@ekelhardt>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <000001c46009$062e8260$5be0d089@ekelhardt>
User-Agent: Mutt/1.5.6i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.1 tagged_above=0.0 required=5.0
tests=MAILTO_TO_SPAM_ADDR
X-Spam-Level: *
X-Archive-Number: 200407/7
X-Sequence-Number: 7398
On Fri, Jul 02, 2004 at 09:48:48 +0200,
Peter Alberer <h9351252@obelix.wu-wien.ac.at> wrote:
>
> Postgres seems to execute the function "submission_status" for every row
> of
> the submissions table (~1500 rows). The query therefore takes quite a
> lot
> time, although in fact no row is returned from the assignments table
> when
> the condition package_id=949589 is used.
If submission_status is invertable you might want to create the
inverse function, mark it immutable and call it with ''closed''.
That would allow the optimizer to compare submissions.submission_id
to a constant.
Another option would be be to create an index on
submission_status(submissions.submission_id).
')
LOG: server process (PID 26174) was terminated by signal 6: Aborted
DETAIL: Failed process was running: INSERT INTO messages (list, message_id, in_reply_to, refs, sent, subject, author, body_plain, headers, raw_message) VALUES ('pgsql-performance', '20040702130710.GA25007@wolff.to', ARRAY['000001c46009$062e8260$5be0d089@ekelhardt'], ARRAY['000001c46009$062e8260$5be0d089@ekelhardt'], 'Jul 2 10:00:36 2004', 'Re: Mysterious performance of query because of plsql function in where condition', 'Bruno Wolff III <bruno@wolff.to>', 'On Fri, Jul 02, 2004 at 09:48:48 +0200,
Peter Alberer <h9351252@obelix.wu-wien.ac.at> wrote:
>
> Postgres seems to execute the function "submission_status" for every row
> of
> the submissions table (~1500 rows). The query therefore takes quite a
> lot
> time, although in fact no row is returned from the assignments table
> when
> the condition package_id=949589 is used.
If submission_status is invertable you might want to create the
inverse function, mark it immutable and call it with ''closed''.
That would allow the optimizer to compare submissions.submission_id
to a constant.
Anoth
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2013-10-06 01:20:45 CEST
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/13268E48
LOG: unexpected pageaddr 0/E7F4000 in log segment 000000010000000000000016, offset 8339456
LOG: redo done at 0/167F0DB8
LOG: last completed transaction was at log time 2013-10-06 01:20:47.092802+02
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
LOG: received smart shutdown request
LOG: autovacuum launcher shutting down
LOG: shutting down
LOG: database system is shut down
LOG: database system was shut down at 2013-10-06 01:22:33 CEST
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
ERROR: extension "hstore" already exists
STATEMENT: CREATE EXTENSION hstore;
ERROR: relation "lists" already exists
STATEMENT: CREATE TABLE lists (
id VARCHAR(64) PRIMARY KEY,
language VARCHAR(32) NOT NULL,
msg_first TIMESTAMP,
msg_last TIMESTAMP,
msg_count INT NOT NULL DEFAULT 0,
CONSTRAINT date_check CHECK (msg_last >= msg_first),
CONSTRAINT count_check CHECK (msg_count >= 0)
);
ERROR: relation "messages" already exists
STATEMENT: CREATE TABLE messages (
id SERIAL PRIMARY KEY,
parent_id INT REFERENCES messages(id),
thread_id INT,
level INT,
hash_id VARCHAR(32) NOT NULL UNIQUE,
list VARCHAR(32) NOT NULL REFERENCES lists(id),
message_id VARCHAR(200),
in_reply_to TEXT[],
refs TEXT[],
sent TIMESTAMP,
subject TEXT,
author TEXT,
body_plain TEXT,
body_tsvector tsvector,
subject_tsvector tsvector,
headers HSTORE,
raw_message TEXT
);
ERROR: relation "message_replies" already exists
STATEMENT: CREATE TABLE message_replies (
id INT REFERENCES messages(id),
parent_id INT REFERENCES messages(id),
PRIMARY KEY (id, parent_id)
);
ERROR: relation "files" already exists
STATEMENT: CREATE TABLE files (
file_name TEXT PRIMARY KEY,
file_size INT,
list TEXT,
messages_ok INT,
messages_err INT,
loaded_date TIMESTAMP
);
ERROR: trigger "update_vectors" for relation "messages" already exists
STATEMENT: CREATE TRIGGER update_vectors BEFORE INSERT OR UPDATE ON messages
FOR EACH ROW EXECUTE PROCEDURE message_update_tsvector();
ERROR: trigger "update_thread" for relation "messages" already exists
STATEMENT: CREATE TRIGGER update_thread BEFORE INSERT ON messages
FOR EACH ROW EXECUTE PROCEDURE message_update_thread();
ERROR: trigger "fill_replies" for relation "messages" already exists
STATEMENT: CREATE TRIGGER fill_replies AFTER INSERT ON messages
FOR EACH ROW EXECUTE PROCEDURE message_fill_replies();
ERROR: relation "message_body_idx" already exists
STATEMENT: CREATE INDEX message_body_idx on messages using gin(body_tsvector);
ERROR: relation "message_subject_idx" already exists
STATEMENT: CREATE INDEX message_subject_idx on messages using gin(subject_tsvector);
ERROR: relation "message_send_idx" already exists
STATEMENT: CREATE INDEX message_send_idx on messages(sent);
ERROR: relation "message_id_idx" already exists
STATEMENT: CREATE INDEX message_id_idx on messages(message_id);
ERROR: relation "message_subj_idx" already exists
STATEMENT: CREATE INDEX message_subj_idx on messages(subject) WHERE (lower(substr(subject,0,4)) != 're:');
ERROR: relation "message_thread_idx" already exists
STATEMENT: CREATE INDEX message_thread_idx on messages(thread_id);
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgadmin-hackers) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgadmin-hackers', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgadmin-support) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgadmin-support', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-admin) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-admin', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-advocacy) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-advocacy', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-announce) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-announce', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-benchmarks) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-benchmarks', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-bugs) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-bugs', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-cluster-hackers) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-cluster-hackers', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-committers) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-committers', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-cygwin) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-cygwin', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-docs) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-docs', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-general) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-general', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-hackers) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-hackers', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-hackers-pitr) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-hackers-pitr', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-hackers-win32) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-hackers-win32', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-chat) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-chat', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-interfaces) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-interfaces', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-jdbc) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-jdbc', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-jobs) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-jobs', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-novice) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-novice', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-odbc) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-odbc', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-patches) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-patches', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-performance) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-performance', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-php) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-php', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-pkg-debian) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-pkg-debian', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-pkg-yum) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-pkg-yum', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-ports) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-ports', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-rrreviewers) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-rrreviewers', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-sql) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-sql', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-students) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-students', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-testers) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-testers', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(pgsql-www) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('pgsql-www', 'english');
ERROR: duplicate key value violates unique constraint "lists_pkey"
DETAIL: Key (id)=(psycopg) already exists.
STATEMENT: INSERT INTO lists (id, language) VALUES ('psycopg', 'english');
ERROR: duplicate key value violates unique constraint "messages_hash_id_key"
DETAIL: Key (hash_id)=(2acbc450260904fa1a1cbb23e8d1488f) already exists.
STATEMENT: INSERT INTO messages (list, message_id, in_reply_to, refs, sent, subject, author, body_plain, headers, raw_message) VALUES ('pgsql-hackers', NULL, NULL, NULL, 'Jan 6 14:52:38 1998', 'Re: [PATCHES] [HACKERS] Patch for psql.c', 'Igor <igor@sba.miami.edu>', 'Yea..supposedly free''ing nulls is ok...but PQclear sends a nasty error
message if a value sent to it is null...I would agree with you that it
shouldn''t be that big of a deal (unless to shut up Purify) to free
nulls..In fact, we should probably patch PQclear to simply return
if result passed to it is null...
Actually, free(NULL) is not supported by all compilers so it might be
safer to make sure we''re not freeing any nulls....One interesting thing to
do would be to write a C program that executes every function in libpq
repeatedly, and under Purify''s watch....Sort of purity test for libpq..:)
=+=------------------------/\---------------------------------=+=
Igor Natanzon |**| E-mail: igor@sba.miami.edu
=+=------------------------\/---------------------------------=+=
On Mon, 2 Jun 1997, James A. Hillyerd wrote:
> On Mon, 2 Jun 1997, Igor wrote:
>
> > Well...I put these PQclear''s in because there were memory leaks in that
> > area. If you put it outside, of the case statement, you start clearing
> > already freed tuples causing a mess (Marc.. that was the reason why
> > regression screwed up on boolean). Now, there are a few more memory leaks
> > (particularly with PGsettings *settings variable) but it doesn''t leak much
> > and will be left alone until 6.2 probably.
> > I guess if you do if(results) PQclear (results)...oh well...There are no
> > memory leaks from PQexec anymore so might just as well leave it alone for
> > now.
> >
>
> I don''t know if everything knows this or not, but free()ing a null pointer
> is valid, so it may be easier to set pointers to null after freeing them,
> then there wouldn''t be much penalty in free()ing them again.
>
> I guess that is sloppy coding though. =)
>
> -james
>
> [ James A. Hillyerd (JH2162) james@blarg.net - Perl Programmer ]
> [ http://www.blarg.net/~james/ http://www.pimedia.com/ ]
> [ 1024/B11C3751 CA 1C B3 A9 07 2F 57 C9 91 F4 73 F2 19 A4 C5 88 ]
>
------------------------------', hstore(ARRAY['date', 'from', 'subject'], ARRAY['Mon, 2 Jun 1997 19:22:24 -0400 (EDT)', 'Igor <igor@sba.miami.edu>', 'Re: [PATCHES] [HACKERS] Patch for psql.c']), 'From igor@sba.miami.edu Tue Jan 6 14:52:38 1998
Date: Mon, 2 Jun 1997 19:22:24 -0400 (EDT)
From: Igor <igor@sba.miami.edu>
Subject: Re: [PATCHES] [HACKERS] Patch for psql.c
Yea..supposedly free''ing nulls is ok...but PQclear sends a nasty error
message if a value sent to it is null...I would agree with you that it
shouldn''t be that big of a deal (unless to shut up Purify) to free
nulls..In fact, we should probably patch PQclear to simply return
if result passed to it is null...
Actually, free(NULL) is not supported by all compilers so it might be
safer to make sure we''re not freeing any nulls....One interesting thing to
do would be to write a C program that executes every function in libpq
repeatedly, and under Purify''s watch....Sort of purity test for libpq..:)
=+=------------------------/\---------------------------------=+=
Igor Natanzon |**| E-mail: igor@sba.miami.edu
=+=------------------------\/---------------------------------=+=
On Mon, 2 Jun 1997, James A. Hillyerd wrote:
> On Mon, 2 Jun 1997, Igor wrote:
>
> > Well...I put these PQclear''s in because there were memory leaks in that
> > area. If you put it outside, of the case statement, you start clearing
> > already freed tuples causing a mess (Marc.. that was the reason why
> > regression screwed up on boolean). Now, there are a few more memory leaks
> > (particularly with PGsettings *settings variable) but it doesn''t leak much
> > and will be left alone until 6.2 probably.
> > I guess if you do if(results) PQclear (results)...oh well...There are no
> > memory leaks from PQexec anymore so might just as well leave it alone for
> > now.
> >
>
> I don''t know if everything knows this or not, but free()ing a null pointer
> is valid, so it may be easier to set pointers to null after freeing them,
> then there wouldn''t be much penalty in free()ing them again.
>
> I guess that is sloppy coding though. =)
>
> -james
>
> [ James A. Hillyerd (JH2162) james@blarg.net - Perl Programmer ]
> [ http://www.blarg.net/~james/ http://www.pimedia.com/ ]
> [ 1024/B11C3751 CA 1C B3 A9 07 2F 57 C9 91 F4 73 F2 19 A4 C5 88 ]
>
------------------------------
')
LOG: checkpoints are occurring too frequently (16 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: checkpoints are occurring too frequently (13 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: sending cancel to blocking autovacuum PID 26243
DETAIL: Process 26229 waits for ShareUpdateExclusiveLock on relation 98440 of database 98309.
STATEMENT: ANALYZE messages
ERROR: canceling autovacuum task
CONTEXT: automatic analyze of table "archie.public.messages"
LOG: checkpoints are occurring too frequently (16 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: checkpoints are occurring too frequently (14 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: checkpoints are occurring too frequently (14 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: checkpoints are occurring too frequently (16 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
PANIC: not enough space in leaf page!
STATEMENT: INSERT INTO messages (list, message_id, in_reply_to, refs, sent, subject, author, body_plain, headers, raw_message) VALUES ('pgsql-hackers', 'Pine.LNX.4.21.0108100903010.6384-100000@bee.proximity.com.au', ARRAY['Pine.LNX.4.30.0108100020320.11408-100000@peter.localdomain'], NULL, 'Aug 9 19:36:26 2001', 'Re: Vague idea for allowing per-column locale', 'Tim Allen <tim@proximity.com.au>', 'On Fri, 10 Aug 2001, Peter Eisentraut wrote:
> We have realized that allowing per-column locale would be difficult with
> the existing C library interface, because setlocale() is a pretty
> inefficient operation. But I think what we could allow, and what would be
> fairly useful, is the choice between the plain C locale and one "real"
> locale of choice (as determined by initdb) on a column or datum basis.
Yes, the C library locale notion is somewhat broken, or at best limited,
imho. It doesn''t fit at all well with the needs of a server that can have
clients in different locales, or even clients in the same place who have
different locale preferences. I guess it''s a pre-ubiquitous-internet
concept. If you keep stretching this idea beyond the model that it
comfortably supports, life will become steadily more difficult, and it may
be better to give up on that model altogether.
A different idea related to this is to treat different text
representations as different data types. In the case of different
multi-byte text representations, this definitely makes sense; in the case
of just different locales for essentially the same character set it might
not be as obviously beneficial, but still merits some consideration, imho.
For converting, say, utf8 to euc-jp, it would be nice to be able to make
use of all the existing infrastructure that PostgreSQL has for type
conversion and type identification. It''d be even nicer if you could make a
table that has, say, one column utf8 (or utf32 even), one column euc-jp
and one column shift-jis, so that you could cache format conversions.
> One possible way to implement this is to set or clear a bit somewhere in
> the header of each text (char, varchar) type datum, depending on what you
> want. Basically, this bit is going to be part of the atttypmod. Then the
> comparison operators would use strcoll or strcmp, depending on the choice,
> and similarly for other functions that are locale-aware.
Under my grand plan one would have to implement comparison operators for
each data type (as well as all the other things one has to implement for a
data type); then it should Just Work, because postgres would know what
comparison to use for each column.
> Does anyone see a problem with this, aside from the fact that this breaks
> the internal representation of the character types (which might have to
> happen anyway if we ever want to do something in this direction)?
> (If this is an acceptable plan then we could tie this in with the proposed
> work of making the LIKE optimization work. We wouldn''t have to make up
> new ugly-named operators, we''d just have to do a bit of plain old type
> casting.)
The separate data types notion would work here also, since one could
declare a column to be of plain vanilla ascii data type, with all
comparisons just a simple comparison of numerical values.
BTW, how does postgres store multibyte text? As char * with a multibyte
encoding? As 16 bit or 32 bit code points? I should of course just look at
the code and find out...:) I guess the former, from Peter''s earlier
comments. It does seem to me that using an explicit 32 bit representation
(or at least providing that as an option) would make life easier in many
ways.
Tim
--
-----------------------------------------------
Tim Allen tim@proximity.com.au
Proximity Pty Ltd http://www.proximity.com.au/
http://www4.tpg.com.au/users/rita_tim/', hstore(ARRAY['received', 'from', 'x-sequence-number', 'x-archive-number', 'cc', 'subject', 'to', 'in-reply-to', 'date', 'message-id', 'content-type', 'mime-version'], ARRAY['from gw.proximity.com.au (198.142.14.74.optusnet.com.au [198.142.14.74] (may be forged))
by postgresql.org (8.11.3/8.11.4) with ESMTP id f79NaOP38966
for <pgsql-hackers@postgresql.org>; Thu, 9 Aug 2001 19:36:24 -0400 (EDT)
(envelope-from tim@proximity.com.au),from bee.proximity.com.au (IDENT:root@bee.proximity.com.au [203.18.84.16])
by gw.proximity.com.au (8.9.3/8.9.3) with ESMTP id JAA08983;
Fri, 10 Aug 2001 09:36:19 +1000,from localhost (tim@localhost)
by bee.proximity.com.au (8.9.3/8.9.3) with ESMTP id JAA13148;
Fri, 10 Aug 2001 09:36:19 +1000', 'Tim Allen <tim@proximity.com.au>', '11978', '200108/315', 'PostgreSQL Development <pgsql-hackers@postgresql.org>', 'Re: Vague idea for allowing per-column locale', 'Peter Eisentraut <peter_e@gmx.net>', '<Pine.LNX.4.30.0108100020320.11408-100000@peter.localdomain>', 'Fri, 10 Aug 2001 09:36:19 +1000 (EST)', '<Pine.LNX.4.21.0108100903010.6384-100000@bee.proximity.com.au>', 'TEXT/PLAIN; charset=US-ASCII', '1.0']), 'From pgsql-hackers-owner@postgresql.org Thu Aug 9 19:36:26 2001
Received: from gw.proximity.com.au (198.142.14.74.optusnet.com.au
[198.142.14.74] (may be forged))
by postgresql.org (8.11.3/8.11.4) with ESMTP id f79NaOP38966
for <pgsql-hackers@postgresql.org>; Thu, 9 Aug 2001 19:36:24 -0400 (EDT)
(envelope-from tim@proximity.com.au)
Received: from bee.proximity.com.au (IDENT:root@bee.proximity.com.au
[203.18.84.16])
by gw.proximity.com.au (8.9.3/8.9.3) with ESMTP id JAA08983;
Fri, 10 Aug 2001 09:36:19 +1000
Received: from localhost (tim@localhost)
by bee.proximity.com.au (8.9.3/8.9.3) with ESMTP id JAA13148;
Fri, 10 Aug 2001 09:36:19 +1000
Date: Fri, 10 Aug 2001 09:36:19 +1000 (EST)
From: Tim Allen <tim@proximity.com.au>
To: Peter Eisentraut <peter_e@gmx.net>
cc: PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: Vague idea for allowing per-column locale
In-Reply-To: <Pine.LNX.4.30.0108100020320.11408-100000@peter.localdomain>
Message-ID: <Pine.LNX.4.21.0108100903010.6384-100000@bee.proximity.com.au>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200108/315
X-Sequence-Number: 11978
On Fri, 10 Aug 2001, Peter Eisentraut wrote:
> We have realized that allowing per-column locale would be difficult with
> the existing C library interface, because setlocale() is a pretty
> inefficient operation. But I think what we could allow, and what would be
> fairly useful, is the choice between the plain C locale and one "real"
> locale of choice (as determined by initdb) on a column or datum basis.
Yes, the C library locale notion is somewhat broken, or at best limited,
imho. It doesn''t fit at all well with the needs of a server that can have
clients in different locales, or even clients in the same place who have
different locale preferences. I guess it''s a pre-ubiquitous-internet
concept. If you keep stretching this idea beyond the model that it
comfortably supports, life will become steadily more difficult, and it may
be better to give up on that model altogether.
A different idea related to this is to treat different text
representations as different data types. In the case of different
multi-byte text representations, this definitely makes sense; in the case
of just different locales for essentially the same character set it might
not be as obviously beneficial, but still merits some consideration, imho.
For converting, say, utf8 to euc-jp, it would be nice to be able to make
use of all the existing infrastructure that PostgreSQL has for type
conversion and type identification. It''d be even nicer if you could make a
table that has, say, one column utf8 (or utf32 even), one column euc-jp
and one column shift-jis, so that you could cache format conversions.
> One possible way to implement this is to set or clear a bit somewhere in
> the header of each text (char, varchar) type datum, depending on what you
> want. Basically, this bit is going to be part of the atttypmod. Then the
> comparison operators would use strcoll or strcmp, depending on the choice,
> and similarly for other functions that are locale-aware.
Under my grand plan one would have to implement comparison operators for
each data type (as well as all the other things one has to implement for a
data type); then it should Just Work, because postgres would know what
comparison to use for each column.
> Does anyone see a problem with this, aside from the fact that this breaks
> the internal representation of the character types (which might have to
> happen anyway if we ever want to do something in this direction)?
> (If this is an acceptable plan then we could tie this in with the proposed
> work of making the LIKE optimization work. We wouldn''t have to make up
> new ugly-named operators, we''d just have to do a bit of plain old type
> casting.)
The separate data types notion would work here also, since one could
declare a column to be of plain vanilla ascii data type, with all
comparisons just a simple comparison of numerical values.
BTW, how does postgres store multibyte text? As char * with a multibyte
encoding? As 16 bit or 32 bit code points? I should of course just look at
the code and find out...:) I guess the former, from Peter''s earlier
comments. It does seem to me that using an explicit 32 bit representation
(or at least providing that as an option) would make life easier in many
ways.
Tim
--
-----------------------------------------------
Tim Allen tim@proximity.com.au
Proximity Pty Ltd http://www.proximity.com.au/
http://www4.tpg.com.au/users/rita_tim/
')
LOG: server process (PID 26229) was terminated by signal 6: Aborted
DETAIL: Failed process was running: INSERT INTO messages (list, message_id, in_reply_to, refs, sent, subject, author, body_plain, headers, raw_message) VALUES ('pgsql-hackers', 'Pine.LNX.4.21.0108100903010.6384-100000@bee.proximity.com.au', ARRAY['Pine.LNX.4.30.0108100020320.11408-100000@peter.localdomain'], NULL, 'Aug 9 19:36:26 2001', 'Re: Vague idea for allowing per-column locale', 'Tim Allen <tim@proximity.com.au>', 'On Fri, 10 Aug 2001, Peter Eisentraut wrote:
> We have realized that allowing per-column locale would be difficult with
> the existing C library interface, because setlocale() is a pretty
> inefficient operation. But I think what we could allow, and what would be
> fairly useful, is the choice between the plain C locale and one "real"
> locale of choice (as determined by initdb) on a column or datum basis.
Yes, the C library locale notion is somewhat broken, or at best limited,
imho. It doesn''t fit at all well with the needs of a server that can have
clients in different locales, or even clients in the same place who have
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2013-10-06 01:25:20 CEST
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/2B0094B0
*** buffer overflow detected ***: postgres: startup process recovering 00000001000000000000002C terminated
======= Backtrace: =========
/lib64/libc.so.6(__fortify_fail+0x37)[0x7fb3c0c0c7a7]
/lib64/libc.so.6(+0xff5a0)[0x7fb3c0c0a5a0]
postgres: startup process recovering 00000001000000000000002C[0x46e642]
postgres: startup process recovering 00000001000000000000002C(gin_redo+0x4fa)[0x46edca]
postgres: startup process recovering 00000001000000000000002C(StartupXLOG+0xbc6)[0x4c5bc6]
postgres: startup process recovering 00000001000000000000002C(StartupProcessMain+0xef)[0x61fa7f]
postgres: startup process recovering 00000001000000000000002C(AuxiliaryProcessMain+0x52f)[0x4d02cf]
postgres: startup process recovering 00000001000000000000002C[0x61d056]
postgres: startup process recovering 00000001000000000000002C[0x61d195]
postgres: startup process recovering 00000001000000000000002C[0x61df05]
/lib64/libc.so.6(+0x35b90)[0x7fb3c0b40b90]
/lib64/libc.so.6(__select+0x13)[0x7fb3c0bf00e3]
postgres: startup process recovering 00000001000000000000002C[0x464d31]
postgres: startup process recovering 00000001000000000000002C(PostmasterMain+0xea3)[0x61f553]
postgres: startup process recovering 00000001000000000000002C(main+0x365)[0x465cd5]
/lib64/libc.so.6(__libc_start_main+0xfd)[0x7fb3c0b2d4bd]
postgres: startup process recovering 00000001000000000000002C[0x465d89]
======= Memory map: ========
00400000-0090e000 r-xp 00000000 08:03 6564024 /home/tomas/pg-devel/bin/postgres
00b0d000-00b0e000 r--p 0050d000 08:03 6564024 /home/tomas/pg-devel/bin/postgres
00b0e000-00b1c000 rw-p 0050e000 08:03 6564024 /home/tomas/pg-devel/bin/postgres
00b1c000-00b73000 rw-p 00000000 00:00 0
00bd8000-00c17000 rw-p 00000000 00:00 0 [heap]
00c17000-00c28000 rw-p 00000000 00:00 0 [heap]
7fb33beac000-7fb33bec1000 r-xp 00000000 08:03 4897146 /usr/lib64/gcc/x86_64-pc-linux-gnu/4.7.3/libgcc_s.so.1
7fb33bec1000-7fb33c0c0000 ---p 00015000 08:03 4897146 /usr/lib64/gcc/x86_64-pc-linux-gnu/4.7.3/libgcc_s.so.1
7fb33c0c0000-7fb33c0c1000 r--p 00014000 08:03 4897146 /usr/lib64/gcc/x86_64-pc-linux-gnu/4.7.3/libgcc_s.so.1
7fb33c0c1000-7fb33c0c2000 rw-p 00015000 08:03 4897146 /usr/lib64/gcc/x86_64-pc-linux-gnu/4.7.3/libgcc_s.so.1
7fb33c0c2000-7fb3c071c000 rw-s 00000000 00:04 150240 /dev/zero (deleted)
7fb3c071c000-7fb3c0728000 r-xp 00000000 08:03 3542900 /lib64/libnss_files-2.15.so
7fb3c0728000-7fb3c0928000 ---p 0000c000 08:03 3542900 /lib64/libnss_files-2.15.so
7fb3c0928000-7fb3c0929000 r--p 0000c000 08:03 3542900 /lib64/libnss_files-2.15.so
7fb3c0929000-7fb3c092a000 rw-p 0000d000 08:03 3542900 /lib64/libnss_files-2.15.so
7fb3c092a000-7fb3c0b0b000 r--p 00000000 08:03 4864412 /usr/lib64/locale/locale-archive
7fb3c0b0b000-7fb3c0cac000 r-xp 00000000 08:03 3542892 /lib64/libc-2.15.so
7fb3c0cac000-7fb3c0eac000 ---p 001a1000 08:03 3542892 /lib64/libc-2.15.so
7fb3c0eac000-7fb3c0eb0000 r--p 001a1000 08:03 3542892 /lib64/libc-2.15.so
7fb3c0eb0000-7fb3c0eb2000 rw-p 001a5000 08:03 3542892 /lib64/libc-2.15.so
7fb3c0eb2000-7fb3c0eb6000 rw-p 00000000 00:00 0
7fb3c0eb6000-7fb3c0fac000 r-xp 00000000 08:03 3542905 /lib64/libm-2.15.so
7fb3c0fac000-7fb3c11ab000 ---p 000f6000 08:03 3542905 /lib64/libm-2.15.so
7fb3c11ab000-7fb3c11ac000 r--p 000f5000 08:03 3542905 /lib64/libm-2.15.so
7fb3c11ac000-7fb3c11ad000 rw-p 000f6000 08:03 3542905 /lib64/libm-2.15.so
7fb3c11ad000-7fb3c11af000 r-xp 00000000 08:03 3542459 /lib64/libdl-2.15.so
7fb3c11af000-7fb3c13af000 ---p 00002000 08:03 3542459 /lib64/libdl-2.15.so
7fb3c13af000-7fb3c13b0000 r--p 00002000 08:03 3542459 /lib64/libdl-2.15.so
7fb3c13b0000-7fb3c13b1000 rw-p 00003000 08:03 3542459 /lib64/libdl-2.15.so
7fb3c13b1000-7fb3c13d3000 r-xp 00000000 08:03 3542893 /lib64/ld-2.15.so
7fb3c148d000-7fb3c158e000 rw-p 00000000 00:00 0
7fb3c158e000-7fb3c1592000 rw-p 00000000 00:00 0
7fb3c15cf000-7fb3c15d0000 rw-p 00000000 00:00 0
7fb3c15d0000-7fb3c15d1000 rw-s 00000000 00:04 13303808 /SYSV0052e2c1 (deleted)
7fb3c15d1000-7fb3c15d2000 rw-p 00000000 00:00 0
7fb3c15d2000-7fb3c15d3000 r--p 00021000 08:03 3542893 /lib64/ld-2.15.so
7fb3c15d3000-7fb3c15d4000 rw-p 00022000 08:03 3542893 /lib64/ld-2.15.so
7fb3c15d4000-7fb3c15d5000 rw-p 00000000 00:00 0
7ffff6c1d000-7ffff6c43000 rw-p 00000000 00:00 0 [stack]
7ffff6cd2000-7ffff6cd3000 r-xp 00000000 00:00 0 [vdso]
ffffffffff600000-ffffffffff601000 r-xp 00000000 00:00 0 [vsyscall]
LOG: startup process (PID 26259) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
LOG: database system was interrupted while in recovery at 2013-10-06 01:26:48 CEST
HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery.
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/2B0094B0
LOG: startup process (PID 12237) was terminated by signal 11: Segmentation fault
LOG: aborting startup due to startup process failure
LOG: database system was interrupted while in recovery at 2013-10-06 01:32:25 CEST
HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery.
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/2B0094B0
LOG: startup process (PID 12242) was terminated by signal 11: Segmentation fault
LOG: aborting startup due to startup process failure
LOG: database system was interrupted while in recovery at 2013-10-06 01:32:28 CEST
HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery.
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/2B0094B0
LOG: startup process (PID 12248) was terminated by signal 11: Segmentation fault
LOG: aborting startup due to startup process failure
LOG: database system was shut down at 2013-10-06 01:33:26 CEST
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
ERROR: duplicate key value violates unique constraint "messages_hash_id_key"
DETAIL: Key (hash_id)=(2acbc450260904fa1a1cbb23e8d1488f) already exists.
STATEMENT: INSERT INTO messages (list, message_id, in_reply_to, refs, sent, subject, author, body_plain, headers, raw_message) VALUES ('pgsql-hackers', NULL, NULL, NULL, 'Jan 6 14:52:38 1998', 'Re: [PATCHES] [HACKERS] Patch for psql.c', 'Igor <igor@sba.miami.edu>', 'Yea..supposedly free''ing nulls is ok...but PQclear sends a nasty error
message if a value sent to it is null...I would agree with you that it
shouldn''t be that big of a deal (unless to shut up Purify) to free
nulls..In fact, we should probably patch PQclear to simply return
if result passed to it is null...
Actually, free(NULL) is not supported by all compilers so it might be
safer to make sure we''re not freeing any nulls....One interesting thing to
do would be to write a C program that executes every function in libpq
repeatedly, and under Purify''s watch....Sort of purity test for libpq..:)
=+=------------------------/\---------------------------------=+=
Igor Natanzon |**| E-mail: igor@sba.miami.edu
=+=------------------------\/---------------------------------=+=
On Mon, 2 Jun 1997, James A. Hillyerd wrote:
> On Mon, 2 Jun 1997, Igor wrote:
>
> > Well...I put these PQclear''s in because there were memory leaks in that
> > area. If you put it outside, of the case statement, you start clearing
> > already freed tuples causing a mess (Marc.. that was the reason why
> > regression screwed up on boolean). Now, there are a few more memory leaks
> > (particularly with PGsettings *settings variable) but it doesn''t leak much
> > and will be left alone until 6.2 probably.
> > I guess if you do if(results) PQclear (results)...oh well...There are no
> > memory leaks from PQexec anymore so might just as well leave it alone for
> > now.
> >
>
> I don''t know if everything knows this or not, but free()ing a null pointer
> is valid, so it may be easier to set pointers to null after freeing them,
> then there wouldn''t be much penalty in free()ing them again.
>
> I guess that is sloppy coding though. =)
>
> -james
>
> [ James A. Hillyerd (JH2162) james@blarg.net - Perl Programmer ]
> [ http://www.blarg.net/~james/ http://www.pimedia.com/ ]
> [ 1024/B11C3751 CA 1C B3 A9 07 2F 57 C9 91 F4 73 F2 19 A4 C5 88 ]
>
------------------------------', hstore(ARRAY['date', 'from', 'subject'], ARRAY['Mon, 2 Jun 1997 19:22:24 -0400 (EDT)', 'Igor <igor@sba.miami.edu>', 'Re: [PATCHES] [HACKERS] Patch for psql.c']), 'From igor@sba.miami.edu Tue Jan 6 14:52:38 1998
Date: Mon, 2 Jun 1997 19:22:24 -0400 (EDT)
From: Igor <igor@sba.miami.edu>
Subject: Re: [PATCHES] [HACKERS] Patch for psql.c
Yea..supposedly free''ing nulls is ok...but PQclear sends a nasty error
message if a value sent to it is null...I would agree with you that it
shouldn''t be that big of a deal (unless to shut up Purify) to free
nulls..In fact, we should probably patch PQclear to simply return
if result passed to it is null...
Actually, free(NULL) is not supported by all compilers so it might be
safer to make sure we''re not freeing any nulls....One interesting thing to
do would be to write a C program that executes every function in libpq
repeatedly, and under Purify''s watch....Sort of purity test for libpq..:)
=+=------------------------/\---------------------------------=+=
Igor Natanzon |**| E-mail: igor@sba.miami.edu
=+=------------------------\/---------------------------------=+=
On Mon, 2 Jun 1997, James A. Hillyerd wrote:
> On Mon, 2 Jun 1997, Igor wrote:
>
> > Well...I put these PQclear''s in because there were memory leaks in that
> > area. If you put it outside, of the case statement, you start clearing
> > already freed tuples causing a mess (Marc.. that was the reason why
> > regression screwed up on boolean). Now, there are a few more memory leaks
> > (particularly with PGsettings *settings variable) but it doesn''t leak much
> > and will be left alone until 6.2 probably.
> > I guess if you do if(results) PQclear (results)...oh well...There are no
> > memory leaks from PQexec anymore so might just as well leave it alone for
> > now.
> >
>
> I don''t know if everything knows this or not, but free()ing a null pointer
> is valid, so it may be easier to set pointers to null after freeing them,
> then there wouldn''t be much penalty in free()ing them again.
>
> I guess that is sloppy coding though. =)
>
> -james
>
> [ James A. Hillyerd (JH2162) james@blarg.net - Perl Programmer ]
> [ http://www.blarg.net/~james/ http://www.pimedia.com/ ]
> [ 1024/B11C3751 CA 1C B3 A9 07 2F 57 C9 91 F4 73 F2 19 A4 C5 88 ]
>
------------------------------
')
LOG: checkpoints are occurring too frequently (19 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: checkpoints are occurring too frequently (18 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
PANIC: not enough space in leaf page!
LOG: server process (PID 12326) was terminated by signal 6: Aborted
DETAIL: Failed process was running: autovacuum: ANALYZE public.messages
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
LOG: select() failed in postmaster: Nepřípustný argument
LOG: database system was interrupted; last known up at 2013-10-06 01:35:11 CEST
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/A022DB0
LOG: startup process (PID 12349) was terminated by signal 11: Segmentation fault
LOG: aborting startup due to startup process failure
LOG: database system was shut down at 2013-10-06 01:38:05 CEST
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
ERROR: duplicate key value violates unique constraint "messages_hash_id_key"
DETAIL: Key (hash_id)=(2acbc450260904fa1a1cbb23e8d1488f) already exists.
STATEMENT: INSERT INTO messages (list, message_id, in_reply_to, refs, sent, subject, author, body_plain, headers, raw_message) VALUES ('pgsql-hackers', NULL, NULL, NULL, 'Jan 6 14:52:38 1998', 'Re: [PATCHES] [HACKERS] Patch for psql.c', 'Igor <igor@sba.miami.edu>', 'Yea..supposedly free''ing nulls is ok...but PQclear sends a nasty error
message if a value sent to it is null...I would agree with you that it
shouldn''t be that big of a deal (unless to shut up Purify) to free
nulls..In fact, we should probably patch PQclear to simply return
if result passed to it is null...
Actually, free(NULL) is not supported by all compilers so it might be
safer to make sure we''re not freeing any nulls....One interesting thing to
do would be to write a C program that executes every function in libpq
repeatedly, and under Purify''s watch....Sort of purity test for libpq..:)
=+=------------------------/\---------------------------------=+=
Igor Natanzon |**| E-mail: igor@sba.miami.edu
=+=------------------------\/---------------------------------=+=
On Mon, 2 Jun 1997, James A. Hillyerd wrote:
> On Mon, 2 Jun 1997, Igor wrote:
>
> > Well...I put these PQclear''s in because there were memory leaks in that
> > area. If you put it outside, of the case statement, you start clearing
> > already freed tuples causing a mess (Marc.. that was the reason why
> > regression screwed up on boolean). Now, there are a few more memory leaks
> > (particularly with PGsettings *settings variable) but it doesn''t leak much
> > and will be left alone until 6.2 probably.
> > I guess if you do if(results) PQclear (results)...oh well...There are no
> > memory leaks from PQexec anymore so might just as well leave it alone for
> > now.
> >
>
> I don''t know if everything knows this or not, but free()ing a null pointer
> is valid, so it may be easier to set pointers to null after freeing them,
> then there wouldn''t be much penalty in free()ing them again.
>
> I guess that is sloppy coding though. =)
>
> -james
>
> [ James A. Hillyerd (JH2162) james@blarg.net - Perl Programmer ]
> [ http://www.blarg.net/~james/ http://www.pimedia.com/ ]
> [ 1024/B11C3751 CA 1C B3 A9 07 2F 57 C9 91 F4 73 F2 19 A4 C5 88 ]
>
------------------------------', hstore(ARRAY['date', 'from', 'subject'], ARRAY['Mon, 2 Jun 1997 19:22:24 -0400 (EDT)', 'Igor <igor@sba.miami.edu>', 'Re: [PATCHES] [HACKERS] Patch for psql.c']), 'From igor@sba.miami.edu Tue Jan 6 14:52:38 1998
Date: Mon, 2 Jun 1997 19:22:24 -0400 (EDT)
From: Igor <igor@sba.miami.edu>
Subject: Re: [PATCHES] [HACKERS] Patch for psql.c
Yea..supposedly free''ing nulls is ok...but PQclear sends a nasty error
message if a value sent to it is null...I would agree with you that it
shouldn''t be that big of a deal (unless to shut up Purify) to free
nulls..In fact, we should probably patch PQclear to simply return
if result passed to it is null...
Actually, free(NULL) is not supported by all compilers so it might be
safer to make sure we''re not freeing any nulls....One interesting thing to
do would be to write a C program that executes every function in libpq
repeatedly, and under Purify''s watch....Sort of purity test for libpq..:)
=+=------------------------/\---------------------------------=+=
Igor Natanzon |**| E-mail: igor@sba.miami.edu
=+=------------------------\/---------------------------------=+=
On Mon, 2 Jun 1997, James A. Hillyerd wrote:
> On Mon, 2 Jun 1997, Igor wrote:
>
> > Well...I put these PQclear''s in because there were memory leaks in that
> > area. If you put it outside, of the case statement, you start clearing
> > already freed tuples causing a mess (Marc.. that was the reason why
> > regression screwed up on boolean). Now, there are a few more memory leaks
> > (particularly with PGsettings *settings variable) but it doesn''t leak much
> > and will be left alone until 6.2 probably.
> > I guess if you do if(results) PQclear (results)...oh well...There are no
> > memory leaks from PQexec anymore so might just as well leave it alone for
> > now.
> >
>
> I don''t know if everything knows this or not, but free()ing a null pointer
> is valid, so it may be easier to set pointers to null after freeing them,
> then there wouldn''t be much penalty in free()ing them again.
>
> I guess that is sloppy coding though. =)
>
> -james
>
> [ James A. Hillyerd (JH2162) james@blarg.net - Perl Programmer ]
> [ http://www.blarg.net/~james/ http://www.pimedia.com/ ]
> [ 1024/B11C3751 CA 1C B3 A9 07 2F 57 C9 91 F4 73 F2 19 A4 C5 88 ]
>
------------------------------
')
LOG: checkpoints are occurring too frequently (27 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: checkpoints are occurring too frequently (24 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: checkpoints are occurring too frequently (18 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
LOG: checkpoints are occurring too frequently (19 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
PANIC: not enough space in leaf page!
LOG: server process (PID 12417) was terminated by signal 6: Aborted
DETAIL: Failed process was running: autovacuum: ANALYZE public.messages
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
LOG: select() failed in postmaster: Nepřípustný argument
LOG: database system was interrupted; last known up at 2013-10-06 01:39:38 CEST
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/A013300
LOG: startup process (PID 12441) was terminated by signal 11: Segmentation fault
LOG: aborting startup due to startup process failure
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: GIN improvements part 1: additional information
In-Reply-To: <5250A7A1.2010701@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox