[HACKERS] Annotated release notes
OK, I have committed changes to release.sgml so most complex entries have a paragraph describing the change. You can see the result at: http://candle.pha.pa.us/main/writings/pgsql/sgml/release.html#RELEASE-7-4 I need people to check this and help me with the items marked 'bjm'. I am confused about the proper text for those sections. --- Tatsuo Ishii wrote: Tatsuo Ishii wrote: I've been pushing this agenda for a few releases now, but some people have been, er, boycotting it. I think, too, that release notes *must* be written incrementally at the same time that the feature change is made. This is the only way we can get accurate and complete release notes, and the descriptions could even include some context, some motivations, etc. We have release cycles of 10 months, and there is no way we can make sensible release notes by gathering individual commit messages over that period of time. Heck, ECPG has a full Informix compatibility mode and there is no mention of that anywhere, because there was no commit Add Informix mode. I suggest we just do it like the documentation: If you don't document it, it doesn't exist. If you don't write a line for the release notes, it doesn't exist either. I tend to agree it. For every release I and my colleague have been working on creating detailed release notes (of course in Japanese), otherwise we cannot tell people what are changed, added or fixed since there is little info in the official release note. This is painful since we have to dig into the mail archives and cvs commit messages to look for what each item of the official release note actually means. These work take at least 2 to 3 weeks with several people involved. The hardest part is what are fixed. The only useful information seems to be the cvs commit messages, however typical messages are something like see recent discussions in the mail archive for more details. This is not very helpful at least for me. Once I proposed that we add a sequence number to each mail and the commit messages point to the number. This way we could easily trace what are the bug report and what are the actual intention for the fix. For some reason noboy was interested in. Maybe this is due to coulture gap... (In Japan giving a sequence number to each mail in mailing lists is quite common). OK, if Tatsuo and SRA are having problems, I have to address it. I can supply a more detailed list to Tatsuo/SRA, or I can beef up the release notes to contain more information. Seems some in the community would like to have this detail so I might as well do it and have it in the official docs. One idea would be to add a section at the bottom of the release notes that goes into detail on changes listed in the release notes above --- that way, people can still skim the 300-line release notes, and if they want detailed information about the optimizer changes or subtle pg_dump fixes, that will be at the bottom. How does that sound? I can start on this for 7.4 next week. It basically means going through the CVS logs again and pulling out additional details. Sounds good. However this kind of information could become huge and I am afraid it does not suite well in the official docs in the source tree. I think putiing it in somewhere in a web site (maybe http://developer.postgresql.org/?) might be more appropreate. What do you think? -- Tatsuo Ishii -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [DOCS] Annotated release notes
Bruce Momjian wrote:
http://candle.pha.pa.us/main/writings/pgsql/sgml/release.html#RELEASE-7-4
I need people to check this and help me with the items marked 'bjm'. I
am confused about the proper text for those sections.
Allow polymorphic SQL functions (Joe)
bjm ??
What isn't clear about this? Should/can we refer to related sections of
the manual?
http://developer.postgresql.org/docs/postgres/xfunc-sql.html#AEN28722
http://developer.postgresql.org/docs/postgres/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC
Allow user defined aggregates to use polymorphic functions (Joe)
bjm ??
Same question. From this url:
http://developer.postgresql.org/docs/postgres/xaggr.html
see this paragraph:
Aggregate functions may use polymorphic state transition functions or
final functions, so that the same functions can be used to implement
multiple aggregates. See Section 33.2.1 for an explanation of
polymorphic functions. Going a step further, the aggregate function
itself may be specified with a polymorphic base type and state type,
allowing a single aggregate definition to serve for multiple input data
types. Here is an example of a polymorphic aggregate:
CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 and 7.3.5 showstopper
Jan Wieck wrote:
Confirmed, that's a bug - pgsql-hackers CC'd and scipt for full
reproduction attached.
Assumptions where correct, bug fixed in REL7_3_STABLE and HEAD. I also
added a slightly modified version of the script that reproduced the bug
to the foreign_key regression test.
Jan
This can also be reproduced in 7.4-beta5.
My guess out of the blue would be, that the rewriter expands the insert
into one insert with the where clause, one update with the negated where
clause. Executed in that order, they are both true ... first there is no
such row, the insert happens, second the row exists and is being updated.
IIRC the refint trigger queue run at the end of the whole statement
tries to heap_fetch() the originally inserted tuple, which is invisible
by that time. I seem to remember that the original version did fetch
them with some snapshot override mode to get it anyway and fire the
trigger. That apparently does not happen any more, so now the duty would
be up to the on update refint trigger which ... er ... recently got
fixed not to check non-changed key references any more ... duh.
I will look a bit deeper into it later tonight. I think if we let the on
update refint trigger check the referenced key again if the old tuple
has xmin = current_xid we should be fine.
Thanks for reporting, Michele. In the meantime, you might want to use a
BEFORE INSERT trigger in PL/pgSQL that tries to UPDATE the row and if
GET DIAGNOSTICS tells it it succeeded, returns NULL to suppress the
INSERT. That should work around the bug for the time being.
Jan
Michele Bendazzoli wrote:
On Thu, 2003-10-30 at 18:29, Jan Wieck wrote:
Not entirely. On which table(s) are the REFERENCES constraints and are
they separate per column constraints or are they multi-column constraints?
here are the constraints of the abilitazione table
ALTER TABLE public.abilitazione
ADD CONSTRAINT abilitazione_pkey PRIMARY KEY(comuneid, cassonettoid,
chiaveid);
ALTER TABLE public.abilitazione
ADD CONSTRAINT abilitazione_cassonettoid_fkey FOREIGN KEY (comuneid,
cassonettoid) REFERENCES public.cassonetto (comuneid, cassonettoid) ON
UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE public.abilitazione
ADD CONSTRAINT abilitazione_chiaveid_fkey FOREIGN KEY (comuneid,
chiaveid) REFERENCES public.chiave (comuneid, chiaveid) ON UPDATE
RESTRICT ON DELETE RESTRICT;
here those of cassonetto and chiave:
ALTER TABLE public.cassonetto
ADD CONSTRAINT cassonetto_pkey PRIMARY KEY(comuneid, cassonettoid);
ALTER TABLE public.chiave
ADD CONSTRAINT chiave_pkey PRIMARY KEY(comuneid, chiaveid);
I get the SQL from pgAdmin3 (great piece of sofware!;-)
It's usually best to cut'n'paste the CREATE TABLE or ALTER TABLE
statements that are used to create the constraints. That way we know
exactly what you're talking about.
Excuse me for the missing SQL, but i had tried to keep the message as
simple as possible.
The unique difference form when the exception was raised and now (that
it isn't) is the rule added:
CREATE OR REPLACE RULE abilita_ins_rl AS ON INSERT TO abilitazione
WHERE (EXISTS (
SELECT 1 FROM abilitazione
WHERE (((abilitazione.comuneid = new.comuneid )
AND (abilitazione.cassonettoid = new.cassonettoid ))
AND (abilitazione.chiaveid = new.chiaveid ù
DO INSTEAD UPDATE abilitazione SET abilitata = new.abilitata
WHERE (((abilitazione.comuneid = new.comuneid )
AND (abilitazione.cassonettoid = new.cassonettoid ))
AND (abilitazione.chiaveid = new.chiaveid ));
I hope now is more clear.
The version is that come with debian unstable (7.3.4 if I remember
correctly)
Thank you for the immediate responses
ciao, Michele
#!/bin/sh
DBNAME=testdb
export DBNAME
dropdb ${DBNAME}
createdb ${DBNAME}
psql -e ${DBNAME} _EOF_
create table t1 (
id1a integer,
id1b integer,
primary key (id1a, id1b)
);
create table t2 (
id2a integer,
id2c integer,
primary key (id2a, id2c)
);
create table t3 (
id3a integer,
id3b integer,
id3c integer,
data text,
primary key (id3a, id3b, id3c),
foreign key (id3a, id3b) references t1 (id1a, id1b),
foreign key (id3a, id3c) references t2 (id2a, id2c)
);
insert into t1 values (1, 11);
insert into t1 values (1, 12);
insert into t1 values (2, 21);
insert into t1 values (2, 22);
insert into t2 values (1, 11);
insert into t2 values (1, 12);
insert into t2 values (2, 21);
insert into t2 values (2, 22);
insert into t3 values (1, 11, 11, 'row1');
insert into t3 values (1, 11, 12, 'row2');
insert into t3 values (1, 12, 11, 'row3');
insert into t3 values (1, 12, 12, 'row4');
insert into t3 values (1, 11, 13, 'row5');
insert into t3 values (1, 13, 11, 'row6');
create rule t3_ins as on insert to t3
where (exists (select 1 from t3
where (((t3.id3a = new.id3a)
[HACKERS] 7.4 and 7.3.5 showstopper (was: Re: [SQL] Bug in Rule+Foreing key constrain?)
Confirmed, that's a bug - pgsql-hackers CC'd and scipt for full
reproduction attached.
This can also be reproduced in 7.4-beta5.
My guess out of the blue would be, that the rewriter expands the insert
into one insert with the where clause, one update with the negated where
clause. Executed in that order, they are both true ... first there is no
such row, the insert happens, second the row exists and is being updated.
IIRC the refint trigger queue run at the end of the whole statement
tries to heap_fetch() the originally inserted tuple, which is invisible
by that time. I seem to remember that the original version did fetch
them with some snapshot override mode to get it anyway and fire the
trigger. That apparently does not happen any more, so now the duty would
be up to the on update refint trigger which ... er ... recently got
fixed not to check non-changed key references any more ... duh.
I will look a bit deeper into it later tonight. I think if we let the on
update refint trigger check the referenced key again if the old tuple
has xmin = current_xid we should be fine.
Thanks for reporting, Michele. In the meantime, you might want to use a
BEFORE INSERT trigger in PL/pgSQL that tries to UPDATE the row and if
GET DIAGNOSTICS tells it it succeeded, returns NULL to suppress the
INSERT. That should work around the bug for the time being.
Jan
Michele Bendazzoli wrote:
On Thu, 2003-10-30 at 18:29, Jan Wieck wrote:
Not entirely. On which table(s) are the REFERENCES constraints and are
they separate per column constraints or are they multi-column constraints?
here are the constraints of the abilitazione table
ALTER TABLE public.abilitazione
ADD CONSTRAINT abilitazione_pkey PRIMARY KEY(comuneid, cassonettoid,
chiaveid);
ALTER TABLE public.abilitazione
ADD CONSTRAINT abilitazione_cassonettoid_fkey FOREIGN KEY (comuneid,
cassonettoid) REFERENCES public.cassonetto (comuneid, cassonettoid) ON
UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE public.abilitazione
ADD CONSTRAINT abilitazione_chiaveid_fkey FOREIGN KEY (comuneid,
chiaveid) REFERENCES public.chiave (comuneid, chiaveid) ON UPDATE
RESTRICT ON DELETE RESTRICT;
here those of cassonetto and chiave:
ALTER TABLE public.cassonetto
ADD CONSTRAINT cassonetto_pkey PRIMARY KEY(comuneid, cassonettoid);
ALTER TABLE public.chiave
ADD CONSTRAINT chiave_pkey PRIMARY KEY(comuneid, chiaveid);
I get the SQL from pgAdmin3 (great piece of sofware!;-)
It's usually best to cut'n'paste the CREATE TABLE or ALTER TABLE
statements that are used to create the constraints. That way we know
exactly what you're talking about.
Excuse me for the missing SQL, but i had tried to keep the message as
simple as possible.
The unique difference form when the exception was raised and now (that
it isn't) is the rule added:
CREATE OR REPLACE RULE abilita_ins_rl AS ON INSERT TO abilitazione
WHERE (EXISTS (
SELECT 1 FROM abilitazione
WHERE (((abilitazione.comuneid = new.comuneid )
AND (abilitazione.cassonettoid = new.cassonettoid ))
AND (abilitazione.chiaveid = new.chiaveid ù
DO INSTEAD UPDATE abilitazione SET abilitata = new.abilitata
WHERE (((abilitazione.comuneid = new.comuneid )
AND (abilitazione.cassonettoid = new.cassonettoid ))
AND (abilitazione.chiaveid = new.chiaveid ));
I hope now is more clear.
The version is that come with debian unstable (7.3.4 if I remember
correctly)
Thank you for the immediate responses
ciao, Michele
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
#!/bin/sh
DBNAME=testdb
export DBNAME
dropdb ${DBNAME}
createdb ${DBNAME}
psql -e ${DBNAME} _EOF_
create table t1 (
id1a integer,
id1b integer,
primary key (id1a, id1b)
);
create table t2 (
id2a integer,
id2c integer,
primary key (id2a, id2c)
);
create table t3 (
id3a integer,
id3b integer,
id3c integer,
data text,
primary key (id3a, id3b, id3c),
foreign key (id3a, id3b) references t1 (id1a, id1b),
foreign key (id3a, id3c) references t2 (id2a, id2c)
);
insert into t1 values (1, 11);
insert into t1 values (1, 12);
insert into t1 values (2, 21);
insert into t1 values (2, 22);
insert into t2 values (1, 11);
insert into t2 values (1, 12);
insert into t2 values (2, 21);
insert into t2 values (2, 22);
insert into t3 values (1, 11, 11, 'row1');
insert into t3 values (1, 11, 12, 'row2');
insert into t3 values (1, 12, 11, 'row3');
insert into t3 values (1, 12, 12, 'row4');
insert into t3 values (1, 11, 13, 'row5');
insert into t3 values (1, 13, 11, 'row6');
create rule t3_ins as on insert to t3
where (exists (select 1 from t3
where
Re: Fwd: Re: [HACKERS] Call for port reports
- Original Message - From: Tom Lane [EMAIL PROTECTED] To: Philip Yarra [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 10:58 PM Subject: Re: Fwd: Re: [HACKERS] Call for port reports Philip Yarra [EMAIL PROTECTED] writes: I've just tried the latest CVS on Tru64 (OSF) and I'm getting a surprising= number of failures. You seem to have some path problems: most of the errors look like + ERROR: could not access file /regress.so: No such file or directory or collateral damage. Check to see if the sed script that inserts path values into the regression scripts is doing the right things. I didn't see these objects actually made in the make log, although getting past all the warnings was painful, so maybe I missed it. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
opteron port [was: Re: [HACKERS] Call for port reports]
Am So, den 26.10.2003 schrieb Bruce Momjian um 02:38: All 93 tests passed. ... Linux pergolesi 2.4.22 #1 SMP Mon Aug 25 20:56:25 CEST 2003 i686 GNU/Linux It says i686 but its AMD Opteron: [EMAIL PROTECTED]:~/pgsql$ cat /proc/cpuinfo |more ... model name : AMD Opteron(tm) Processor 240 ... I am confused how to handle this. Is this running in 32-bit mode? I am inclined to mention Opteron only when tested in 64-bit mode, because I think we all assume a 32-bit Opteron is the same as a standard AMD/Intel. Does uname report differently in 64-bit mode. You are right. Its now just like an i386 so it doesn't make sense to list it. When I will get access to an 64bit Opteron system I will test it again. -- Nol Kthe noel debian.org Debian GNU/Linux, www.debian.org signature.asc Description: Dies ist ein digital signierter Nachrichtenteil
Re: [HACKERS] Port Reports: UnixWare/Failure/Priviledge Test
--On Wednesday, October 29, 2003 15:26:39 -0500 Tom Lane [EMAIL PROTECTED] wrote: [snip] Is this a bug, or is it correct-per-spec behavior? It's surely likely to confuse people. I wonder whether superusers shouldn't be allowed to revoke privileges granted by other people. As the code stands, they cannot. It seems to me that a superuser SHOULD be able to affect ANY permissions on ANY object in the DB. [snip] -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] Port Reports: UnixWare/Failure/Priviledge Test
Okay, the cause of the permissions regression failure is this: Larry is running the regression tests as a superuser, but not as the original postgres superuser. This means that when the privileges regression test does REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC; nothing happens, because the revoke is implicitly assumed to mean revoke whatever privileges I granted, and Larry's superuser hasn't granted any. The public privileges on language SQL were granted by user postgres, and they remain in force. So the later CREATE FUNCTION that the test expects to fail, succeeds. Is this a bug, or is it correct-per-spec behavior? It's surely likely to confuse people. I wonder whether superusers shouldn't be allowed to revoke privileges granted by other people. As the code stands, they cannot. If it isn't a bug, I think we'll have to document that the privileges regression test fails when you run it as a non-original superuser. Ugh. I've also found some corner-case bugs in ACL manipulation that arise from the fact that Peter changed the code to allow zero-length ACL arrays; seems he missed one or two consequences of that change. Will fix these, but it doesn't affect the main issue. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] SCO released UP3 today... (fwd)
I didn't see this come back in, so, I'm resending it. LER Forwarded Message Date: Wednesday, October 29, 2003 22:26:43 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: PostgreSQL Hackers Mailing List [EMAIL PROTECTED] Cc: Subject: SCO released UP3 today... which means we can now do a version test to get around the need for the -Kno_host in src/templates/unixware. I need someone's help to get the following done: 1) compile a program that looks for __SCO_VERSION__ = 401200310 and 2) if it is, remove the -Kno_host from the CFLAGS stuff for cc. Can one of the autoconf guru's help here? LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 -- End Forwarded Message -- -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---BeginMessage--- which means we can now do a version test to get around the need for the -Kno_host in src/templates/unixware. I need someone's help to get the following done: 1) compile a program that looks for __SCO_VERSION__ = 401200310 and 2) if it is, remove the -Kno_host from the CFLAGS stuff for cc. Can one of the autoconf guru's help here? LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature ---End Message--- pgp1.pgp Description: PGP signature
Re: [HACKERS] 7.4 compatibility question
Bruce Momjian writes: Bug tracking systems have the same limitation as incremental release notes --- youi have to do a lot of piecemeal work to get complete output at the end, rather than doing it more efficiently in one batch. Most people working on PostgreSQL are volunteers, and one of my primary jobs is to make it easy for them --- if it takes me a week to get the release notes together --- so be it --- I am making it easier for others. That is not the scalable community approach that has been successful in other areas of development. You might as well say, Just tell me all the features you need and I'll implement them. Now *that* would make it easy for other people. Once upon a time we thought that documentation wasn't important or that is was hindering people to get involved. I think that has largely been disproven and we have been very successful with the document the code when you write it approach. The same approach can be used for the release notes. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.4 compatibility question
Bruce Momjian writes: I have added my first release note detail item. I used footnote to add a description to the first release note item. Please don't use footnotes. They make things really hard to read. There are plenty of other mechanisms to organize information. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] O_DIRECT in freebsd
Greg Stark wrote: Manfred Spraul [EMAIL PROTECTED] writes: One problem for WAL is that O_DIRECT would disable the write cache - each operation would block until the data arrived on disk, and that might block other backends that try to access WALWriteLock. Perhaps a dedicated backend that does the writeback could fix that. aio seems a better fit. Has anyone tried to use posix_fadvise for the wal logs? http://www.opengroup.org/onlinepubs/007904975/functions/posix_fadvise.html Linux supports posix_fadvise, it seems to be part of xopen2k. Odd, I don't see it anywhere in the kernel. I don't know what syscall it's using to do this tweaking. At least in 2.6: linux/mm/fadvise.c, the syscall is fadvise64 or 64_64 This is the only option that seems useful for postgres for both the WAL and vacuum (though in other threads it seems the problems with vacuum lie elsewhere): POSIX_FADV_DONTNEED attempts to free cached pages associated with the specified region. This is useful, for example, while streaming large files. A program may periodically request the kernel to free cached data that has already been used, so that more useful cached pages are not discarded instead. Pages that have not yet been written out will be unaffected, so if the application wishes to guarantee that pages will be released, it should call fsync or fdatasync first. I agree. Either immediately after each flush syscall, or just before closing a log file and switching to the next. Perhaps POSIX_FADV_RANDOM and POSIX_FADV_SEQUENTIAL could be useful in a backend before starting a sequential scan or index scan, but I kind of doubt it. IIRC the recommendation is ~20% total memory for the postgres user space buffers. That's quite a lot - it might be sufficient to protect that cache from vacuum or sequential scans. AddBufferToFreeList already contains a comment that this is the right place to try buffer replacement strategies. -- Manfred ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] O_DIRECT in freebsd
DB2 supports cooked and raw file systems - SMS (System Manged Space) and DMS (Database Managed Space) tablespaces. The DB2 experience is that DMS tends to outperform SMS but requires considerable tuning and administrative overhead to see these wins. -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 7.4 compatibility question
Peter Eisentraut wrote: Bruce Momjian writes: I have added my first release note detail item. I used footnote to add a description to the first release note item. Please don't use footnotes. They make things really hard to read. There are plenty of other mechanisms to organize information. OK, please suggest what I should use. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Please help
I've have a MAJOR crash an hour ago and postgresql doesn't start anymoe (version 7.3.4). Here's the error log: Oct 30 17:16:20 server postgres[4135]: [1-2]This probably means that some data blocks are corrupted Oct 30 17:16:20 server postgres[4135]: [1-3]and you will have to use the last backup for recovery. Oct 30 17:16:20 server postgres[4135]: [2] LOG: checkpoint record is at 36/C27C14C0 Oct 30 17:16:20 server postgres[4135]: [3] LOG: redo record is at 36/C2782998; undo record is at 0/0; shutdown FALSE Oct 30 17:16:20 server postgres[4135]: [4] LOG: next transaction id: 203794305; next oid: 32417798 Oct 30 17:16:20 server postgres[4135]: [5] LOG: database system was not properly shut down; automatic recovery in progress Oct 30 17:16:20 server postgres[4135]: [6] LOG: redo starts at 36/C2782998 Oct 30 17:16:21 server postgres[4135]: [7] PANIC: Invalid page header in block 6157 of 29135442 Oct 30 17:16:21 server postgres[4132]: [1] LOG: startup process (pid 4135) was terminated by signal 6 Oct 30 17:16:21 server postgres[4132]: [2] LOG: aborting startup due to startup process failure Is there anything I can do not to reload all backups? Regards -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PQunescapeBytea code
Someething to consider for after the 7.4 release, perhaps...
As per today's CVS version, PQunescapeBytea() does the following when
it encounters an escaped character (i.e., a backslash) in the escaped
string strtext at offset i:
[if (strtext[i] == '\\')]
i++;
if (strtext[i] == '\\')
buffer[j++] = strtext[i++];
else
{
if ((isdigit(strtext[i]))
(isdigit(strtext[i + 1]))
(isdigit(strtext[i + 2])))
{
byte = VAL(strtext[i++]);
byte = (byte 3) + VAL(strtext[i++]);
buffer[j++] = (byte 3) + VAL(strtext[i++]);
}
}
This code completely ignores any other usage of the backslash in the
escaped string, generating no output for unknown escape sequences. Is
that the desired behaviour? The code would be a little simpler if it
were to allow al characters to be escaped, which means ignoring the
backslash but not the following character:
i++;
if (isdigit(strtext[i]) isdigit(strtext[i+1]) isdigit(strtext[i+2]))
{
byte = VAL(strtext[i]);
i++;
byte = (byte3) + VAL(strtext[i]);
i++;
byte = (byte3) + VAL(strtext[i]);
buffer[j++] = byte;
}
else
{
buffer[j++] = strtext[i++];
}
In fact, the else part is identical to the normal (non-escaped) part of
the loop, so it could probably be merged--leaving only the octal parsing
part as a special case.
Then the whole loop could become something like this:
[unsigned char c;]
for (i=j=buflen=0; i(int)strtextlen; ++i, buffer[j++]=c)
{
c = strtext[i];
if (c == '\\')
{
c = strtext[i++]; /* Skip backslash */
if (isdigit(c) isdigit(strtext[i+1]) isdigit(strtext[i+2]))
{
/* Parse octal number */
byte = VAL(strtext[i++]);
byte = (byte 3) + VAL(strtext[i++]);
c = (byte 3) + VAL(strtext[i]);
}
}
}
...Which saves 8 lines, reduces the number of special cases, adds some
comments, and permits arbitrary characters to be escaped.
Jeroen
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PQunescapeBytea code
On Thu, Oct 30, 2003 at 08:24:13PM +0100, Jeroen T. Vermeulen wrote:
Then the whole loop could become something like this:
Okay, that code wasn't entirely correct but it gets the idea across. In
C++ terms, what I arrived at was:
string result;
for (int i=0; iF.size(); ++i)
{
unsigned char c = p[i];
if (c == '\\')
{
c = p[++i];
if (isdigit(c) isdigit(p[i+1]) isdigit(p[i+2]))
{
c = (VAL(p[c])9) | (VAL(p[i+1])3) | VAL(p[i+2]);
i += 2;
}
}
result += char(c);
}
Simple, no?
Jeroen
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] CREATE TYPE for case insensitive text and varchar
Hallo. i just coded a new datatype named cistring, that can be used in every function that accepts text or varchar, and has the great ability to be ordered case insensitive in ORDER BY- Clauses and Indices. The problem is, that its coded like the text-type, so it is variable length completly. I miss the possibility to code something like a userdifined varchar(n). I don't know how to create an input function, that accepts the size of the type, or a function, how to declare such types at all. I think, somethink like a userdefined numeric(n,m) isn't possible, too, or am I wrong? Thank you for all replies, Daniel Migowski ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Deadlock problem
Hi, I am facing a strange problem and thats bugging me for a long time, I am using postgres version 7.2.1. I have written an application in C which tries to drop a trigger and simultaneously read from a table thats has data related to that trigger. the whole database comes into a deadlock stage where drop() keeps waiting on one hand and select() keeps waiting on other hand. i have the stack trace of the processes that are performing the operation For Drop: process doing drop trigger (gdb) where #0 0x420e8a42 in semop () from /lib/i686/libc.so.6 #1 0x0810f028 in IpcSemaphoreLock () #2 0x081132c0 in ProcSleep () #3 0x081124e5 in WaitOnLock () #4 0x08112289 in LockAcquire () #5 0x081116d8 in LockRelation () #6 0x0807407b in relation_openr () #7 0x08074195 in heap_openr () #8 0x080bed34 in DropTrigger () #9 0x081176c7 in pg_exec_query_string () #10 0x0811876a in PostgresMain () #11 0x080fde2a in DoBackend () #12 0x080fd77d in BackendStartup () #13 0x080fc8c1 in ServerLoop () #14 0x080fc2db in PostmasterMain () #15 0x080da152 in main () #16 0x42017499 in __libc_start_main () from /lib/i686/libc.so.6 (gdb) For Select() process's doing select (gdb) where #0 0x420e8a42 in semop () from /lib/i686/libc.so.6 #1 0x0810f028 in IpcSemaphoreLock () #2 0x081132c0 in ProcSleep () #3 0x081124e5 in WaitOnLock () #4 0x08112289 in LockAcquire () #5 0x081116d8 in LockRelation () #6 0x0807407b in relation_openr () #7 0x08074195 in heap_openr () #8 0x0810905a in fireRIRrules () #9 0x08108e21 in ApplyRetrieveRule () #10 0x08109134 in fireRIRrules () #11 0x08108ff9 in fireRIRrules () #12 0x08108e21 in ApplyRetrieveRule () #13 0x08109134 in fireRIRrules () #14 0x08108e21 in ApplyRetrieveRule () #15 0x08109134 in fireRIRrules () #16 0x081095ec in QueryRewrite () #17 0x081173c9 in pg_analyze_and_rewrite () #18 0x08117635 in pg_exec_query_string () #19 0x0811876a in PostgresMain () #20 0x080fde2a in DoBackend () #21 0x080fd77d in BackendStartup () #22 0x080fc8c1 in ServerLoop () ---Type return to continue, or q return to quit--- #23 0x080fc2db in PostmasterMain () #24 0x080da152 in main () #25 0x42017499 in __libc_start_main () from /lib/i686/libc.so.6 (gdb) Looks like both try to have a lock and that leads to a deadlock situation. internally does drop trigger and select try to lock a particluar table? how can i get details whats happening internally in postgres for these operations? I can provide more info if needed, hope the stack traces are helpful. Kindly help me, this problem is causinig lot of problem for me. Thanks in advance regards vatsal _ **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
Re: [HACKERS] Deadlock problem
On 30 Oct 2003, Vatsal Avasthi wrote: Hi, I am facing a strange problem and thats bugging me for a long time, I am using postgres version 7.2.1. Is it possible for you to upgrade to 7.2.4 just to make sure it's not a problem that was fixed from 7.2.1 to 7.2.4? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_user
you can also patch your kernel and when you write cat /etc/passwd system give you only your line , whitout any others users, so exacly what you need , in pgsql i think that users dont need to know about others , and also them databases, i call it security :) On Mon, 27 Oct 2003, Jan Wieck wrote: ivan wrote: hi can we change initdb when view pg_user is createing to : CREATE VIEW pg_user AS \ SELECT \ usename, \ usesysid, \ usecreatedb, \ usesuper, \ usecatupd, \ ''::text as passwd, \ valuntil, \ useconfig \ FROM pg_shadow WHERE usename = SESSION_USER; No, at least not without a complete proposal how to retain the current behaviour of pg_tables, pg_views, psql's \d and other places that rely on pg_user being able to display all users. It's the same thing with your /etc/passwd. chmod o-rwx /etc/passwd will hide the usernames but break many utilities. If you don't want someone to know all the logins, don't give him one. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_user
rfc 1925 (see http://www.faqs.org/rfcs/rfc1925.html ) states: With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. You can call it security if you like, but I call it trying to make a pig fly. If you don't want your users to know about each other then put them on different clusters. Or if they need access to the same data then mediate access via a middle layer at the server end instead of allowing direct access to the database(s) - three layer models are very common for this and other reasons. cheers andrew ivan wrote: you can also patch your kernel and when you write cat /etc/passwd system give you only your line , whitout any others users, so exacly what you need , in pgsql i think that users dont need to know about others , and also them databases, i call it security :) On Mon, 27 Oct 2003, Jan Wieck wrote: ivan wrote: hi can we change initdb when view pg_user is createing to : CREATE VIEW pg_user AS \ SELECT \ usename, \ usesysid, \ usecreatedb, \ usesuper, \ usecatupd, \ ''::text as passwd, \ valuntil, \ useconfig \ FROM pg_shadow WHERE usename = SESSION_USER; No, at least not without a complete proposal how to retain the current behaviour of pg_tables, pg_views, psql's \d and other places that rely on pg_user being able to display all users. It's the same thing with your /etc/passwd. chmod o-rwx /etc/passwd will hide the usernames but break many utilities. If you don't want someone to know all the logins, don't give him one. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [BUGS] Autocomplete TAB on Postgres7.4beta5 not working?
Andrew Dunstan [EMAIL PROTECTED] writes: 2. include catalog objects in expansion iff we are expanding pg_ + optional suffix (probably best of both worlds). That seems like the best compromise position anyone offered, so I have made the code work that way. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_user
On Thu, 30 Oct 2003, ivan wrote: you can also patch your kernel and when you write cat /etc/passwd system give you only your line , whitout any others users, so exacly what you need , in pgsql i think that users dont need to know about others , and also them databases, i call it security :) technically, that's just obscurity. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_user
ivan wrote: you can also patch your kernel and when you write cat /etc/passwd system give you only your line , whitout any others users, so exacly what you need , in pgsql i think that users dont need to know about others , and also them databases, i call it security :) No, it's not security, it is obscurity. The point is that this modification is not backward compatible and the only scenario I can imagine where it would be good to have this is for a hosting provider who want's to cram up multiple hosted databases under one postmaster. I am not per se against such change. It never striked me as a good idea in general that we only have the one, shared pg_shadow catalog and all databases share all users. So I think what I try to say is ... back to the drawing board, because your initial solution is not acceptable. Jan On Mon, 27 Oct 2003, Jan Wieck wrote: ivan wrote: hi can we change initdb when view pg_user is createing to : CREATE VIEW pg_user AS \ SELECT \ usename, \ usesysid, \ usecreatedb, \ usesuper, \ usecatupd, \ ''::text as passwd, \ valuntil, \ useconfig \ FROM pg_shadow WHERE usename = SESSION_USER; No, at least not without a complete proposal how to retain the current behaviour of pg_tables, pg_views, psql's \d and other places that rely on pg_user being able to display all users. It's the same thing with your /etc/passwd. chmod o-rwx /etc/passwd will hide the usernames but break many utilities. If you don't want someone to know all the logins, don't give him one. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Please help
[EMAIL PROTECTED] writes: I've have a MAJOR crash an hour ago and postgresql doesn't start anymoe (version 7.3.4). Oct 30 17:16:21 server postgres[4135]: [7] PANIC: Invalid page header in block 6157 of 29135442 Oct 30 17:16:21 server postgres[4132]: [1] LOG: startup process (pid 4135) was terminated by signal 6 Oct 30 17:16:21 server postgres[4132]: [2] LOG: aborting startup due to startup process failure Is there anything I can do not to reload all backups? You could try turning on zero_damaged_pages in postgresql.conf. If you are lucky, the page in question is going to be rewritten from WAL anyway. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] CREATE TYPE for case insensitive text and varchar
Daniel Migowski [EMAIL PROTECTED] writes: I miss the possibility to code something like a userdifined varchar(n). You're out of luck on that. The data types that can have precision parameters attached to them are hard-wired into the parser. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Deadlock problem
Vatsal Avasthi [EMAIL PROTECTED] writes: I am using postgres version 7.2.1. Looks like both try to have a lock and that leads to a deadlock situation. It's hard to believe that SELECT and DROP TRIGGER alone could deadlock; and if they did, you should get a deadlock failure report, not an indefinite wait. I think more likely the situation is that some third client process is holding open a transaction that has some kind of lock on the table. DROP TRIGGER would then block waiting for that process (since it needs to get exclusive lock on the table). And then, fresh SELECTs (or anything else) would stack up behind the DROP TRIGGER. This is not a deadlock though, just everyone waiting for the original lock holder to get off his duff and do something. If you are unconvinced, I would recommend updating to 7.3.4 or 7.4beta so that you can get more information by looking at the pg_locks system view. It's difficult to tell what's happening in 7.2 or older. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Please help
Thanks Tom, The answer came too late and I could'nt wait. pg_resetlog did nearly the trick, Only one database was really hurt. So I reloaded all but this one from pg_dumpall then the last one from backup... I'm cursed On Thu, 30 Oct 2003, Tom Lane wrote: Date: Thu, 30 Oct 2003 17:25:02 -0500 From: Tom Lane [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: [HACKERS] Please help [EMAIL PROTECTED] writes: I've have a MAJOR crash an hour ago and postgresql doesn't start anymoe (version 7.3.4). Oct 30 17:16:21 server postgres[4135]: [7] PANIC: Invalid page header in block 6157 of 29135442 Oct 30 17:16:21 server postgres[4132]: [1] LOG: startup process (pid 4135) was terminated by signal 6 Oct 30 17:16:21 server postgres[4132]: [2] LOG: aborting startup due to startup process failure Is there anything I can do not to reload all backups? You could try turning on zero_damaged_pages in postgresql.conf. If you are lucky, the page in question is going to be rewritten from WAL anyway. regards, tom lane -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Open items
Bruce Momjian [EMAIL PROTECTED] writes: We only have a few open items left. Can we finish them so we can move toward final release? The list seems to be nearly down to this: Rename dump GUC variable to be more generic Sure, if we can agree on a name. We have a few options here. Currently it is check_function_bodies. The ideas were validation mode: I think I'd prefer to keep foreign key check disabling separate. Or at least make it separately selectable. Maybe validation_mode could have multiple levels (off, safe, risky)? and an even more generic restore_mode where the restore_mode could control even more things, such as doing an ANALYZE before an ALTER TABLE ADD CONSTRAINT. Given the apparent lack of interest in this topic, I propose that we just leave the variable name as-is. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PQunescapeBytea code
Actually I was looking at that code today and it does not ignore something if it is escaped by a backslash on not on the list. It eats the backslash and then continues the loop so next time that character will be parsed normally. However PQunescapeBytea is _very_ slow. I am storing fairly large (several hundered K) byte strings into Bytea's and it can take 30 seconds or more to convert them back into binary data. I wrote a new version of PQunescapeBytea that uses pointers instead of arrays to store the string in, this increases the speed about 30 fold on my strings and still has the same behavior. I wasn't sure if this would be something I should submit as a patch or not, is anyone interested in this? If they are I'll try to figure out how to submit a patch. --- Adam Kavan --- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] 7.4RC1 planned for Monday
Barring the discovery of any major new bugs, the core committee has agreed to release 7.4RC1 on Monday. Time to get those last-minute fixes in place. I currently have the following issues on my radar screen: Force GRANT/REVOKE by superuser to act as though owner of object? Change libpgtcl to use ByteArray for lo_read/lo_write (ljb's patch) Add option for parallelism limit in make check (Andrew Dunstan's patch) rule/foreign key interaction reported by Michele Bendazzoli plus various minor documentation fixes. Not much there... BTW, 7.4 final will be as early as the following Monday if no problems are detected. We will decide on a week-to-week basis whether we are ready to release final or not. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 7.4RC1 planned for Monday
Hello, I know I will probably be flamed into oblivion for this but I would like to make a suggestion about the upcoming release. What if we delayed until the end of the year? The two reasons that I can come up with are: 1. The irritating (but work around capable) bigint index issue. 2. More importantly the recent potential discovery by Jan on vacuum. I have several high end users that are really beating their heads against the wall with even lazy vacuum because of how brutal it can be on the system. If we could make vacuum a little less harsh it could be a large boon. If I am totally off my rocker, so be it but if we were to hit the streets with 7.4 and a vacuum that was 70% (ex) less brutal on the machine it would be a pretty significant statement. Yes all the other fixes are great and cool. Sincerely, Joshua D. Drake Tom Lane wrote: Barring the discovery of any major new bugs, the core committee has agreed to release 7.4RC1 on Monday. Time to get those last-minute fixes in place. I currently have the following issues on my radar screen: Force GRANT/REVOKE by superuser to act as though owner of object? Change libpgtcl to use ByteArray for lo_read/lo_write (ljb's patch) Add option for parallelism limit in make check (Andrew Dunstan's patch) rule/foreign key interaction reported by Michele Bendazzoli plus various minor documentation fixes. Not much there... BTW, 7.4 final will be as early as the following Monday if no problems are detected. We will decide on a week-to-week basis whether we are ready to release final or not. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 7.4RC1 planned for Monday
Joshua D. Drake [EMAIL PROTECTED] writes: What if we delayed until the end of the year? Nope, not for those items. There is still some thought of a very short release cycle (a few months) for 7.5, and we could possibly address the vacuum issue in that timeframe, if the recent ideas about it prove out. But there is no consensus on how to fix the integer-index issues, and I'm not willing to hold 7.4 (or even 7.5) hostage to finding one. Sooner or later you have to say this release is done, let's ship it. It's way too late to go back into invention mode for 7.4. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 7.4RC1 planned for Monday
Sooner or later you have to say this release is done, let's ship it. It's way too late to go back into invention mode for 7.4. I agree with the argument. It is just that the Vacuum one... well is very tempting. On the 7.5 cycle though... I thought 7.5 was basically for win32? Sincerely, Joshua Drake regards, tom lane -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] O_DIRECT in freebsd
My experience with DB2 showed that properly setup DMS tablespaces provided a significant performance benefit. I have also seen that the average DBA does not generally understand the data or access patterns in the database. Given that, they don't correctly setup table spaces in general, filesystem or raw. Likewise, where it is possible to tie a tablespace to a memory buffer pool, the average DBA does not setup it up to a performance advantage either. However, are we talking about well tuned setups by someone who does understand the data and the general access patterns? For a DBA like that, they should be able to take advantage of these features and get significantly better results. I would not say it requires considerable tuning, but an understanding of data, storage and access patterns. Additionally, these features did not cause our group considerable administrative overhead. Jordan Henderson On Thursday 30 October 2003 12:55, Sailesh Krishnamurthy wrote: DB2 supports cooked and raw file systems - SMS (System Manged Space) and DMS (Database Managed Space) tablespaces. The DB2 experience is that DMS tends to outperform SMS but requires considerable tuning and administrative overhead to see these wins. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 7.4RC1 planned for Monday
On Thu, 30 Oct 2003, Joshua D. Drake wrote: Hello, I know I will probably be flamed into oblivion for this but I would like to make a suggestion about the upcoming release. What if we delayed until the end of the year? The two reasons that I can come up with are: 1. The irritating (but work around capable) bigint index issue. 2. More importantly the recent potential discovery by Jan on vacuum. I have several high end users that are really beating their heads against the wall with even lazy vacuum because of how brutal it can be on the system. If we could make vacuum a little less harsh it could be a large boon. Are these folks for whom the autovacuum daemon provides no relief? I think it's too late in the release cycle to put everything on hold, especially with no known fix in sight (for bigint at least.) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4RC1 planned for Monday
scott.marlowe [EMAIL PROTECTED] writes: Are these folks for whom the autovacuum daemon provides no relief? If I understood correctly, Josh was complaining about VACUUM sucking too much of his disk bandwidth. autovacuum wouldn't help that --- in fact would likely make it worse, since a cron-driven vacuum script can at least be scheduled for low-load times of day. autovacuum is likely to kick in at the least convenient times. However, we have at this point got only speculative solutions to the too-much-bandwidth problem. If we had something ready to go today, I'd be as willing as the next guy to cram it into 7.4. I'm not willing to go back into development mode for 7.4, though. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] O_DIRECT in freebsd
-Original Message- From: Jordan Henderson [mailto:[EMAIL PROTECTED] Sent: Thursday, October 30, 2003 4:31 PM To: [EMAIL PROTECTED]; Doug McNaught Cc: Christopher Kings-Lynne; PostgreSQL-development Subject: Re: [HACKERS] O_DIRECT in freebsd My experience with DB2 showed that properly setup DMS tablespaces provided a significant performance benefit. I have also seen that the average DBA does not generally understand the data or access patterns in the database. Given that, they don't correctly setup table spaces in general, filesystem or raw. Likewise, where it is possible to tie a tablespace to a memory buffer pool, the average DBA does not setup it up to a performance advantage either. However, are we talking about well tuned setups by someone who does understand the data and the general access patterns? For a DBA like that, they should be able to take advantage of these features and get significantly better results. I would not say it requires considerable tuning, but an understanding of data, storage and access patterns. Additionally, these features did not cause our group considerable administrative overhead. If it is possible for a human with knowledge of this domain to make good decisions, it ought to be possible to store the same information into an algorithm that operates off of collected statistics. After some time has elapsed, and an average access pattern of some sort has been reached, the available resources could be divided in a fairly efficient way. It might be nice to be able to tweak it, but I would rather have the computer make the calculations for me. Just a thought. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] O_DIRECT in freebsd
Jordan == Jordan Henderson [EMAIL PROTECTED] writes: Jordan significantly better results. I would not say it requires Jordan considerable tuning, but an understanding of data, storage Jordan and access patterns. Additionally, these features did not Jordan cause our group considerable administrative overhead. I won't dispute the specifics. I have only worked on the DB2 engine - never written an app for it nor administered it. You're right - the bottomline is that you can get a significant performance advantage provided you care enough to understand what's going on. Anyway, I merely responded to provide a data point. Will PostgreSQL users/administrators care for additional knobs or is there a preference for keep it simple, stupid ? -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 7.4RC1 planned for Monday
If I understood correctly, Josh was complaining about VACUUM sucking too much of his disk bandwidth. autovacuum wouldn't help that --- in fact would likely make it worse, since a cron-driven vacuum script can at least be scheduled for low-load times of day. autovacuum is likely to kick in at the least convenient times. Exactly! However, we have at this point got only speculative solutions to the too-much-bandwidth problem. If we had something ready to go today, I'd be as willing as the next guy to cram it into 7.4. I'm not willing to go back into development mode for 7.4, though. regards, tom lane -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.4RC1 planned for Monday
Nope, not for those items. There is still some thought of a very short release cycle (a few months) for 7.5, and we could possibly address the vacuum issue in that timeframe, if the recent ideas about it prove out. But there is no consensus on how to fix the integer-index issues, and I'm not willing to hold 7.4 (or even 7.5) hostage to finding one. The idea of very short release cycle for 7.5 is interesting. What is the core's decision for point-in-time-recovery? Maybe the decision is 7.5 does not include point-in-time-recovery? -- Tatsuo Ishii ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4RC1 planned for Monday
Tatsuo Ishii [EMAIL PROTECTED] writes: The idea of very short release cycle for 7.5 is interesting. What is the core's decision for point-in-time-recovery? Maybe the decision is 7.5 does not include point-in-time-recovery? We'd like to have it in 7.5. Whether it will get done in time is impossible to predict at this point... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 7.4RC1 planned for Monday
On Thu, 30 Oct 2003, Tom Lane wrote: rule/foreign key interaction reported by Michele Bendazzoli In the interests of disclosure, if the case in question for the rule fails, almost certainly deferred fk constraints will as well which I think makes this a must fix for 7.4 and is another push to getting a 7.3.5. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PQunescapeBytea code
Adam Kavan wrote: Actually I was looking at that code today and it does not ignore something if it is escaped by a backslash on not on the list. It eats the backslash and then continues the loop so next time that character will be parsed normally. However PQunescapeBytea is _very_ slow. I am storing fairly large (several hundered K) byte strings into Bytea's and it can take 30 seconds or more to convert them back into binary data. I wrote a new version of PQunescapeBytea that uses pointers instead of arrays to store the string in, this increases the speed about 30 fold on my strings and still has the same behavior. I wasn't sure if this would be something I should submit as a patch or not, is anyone interested in this? If they are I'll try to figure out how to submit a patch. Are you testing againts 7.3.X or 7.4? 7.4 has a faster version. If you are testing against 7.4, do a diff -c against the old and new files and send it to the patches list. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Experimental patch for inter-page delay in VACUUM
Attached is an extremely crude prototype patch for making VACUUM delay
by a configurable amount between pages, in hopes of throttling its disk
bandwidth consumption. By default, there is no delay (so no change in
behavior). In some quick testing, setting vacuum_page_delay to 10
(milliseconds) seemed to greatly reduce a background VACUUM's impact
on pgbench timing on an underpowered machine. Of course, it also makes
VACUUM a lot slower, but that's probably not a serious concern for
background VACUUMs.
I am not proposing this for application to the master sources yet, but
I would be interested to get some feedback from people who see serious
performance degradation while VACUUM is running. Does it help? What do
you find to be a good setting for vacuum_page_delay?
Assuming that this is found to be useful, the following issues would
have to be dealt with before the patch would be production quality:
1. The patch depends on usleep() which is not present on all platforms,
and may have unwanted side-effects on SIGALRM processing on some
platforms. We'd need to replace that with something else, probably
a select() call.
2. I only bothered to insert delays in the processing loops of plain
VACUUM and btree index cleanup. VACUUM FULL and cleanup of non-btree
indexes aren't done yet.
3. No documentation...
The patch is against CVS tip, but should apply cleanly to any recent
7.4 beta. You could likely adapt it to 7.3 without much effort.
regards, tom lane
*** src/backend/access/nbtree/nbtree.c.orig Mon Sep 29 19:40:26 2003
--- src/backend/access/nbtree/nbtree.c Thu Oct 30 21:02:55 2003
***
*** 18,23
--- 18,25
*/
#include postgres.h
+ #include unistd.h
+
#include access/genam.h
#include access/heapam.h
#include access/nbtree.h
***
*** 27,32
--- 29,37
#include storage/smgr.h
+ extern intvacuum_page_delay;
+
+
/* Working state for btbuild and its callback */
typedef struct
{
***
*** 610,615
--- 615,623
CHECK_FOR_INTERRUPTS();
+ if (vacuum_page_delay 0)
+ usleep(vacuum_page_delay * 1000);
+
ndeletable = 0;
page = BufferGetPage(buf);
opaque = (BTPageOpaque) PageGetSpecialPointer(page);
***
*** 736,741
--- 744,754
Buffer buf;
Pagepage;
BTPageOpaque opaque;
+
+ CHECK_FOR_INTERRUPTS();
+
+ if (vacuum_page_delay 0)
+ usleep(vacuum_page_delay * 1000);
buf = _bt_getbuf(rel, blkno, BT_READ);
page = BufferGetPage(buf);
*** src/backend/commands/vacuumlazy.c.orig Thu Sep 25 10:22:58 2003
--- src/backend/commands/vacuumlazy.c Thu Oct 30 21:07:58 2003
***
*** 37,42
--- 37,44
*/
#include postgres.h
+ #include unistd.h
+
#include access/genam.h
#include access/heapam.h
#include access/xlog.h
***
*** 88,93
--- 90,97
static TransactionId OldestXmin;
static TransactionId FreezeLimit;
+ int vacuum_page_delay = 0; /* milliseconds per page */
+
/* non-export function prototypes */
static void lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
***
*** 228,233
--- 232,240
CHECK_FOR_INTERRUPTS();
+ if (vacuum_page_delay 0)
+ usleep(vacuum_page_delay * 1000);
+
/*
* If we are close to overrunning the available space for
* dead-tuple TIDs, pause and do a cycle of vacuuming before we
***
*** 469,474
--- 476,484
CHECK_FOR_INTERRUPTS();
+ if (vacuum_page_delay 0)
+ usleep(vacuum_page_delay * 1000);
+
tblk = ItemPointerGetBlockNumber(vacrelstats-dead_tuples[tupindex]);
buf = ReadBuffer(onerel, tblk);
LockBufferForCleanup(buf);
***
*** 799,804
--- 809,817
hastup;
CHECK_FOR_INTERRUPTS();
+
+ if (vacuum_page_delay 0)
+ usleep(vacuum_page_delay * 1000);
blkno--;
*** src/backend/utils/misc/guc.c.orig Sun Oct 19 19:47:54 2003
--- src/backend/utils/misc/guc.cThu Oct 30 21:15:46 2003
***
*** 73,78
--- 73,79
extern intCommitDelay;
extern intCommitSiblings;
extern char *preload_libraries_string;
+ extern intvacuum_page_delay;
#ifdef HAVE_SYSLOG
extern char *Syslog_facility;
***
*** 1188,1193
--- 1189,1203
},
log_min_duration_statement,
-1, -1, INT_MAX / 1000,
Re: [HACKERS] 7.4RC1 planned for Monday
On Thu, 30 Oct 2003, David Fetter wrote: On Thu, Oct 30, 2003 at 09:08:43PM -0400, Tom Lane wrote: Barring the discovery of any major new bugs, the core committee has agreed to release 7.4RC1 on Monday. Time to get those last-minute fixes in place. I currently have the following issues on my radar screen: Force GRANT/REVOKE by superuser to act as though owner of object? Change libpgtcl to use ByteArray for lo_read/lo_write (ljb's patch) Add option for parallelism limit in make check (Andrew Dunstan's patch) rule/foreign key interaction reported by Michele Bendazzoli plus various minor documentation fixes. Not much there... BTW, 7.4 final will be as early as the following Monday if no problems are detected. We will decide on a week-to-week basis whether we are ready to release final or not. Any chance of putting up a torrent for it? I'd be happy to host, but I'd have to get the link on the downloads page somehow :) Put up a what ... ? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.4RC1 planned for Monday
On Thu, 30 Oct 2003, Joshua D. Drake wrote: Sooner or later you have to say this release is done, let's ship it. It's way too late to go back into invention mode for 7.4. I agree with the argument. It is just that the Vacuum one... well is very tempting. On the 7.5 cycle though... I thought 7.5 was basically for win32? Nope, it is *hoped* that v7.5 will include win32 ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4RC1 planned for Monday
On Fri, 31 Oct 2003, Tatsuo Ishii wrote: Nope, not for those items. There is still some thought of a very short release cycle (a few months) for 7.5, and we could possibly address the vacuum issue in that timeframe, if the recent ideas about it prove out. But there is no consensus on how to fix the integer-index issues, and I'm not willing to hold 7.4 (or even 7.5) hostage to finding one. The idea of very short release cycle for 7.5 is interesting. What is the core's decision for point-in-time-recovery? Maybe the decision is 7.5 does not include point-in-time-recovery? Does anyone have anything ready to put into CVS as soon as we start v7.5, or shortly afterwards? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 7.4RC1 planned for Monday
Marc G. Fournier [EMAIL PROTECTED] writes: On Thu, 30 Oct 2003, David Fetter wrote: Any chance of putting up a torrent for it? I'd be happy to host, but I'd have to get the link on the downloads page somehow :) Put up a what ... ? Google for BitTorrent. It's a pretty darn cool app but I don't think the PG source tarball is really big enough to be worth the trouble... It's great for things like CD images though. -Doug ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 7.4RC1 planned for Monday
Does anyone have anything ready to put into CVS as soon as we start v7.5, or shortly afterwards? Check bruce's 7.5 patches list (can't remember the address though :) ) I have this COMMENT ON thing ready to go, except for this darn taking in unsigned ints from the parser business that I haven't had any suggests for :P Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4RC1 planned for Monday
Marc G. Fournier wrote: On Fri, 31 Oct 2003, Tatsuo Ishii wrote: Nope, not for those items. There is still some thought of a very short release cycle (a few months) for 7.5, and we could possibly address the vacuum issue in that timeframe, if the recent ideas about it prove out. But there is no consensus on how to fix the integer-index issues, and I'm not willing to hold 7.4 (or even 7.5) hostage to finding one. The idea of very short release cycle for 7.5 is interesting. What is the core's decision for point-in-time-recovery? Maybe the decision is 7.5 does not include point-in-time-recovery? Does anyone have anything ready to put into CVS as soon as we start v7.5, or shortly afterwards? Well, I have patches2: http:/momjian.postgresql.org/cgi-bin/pgpatches2 There are a few things ready for application in there, plus other items to start work on. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 7.4RC1 planned for Monday
I meant related to PITR? :) On Thu, 30 Oct 2003, Bruce Momjian wrote: Marc G. Fournier wrote: On Fri, 31 Oct 2003, Tatsuo Ishii wrote: Nope, not for those items. There is still some thought of a very short release cycle (a few months) for 7.5, and we could possibly address the vacuum issue in that timeframe, if the recent ideas about it prove out. But there is no consensus on how to fix the integer-index issues, and I'm not willing to hold 7.4 (or even 7.5) hostage to finding one. The idea of very short release cycle for 7.5 is interesting. What is the core's decision for point-in-time-recovery? Maybe the decision is 7.5 does not include point-in-time-recovery? Does anyone have anything ready to put into CVS as soon as we start v7.5, or shortly afterwards? Well, I have patches2: http:/momjian.postgresql.org/cgi-bin/pgpatches2 There are a few things ready for application in there, plus other items to start work on. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 7.4RC1 planned for Monday
Oh, sorry, only read your part --- I have not heard anything about PITR from Patrick. I talked to him about a month ago and he hadn't made much headway. --- Marc G. Fournier wrote: I meant related to PITR? :) On Thu, 30 Oct 2003, Bruce Momjian wrote: Marc G. Fournier wrote: On Fri, 31 Oct 2003, Tatsuo Ishii wrote: Nope, not for those items. There is still some thought of a very short release cycle (a few months) for 7.5, and we could possibly address the vacuum issue in that timeframe, if the recent ideas about it prove out. But there is no consensus on how to fix the integer-index issues, and I'm not willing to hold 7.4 (or even 7.5) hostage to finding one. The idea of very short release cycle for 7.5 is interesting. What is the core's decision for point-in-time-recovery? Maybe the decision is 7.5 does not include point-in-time-recovery? Does anyone have anything ready to put into CVS as soon as we start v7.5, or shortly afterwards? Well, I have patches2: http:/momjian.postgresql.org/cgi-bin/pgpatches2 There are a few things ready for application in there, plus other items to start work on. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.4RC1 planned for Monday
Marc G. Fournier [EMAIL PROTECTED] writes: Does anyone have anything ready to put into CVS as soon as we start v7.5, or shortly afterwards? That brings up another question, which is when to create the REL7_4_STABLE branch in CVS. Offhand I think it would be good to do it when we make RC1; any thoughts? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.4RC1 planned for Monday
On Thu, Oct 30, 2003 at 09:51:24PM -0500, Doug McNaught wrote: Marc G. Fournier [EMAIL PROTECTED] writes: On Thu, 30 Oct 2003, David Fetter wrote: Any chance of putting up a torrent for it? I'd be happy to host, but I'd have to get the link on the downloads page somehow :) Put up a what ... ? Google for BitTorrent. It's a pretty darn cool app but I don't think the PG source tarball is really big enough to be worth the trouble... It's great for things like CD images though. It's big enough that it'd be nice to be able to distribute the load a little :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] CREATE TYPE for case insensitive text and varchar
Tom Lane wrote:
Daniel Migowski [EMAIL PROTECTED] writes:
I miss the possibility to code something like a userdifined
varchar(n).
You're out of luck on that. The data types that can have precision
parameters attached to them are hard-wired into the parser.
Maybe you don't need to invent a whole new data type but the existing
varchar can serve just fine?
The attached script for version 7.3.4 (does not work with 7.4)
demonstrates how to add case insensitive operators *=, * and so on
including an operator class for btree to the existing varchar.
All one has to do is to use *= instead of = in queries. Indexes, even
unique, based on case insensitive comparision are possible too and well
supported. The only thing I think wouldn't work are IN and NOT IN
constructs.
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
#!/bin/sh
DBNAME=ci_testdb
export DBNAME
dropdb ${DBNAME}
createdb ${DBNAME}
psql ${DBNAME} _EOF_
--
-- Case insensitive comparision functions
--
create function varchar_cieq(varchar, varchar) returns bool
as '
begin
return varchareq(lower(\$1), lower(\$2));
end;
' language plpgsql;
create function varchar_cine(varchar, varchar) returns bool
as '
begin
return varcharne(lower(\$1), lower(\$2));
end;
' language plpgsql;
create function varchar_cilt(varchar, varchar) returns bool
as '
begin
return varcharlt(lower(\$1), lower(\$2));
end;
' language plpgsql;
create function varchar_cile(varchar, varchar) returns bool
as '
begin
return varcharle(lower(\$1), lower(\$2));
end;
' language plpgsql;
create function varchar_cigt(varchar, varchar) returns bool
as '
begin
return varchargt(lower(\$1), lower(\$2));
end;
' language plpgsql;
create function varchar_cige(varchar, varchar) returns bool
as '
begin
return varcharge(lower(\$1), lower(\$2));
end;
' language plpgsql;
create function varchar_cicmp(varchar, varchar) returns int4
as '
begin
return varcharcmp(lower(\$1), lower(\$2));
end;
' language plpgsql;
--
-- Case insensitive operators
--
create operator * (
procedure = varchar_cilt,
leftarg = varchar, rightarg = varchar,
commutator = *, negator = *=,
restrict = scalarltsel, join = scalarltjoinsel
);
create operator *= (
procedure = varchar_cieq,
leftarg = varchar, rightarg = varchar,
commutator = *=, negator = *,
restrict = eqsel, join = eqjoinsel,
sort1 = *, sort2 = *,
hashes
);
create operator * (
procedure = varchar_cine,
leftarg = varchar, rightarg = varchar,
commutator = *, negator = *=,
restrict = neqsel, join = neqjoinsel
);
create operator *!= (
procedure = varchar_cine,
leftarg = varchar, rightarg = varchar,
commutator = *, negator = *=,
restrict = neqsel, join = neqjoinsel
);
create operator * (
procedure = varchar_cigt,
leftarg = varchar, rightarg = varchar,
commutator = *, negator = *=,
restrict = scalargtsel, join = scalargtjoinsel
);
create operator *= (
procedure = varchar_cile,
leftarg = varchar, rightarg = varchar,
commutator = *=, negator = *,
restrict = scalarltsel, join = scalarltjoinsel
);
create operator *= (
procedure = varchar_cige,
leftarg = varchar, rightarg = varchar,
commutator = *=, negator = *,
restrict = scalargtsel, join = scalargtjoinsel
);
--
-- And the operator class for case insensitive indexes
--
create operator class varchar_ciops
for type varchar using btree as
operator 1 * (varchar, varchar),
operator 2 *= (varchar, varchar),
operator 3 *= (varchar, varchar),
operator 4 *= (varchar, varchar),
operator 5 * (varchar, varchar),
function 1 varchar_cicmp(varchar, varchar);
create table citest_t1 (
id varchar(10),
datatext
);
create unique index citest_t1_idx on citest_t1 (id varchar_ciops);
insert into citest_t1 values ('hello', 'world');
insert into citest_t1 values ('goodbye', 'world');
insert into citest_t1 values ('Hello', 'World');
set enable_seqscan to off;
set enable_indexscan to on;
explain select * from citest_t1 where id *= 'hello';
explain select * from citest_t1 where id = 'hello';
select * from citest_t1 where id *= 'HELLO';
select * from citest_t1 where id *!= 'HELLO';
select * from citest_t1 where id = 'HELLO';
_EOF_
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] 7.4RC1 planned for Monday
The world rejoiced as [EMAIL PROTECTED] (Joshua D. Drake) wrote: 2. More importantly the recent potential discovery by Jan on vacuum. I have several high end users that are really beating their heads against the wall with even lazy vacuum because of how brutal it can be on the system. If we could make vacuum a little less harsh it could be a large boon. Boon it would be, I agree. But from what I can tell, Jan has only just gotten to the point of being able to replicate the behaviour, with some initial attempts to address it. He only mentioned it a few days ago. That doesn't establish that there is a comprehensive answer that's ready to deploy. Perhaps there will be something next week, but it may very well take longer. We have been living with the current conditions for several versions; if it is tempting enough, perhaps it will argue for a quick 7.4.1. Indeed, since the functionality has affected various versions, it is not unthinkable that a solution might even be amenable to backporting. But there is a point in time at which to say, Shoot the engineers, and release the product. :-) I rather think it would be a risky endeavour to hold things off on the _possibility_ that something might happen soon on this, particularly when this was not an expected enhancement. I'm certainly not arguing against the improvement; in separate non-news, I'm still lobbying for my suggestion, of a VACUUM CACHE, which would go after the 'low hanging fruit' of going after pages that are currently in memory. No going after whole tables; just the bits that require no I/O (save for indexes) because they're already known to be in memory. -- http://www3.sympatico.ca/cbbrowne/oses.html Rules of the Evil Overlord #121. If I come into possession of an artifact which can only be used by the pure of heart, I will not attempt to use it regardless. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4RC1 planned for Monday
Tatsuo Ishii wrote: Nope, not for those items. There is still some thought of a very short release cycle (a few months) for 7.5, and we could possibly address the vacuum issue in that timeframe, if the recent ideas about it prove out. But there is no consensus on how to fix the integer-index issues, and I'm not willing to hold 7.4 (or even 7.5) hostage to finding one. The idea of very short release cycle for 7.5 is interesting. What is the core's decision for point-in-time-recovery? Maybe the decision is 7.5 does not include point-in-time-recovery? I believe we were thinking PITR or Win32, or both could trigger a short 7.5 release cycle. However, it doesn't seem either is ready. If we do a short cycle, will we have enough features to justify a release? We could try to get PITR and Win32 done by January 1 and see if that can happen. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 7.4RC1 planned for Monday
Joshua D. Drake wrote: Sooner or later you have to say this release is done, let's ship it. It's way too late to go back into invention mode for 7.4. I agree with the argument. It is just that the Vacuum one... well is very tempting. Since improving the buffer cache policy will not change any visible functionality other than performance ... maybe you want to convince some people that if we find a substantial improvement for the cache policy soon to put it into a 7.4.x release. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.4RC1 planned for Monday
Jan Wieck [EMAIL PROTECTED] writes: Since improving the buffer cache policy will not change any visible functionality other than performance ... maybe you want to convince some people that if we find a substantial improvement for the cache policy soon to put it into a 7.4.x release. It's way premature to argue about this when we have no patch in hand to consider. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 7.4RC1 planned for Monday
Stephan Szabo [EMAIL PROTECTED] writes: On Thu, 30 Oct 2003, Tom Lane wrote: rule/foreign key interaction reported by Michele Bendazzoli In the interests of disclosure, if the case in question for the rule fails, almost certainly deferred fk constraints will as well which I think makes this a must fix for 7.4 and is another push to getting a 7.3.5. Hm, does Jan's just-committed fix address the concern you had? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Call for port reports
Bruce Momjian [EMAIL PROTECTED] writes: One other idea would be to set CFLAGS to before including template, and just test to see if it is still after --- that might be cleaner than saving the original value and comparing. Yeah, that bothered me a bit too --- what if the template tries to set CFLAGS to its already-existing value? I was thinking that unsetting CFLAGS before running the template would be the best answer. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] O_DIRECT in freebsd
Personally, I think it is useful to have features. I quite understand the difficulties in maintaining some features however. Also having worked on internals for commercial DB engines, I have specifically how code/data paths can be shortened. I would not make the choice for someone to be forced into using a product in a specific manner. Instead, I would let them decide whether to choose a simple setup or, if they are up to it, something with better performance. I would not prune the options out. In doing so, we limit what a knowledgeable person can do a priori. Jordan Henderson On Thursday 30 October 2003 19:59, Sailesh Krishnamurthy wrote: Jordan == Jordan Henderson [EMAIL PROTECTED] writes: Jordan significantly better results. I would not say it requires Jordan considerable tuning, but an understanding of data, storage Jordan and access patterns. Additionally, these features did not Jordan cause our group considerable administrative overhead. I won't dispute the specifics. I have only worked on the DB2 engine - never written an app for it nor administered it. You're right - the bottomline is that you can get a significant performance advantage provided you care enough to understand what's going on. Anyway, I merely responded to provide a data point. Will PostgreSQL users/administrators care for additional knobs or is there a preference for keep it simple, stupid ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Experimental patch for inter-page delay in VACUUM
Tom Lane wrote: Attached is an extremely crude prototype patch for making VACUUM delay by a configurable amount between pages, Cool! Assuming that this is found to be useful, the following issues would have to be dealt with before the patch would be production quality: 2. I only bothered to insert delays in the processing loops of plain VACUUM and btree index cleanup. VACUUM FULL and cleanup of non-btree indexes aren't done yet. I thought we didn't want the delay in vacuum full since it locks things down, we want vacuum full to finish ASAP. As opposed to normal vacuum which would be fired by the autovacuum daemon. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Rule regression failure freebsd?
See attached regression.diffs. Chris parallel group (5 tests): select_views portals_p2 cluster foreign_key rules select_views ... ok portals_p2 ... ok rules... FAILED foreign_key ... ok cluster ... ok parallel group (14 tests): limit truncate prepare sequence copy2 without_oid po lymorphism domain rangefuncs stats conversion temp plpgsql alter_table limit... ok plpgsql ... ok copy2... ok temp ... ok domain ... ok rangefuncs ... ok prepare ... ok without_oid ... ok conversion ... ok truncate ... ok alter_table ... ok sequence ... ok polymorphism ... ok stats... ok == shutting down postmaster == === 1 of 93 tests failed. === *** ./expected/rules.outThu Sep 25 14:58:06 2003 --- ./results/rules.out Fri Oct 31 13:21:22 2003 *** *** 1314,1320 SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename; tablename |rulename | definition ! ---+-+--- pg_settings | pg_settings_n | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING; pg_settings | pg_settings_u | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config; rtest_emp | rtest_emp_del | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) VALUES (old.ename, current_user(), 'fired'::bpchar, '$0.00'::money, old.salary); --- 1314,1320 SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename; tablename |rulename| definition ! ++- pg_settings| pg_settings_n | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING; pg_settings| pg_settings_u | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config; rtest_emp | rtest_emp_del | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) VALUES (old.ename, current_user(), 'fired'::bpchar, '$0.00'::money, old.salary); *** *** 1339,1350 rtest_v1 | rtest_v1_del| CREATE RULE rtest_v1_del AS ON DELETE TO rtest_v1 DO INSTEAD DELETE FROM rtest_t1 WHERE (rtest_t1.a = old.a); rtest_v1 | rtest_v1_ins| CREATE RULE rtest_v1_ins AS ON INSERT TO rtest_v1 DO INSTEAD INSERT INTO rtest_t1 (a, b) VALUES (new.a, new.b); rtest_v1 | rtest_v1_upd| CREATE RULE rtest_v1_upd AS ON UPDATE TO rtest_v1 DO INSTEAD UPDATE rtest_t1 SET a = new.a, b = new.b WHERE (rtest_t1.a = old.a); shoelace | shoelace_del| CREATE RULE shoelace_del AS ON DELETE TO shoelace DO INSTEAD DELETE FROM shoelace_data WHERE (shoelace_data.sl_name = old.sl_name); shoelace | shoelace_ins| CREATE RULE shoelace_ins AS ON INSERT TO shoelace DO INSTEAD INSERT INTO shoelace_data (sl_name, sl_avail, sl_color, sl_len, sl_unit) VALUES (new.sl_name, new.sl_avail, new.sl_color,
Re: [HACKERS] 7.4RC1 planned for Monday
On Thu, 30 Oct 2003, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: On Thu, 30 Oct 2003, Tom Lane wrote: rule/foreign key interaction reported by Michele Bendazzoli In the interests of disclosure, if the case in question for the rule fails, almost certainly deferred fk constraints will as well which I think makes this a must fix for 7.4 and is another push to getting a 7.3.5. Hm, does Jan's just-committed fix address the concern you had? Head now passes the case I'd thought of: create table ta1(a int primary key); create table ta2(a int references ta1 initially deferred); begin; insert into ta2 values (3); update ta2 set a=3 where a=3; -- should error, but might not if the update isn't checked end; I'm thinking that this is another test that probably belongs in the foreign key regression. Does anyone object to me sending a patch to add this and a couple of related cases? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Rule regression failure freebsd?
Christopher Kings-Lynne [EMAIL PROTECTED] writes: See attached regression.diffs. Looks like Jan forgot to update this expected file to match his changes. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Experimental patch for inter-page delay in VACUUM
Matthew T. O'Connor [EMAIL PROTECTED] writes: Tom Lane wrote: 2. I only bothered to insert delays in the processing loops of plain VACUUM and btree index cleanup. VACUUM FULL and cleanup of non-btree indexes aren't done yet. I thought we didn't want the delay in vacuum full since it locks things down, we want vacuum full to finish ASAP. As opposed to normal vacuum which would be fired by the autovacuum daemon. My thought was that it'd be up to the user to set vacuum_page_delay appropriately for what he is doing. It might or might not ever make sense to use a nonzero delay in VACUUM FULL, but the facility should be there. (Since plain and full VACUUM share the same index cleanup code, it would take some klugery to implement a policy of no delays for VACUUM FULL anyway.) Best practice would likely be to leave the default vacuum_page_delay at zero, and have the autovacuum daemon set a nonzero value for vacuums it issues. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] static pg_dump
Hey guys, Is there any way we could build a static version of the 7.4 pg_dump, to make it easy to dump existing databases using the 7.4 dump? Otherwise, it's quite difficult to arrange to have two different postgres installations, etc... Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
