Re: [HACKERS] Patch for automating partitions in PostgreSQL 8.4 Beta 2
gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing - fwrapv -bundle -multiply_defined suppress regress.o - bundle_loader ../../../src/backend/postgres -L../../../src/port -o regress.so cp ../../../contrib/spi/refint.so refint.so cp ../../../contrib/spi/autoinc.so autoinc.so gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing - fwrapv pg_regress.o pg_regress_main.o -L../../../src/port -Wl,- dead_strip_dylibs -lpgport -lz -lreadline -lm -o pg_regress make -C config all make[1]: Nothing to be done for `all'. /bin/sh: /Users/gj/Projects/postgres-head/pgsql/partition.sh: No such file or directory make: *** [all] Error 127 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for automating partitions in PostgreSQL 8.4 Beta 2
PFA. This file is to be kept in 'pgsql_init' base directory. On Tue, Jun 9, 2009 at 12:54 PM, Grzegorz Jaskiewicz g...@pointblue.com.plwrote: gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -bundle -multiply_defined suppress regress.o -bundle_loader ../../../src/backend/postgres -L../../../src/port -o regress.so cp ../../../contrib/spi/refint.so refint.so cp ../../../contrib/spi/autoinc.so autoinc.so gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv pg_regress.o pg_regress_main.o -L../../../src/port -Wl,-dead_strip_dylibs -lpgport -lz -lreadline -lm -o pg_regress make -C config all make[1]: Nothing to be done for `all'. /bin/sh: /Users/gj/Projects/postgres-head/pgsql/partition.sh: No such file or directory make: *** [all] Error 127 partition.sh Description: Bourne shell script -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
Floris Bos / Maxnet wrote: I am having the problem that some queries are unable to find rows when using the index. When I force a sequential scan, by doing set enable_indexscan=false; set enable_bitmapscan=false;, the same queries work fine. Not a hacker myself, but I can tell you that the first question you'll be asked is can you produce a test case? If you can generate the problem from a test table+generated data that will let people figure out the problem for you. If not, details of the table schema will be needed, and is there any pattern to the missed rows? Also - compile settings, character set and locale details might be relevant too. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
And can you post an explain plan for the incorrect scan? In particular is it using a bitmap index scan or a regular index scan? Or does it happen with either? -- Greg On 9 Jun 2009, at 09:43, Richard Huxton d...@archonet.com wrote: Floris Bos / Maxnet wrote: I am having the problem that some queries are unable to find rows when using the index. When I force a sequential scan, by doing set enable_indexscan=false; set enable_bitmapscan=false;, the same queries work fine. Not a hacker myself, but I can tell you that the first question you'll be asked is can you produce a test case? If you can generate the problem from a test table+generated data that will let people figure out the problem for you. If not, details of the table schema will be needed, and is there any pattern to the missed rows? Also - compile settings, character set and locale details might be relevant too. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for automating partitions in PostgreSQL 8.4 Beta 2
Hi, The patch automates table partitioning to support Range and Hash partitions. Please refer to attached readme file for further details. The syntax used conforms to most of the suggestions mentioned in http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php, barring the following: -- Specification of partition names is optional. System will be able to generate partition names in such cases. -- Sub partitioning Some comments based on a brief glance of the patch: - The logic to execute the partition triggers last still needs some more work IMHO. Relying on just the names might not get accepted. I think you should pay attention to Andrew Dunstan's suggestion in an earlier mail to have tgkind enumerations to generalize the same or discuss it further. the scheme should turn tgisconstraint into a multi-valued item (tgkind: 'u' = userland, 'c'= constraint, 'p' = partition or some such). - Similarly, assigning of_relname_oid names to overflow tables also might not work. The best way ahead could be to invent a new relkind RELKIND_OVERFLOW to handle it. Or maybe we can have a new schema pg_overflow to store the overflow relation with the same name (suffixed with _overflow to make it clearer) as the parent relation too. The relkind solution might be cleaner though. This might need further discussion. In general, it is definitely not a bad idea to discuss such sub-problems on the list :) - Am I reading the patch correctly that you do not end up creating indexes on the children tables? That is a big problem! - You can remove the remnants of the first patch like the MutateColumnRefs() function, for example (I agree this is WIP, but unwanted/unused functions unnecessarily add to the size). With large patches, the more precise the patch, the better it will be for reviewers/readers. Great work all in all! Regards, Nikhils -- http://www.enterprisedb.com
Re: [HACKERS] Patch for automating partitions in PostgreSQL 8.4 Beta 2
still doesn't work: make[1]: Leaving directory `/home/gjaskie/Projects/postgres/pgsql/config' /home/gjaskie/Projects/postgres/pgsql/partition.sh: line 14: a.keyorder,: command not found /home/gjaskie/Projects/postgres/pgsql/partition.sh: line 15: where: command not found : command not foundcts/postgres/pgsql/partition.sh: line 16: Please make sure you test patches before sending here, on clean checkout!. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
Hi,
Richard Huxton wrote:
Not a hacker myself, but I can tell you that the first question you'll
be asked is can you produce a test case? If you can generate the
problem from a test table+generated data that will let people figure out
the problem for you.
Unfortunately, I have not been able to produce a test case (yet) on a
small data set.
While the data in the database is public information, the whole database
is about 100 GB, and therefore kinda hard to share.
If not, details of the table schema will be needed, and is there any
pattern to the missed rows? Also - compile settings, character set and
locale details might be relevant too.
==
Compile settings
==
No fancy settings.
- Clean Opensolaris 2009.06 installation
- Installed gcc and gmake packages.
- Downloaded source and did a ./configure --disable-readline ; gmake ;
gmake install
==
Postgresql settings
==
The following settings differ from the defaults:
--
shared_buffers=3500MB
maintenance_work_mem = 128MB
fsync = off
synchronous_commit = off
checkpoint_segments = 25
--
The locale used when creating the database is SQL_ASCII
==
Hardware
==
Tyan barebone
2x Opteron 2376 quadcore
32 GB reg ecc memory
1x Intel X25-E 32 GB SSD for OS and pg_xlog directory
2x Intel X25-E 64 GB SSD (ZFS striping) for the database
==
Table layout
==
--
Table public.posts_index
Column | Type |
Modifiers
++---
cid| integer| not null default
nextval('posts_index_cid
_seq'::regclass)
groupid| integer| not null
startdate | integer| not null
poster | character varying(64) | not null
basefile | character varying(64) | not null
subject| character varying(255) | not null
size | real |
nfo| boolean|
c | boolean|
parts | integer|
totalparts | integer|
imdb | integer|
ng1| boolean| default false
g2 | integer| default 0
g3 | integer| default 0
data | bytea |
Indexes:
posts_index5_pkey PRIMARY KEY, btree (cid) CLUSTER
gr_idx btree (groupid, (- cid))
pgb_idx btree (poster, groupid, basefile)
--
Only noticed problems with the pgb_idx index so far.
The problem only occurs on a subset of the rows, at a time.
After adding/updating rows and doing a reindex, the rows that were
missing before sometimes suddenly do work, but then different ones do not.
And can you post an explain plan for the incorrect scan? In particular is it
using a bitmap index scan or a regular index scan? Or does it happen with
either?
Happens with both.
Index scan:
===
= explain SELECT count(*) FROM posts_index WHERE
poster='y...@power-post.org (Yenc-PP-AA)' AND groupid=300 AND
basefile='NIB8124849';
QUERY PLAN
Aggregate (cost=11.25..11.26 rows=1 width=0)
- Index Scan using pgb_idx on posts_index (cost=0.00..11.25
rows=1 width=0)
Index Cond: (((poster)::text = 'y...@power-post.org
(Yenc-PP-AA)'::text) AND (groupid = 300) AND ((basefile)::text =
'NIB8124849'::text))
= SELECT count(*) FROM posts_index WHERE poster='y...@power-post.org
(Yenc-PP-AA)' AND groupid=300 AND basefile='NIB8124849';
count
---
0
===
When I disable index scan, it uses bitmap without luck:
==
= set enable_indexscan=false;
SET
= explain SELECT count(*) FROM posts_index WHERE
poster='y...@power-post.org (Yenc-PP-AA)' AND groupid=300 AND
basefile='NIB8124849';
QUERY PLAN
--
Aggregate (cost=11.26..11.27 rows=1 width=0)
- Bitmap Heap Scan on posts_index (cost=7.24..11.26 rows=1 width=0)
Recheck Cond: (((poster)::text = 'y...@power-post.org
(Yenc-PP-AA)'::text) AND (groupid = 300) AND ((basefile)::text =
'NIB8124849'::text))
- Bitmap Index Scan on pgb_idx (cost=0.00..7.24 rows=1 width=0)
Index Cond: (((poster)::text = 'y...@power-post.org
(Yenc-PP-AA)'::text) AND (groupid = 300) AND ((basefile)::text =
'NIB8124849'::text))
= SELECT count(*) FROM posts_index WHERE poster='y...@power-post.org
(Yenc-PP-AA)' AND groupid=300 AND basefile='NIB8124849';
count
---
0
==
Sequential scan does find the row:
==
= set enable_indexscan=false;
SET
= set enable_bitmapscan=false;
SET
= explain SELECT count(*) FROM posts_index WHERE
poster='y...@power-post.org (Yenc-PP-AA)' AND groupid=300 AND
basefile='NIB8124849';
QUERY PLAN
Re: [HACKERS] Patch for automating partitions in PostgreSQL 8.4 Beta 2
I did a fresh checkout and applied patch and added files and it works at my
end.
Is there any problem with formatting of the file? May be some
characters('\') missing in conversion?
On Tue, Jun 9, 2009 at 4:14 PM, gj g...@pointblue.com.pl wrote:
still doesn't work:
make[1]: Leaving directory `/home/gjaskie/Projects/postgres/pgsql/config'
/home/gjaskie/Projects/postgres/pgsql/partition.sh: line 14: a.keyorder,:
command not found
/home/gjaskie/Projects/postgres/pgsql/partition.sh: line 15: where: command
not
found
: command not foundcts/postgres/pgsql/partition.sh: line 16:
Please make sure you test patches before sending here, on clean checkout!.
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
Floris Bos / Maxnet b...@je-eigen-domein.nl writes: Richard Huxton wrote: Not a hacker myself, but I can tell you that the first question you'll be asked is can you produce a test case? If you can generate the problem from a test table+generated data that will let people figure out the problem for you. Unfortunately, I have not been able to produce a test case (yet) on a small data set. While the data in the database is public information, the whole database is about 100 GB, and therefore kinda hard to share. Seems like we'd only need a dump of the one problem table, not the entire database. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for automating partitions in PostgreSQL 8.4 Beta 2
On Tue, 9 Jun 2009 at 13:52:08, Kedar Potdar wrote:
I did a fresh checkout and applied patch and added files and it works at my
end.
Is there any problem with formatting of the file? May be some
characters('\') missing in conversion?
For one, I think you should put it around in quotes, when you echo something out
- just in case.
Second, isn't there any better way to do it, than in shell script ?
Shouldn't that bit be called on make check, not on build (make).?
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [Fwd: Re: [HACKERS] dblink patches for comment]
Joe Conway m...@joeconway.com writes:
I think the attached is what you had in mind. But I don't know right off
how to trigger the failure (and therefore how to test the solution). A
naive test with two databases, one LATIN2, the other UTF8 does not
produce the error with simple text literals.
I can reproduce an error (and verify the patch corrects it) using this
test case:
select 'àx÷y'::text as x;
select * from dblink('dbname = u8', $$select 'àx÷y'::text$$)
as t1 (x text);
(The two non-ASCII characters are octal 340 and 367, if they don't come
through properly in your mail.) Execute in a LATIN1 database (being sure
client_encoding is also LATIN1), connecting to a database with encoding
UTF8. With the patch, both commands give the same results; without,
I get
ERROR: invalid byte sequence for encoding UTF8: 0xe078f7
HINT: This error can also happen if the byte sequence does not match the
encoding expected by the server, which is controlled by client_encoding.
CONTEXT: Error occurred on dblink connection named unnamed: could not
execute query.
Please get this committed soon, we have other stuff to get done
(like a pgindent run).
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] page is uninitialized --- fixing
A couple of people in recent years have had a problem with page X is uninitialised -- fixing messages. I have a case now with 569357 consecutive pages that required fixing in pg_attribute. We looked at pages by hand and they really are uninitialised, but otherwise what we would expect for size, name etc.. Clearly this is way too many pages to be easily explainable. Historically, this type of error has occurred mostly on servers that have been through a recovery, so I have investigated it with that in mind as a potential error source. Nothing found on that score, though rsync is in use, as before. One factor here is that temp tables are very heavily used. The size of the pg_attribute table is *roughly* what I would expect, given the frequency of temp table creation, numbers of cols used and lack of vacuum. The server did have non-ECC memory and there have been a few other memory issues, but I'm still a little worried by this. A completely separate client has twice had corrupted indexes on pg_class in last 6 months, again a heavy user of temp tables. I've looked for issues around the idea of all-temp catalog pages causing an problem, but not seen anything as yet. Any issues or ideas worth investigating? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Problem with listen_addresses = '*' on 8.4beta2 on AIX
Building 8.4beta2 on my AIX test machine works fine, but when I set listen_addresses = '*' in postgresql.conf, the server fails to start. This is what I get: LOG: 0: could not translate service 5432 to address: Hostname and service name not provided or found LOCATION: StreamServerPort, pqcomm.c:294 WARNING: 01000: could not create listen socket for * LOCATION: PostmasterMain, postmaster.c:843 FATAL: XX000: could not create any TCP/IP sockets LOCATION: PostmasterMain, postmaster.c:848 It works fine if listen_addresses is the empty string or the hostname, only with the asterisk it seems to fail. These are the relevant entries in postgresql.conf: listen_addresses = '*' port = 5432 Relevant parts of pg_config: CONFIGURE = '--prefix=/postgres/8.4' '--with-includes=/usr/local/include' '--with-libraries=/usr/local/lib' '--enable-debug' '--enable-cassert' '--with-ldap' '--without-readline' '--enable-integer-datetimes' '--without-zlib' '--enable-thread-safety' 'CC=gcc -maix64' 'LDFLAGS=-Wl,-bbigtoc' CC = gcc -maix64 CPPFLAGS = -I/usr/local/include CFLAGS = -O0 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels -fno-strict-aliasing -g CFLAGS_SL = LDFLAGS = -Wl,-bbigtoc -L/usr/local/lib -Wl,-blibpath:/postgres/8.4/lib:/usr/local/lib:/usr/lib:/lib LDFLAGS_SL = -Wl,-bnoentry -Wl,-H512 -Wl,-bM:SRE LIBS = -lpgport -lld -lm VERSION = PostgreSQL 8.4beta2 The operating system is AIX 5.3 ML 03. This change must have broken the code: http://archives.postgresql.org/pgsql-committers/2009-01/msg00297.php If listen_addresses is *, then getaddrinfo() will be called with NULL for both the first and the second argument, which is not allowed according to the documentation: http://publib.boulder.ibm.com/infocenter/pseries/v5r3/index.jsp?topic=/com.ibm.aix.commtechref/doc/commtrf2/getaddrinfo.htm and should and does return EAI_NONAME = 8. I guess that the buildfarm did not fail because it does not use '*'. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
On Tue, 2009-06-09 at 13:40 +0200, Floris Bos / Maxnet wrote: fsync = off That's a bad plan if you care about your database. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Cursor with hold emits the same row more than once across commits in 8.3.7
Mark Kirkwood mark.kirkw...@catalyst.net.nz writes: A construction of the form DECLARE cur CURSOR WITH HOLD FOR SELECT * FROM obj loop FETCH 1000 FROM cur process 'em COMMIT results in some of the same rows being emitted more than once, altho the final rowcount is correct (i.e some rows end up being never seen). I poked into this a bit, and it looks sort of nasty. Mark's immediate complaint is a consequence of the synchronize_seqscan patch, but there are other issues too. The problem comes from the fact that a WITH HOLD cursor is initially treated the same as a regular cursor, ie, we just fetch on demand. If it's still open at transaction commit, we do this: ExecutorRewind(); fetch all the rows into a tuplestore; advance the tuplestore past the number of rows previously fetched; and then later transactions can fetch-on-demand from the tuplestore. The reason for the bug is that with synchronize_seqscan on, a SeqScan node that gets rewound does not necessarily restart from the same point in the table that it initially started reading from. So the initial fetch grabs 1000 rows, but then when we rewind, the first 1000 rows loaded into the tuplestore may come from a different range of the table. This does not only affect cursors WITH HOLD. Some paths in the cursor MOVE logic also rely on ExecutorRewind to behave sanely. We could probably fix this specific issue by refactoring things in such a way that the seqscan start point is frozen on the first read and re-used after rewinds. However, it strikes me also that a cursor query containing volatile functions is going to cause some similar issues --- you can't just re-execute the query for the same rows and expect to get stable results. What should we do about that? The technically best solution is probably similar to what Materialize nodes do, ie, read the query only once and be careful to stash rows aside into a tuplestore as they are read. This would fix both issues with one patch. The problem with that is that if the user doesn't actually do any backwards fetching, you waste all the tuplestore maintenance work. Or we could just document that cursors containing volatile functions don't behave stably if you try to read backwards; or try to enforce that you can't do so. The volatile-function issue has been there since the dawn of time, and we've never had a complaint about it AFAIR. So maybe trying to fix it isn't a good thing and we should just document the behavior. But the syncscan instability is new and probably ought to be dealt with. Thoughts? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
* Simon Riggs: On Tue, 2009-06-09 at 13:40 +0200, Floris Bos / Maxnet wrote: fsync = off That's a bad plan if you care about your database. It shouldn't introduce this type of corruption, though. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
Hi, Simon Riggs wrote: On Tue, 2009-06-09 at 13:40 +0200, Floris Bos / Maxnet wrote: fsync = off That's a bad plan if you care about your database. I am aware of the risk of dataloss in case of power failure, etc. However fsync=on is simply too slow for my purpose, and it concerns data that can be regenerated from its source. The website this setup is for has been running various previous versions of PostgreSQL with fsync=off since 2005. So I still expect it to work. Yours sincerely, Floris Bos -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Not quite a security hole in internal_in
I noticed the following core-dump situation in CVS HEAD: regression=# select array_agg_finalfn(null); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. (You won't see a crash if you don't have Asserts on.) The proximate cause of this is that array_agg_finalfn is being a bit overoptimistic about what it can Assert: /* cannot be called directly because of internal-type argument */ Assert(fcinfo-context (IsA(fcinfo-context, AggState) || IsA(fcinfo-context, WindowAggState))); if (PG_ARGISNULL(0)) PG_RETURN_NULL(); /* returns null iff no input values */ We should switch the order of the null-test and the Assert. However, this brings up the question of exactly why the assumption embedded in that comment is wrong. You're not supposed to be able to call internal-accepting functions from SQL, and yet here I did so. The reason I could get past the type-safety check is that internal_in, which normally throws an error if one tries to create a constant of type internal, is marked STRICT in pg_proc, and so it doesn't get called for nulls. This would be a serious security problem if it weren't for the fact that nearly all internal-accepting functions in the backend are also marked STRICT, and so they won't get called in this type of scenario. A query to pg_proc shows that the only ones that aren't strict are regression=# select oid::regprocedure from pg_proc where 'internal'::regtype = any (proargtypes) and not proisstrict; oid array_agg_transfn(internal,anyelement) array_agg_finalfn(internal) domain_recv(internal,oid,integer) (3 rows) The first two are new in 8.4, and the third has adequate defenses already. So we don't have a security hole in any released version right now. However, this is obviously something that could bite us in the future. What I think we should do about it is mark internal_in as nonstrict, so that it gets called and can throw error for a null. Probably the same for all the other pseudotypes in pseudotypes.c, although internal is the only one that we consider to be a security-critical datatype. Normally we would consider a pg_proc change as requiring a catversion bump. Since we are already past 8.4 beta we couldn't do that without forcing an initdb for beta testers. What I'd like to do about this is change the proisstrict settings in pg_proc.h but not bump catversion. This will ensure the fix is in place and protecting future coding, although possibly not getting enforced in 8.4 production instances that were upgraded from beta (if there are any such). Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [Fwd: Re: [HACKERS] dblink patches for comment]
Tom Lane wrote: Please get this committed soon, we have other stuff to get done (like a pgindent run). Thanks -- committed. Joe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not quite a security hole in internal_in
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Normally we would consider a pg_proc change as requiring a catversion bump. Since we are already past 8.4 beta we couldn't do that without forcing an initdb for beta testers. I think a serious issue like this warrants a bump. It seems like you are saying that at any other time in the release cycle this would be an automatic bump, so let's keep a consistent policy and bump it. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200906091241 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkoukLkACgkQvJuQZxSWSshalACg8UfcyvTF2TxazvwwzxDNDIuM dpEAoJYVaS8czeR79dyJOTAoXLghSgKS =21ax -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not quite a security hole in internal_in
On Tue, Jun 9, 2009 at 11:31 AM, Tom Lanet...@sss.pgh.pa.us wrote: Normally we would consider a pg_proc change as requiring a catversion bump. Since we are already past 8.4 beta we couldn't do that without forcing an initdb for beta testers. What I'd like to do about this is change the proisstrict settings in pg_proc.h but not bump catversion. This will ensure the fix is in place and protecting future coding, although possibly not getting enforced in 8.4 production instances that were upgraded from beta (if there are any such). why not bump it just at the final release. i don't think beta testers are on production so they still have to initdb production servers anyway -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
Well sure it could -- once. It wouldn't be reproducible in a freshly rebuilt index unless he's crashing his machine every time. -- Greg On 9 Jun 2009, at 17:12, Florian Weimer fwei...@bfk.de wrote: * Simon Riggs: On Tue, 2009-06-09 at 13:40 +0200, Floris Bos / Maxnet wrote: fsync = off That's a bad plan if you care about your database. It shouldn't introduce this type of corruption, though. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not quite a security hole in internal_in
Jaime Casanova jcasa...@systemguards.com.ec writes: why not bump it just at the final release. There aren't going to be any more betas, so it's now or not at all. I don't think we want to plan a catversion bump between RC and final. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
Floris, The website this setup is for has been running various previous versions of PostgreSQL with fsync=off since 2005. So I still expect it to work. You've been lucky, that's all. Our documentation has been clear, back to version 7.0, that turning fsync=off carries the risk that you will have to recreate your entire database in the event of unexpected shutdown. That's not new. So, the operative question is: was 8.4 shut down with -immediate or otherwise unexpectedly? If so, then we don't have a bug. If 8.4 was never shut down, then we have some strange behavior which bears looking into. And you've found a wierd corner case, which is what we count on our users for. Thanks for testing. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not quite a security hole in internal_in
Tom Lane wrote: Normally we would consider a pg_proc change as requiring a catversion bump. Since we are already past 8.4 beta we couldn't do that without forcing an initdb for beta testers. What I'd like to do about this is change the proisstrict settings in pg_proc.h but not bump catversion. This will ensure the fix is in place and protecting future coding, although possibly not getting enforced in 8.4 production instances that were upgraded from beta (if there are any such). How common is this scenario? It's certainly not something I ever do. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
* Josh Berkus: Our documentation has been clear, back to version 7.0, that turning fsync=off carries the risk that you will have to recreate your entire database in the event of unexpected shutdown. That's not new. The documentation does not say this. Instead, there's the following rather explicit explanation that only OS crashes matter: | (Crashes of the database software itself are not a risk factor | here. Only an operating-system-level crash creates a risk of | corruption.) If it really matters how PostgreSQL is shut down in fsync = off mode (while the operating system keeps running), the documentation is seriously wrong here. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pgindent run coming
It is time to run pgindent on CVS HEAD for 8.4. I am thinking of running it at zero-hour GMT tomorrow, meaning five hours from now. Any objections? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not quite a security hole in internal_in
On Tue, Jun 9, 2009 at 12:41 PM, Greg Sabino Mullaneg...@turnstep.com wrote: Normally we would consider a pg_proc change as requiring a catversion bump. Since we are already past 8.4 beta we couldn't do that without forcing an initdb for beta testers. I think a serious issue like this warrants a bump. It seems like you are saying that at any other time in the release cycle this would be an automatic bump, so let's keep a consistent policy and bump it. I agree. We don't want people who are running beta2 to think that nothing has changed when that's actually not the case. If someone is really inconvenienced by it and wants to ignore this problem, they can find a way to bypass the check. I suspect there probably aren't very many such people, though. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Cursor with hold emits the same row more than once across commits in 8.3.7
On Tue, Jun 9, 2009 at 12:07 PM, Tom Lanet...@sss.pgh.pa.us wrote: The technically best solution is probably similar to what Materialize nodes do, ie, read the query only once and be careful to stash rows aside into a tuplestore as they are read. This would fix both issues with one patch. The problem with that is that if the user doesn't actually do any backwards fetching, you waste all the tuplestore maintenance work. This seems like the only option that will produce correct answers, so it gets my vote. How much is the performance penalty for materializing the tuplestore? I'm inclined to think that whatever it is, you just have to pay it if you ask for a WITH HOLD cursor. I suppose in theory you could try to figure out whether materialization is really necessary (let's see... no seqscans here and no volatile functions... ok, so we can cheat...) but that seems likely to lead to future bugs as the rules for which things are safe change. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
Florian Weimer wrote: * Josh Berkus: Our documentation has been clear, back to version 7.0, that turning fsync=off carries the risk that you will have to recreate your entire database in the event of unexpected shutdown. That's not new. The documentation does not say this. Instead, there's the following rather explicit explanation that only OS crashes matter: | (Crashes of the database software itself are not a risk factor | here. Only an operating-system-level crash creates a risk of | corruption.) If it really matters how PostgreSQL is shut down in fsync = off mode (while the operating system keeps running), the documentation is seriously wrong here. Yeah, AFAICT the writes are handed off to the operating system (just not synced), so if it flushes its caches sanely at all there shouldn't be a problem. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Cursor with hold emits the same row more than once across commits in 8.3.7
Robert Haas escribió: I suppose in theory you could try to figure out whether materialization is really necessary (let's see... no seqscans here and no volatile functions... ok, so we can cheat...) but that seems likely to lead to future bugs as the rules for which things are safe change. Another thing we could do is disable syncscan if we see a WITH HOLD cursor, but I guess it's not future-proof either. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not quite a security hole in internal_in
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: This will ensure the fix is in place and protecting future coding, although possibly not getting enforced in 8.4 production instances that were upgraded from beta (if there are any such). How common is this scenario? It's certainly not something I ever do. I would agree that it should be pretty darn rare. But even so, this is not a fix for an immediate bug but just safety against possible future bugs. So even if there is somebody out there who manages to miss having the fix, I think they are not at serious risk. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Cursor with hold emits the same row more than once across commits in 8.3.7
Robert Haas robertmh...@gmail.com writes: This seems like the only option that will produce correct answers, so it gets my vote. How much is the performance penalty for materializing the tuplestore? I'm inclined to think that whatever it is, you just have to pay it if you ask for a WITH HOLD cursor. I don't mind paying it for a WITH HOLD cursor, since by definition you're asking for a more expensive behavior there. The thing that is bothering me more is whether we want to pay a price for a *non* WITH HOLD cursor. You can get instability for seqscan or volatile functions if you just try MOVE BACKWARD ALL and re-read. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [BUGS] Cursor with hold emits the same row more than once across commits in 8.3.7
On Tue, 2009-06-09 at 12:07 -0400, Tom Lane wrote: We could probably fix this specific issue by refactoring things in such a way that the seqscan start point is frozen on the first read and re-used after rewinds. I don't know what you mean by frozen exactly, but the start point of a synchronized scan is stored in shared memory; otherwise, it wouldn't know where to stop. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
Alvaro Herrera alvhe...@commandprompt.com wrote: Yeah, AFAICT the writes are handed off to the operating system (just not synced), so if it flushes its caches sanely at all there shouldn't be a problem. I would certainly *hope* that's the case. We sometimes use fsync=off for conversions, where we plan to just start over if the conversion crashes, and set it to on when the conversion is done. It would be disturbing to discover that fsync=off also means don't bother to write dirty buffers to the OS before shutdown. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [BUGS] Cursor with hold emits the same row more than once across commits in 8.3.7
On Tue, 2009-06-09 at 10:51 -0700, Jeff Davis wrote: On Tue, 2009-06-09 at 12:07 -0400, Tom Lane wrote: We could probably fix this specific issue by refactoring things in such a way that the seqscan start point is frozen on the first read and re-used after rewinds. I don't know what you mean by frozen exactly, but the start point of a synchronized scan is stored in shared memory; otherwise, it wouldn't know where to stop. Correction: I didn't actually mean _shared_ memory there. It's just backend-local memory. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] page is uninitialized --- fixing
Simon Riggs si...@2ndquadrant.com writes: A couple of people in recent years have had a problem with page X is uninitialised -- fixing messages. I have a case now with 569357 consecutive pages that required fixing in pg_attribute. We looked at pages by hand and they really are uninitialised, but otherwise what we would expect for size, name etc.. What PG version? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
Alvaro, Kevin, Yeah, AFAICT the writes are handed off to the operating system (just not synced), so if it flushes its caches sanely at all there shouldn't be a problem. I would certainly *hope* that's the case. We sometimes use fsync=off for conversions, where we plan to just start over if the conversion crashes, and set it to on when the conversion is done. It would be disturbing to discover that fsync=off also means don't bother to write dirty buffers to the OS before shutdown. It doesn't. But what I don't trust, and the *first* place I'd look for problems, is whether the OS flushes *all* dirty buffers to disk in the event the application gets killed. That's why I want more information on Floris' case. Was 8.4 killed or shut down with -m immediate? Or the os rebooted with 8.4 running? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Cursor with hold emits the same row more than once across commits in 8.3.7
Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: This seems like the only option that will produce correct answers, so it gets my vote. How much is the performance penalty for materializing the tuplestore? I'm inclined to think that whatever it is, you just have to pay it if you ask for a WITH HOLD cursor. I don't mind paying it for a WITH HOLD cursor, since by definition you're asking for a more expensive behavior there. The thing that is bothering me more is whether we want to pay a price for a *non* WITH HOLD cursor. You can get instability for seqscan or volatile functions if you just try MOVE BACKWARD ALL and re-read. I would expect to pay more for a scrollable cursor than non- scrollable; and in fact, the fine manual says Depending upon the complexity of the query's execution plan, specifying SCROLL might impose a performance penalty on the query's execution time. That would tend to argue in favor of taking the time to produce correct answers. It does raise a question, though, about another sentence in the same paragraph: The default is to allow scrolling in some cases; this is not the same as specifying SCROLL. Either we make people pay for this when they haven't specified SCROLL but PostgreSQL has historically given it to them anyway, or we might break existing applications. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Cursor with hold emits the same row more than once across commits in 8.3.7
On Tue, Jun 9, 2009 at 1:47 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: This seems like the only option that will produce correct answers, so it gets my vote. How much is the performance penalty for materializing the tuplestore? I'm inclined to think that whatever it is, you just have to pay it if you ask for a WITH HOLD cursor. I don't mind paying it for a WITH HOLD cursor, since by definition you're asking for a more expensive behavior there. The thing that is bothering me more is whether we want to pay a price for a *non* WITH HOLD cursor. You can get instability for seqscan or volatile functions if you just try MOVE BACKWARD ALL and re-read. [ reads the fine manual ] It seems like we need to materialize if you ask for WITH HOLD or SCROLL. I guess the question is what to do if you haven't specified either and then try to scroll anyway. The manual says that it may fail, but it doesn't say that might seem to work but actually return wrong answers. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] information_schema.columns changes needed for OLEDB
On Monday 08 June 2009 07:12:33 Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: On Sunday 31 May 2009 18:41:55 Tom Lane wrote: AFAICS, the SQL standard demands that precision and scale fields be non-null all the time for those data types where they make sense (this is encoded in the CHECK CONSTRAINTs that are declared for the various information-schema tables, see particularly 21.15 DATA_TYPE_DESCRIPTOR base table in SQL99). DATE is clearly wrong per spec, but it's not the only problem. The DATE change is the only thing I'd be prepared to make right now. At this point I think the clear decision is we're not changing anything for 8.4. I've put the issue on the TODO list for future development cycles. After gathering that there will probably be some other changes before release that will require an initdb (even without catversion bump), and after reexamining the issue, I think it's trivial and uncontroversial to fix the datetime issues: diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 9c5672f..cb0296a 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -160,12 +160,12 @@ CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer RETURNS NULL ON NULL INPUT AS $$SELECT - CASE WHEN $2 = -1 /* default typmod */ - THEN null + CASE WHEN $1 IN (1082) /* date */ + THEN 0 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */ - THEN $2 + THEN CASE WHEN $2 = -1 THEN 6 ELSE $2 END WHEN $1 IN (1186) /* interval */ - THEN $2 65535 + THEN CASE WHEN $2 = -1 THEN 6 ELSE $2 65535 END ELSE null END$$; I have also prepared a patch that creates more realistic values for character_octet_length based on encoding information, which I will propose for 8.5. The issue of whether to report null or some large value for unlimited length data types needs some more thought. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Cursor with hold emits the same row more than once across commits in 8.3.7
On Tue, Jun 9, 2009 at 1:00 PM, Kevin Grittnerkevin.gritt...@wicourts.gov wrote: the same paragraph: The default is to allow scrolling in some cases; in some cases... like in but not all... ? this doesn't sound like a vow to me. if the user really wants SCROLLing ability he should have been specified that way... i say pay the price for WITH HOLD and SCROLL and don't allow scrolling ability if SCROLL hasn't been specified -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] page is uninitialized --- fixing
On Tue, 2009-06-09 at 13:54 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: A couple of people in recent years have had a problem with page X is uninitialised -- fixing messages. I have a case now with 569357 consecutive pages that required fixing in pg_attribute. We looked at pages by hand and they really are uninitialised, but otherwise what we would expect for size, name etc.. What PG version? PG 8.3.7 on CentOS. Other client mentioned is 8.3 on SuSE. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] information_schema.columns changes needed for OLEDB
Peter Eisentraut pete...@gmx.net writes: diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 9c5672f..cb0296a 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -160,12 +160,12 @@ CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer RETURNS NULL ON NULL INPUT AS $$SELECT - CASE WHEN $2 = -1 /* default typmod */ - THEN null + CASE WHEN $1 IN (1082) /* date */ + THEN 0 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */ - THEN $2 + THEN CASE WHEN $2 = -1 THEN 6 ELSE $2 END WHEN $1 IN (1186) /* interval */ - THEN $2 65535 + THEN CASE WHEN $2 = -1 THEN 6 ELSE $2 65535 END ELSE null END$$; Just for the record, this is a perfect example of why -u format sucks. How many people think they can look at this and know exactly what the change does? I'll be back to weigh in on the merits of the patch after I've converted it to -c format so I can understand it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not quite a security hole in internal_in
On Tue, Jun 9, 2009 at 11:14 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Andrew Dunstan and...@dunslane.net writes:
Tom Lane wrote:
This will ensure the fix is in place and protecting future coding,
although possibly not getting enforced in 8.4 production instances that
were upgraded from beta (if there are any such).
How common is this scenario? It's certainly not something I ever do.
I would agree that it should be pretty darn rare. But even so, this
is not a fix for an immediate bug but just safety against possible
future bugs. So even if there is somebody out there who manages to miss
having the fix, I think they are not at serious risk.
Can we hold it till 8.4.1? Or is that not an option?
Best regards,
--
Lets call it Postgres
EnterpriseDB http://www.enterprisedb.com
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
Mail sent from my BlackLaptop device
Re: [HACKERS] information_schema.columns changes needed for OLEDB
Peter Eisentraut pete...@gmx.net writes: After gathering that there will probably be some other changes before release that will require an initdb (even without catversion bump), and after reexamining the issue, I think it's trivial and uncontroversial to fix the datetime issues: For the benefit of anyone else finding this hard to read, I've reformatted as *** src/backend/catalog/information_schema.sql.orig Tue Feb 24 11:10:16 2009 --- src/backend/catalog/information_schema.sql Tue Jun 9 14:21:37 2009 *** *** 160,171 RETURNS NULL ON NULL INPUT AS $$SELECT ! CASE WHEN $2 = -1 /* default typmod */ !THEN null WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */ !THEN $2 WHEN $1 IN (1186) /* interval */ !THEN $2 65535 ELSE null END$$; --- 160,171 RETURNS NULL ON NULL INPUT AS $$SELECT ! CASE WHEN $1 IN (1082) /* date */ !THEN 0 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */ !THEN CASE WHEN $2 = -1 THEN 6 ELSE $2 END WHEN $1 IN (1186) /* interval */ !THEN CASE WHEN $2 = -1 THEN 6 ELSE $2 65535 END ELSE null END$$; I would suggest coding the inner cases as CASE WHEN $2 0 THEN ...default... since the general practice in the C code is to treat any negative value as meaning unspecified. Otherwise, +1. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not quite a security hole in internal_in
Gurjeet Singh singh.gurj...@gmail.com writes: Can we hold it till 8.4.1? Or is that not an option? What advantage would that have? We certainly wouldn't wish to put a catversion change into 8.4.1. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] Cursor with hold emits the same row more than once across commits in 8.3.7
Jeff Davis pg...@j-davis.com writes: On Tue, 2009-06-09 at 10:51 -0700, Jeff Davis wrote: I don't know what you mean by frozen exactly, but the start point of a synchronized scan is stored in shared memory; otherwise, it wouldn't know where to stop. Correction: I didn't actually mean _shared_ memory there. It's just backend-local memory. Well, wherever it's stored, it's a demonstrable fact that we're not getting the same rows after ExecutorRewind(); and that we do get the same rows out if we disable synchronize_seqscans in Mark's test case. I haven't got round to identifying exactly what to change if we decide to go for a narrow fix instead of storing the query results at a higher level. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not quite a security hole in internal_in
Greg Sabino Mullane g...@turnstep.com writes: Normally we would consider a pg_proc change as requiring a catversion bump. Since we are already past 8.4 beta we couldn't do that without forcing an initdb for beta testers. I think a serious issue like this warrants a bump. It seems like you are saying that at any other time in the release cycle this would be an automatic bump, so let's keep a consistent policy and bump it. This type of argument comes up all the time during beta period, and we have made the decision both ways in the past. There isn't a consistent policy about it, it's case-by-case. The reason we bump catversion during development cycles is to keep developers from wasting their time chasing imaginary bugs when their backend executable is subtly incompatible with the contents of their databases. (As happened more than a few times, before we invented catversion :-(.) The bump is automatic only because it's cheaper to just do it than to think hard about whether you've created such a risk. This change doesn't create any compatibility issues of that sort, and unlike in development, there is a real cost to a catversion bump --- it will force an extra initdb on beta testers, who may have loaded databases of considerable size. For production releases, the argument to bump catversion is to be real sure that all 8.4 (or whatever) installations have the same initial catalog contents. That argument does apply here, but since this is just a protective change and not known to be needed to prevent any live bug, I don't think it's worth complicating beta testers' lives for. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
Josh Berkus j...@agliodbs.com writes: It doesn't. But what I don't trust, and the *first* place I'd look for problems, is whether the OS flushes *all* dirty buffers to disk in the event the application gets killed. Why wouldn't you trust it? The sort of thing you seem to be thinking about would require tracking which process(es) wrote each dirty buffer and then going back and dropping selected dirty buffers when a process exits abnormally. I can hardly imagine any OS wishing to do that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postmaster recovery and automatic restart suppression
Hi, -Original Message- From: ext Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, June 09, 2009 1:35 AM To: Robert Haas Cc: Greg Stark; Simon Riggs; Fujii Masao; Kolb, Harald (NSN - DE/Munich); pgsql-hackers@postgresql.org; Czichy, Thoralf (NSN - FI/Helsinki) Subject: Re: [HACKERS] postmaster recovery and automatic restart suppression Robert Haas robertmh...@gmail.com writes: I see that you've carefully not quoted Greg's remark about mechanism not policy with which I completely agree. Mechanism should exist to support useful policy. I don't believe that the proposed switch has any real-world usefulness. regards, tom lane There are some good reasons why a switchover could be an appropriate means in case the DB is facing troubles. It may be that the root cause is not the DB itsself, but used resources or other things which are going crazy and hit the DB first ( we've seen a lot of these unbelievable things which made us quite sensible for robustness aspects). Therefore we want to have control on the DB recovery. If you don't want to see this option as a GUC parameter, would it be acceptable to have it as a new postmaster cmd line option ? Regards, Harald Kolb. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
Hi, Josh Berkus wrote: It doesn't. But what I don't trust, and the *first* place I'd look for problems, is whether the OS flushes *all* dirty buffers to disk in the event the application gets killed. That's why I want more information on Floris' case. Was 8.4 killed or shut down with -m immediate? Or the os rebooted with 8.4 running? The only reboots I have done on that server were with the reboot system command, which should send a SIGTERM to all processes first including PostgreSQL, before pulling the plug. I do recall that during the execution of vacuum full the psql client program once did report that it lost connection with the server, but was able to reconnect. Maybe the server processes handling the connection died then, but I am not sure of that, and it only happened once. Anyway, the problem also occurs when there is no reboot or unexpected event between the reindex and the query. After a REINDEX it is able to find the row it was missing first, but then other rows become missing. All in the same psql session: === usenet= SELECT count(*) FROM posts_index WHERE poster='y...@power-post.org (Yenc-PP-AA)' AND groupid=300 AND basefile='NIB8124849'; count --- 0 (1 row) usenet= reindex index pgb_idx; REINDEX usenet= SELECT count(*) FROM posts_index WHERE poster='y...@power-post.org (Yenc-PP-AA)' AND groupid=300 AND basefile='NIB8124849'; count --- 1 (1 row) usenet= SELECT count(*) FROM posts_index WHERE poster='y...@power-post.org (Yenc-PP-AA)' AND basefile='frx-fffe' AND groupid=757; count --- 0 (1 row) usenet= set enable_indexscan=false; SET usenet= set enable_bitmapscan=false; SET usenet= SELECT count(*) FROM posts_index WHERE poster='y...@power-post.org (Yenc-PP-AA)' AND basefile='frx-fffe' AND groupid=757; count --- 1 (1 row) === Yours sincerely, Floris Bos -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postmaster recovery and automatic restart suppression
On Tue, 2009-06-09 at 20:59 +0200, Kolb, Harald (NSN - DE/Munich) wrote: There are some good reasons why a switchover could be an appropriate means in case the DB is facing troubles. It may be that the root cause is not the DB itsself, but used resources or other things which are going crazy and hit the DB first ( we've seen a lot of these unbelievable things which made us quite sensible for robustness aspects). Therefore we want to have control on the DB recovery. If you don't want to see this option as a GUC parameter, would it be acceptable to have it as a new postmaster cmd line option ? Even if you had this, you still need to STONITH just in case the failover happens by mistake. If you still have to take an action to be certain, what is the point of the feature? Most losses of availability are caused by human error and this seems like one more way to blow your remaining toes off. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postmaster recovery and automatic restart suppression
Kolb, Harald (NSN - DE/Munich) harald.k...@nsn.com writes: If you don't want to see this option as a GUC parameter, would it be acceptable to have it as a new postmaster cmd line option ? That would make two kluges, not one (we don't do options that are settable in only one way). And it does nothing whatever to address my objection to the concept. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postmaster recovery and automatic restart suppression
Kolb, Harald (NSN - DE/Munich) harald.k...@nsn.com wrote: From: ext Tom Lane [mailto:t...@sss.pgh.pa.us] Mechanism should exist to support useful policy. I don't believe that the proposed switch has any real-world usefulness. There are some good reasons why a switchover could be an appropriate means in case the DB is facing troubles. It may be that the root cause is not the DB itsself, but used resources or other things which are going crazy and hit the DB first Would an example of this be that one drive in a RAID has gone bad and the hot spare rebuild has been triggered, leading to poor performance for a while? Is that the sort of issue where you see value? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postmaster recovery and automatic restart suppression
Not really since once you fail over you may as well stop the rebuild since you'll have to restore the whole database. Moreover wouldn't that have to be a manual decision? The closest thing I can come to a use case would be if you run a very large cluster with hundreds of read-only replicas. If one has problems you would rather the load balancer notice and take it out of rotation immediately rather than have it flap and continue to cause problems. Even there it would be dicey since a software bug could easily cause all your replicas to start misbehaving simultaneously. It would suck to see them all shut down one by one... -- Greg On 9 Jun 2009, at 20:53, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Kolb, Harald (NSN - DE/Munich) harald.k...@nsn.com wrote: From: ext Tom Lane [mailto:t...@sss.pgh.pa.us] Mechanism should exist to support useful policy. I don't believe that the proposed switch has any real-world usefulness. There are some good reasons why a switchover could be an appropriate means in case the DB is facing troubles. It may be that the root cause is not the DB itsself, but used resources or other things which are going crazy and hit the DB first Would an example of this be that one drive in a RAID has gone bad and the hot spare rebuild has been triggered, leading to poor performance for a while? Is that the sort of issue where you see value? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postmaster recovery and automatic restart suppression
Kevin Grittner kevin.gritt...@wicourts.gov writes: Kolb, Harald (NSN - DE/Munich) harald.k...@nsn.com wrote: There are some good reasons why a switchover could be an appropriate means in case the DB is facing troubles. It may be that the root cause is not the DB itsself, but used resources or other things which are going crazy and hit the DB first Would an example of this be that one drive in a RAID has gone bad and the hot spare rebuild has been triggered, leading to poor performance for a while? Is that the sort of issue where you see value? How would that be connected to a no restart on crash setting? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] page is uninitialized --- fixing
Simon Riggs si...@2ndquadrant.com writes: A couple of people in recent years have had a problem with page X is uninitialised -- fixing messages. I have a case now with 569357 consecutive pages that required fixing in pg_attribute. We looked at pages by hand and they really are uninitialised, but otherwise what we would expect for size, name etc.. Clearly this is way too many pages to be easily explainable. It's probably too late to tell now, but I wonder if those pages actually existed or were just a hole in the file. A perhaps-plausible mechanism for them to appear is that the FSM spits out some ridiculously large page number as being the next place to insert something into pg_attribute, the system plops down a new tuple into that page, and behold you have a large hole that reads as zeroes. Another interesting question is whether the range began or ended at a 1GB segment boundary, in which case something in or around the segmenting logic could be at fault. (Hmm ... actually 1GB is only 131072 pages anyway, so your hole definitely spanned several segments. That seems like the next place to look.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with listen_addresses = '*' on 8.4beta2 on AIX
Albe Laurenz laurenz.a...@wien.gv.at writes: Building 8.4beta2 on my AIX test machine works fine, but when I set listen_addresses = '*' in postgresql.conf, the server fails to start. I grow weary of AIX's seemingly-unlimited supply of getaddrinfo peculiarities. What about forgetting the whole matter and always using our src/port/ implementation on that OS? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] page is uninitialized --- fixing
On Tue, 2009-06-09 at 16:17 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: A couple of people in recent years have had a problem with page X is uninitialised -- fixing messages. I have a case now with 569357 consecutive pages that required fixing in pg_attribute. We looked at pages by hand and they really are uninitialised, but otherwise what we would expect for size, name etc.. Clearly this is way too many pages to be easily explainable. It's probably too late to tell now, but I wonder if those pages actually existed or were just a hole in the file. A perhaps-plausible mechanism for them to appear is that the FSM spits out some ridiculously large page number as being the next place to insert something into pg_attribute, the system plops down a new tuple into that page, and behold you have a large hole that reads as zeroes. Another interesting question is whether the range began or ended at a 1GB segment boundary, in which case something in or around the segmenting logic could be at fault. (Hmm ... actually 1GB is only 131072 pages anyway, so your hole definitely spanned several segments. That seems like the next place to look.) The hole started about 0.75GB in file 0 and spanned 4 complete 1GB segments before records started again in file 5. The hole segments were all 1GB in size, and the pages either size of the hole were undamaged. A corrupt record of a block number would do this in XLogReadBuffer() if we had full page writes enabled. But it would have to be corrupt between setting it correctly and the CRC check on the WAL record. Which is a fairly small window of believability. Should there be a sanity check on how far a relation can be extended in recovery? Not sure if that would work with normal mode ReadBuffer() - it should fail somewhere in smgr or in bufmgr. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postmaster recovery and automatic restart suppression
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Kolb, Harald (NSN - DE/Munich) harald.k...@nsn.com wrote: There are some good reasons why a switchover could be an appropriate means in case the DB is facing troubles. It may be that the root cause is not the DB itself, but used resources or other things which are going crazy and hit the DB first Would an example of this be that one drive in a RAID has gone bad and the hot spare rebuild has been triggered, leading to poor performance for a while? Is that the sort of issue where you see value? How would that be connected to a no restart on crash setting? It wouldn't; but I'm trying to better understand the problem the OP is trying to solve, to see where that leads. My first reaction on hearing the request was that it might have *some* use; but in trying to recall any restart where it is what I would have wanted, I come up dry. I haven't even really come up with a good hypothetical use case. But I get the feeling the OP has had some problem this is attempting to address. I'm just not clear what that is. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with listen_addresses = '*' on 8.4beta2 on AIX
Tom Lane wrote: Albe Laurenz laurenz.a...@wien.gv.at writes: Building 8.4beta2 on my AIX test machine works fine, but when I set listen_addresses = '*' in postgresql.conf, the server fails to start. I grow weary of AIX's seemingly-unlimited supply of getaddrinfo peculiarities. What about forgetting the whole matter and always using our src/port/ implementation on that OS? I'm assuming that the OP has tested that 8.3 works on the same machine with listen_addresses = '*'. If so, then this seems a reasonable suggestion. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with listen_addresses = '*' on 8.4beta2 on AIX
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: I grow weary of AIX's seemingly-unlimited supply of getaddrinfo peculiarities. What about forgetting the whole matter and always using our src/port/ implementation on that OS? I'm assuming that the OP has tested that 8.3 works on the same machine with listen_addresses = '*'. If so, then this seems a reasonable suggestion. The only serious knock I can see on our implementation is that it lacks IPv6 support. So we'd not want to use it on AIX versions that are IPv6-capable. Which are those, if any, and do they have any of these getaddrinfo issues? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] page is uninitialized --- fixing
Simon Riggs si...@2ndquadrant.com writes: A corrupt record of a block number would do this in XLogReadBuffer() if we had full page writes enabled. But it would have to be corrupt between setting it correctly and the CRC check on the WAL record. Which is a fairly small window of believability. Should there be a sanity check on how far a relation can be extended in recovery? As you say, that's not a highly believable theory. I'd prefer not to put an arbitrary limit into the recovery code unless we can positively pin this down as the source of the problem. Is there any particular reason to suppose that the empty pages appeared during a crash recovery? Have you read through md.c? I seem to recall there are some slightly squirrelly choices made there about segment-extension behavior. Maybe it could've done the wrong thing here during normal operation. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with listen_addresses = '*' on 8.4beta2 on AIX
On Tue, Jun 9, 2009 at 5:24 PM, Tom Lanet...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: I grow weary of AIX's seemingly-unlimited supply of getaddrinfo peculiarities. What about forgetting the whole matter and always using our src/port/ implementation on that OS? I'm assuming that the OP has tested that 8.3 works on the same machine with listen_addresses = '*'. If so, then this seems a reasonable suggestion. The only serious knock I can see on our implementation is that it lacks IPv6 support. So we'd not want to use it on AIX versions that are IPv6-capable. Which are those, if any, and do they have any of these getaddrinfo issues? actually, aix was the first commercial unix distribution to support ipv6 afaik...as of 4.3 iirc. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] page is uninitialized --- fixing
On Tue, 2009-06-09 at 17:28 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: A corrupt record of a block number would do this in XLogReadBuffer() if we had full page writes enabled. But it would have to be corrupt between setting it correctly and the CRC check on the WAL record. Which is a fairly small window of believability. Should there be a sanity check on how far a relation can be extended in recovery? As you say, that's not a highly believable theory. I'd prefer not to put an arbitrary limit into the recovery code unless we can positively pin this down as the source of the problem. Is there any particular reason to suppose that the empty pages appeared during a crash recovery? Probably because my Rorschach tests all look like database recoveries. Have you read through md.c? I seem to recall there are some slightly squirrelly choices made there about segment-extension behavior. Maybe it could've done the wrong thing here during normal operation. Yes, but will do again if you think I should check. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postmaster recovery and automatic restart suppression
On Tue, 2009-06-09 at 15:48 -0500, Kevin Grittner wrote: My first reaction on hearing the request was that it might have *some* use; but in trying to recall any restart where it is what I would have wanted, I come up dry. I haven't even really come up with a good hypothetical use case. But I get the feeling the OP has had some problem this is attempting to address. I'm just not clear what that is. I think we need to answer why shutting the database down is insufficient response to the need to having it be shutdown in the event of failover. It always sounds neat to have a new feature, but often we already have it. (I'm sure I'm as guilty of that as the next person). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #4822: xmlattributes encodes '' twice
On Thursday 28 May 2009 13:31:16 Itagaki Takahiro wrote: Here is a patch to fix the bug. I added a parameter 'encode' to map_sql_value_to_xml_value() and pass false for xml attributes. I have committed your patch with minor editing. Thanks. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #4822: xmlattributes encodes '' twice
On Sunday 31 May 2009 20:00:44 Tom Lane wrote: Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: Here is a patch to fix the bug. I added a parameter 'encode' to map_sql_value_to_xml_value() and pass false for xml attributes. One thing I was wondering about, which is sort of highlighted by your patch, is why is there the special exception for XML type in the existing code, and how does that interact with this behavior? This is so that xmlelement(name element, xml 'foo/') results in elementfoo//element and xmlelement(name claim, text '1 2') results in claim1 lt; 2/claim Seems like there could be cases where we're getting one too many or too few encoding passes when the input is XML. The patch doesn't actually change anything when the input datum is of type XML. But anyway I have added a few regression test bits to make the expectations more explicit. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with listen_addresses = '*' on 8.4beta2 on AIX
Merlin Moncure mmonc...@gmail.com writes: Tom Lane wrote: The only serious knock I can see on our implementation is that it lacks IPv6 support. So we'd not want to use it on AIX versions that are IPv6-capable. Which are those, if any, and do they have any of these getaddrinfo issues? actually, aix was the first commercial unix distribution to support ipv6 afaik...as of 4.3 iirc. Drat. Okay, that easy solution probably won't fly. Anyone with AIX access want to try to develop a patch that covers this case without breaking the other ones? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not quite a security hole in internal_in
Tom Lane t...@sss.pgh.pa.us writes: This would be a serious security problem if it weren't for the fact that nearly all internal-accepting functions in the backend are also marked STRICT, and so they won't get called in this type of scenario. A query to pg_proc shows that the only ones that aren't strict are regression=# select oid::regprocedure from pg_proc where 'internal'::regtype = any (proargtypes) and not proisstrict; oid array_agg_transfn(internal,anyelement) array_agg_finalfn(internal) domain_recv(internal,oid,integer) (3 rows) The first two are new in 8.4, and the third has adequate defenses already. So we don't have a security hole in any released version right now. How about contrib/ ? I have this in my test 8.3.7 database: seb= select oid::regprocedure from pg_proc where 'internal'::regtype = any (proargtypes) and not proisstrict; oid --- domain_recv(internal,oid,integer) utils_pg.gtrgm_same(utils_pg.gtrgm,utils_pg.gtrgm,internal) utils_pg.gin_extract_trgm(text,internal) utils_pg.gin_extract_trgm(text,internal,internal) utils_pg.gin_trgm_consistent(internal,internal,text) utils_pg.ghstore_compress(internal) utils_pg.ghstore_decompress(internal) utils_pg.ghstore_picksplit(internal,internal) utils_pg.ghstore_union(internal,internal) utils_pg.ghstore_same(internal,internal,internal) utils_pg.ghstore_consistent(internal,internal,integer) utils_pg.gin_extract_hstore(internal,internal) utils_pg.gin_extract_hstore_query(internal,internal,smallint) utils_pg.gin_consistent_hstore(internal,smallint,internal) utils_pg.gtrgm_consistent(utils_pg.gtrgm,internal,integer) utils_pg.gtrgm_compress(internal) utils_pg.gtrgm_decompress(internal) utils_pg.gtrgm_picksplit(internal,internal) utils_pg.gtrgm_union(bytea,internal) (19 rows) -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not quite a security hole in internal_in
Sergey Burladyan eshkin...@gmail.com writes: How about contrib/ ? I have this in my test 8.3.7 database: That stuff should all be marked strict ... on the whole I'm not sure that contrib is null-safe anyway, independently of this particular issue. AFAIK no one's really gone through it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent run coming
Bruce Momjian wrote: It is time to run pgindent on CVS HEAD for 8.4. I am thinking of running it at zero-hour GMT tomorrow, meaning five hours from now. Any objections? I ran pgindent and was concerned enough about the results so I am posting here rather than applying any changes. I used the old way of generating typedefs and the new buildfarm method output from diffs from http://www.pgbuildfarm.org/cgi-bin/typedefs.pl. The typedef lists and diff are here: http://momjian.us/expire/pgindent/ You can see the typedef lists are of similar size: 2775 typedefs.old 2123 typedefs.new and the diffs generates are a similar number of lines: 133657 diff.old_typedefs 135042 diff.new_typedefs I saw a few odd things. Most importantly, it seems 'stat' was introduced as a typedef on _both_ lists, yielding weird changes like: - ret = stat(indir, st); + ret = stat (indir, st); and even odder: - stat-weight |= 1 3; + stat -weight |= 1 3; stat was not a typedef in 8.3 or pgindent would have done this for 8.3, but I can't figure out what has changed to make it appear for 8.4. I see this in the objdump output (my OS has not changed from 8.3): 31357 EXCL 0 0 3e64 97648 /usr/include/time.h 31358 EXCL 0 0 7638 97624 /usr/include/sys/time.h 31359 EXCL 0 0 97648 /usr/include/time.h 31360 EXCL 0 0 1dff 25540 /usr/include/fcntl.h 31361 BINCL 0 0 000144be 449667 /usr/include/sys/stat.h 31362 LSYM 0 0 449691 ostat:T(51,1)=s64st_dev:(0,9),0,16;st_ino:(9,20),32,32;st_mode:(9,22),64,16;st_nlink:(9,23),80,16;st_u id:(0,9),96,16;st_gid:(0,9),112,16;st_rdev:(0,9),128,16;st_size:(0,3),160,32;st_atimespec:(48,2),192,64;st_mtimespec:(48,2),256,64;st_ctimespec:(4 8,2),320,64;st_blksize:(0,3),384,32;st_blocks:(0,3),416,32;st_flags:(0,5),448,32;st_gen:(0,5),480,32;; 31363 LSYM 0 0 450042 stat32:T(51,2)=s96st_dev:(9,17),0,32;st_ino:(9,20),32,32;st_mode:(9,22),64,16;st_nlink:(9,23),80,16;st _uid:(9,28),96,32;st_gid:(9,19),128,32;st_rdev:(9,17),160,32;st_atimespec:(48,2),192,64;st_mtimespec:(48,2),256,64;st_ctimespec:(48,2),320,64;st_s ize:(0,3),384,32;st_size1:(0,3),416,32;st_blocks:(0,3),448,32;st_blocks1:(0,3),480,32;st_blksize:(0,5),512,32;st_flags:(0,5),544,32;st_gen:(0,5),5 76,32;st_lspare:(0,3),608,32;st_qspare:(51,3)=ar(0,1);0;3;(0,3),640,128;; -- 31364 LSYM 0 0 450510 stat:T(51,4)=s96st_dev:(9,17),0,32;st_ino:(9,20),32,32;st_mode:(9,22),64,16;st_nlink:(9,23),80,16;st_u id:(9,28),96,32;st_gid:(9,19),128,32;st_rdev:(9,17),160,32;st_atimespec:(48,2),192,64;st_mtimespec:(48,2),256,64;st_ctimespec:(48,2),320,64;st_siz e:(9,24),384,64;st_blocks:(9,8),448,64;st_blksize:(0,5),512,32;st_flags:(0,5),544,32;st_gen:(0,5),576,32;st_lspare:(0,3),608,32;st_qspare:(51,5)=a r(0,1);0;1;(9,8),640,128;; It is coming from the postgres binary. The typedef is coming from the indicated line, and from /usr/include/sys/stat.h, where there is no typedef for stat. Obviously Linux or the buildfarm is finding the same issue, but I have no idea why. My only guess right now is that we are linking postgres differently than we did for 8.3 and that is bringing in new wrong typedef symbols. I will have to research this further tomorrow. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent run coming
Bruce Momjian wrote: The typedef is coming from the indicated line, and from /usr/include/sys/stat.h, where there is no typedef for stat. Obviously Linux or the buildfarm is finding the same issue, but I have no idea why. My only guess right now is that we are linking postgres differently than we did for 8.3 and that is bringing in new wrong typedef symbols. I will have to research this further tomorrow. I was able to reproduce the incorrect stat typedef here in a small test program by just including sys/stat.h so I will research tomorrow how to fix this. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent run coming
Bruce Momjian br...@momjian.us writes: I saw a few odd things. Most importantly, it seems 'stat' was introduced as a typedef on _both_ lists, yielding weird changes like: The standard headers do define struct stat. I wonder whether the objdump kluge we are using is unable to distinguish typedef names from struct tags. I will have to research this further tomorrow. We don't have a lot of time for research. Maybe the best thing is to just manually remove stat from the typedef list (along with anything else that clearly shouldn't be there)? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent run coming
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I saw a few odd things. Most importantly, it seems 'stat' was introduced as a typedef on _both_ lists, yielding weird changes like: The standard headers do define struct stat. I wonder whether the objdump kluge we are using is unable to distinguish typedef names from struct tags. I will have to research this further tomorrow. We don't have a lot of time for research. Maybe the best thing is to just manually remove stat from the typedef list (along with anything else that clearly shouldn't be there)? The problem is that there are other symbols I don't know about and the diff is very large. I have found that the problem was caused when we added Linux support to find_typedef and I have a way to get an accurate list on my machine. I will generate a proper list on my machine tomorrow then test Linux here to see if I can get it to generate the right list too. But odds are we are not going to have time to re-run the list on the build farm even if I can get Linux working here. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent run coming
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I saw a few odd things. Most importantly, it seems 'stat' was introduced as a typedef on _both_ lists, yielding weird changes like: The standard headers do define struct stat. I wonder whether the objdump kluge we are using is unable to distinguish typedef names from struct tags. I will have to research this further tomorrow. We don't have a lot of time for research. Maybe the best thing is to just manually remove stat from the typedef list (along with anything else that clearly shouldn't be there)? Do you want me to just run with my old typedef list now and apply it? We an always rerun tomorrow if we get a better typedef list. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent run coming
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: We don't have a lot of time for research. Maybe the best thing is to just manually remove stat from the typedef list (along with anything else that clearly shouldn't be there)? Do you want me to just run with my old typedef list now and apply it? We an always rerun tomorrow if we get a better typedef list. I'd rather have *one* run with the final typedef list. If you don't have that list yet, wait till you do. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent run coming
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: We don't have a lot of time for research. Maybe the best thing is to just manually remove stat from the typedef list (along with anything else that clearly shouldn't be there)? Do you want me to just run with my old typedef list now and apply it? We an always rerun tomorrow if we get a better typedef list. I'd rather have *one* run with the final typedef list. If you don't have that list yet, wait till you do. OK, Andrew, would you use the find_typedef file that is in CVS HEAD and run that. I think that will fix our problem and then I can use the buildfarm version. How often does that run and does it pull the script from CVS HEAD? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent run coming
Bruce Momjian wrote:
OK, Andrew, would you use the find_typedef file that is in CVS HEAD and
run that. I think that will fix our problem and then I can use the
buildfarm version. How often does that run and does it pull the script
from CVS HEAD?
The buildfarm does not run the find-typedefs script. Its code for this
is below. My Unix machine runs this once a day. I can do runs on Windows
and Cygwin manually.
If there is changed logic tell me what it is and I'll try to get it done.
cheers
andrew
-
sub find_typedefs
{
my @err = `objdump -W 21`;
@err = () if `uname -s 21` =~ /CYGWIN/i;
my %syms;
my @dumpout;
my @flds;
foreach my $bin (glob($installdir/bin/*),
glob($installdir/lib/*),
glob($installdir/lib/postgresql/*))
{
next if $bin =~ m!bin/(ipcclean|pltcl_)!;
next unless -f $bin;
if (@err == 1) # Linux
{
@dumpout = `objdump -W $bin 2/dev/null | egrep -A3
'(DW_TAG_typedef|DW_TAG_structure_type|DW_TAG_union_type)' 2/dev/null`;
foreach (@dumpout)
{
@flds = split;
next if (($flds[0] ne 'DW_AT_name' $flds[1] ne 'DW_AT_name' ) ||
$flds[-1] =~ /^DW_FORM_str/);
$syms{$flds[-1]} =1;
}
}
else
{
@dumpout = `objdump --stabs $bin 2/dev/null`;
foreach (@dumpout)
{
@flds = split;
next if (@flds 7);
next if ($flds[1] ne 'LSYM' || $flds[6] !~ /([^:]+):[tT]/);
$syms{$1} =1;
}
}
}
my @badsyms = grep { /\s/ } keys %syms;
push(@badsyms,'date','interval','timestamp','ANY');
delete @sy...@badsyms};
my @goodsyms = sort keys %syms;
my @foundsyms;
my %foundwords;
my $setfound = sub
{
return unless (-f $_ /^.*\.[chly]\z/);
my @lines;
my $handle;
open ($handle,$_);
while (my $line=$handle)
{
foreach my $word (split(/\W+/,$line))
{
$foundwords{$word} = 1;
}
}
close($handle);
};
File::Find::find($setfound,$branch_root/pgsql);
foreach my $sym (@goodsyms)
{
push(@foundsyms,$sym\n) if exists $foundwords{$sym};
}
writelog('typedefs',\...@foundsyms);
$steps_completed .= find-typedefs;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
