Re: [HACKERS] Proposal: global index
On 2017-08-18 11:12, Ildar Musin wrote: Hi hackers, While we've been developing pg_pathman extension one of the most frequent questions we got from our users was about global index support. We cannot provide it within an extension. And I couldn't find any recent discussion about someone implementing it. So I'm thinking about giving it a shot and start working on a patch for postgres. Sorry to be dense but what exactly is a "Global Index"? You mention pg_pathman; is a global index related to just partitions? Or is it a more generally applicable concept? Could you (or someone) perhaps expand a little? thanks, Erik Rijkers -- 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] Let us fix the documentation
On Wed, December 11, 2013 22:51, AK wrote: > The following url seems to be slightly incorrect: > > http://www.postgresql.org/docs/9.3/static/sql-prepare.html > > PREPARE usrrptplan (int) AS > SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid > AND l.date = $2; > EXECUTE usrrptplan(1, current_date); > > I guess the first line of the example should be: > > PREPARE usrrptplan (int, date) AS > > What do you think? > read the next line: "Note that the data type of the second parameter is not specified, so it is inferred from the context in which $2 is used." (So the example is correct as is) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] invalid magic number in log segment
I don't know whether the below constitutes a bug, but: Daily (sometimes even more often) I recompile 9.4devel (after git pull) to run a large dev database (100 GB or so). To avoid frequent initdb and many-hour-restore of data, I do this only when the following two #defines are unchanged: CATALOG_VERSION_NO in src/include/catalog/catversion.h, and PG_CONTROL_VERSION in src/include/catalog/pg_control.h the goal being to always run the latest db, without having to reload the ~100 GB dev db unexpectedly at inconvenient times. Generally, this works OK. However, the last few weeks I sometimes get, after such recompiling, 'invalid magic number' errors from which I don't know how to recover (it means, apparently, an initdb is needed and I have then to reload the database). 2013-12-11 00:15:25.627 CET 25304 LOG: received smart shutdown request 2013-12-11 00:15:25.631 CET 25306 LOG: shutting down 2013-12-11 00:15:25.904 CET 25306 LOG: database system is shut down 2013-12-11 08:11:59.858 CET 25490 LOG: database system was shut down at 2013-12-11 00:15:25 CET 2013-12-11 08:11:59.901 CET 25490 LOG: invalid magic number D078 in log segment 000100630034, offset 0 2013-12-11 08:11:59.901 CET 25490 LOG: invalid primary checkpoint record 2013-12-11 08:11:59.901 CET 25490 LOG: invalid magic number D078 in log segment 000100630034, offset 0 2013-12-11 08:11:59.901 CET 25490 LOG: invalid secondary checkpoint record 2013-12-11 08:11:59.901 CET 25490 PANIC: could not locate a valid checkpoint record 2013-12-11 08:12:00.326 CET 25492 FATAL: the database system is starting up 2013-12-11 08:12:01.328 CET 25493 FATAL: the database system is starting up 2013-12-11 08:12:01.682 CET 25489 LOG: startup process (PID 25490) was terminated by signal 6: Aborted 2013-12-11 08:12:01.682 CET 25489 LOG: aborting startup due to startup process failure My question is two-fold: 1. (general:) is this 'invalid magic number' unexpected, and should it be reported always? 2. (for my setup specifically:) is there any way that I can recognize, beforehand, at the code base level, such an impending 'invalid magic number' state? Can de db be recovered from easily? (although this dev database is expendable, it takes many hours to rebuild; I'd like to avoid that if possible). thanks, Erikjan Rijkers -- 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] 9.3 Beta1 status report
I just spotted some more small stuff: s/IF NOT EXIST /IF NOT EXISTS /g # 2 x It actually had me doubting, but yes that -S should be there... Thanks, Erik Rijkers -- 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] WIP: index support for regexp search
On Tue, April 2, 2013 23:54, Alexander Korotkov wrote: > [trgm-regexp-0.15.patch.gz] Yes, it does look good now; Attached a list of measurements. Most of the searches that I put in that test-program are now speeded up very much. There still are a few regressions, for example: HEAD azjunk6 x[aeiou]{4,5}q 83 Seq Scan 1393.465 ms trgm_regex15 azjunk6 x[aeiou]{4,5}q 83 Bitmap Heap Scan 1728.319 ms HEAD azjunk7 x[aeiou]{1,3}q 190031 Seq Scan 16819.555 ms trgm_regex15 azjunk7 x[aeiou]{1,3}q 190031 Bitmap Heap Scan 21286.804 ms Not exactly negligible, and ideally those regressions would be removed but with the huge advantages for other cases I'd say it's worth it. hth, Erik Rijkers re-head-13-15-20130403-0708.txt.bz2 Description: BZip2 compressed data -- 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] WIP: index support for regexp search
On Mon, April 1, 2013 23:15, Alexander Korotkov wrote: [trgm-regexp-0.14.patch.gz] Hi Alexander, Something went wrong in this version of the patch: many (most) queries that were earlier spectacularly fast have become slow, often slower than a seqscan or only marginally faster. See the attached numbers; it compares head(seqscan) with trgm-regex patch versions 13 and 14. I did not even complete the test-run because version 14 is so clearly inferior to 13 (and earlier, as far as I can remember). (let me know if you want the whole result, I can run it overnight) Thanks, Erik Rijkers re-head-13-14-20130402-2219.txt.bz2 Description: BZip2 compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] matview join view error
With 9.3devel, I can't seem to join a matview to a view; surely that should be allowed? Here is an example: -8<-- #!/bin/sh echo " drop table if exists t1 cascade; drop table if exists t2 cascade; drop materialized view if exists mv ; create table t1 as select chr(i) as c1, i from generate_series(65, 90) as f(i); create table t2 as select chr(i) as c2, i from generate_series(65, 90) as f(i); create materialized view mv as select c1, i from t1 where i between 75 and 85; create or replace view v as select c2, i from t2; " | psql -qX echo " select m.* , v.* from mv m left join v on v.i = m.i " | psql -qXa -8<-- This results in: ERROR: could not open file "base/21282/15840421": No such file or directory (15840421 is the pg_class.relfilenode of view 'v'). Thanks, Erik Rijkers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers