Re: [HACKERS] Remove pg_am.amindexnulls?
On Fri, Jan 7, 2011 at 11:41 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Jan 7, 2011 at 8:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Between amclusterable, amsearchnulls, and amoptionalkey, I believe that we have quite enough flags already to cover what anything else actually needs-to-know about the AM's behavior. I've pretty much come to the conclusion that pg_am is much better at providing the illusion of abstraction than it is at providing actual abstraction. IIUC, the chances that a third-party AM would need to patch core are nearly 100% anyway, so I'm not inclined to spend much mental energy trying to figure out what flags it might hypothetically need. Well, I'll grant that allowing loadable modules to emit and replay WAL records is an unsolved problem, but the existence of that problem doesn't mean that we should entirely give up on keeping AMs modular. I believe that they *are* pretty modular except for that one issue. I'm not in a hurry to chuck the current system completely, but it strikes me that the planner basically has to know everything about what the AMs can do. I agree there's some value in encapsulating that behind Booleans, but adding a new AM, or a new feature to an existing AM, can be expected to result in regular rearrangements of those Booleans. So it seems a bit porous. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] obj_unique_identifier(oid)
On Sat, Jan 8, 2011 at 1:59 AM, Joel Jacobson j...@gluefinance.com wrote: 2011/1/7 Jim Nasby j...@nasby.net: BTW, if you're looking at making pg_depnd easier to use, see http://archives.postgresql.org/message-id/129774-sup-2...@alvh.no-ip.org I guess there are more than one ways to do it, C, sql, plperl, plpgsql. :) I guess at least one of the methods should be provided in the vanilla distro. :) I guess the point is that if this gets committed as a core function written in C, we don't need any other implementations. But I don't recall ever seeing a commit for that one go by... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] join functions
On Fri, Jan 7, 2011 at 8:34 AM, Zotov zo...@oe-it.ru wrote: Thank you for your answer. Sorry, what i don`t find it myself. But I can`t see when it will be done? If nobody can do this (maybe no time) what i can do to help?! I know C - Language only to read code. (I`m Delphi-programmer) and this is not that feature what i can do myself. I can try, but... It`s look like difficult. Yeah, it's difficult. I don't think it can be done without the generalized inner-indexscan stuff Tom was working on a few months back, but I'm not sure what the status of that is at the moment. For now, your best option is probably to write a PL/pgsql function that iterates over table1 and then does a SELECT that calls func1() and does whatever else for each row in table1. This can be a little slow but I think it's the only option in existing releases of PostgreSQL. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] DISCARD ALL ; stored procedures
On Fri, Jan 7, 2011 at 1:29 PM, Stephen Frost sfr...@snowman.net wrote: #1. Add a new 'Top-but-removed-on-DISCARD' context and modify the PLs to use that instead of TopMemoryContext and require any other contexts they create to be children of it. I'm guessing that just resetting the memory context is going to result in things breaking all over the place - the PL might have dangling pointers into the context. And it might have other resources that we don't know about. Thus I think we need: #2. Add another entry point to the PLs in pg_pltemplate.h which is a function to be called on DISCARD. ...except I think that the principal thing you need to modify is pl_language, rather than pl_pltemplate. If we go this route, then (1) it can't be back-patched, obviously, and (2) we need to think a little bit harder about what we're asking to have discarded, because I think it's going to be a lot more than just cached plans. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] obj_unique_identifier(oid)
Robert Haas robertmh...@gmail.com writes: I guess the point is that if this gets committed as a core function written in C, we don't need any other implementations. But I don't recall ever seeing a commit for that one go by... http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=6cc2deb86e9183262493a6537700ee305fb3e096 Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] Remove pg_am.amindexnulls?
Tom Lane t...@sss.pgh.pa.us Now that GIN can index null items, its amindexnulls flag in pg_am really ought to be set true. However, looking at the usage of that column, I wonder whether we shouldn't just delete it instead. The only present usage is a rather convoluted test in CLUSTER to see if the index is safely clusterable --- and AFAICS that test has been obsoleted anyhow by the later addition of pg_am.amclusterable. Anyone against simplifying matters by getting rid of pg_am.amindexnulls? There has been multiple attempts at implementing bitmap indexes in the recent past, any impact between that efforts and your change? That's all I can think about and I don't even know how much it's related… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] We need to log aborted autovacuums
David Fetter da...@fetter.org writes: On Fri, Jan 07, 2011 at 08:15:12PM -0500, Greg Smith wrote: [1] Silly aside: I was thinking today that I should draw a chart of all the common objections to code that show up here, looking like those maps you see when walking into a mall. Then we can give a copy to new submitters with a big you are here X marking where they have inadvertently wandered onto. Actually, that'd make a great flow chart on a T-shirt :) Yeah, here's some more visual inspiration to get that: http://xkcd.com/844/ Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] join functions
On 5 Jan 2011, at 02:12, Zotov zo...@oe-it.ru wrote: Why doesn`t work this query? select table1.field1, func1.field2 from table1 left outer join func1 (table1.field1) on true where func1.field3 in (20, 100); The approach people usually use is: SELECT f1, (fn).field2 FROM ( SELECT field1 as f1, func1(field1) as fn FROM table1 OFFSET 0 ) ss WHERE (fn).field3 IN (20, 100) ; OFFSET 0 is there to prevent the function from getting called more than once. Also note that this will scan the whole table. There might be a way to avoid that by creating an index on ((func1 (field1)).field3) and removing OFFSET 0, but only if the function is IMMUTABLE. Regards, Marko Tiikkaja
Re: [HACKERS] obj_unique_identifier(oid)
2011/1/8 Dimitri Fontaine dimi...@2ndquadrant.fr: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=6cc2deb86e9183262493a6537700ee305fb3e096 Nice! Has the patch been accepted and will be made available in future versions of pg? Also, why return NULL for pinned objects? They can also be described using a unique identifier. (+ /* for pinned items in pg_depend, return null */) It is useful to describe such objects to be able to diff different versions of pg, i.e. comparing which pinned objects exists, doing so can tell you the odds for an application depending on certain pinned objects being compatible with a specific version of the database. -- Best regards, Joel Jacobson Glue Finance -- 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] DISCARD ALL ; stored procedures
* Robert Haas (robertmh...@gmail.com) wrote: On Fri, Jan 7, 2011 at 1:29 PM, Stephen Frost sfr...@snowman.net wrote: #1. Add a new 'Top-but-removed-on-DISCARD' context and modify the PLs to use that instead of TopMemoryContext and require any other contexts they create to be children of it. I'm guessing that just resetting the memory context is going to result in things breaking all over the place - the PL might have dangling pointers into the context. After looking through the code more, we actually already say use this context for stuff you allocate in fn_extra, but it doesn't look like the PLs are respecting or using that. We do have a function which resets fn_extra already (fmgr_finfo_copy) but I'm not sure under what conditions it's used and I'm not sure why it doesn't leak memory by doing that. If we can figure out the list of functions that have been called, get at all of their fn_extra pointers to set them to NULL, and nuke the context that they're created in, that should work. The PLs in core appear to be good about using fn_extra and resetting it should be sufficient to force a recompile of the stored procedures. It also looks like they shouldn't have any issue surviving that reset. And it might have other resources that we don't know about. Thus I think we need: This is certainly a concern and would be a reason to offer a seperate function for the PLs to use, but I'm not sure we need to jump there right away. I'd like to see if the core/contrib PLs can all handle the above approach and then see if third-party PLs complain. #2. Add another entry point to the PLs in pg_pltemplate.h which is a function to be called on DISCARD. ...except I think that the principal thing you need to modify is pl_language, rather than pl_pltemplate. Right, sorry. If we go this route, then (1) it can't be back-patched, obviously, and (2) we need to think a little bit harder about what we're asking to have discarded, because I think it's going to be a lot more than just cached plans. I'm not ready to give up quite yet, but I agree that we might end up there. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] obj_unique_identifier(oid)
On Sat, Jan 8, 2011 at 14:05, Joel Jacobson j...@gluefinance.com wrote: 2011/1/8 Dimitri Fontaine dimi...@2ndquadrant.fr: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=6cc2deb86e9183262493a6537700ee305fb3e096 Nice! Has the patch been accepted and will be made available in future versions of pg? Yes. Once things are committed to the main repository, they are only backed out if someone finds a major issue with them that is not fixable (ina reasonable timeframe). That almost never happens. We don't keep unapproved patches or development branches in the main repository - those are all in the personal repositories of the developers. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] making an unlogged table logged
On Tue, Jan 4, 2011 at 9:41 PM, Robert Haas robertmh...@gmail.com wrote: Generally, to do this, it would be necessary to do the following things (plus anything I'm forgetting): It occurs to me that almost exactly this same procedure could be used to make a *temporary* table into a permanent table. You'd have to also change the schema, and there'd be some other adjustments, but overall it'd be pretty similar. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Error code for terminating connection due to conflict with recovery
While looking at the backend code, I realized that error code for terminating connection due to conflict with recovery is ERRCODE_ADMIN_SHUTDOWN. I thought the error code is for somewhat a human interruption, such as shutdown command issued by pg_ctl. Is the usage of the error code appropreate? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Streaming base backups
On Thu, Jan 6, 2011 at 23:57, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Looks like pg_streamrecv creates the pg_xlog and pg_tblspc directories, because they're not included in the streamed tar. Wouldn't it be better to include them in the tar as empty directories at the server-side? Otherwise if you write the tar file to disk and untar it later, you have to manually create them. Attached is an updated patch that does this. It also collects all the header records as a single resultset at the beginning. This made for cleaner code, but more importantly makes it possible to get the total size of the backup even if there are multiple tablespaces. It also changes the tar members to use relative paths instead of absolute ones - since we send the root of the directory in the header anyway. That also takes away the ./ portion in all tar members. git branch on github updated as well, of course. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ *** a/doc/src/sgml/protocol.sgml --- b/doc/src/sgml/protocol.sgml *** *** 1458,1463 The commands accepted in walsender mode are: --- 1458,1555 /para /listitem /varlistentry + + varlistentry + termBASE_BACKUP replaceableoptions/literal;/replaceablelabel//term + listitem + para + Instructs the server to start streaming a base backup. + The system will automatically be put in backup mode with the label + specified in replaceablelabel/ before the backup is started, and + taken out of it when the backup is complete. The following options + are accepted: + variablelist +varlistentry + termliteralPROGRESS//term + listitem + para + Request information required to generate a progress report. This will + send back an approximate size in the header of each tablespace, which + can be used to calculate how far along the stream is done. This is + calculated by enumerating all the file sizes once before the transfer + is even started, and may as such have a negative impact on the + performance - in particular it may take longer before the first data + is streamed. Since the database files can change during the backup, + the size is only approximate and may both grow and shrink between + the time of approximation and the sending of the actual files. + /para + /listitem +/varlistentry + /variablelist + /para + para + When the backup is started, the server will first send a header in + ordinary result set format, followed by one or more CopyResponse + results, one for PGDATA and one for each additional tablespace other + than literalpg_default/ and literalpg_global/. The data in + the CopyResponse results will be a tar format (using ustar00 + extensions) dump of the tablespace contents. + /para + para + The header is an ordinary resultset with one row for each tablespace. + The fields in this row are: + variablelist +varlistentry + termspcoid/term + listitem + para + The oid of the tablespace, or literalNULL/ if it's the base + directory. + /para + /listitem +/varlistentry +varlistentry + termspclocation/term + listitem + para + The full path of the tablespace directory, or literalNULL/ + if it's the base directory. + /para + /listitem +/varlistentry +varlistentry + termsize/term + listitem + para + The approximate size of the datablock, if progress report has + been requested; otherwise it's literalNULL/. + /para + /listitem +/varlistentry + /variablelist + /para + para + The tar archive for the data directory and each tablespace will contain + all files in the directories, regardless of whether they are + productnamePostgreSQL/ files or other files added to the same + directory. The only excluded files are: + itemizedlist spacing=compact mark=bullet +listitem + para + filenamepostmaster.pid/ + /para +/listitem +listitem + para + filenamepg_xlog/ (including subdirectories) + /para +/listitem + /itemizedlist + Owner, group and file mode are set if the underlying filesystem on + the server supports it. + /para + /listitem + /varlistentry /variablelist /para *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *** *** 8308,8313 pg_start_backup(PG_FUNCTION_ARGS) --- 8308,8328 text *backupid = PG_GETARG_TEXT_P(0); bool fast = PG_GETARG_BOOL(1); char
Re: [HACKERS] obj_unique_identifier(oid)
2011/1/8 Magnus Hagander mag...@hagander.net: Yes. Once things are committed to the main repository, they are only backed out if someone finds a major issue with them that is not fixable (ina reasonable timeframe). That almost never happens. We don't keep unapproved patches or development branches in the main repository - those are all in the personal repositories of the developers. Thanks for clarifying. I found a bug in the pg_catalog.pg_describe_object function. The query below should not return any rows, because if it does, then there are oids with non-unique descriptions. While the description is good enough for a human to interpret, it cannot be used in an application as a unique identifier unless it is really unique. WITH all_objects AS ( SELECT classid, objid, objsubid FROM pg_depend UNION SELECT refclassid, refobjid, refobjsubid FROM pg_depend ) SELECT pg_catalog.pg_describe_object(classid,objid,objsubid) FROM all_objects GROUP BY pg_catalog.pg_describe_object(classid,objid,objsubid) HAVING COUNT(*) 1 pg_describe_object function 2 ginarrayextract(anyarray,internal) of operator family array_ops for access method gin function 4 ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal) of operator family array_ops for access method gin function 3 ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of operator family array_ops for access method gin function 1 network_cmp(inet,inet) of operator family array_ops for access method gin function 1 bttextcmp(text,text) of operator family array_ops for access method gin (5 rows) There are 94 objects such objects: classid | objid | objsubid | obj_unique_identifier | pg_describe_object -+---+--+--+ 2603 | 10606 |0 | pg_amproc.gin.pg_catalog.array_ops.pg_catalog._text.pg_catalog._text.1 | function 1 bttextcmp(text,text) of operator family array_ops for access method gin 2603 | 10610 |0 | pg_amproc.gin.pg_catalog.array_ops.pg_catalog._varchar.pg_catalog._varchar.1 | function 1 bttextcmp(text,text) of operator family array_ops for access method gin 2603 | 10650 |0 | pg_amproc.gin.pg_catalog.array_ops.pg_catalog._inet.pg_catalog._inet.1 | function 1 network_cmp(inet,inet) of operator family array_ops for access method gin 2603 | 10654 |0 | pg_amproc.gin.pg_catalog.array_ops.pg_catalog._cidr.pg_catalog._cidr.1 | function 1 network_cmp(inet,inet) of operator family array_ops for access method gin 2603 | 10631 |0 | pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bytea.pg_catalog._bytea.2 | function 2 ginarrayextract(anyarray,internal) of operator family array_ops for access method gin 2603 | 10671 |0 | pg_amproc.gin.pg_catalog.array_ops.pg_catalog._macaddr.pg_catalog._macaddr.2 | function 2 ginarrayextract(anyarray,internal) of operator family array_ops for access method gin 2603 | 10667 |0 | pg_amproc.gin.pg_catalog.array_ops.pg_catalog._interval.pg_catalog._interval.2 | function 2 ginarrayextract(anyarray,internal) of operator family array_ops for access method gin 2603 | 10675 |0 | pg_amproc.gin.pg_catalog.array_ops.pg_catalog._name.pg_catalog._name.2 | function 2 ginarrayextract(anyarray,internal) of operator family array_ops for access method gin 2603 | 10719 |0 | pg_amproc.gin.pg_catalog.array_ops.pg_catalog._tinterval.pg_catalog._tinterval.2 | function 2 ginarrayextract(anyarray,internal) of operator family array_ops for access method gin 2603 | 10607 |0 | pg_amproc.gin.pg_catalog.array_ops.pg_catalog._text.pg_catalog._text.2 | function 2 ginarrayextract(anyarray,internal) of operator family array_ops for access method gin 2603 | 10611 |0 | pg_amproc.gin.pg_catalog.array_ops.pg_catalog._varchar.pg_catalog._varchar.2 | function 2 ginarrayextract(anyarray,internal) of operator family array_ops for access method gin 2603 | 10655 |0 | pg_amproc.gin.pg_catalog.array_ops.pg_catalog._cidr.pg_catalog._cidr.2 | function 2 ginarrayextract(anyarray,internal) of operator family array_ops for access method gin 2603 | 10707 |0 | pg_amproc.gin.pg_catalog.array_ops.pg_catalog._timestamp.pg_catalog._timestamp.2 | function 2 ginarrayextract(anyarray,internal) of operator family array_ops for access method gin 2603 | 10711 |0 |
[HACKERS] pg_upgrade fixes, #99 ;-)
In my first attached, applied patch, I have found a way to speed
relations lookups in pg_upgrade. I knew there was a way to optimize
this but it was not clear until my major cleanups. Instead of doing
effectively a nested loop join on old/new relations, I now order them
and use a 1:1 mergejoin. This should speed up pg_upgrade for many
relations.
The second patch removes a hack for toast relations that is unnecessary
now that we always preserve pg_class.oid. The old code preserved
relfilenodes for non-toast tables and oids for toast relations, which
was obviously confusing and non-optimal.
Remember, this code was not originally written by me but someone at
EnteprriseDB, so I didn't fully understand it until now.
I love removing functions!
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
commit 002c105a0706bd1c1e939fe0f47ecdceeae6c52d
Author: Bruce Momjian br...@momjian.us
Date: Sat Jan 8 13:44:44 2011 -0500
In pg_upgrade, remove functions that did sequential array scans looking
up relations, but rather order old/new relations and use the same array
index value for both. This should speed up pg_upgrade for databases
with many relations.
diff --git a/contrib/pg_upgrade/function.c b/contrib/pg_upgrade/function.c
index cb9576a..d7c790c 100644
*** /tmp/UF8KGb_function.c Sat Jan 8 13:46:55 2011
--- /tmp/4GeXcc_function.c Sat Jan 8 13:46:55 2011
***
*** 13,25
/*
! * install_support_functions_in_db()
*
* pg_upgrade requires some support functions that enable it to modify
* backend behavior.
*/
void
! install_support_functions_in_db(const char *db_name)
{
PGconn *conn = connectToServer(new_cluster, db_name);
--- 13,25
/*
! * install_support_functions_in_new_db()
*
* pg_upgrade requires some support functions that enable it to modify
* backend behavior.
*/
void
! install_support_functions_in_new_db(const char *db_name)
{
PGconn *conn = connectToServer(new_cluster, db_name);
*** install_support_functions_in_db(const ch
*** 87,93
void
! uninstall_support_functions(void)
{
int dbnum;
--- 87,93
void
! uninstall_support_functions_from_new_cluster(void)
{
int dbnum;
diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c
index 50e4de2..c805a04 100644
*** /tmp/KMuVfb_info.c Sat Jan 8 13:46:55 2011
--- /tmp/gznt7a_info.c Sat Jan 8 13:46:55 2011
*** static void create_rel_filename_map(cons
*** 21,30
const DbInfo *old_db, const DbInfo *new_db,
const RelInfo *old_rel, const RelInfo *new_rel,
FileNameMap *map);
- static RelInfo *relarr_lookup_rel_name(ClusterInfo *cluster, RelInfoArr
*rel_arr,
- const char *nspname, const char *relname);
- static RelInfo *relarr_lookup_rel_oid(ClusterInfo *cluster, RelInfoArr
*rel_arr,
- Oid oid);
/*
--- 21,26
*** gen_db_file_maps(DbInfo *old_db, DbInfo
*** 42,59
int relnum;
int num_maps = 0;
maps = (FileNameMap *) pg_malloc(sizeof(FileNameMap) *
old_db-rel_arr.nrels);
for (relnum = 0; relnum old_db-rel_arr.nrels; relnum++)
{
RelInfo*old_rel = old_db-rel_arr.rels[relnum];
! RelInfo*new_rel;
!
! /* old/new relation names always match */
! new_rel = relarr_lookup_rel_name(new_cluster, new_db-rel_arr,
!
old_rel-nspname, old_rel-relname);
create_rel_filename_map(old_pgdata, new_pgdata, old_db, new_db,
old_rel, new_rel, maps + num_maps);
num_maps++;
--- 38,59
int relnum;
int num_maps = 0;
+ if (old_db-rel_arr.nrels != new_db-rel_arr.nrels)
+ pg_log(PG_FATAL, old and new databases \%s\ have a different
number of relations\n,
+ old_db-db_name);
+
maps = (FileNameMap *) pg_malloc(sizeof(FileNameMap) *
old_db-rel_arr.nrels);
for (relnum = 0; relnum old_db-rel_arr.nrels; relnum++)
{
RelInfo*old_rel = old_db-rel_arr.rels[relnum];
! RelInfo*new_rel = old_db-rel_arr.rels[relnum];
+ if (old_rel-reloid != new_rel-reloid)
+ pg_log(PG_FATAL, mismatch of relation id: database
\%s\,
Re: [HACKERS] WIP: Range Types
When writing the generic range output function, it needs to know the specific range type in order to call the subtype's output function. Records accomplish this by using a special cache based on the typmod, apparently, which looks like a hack to me. Arrays accomplish this by storing the specific type in every array value. That seems very wasteful in the case of range types (which only hold two values). I thought I could get away with using get_fn_expr_argtype() for most of the generic functions, but apparently that can't always provide an answer. Any ideas? Maybe, with alignment and a flags byte (to hold inclusivity, infinite boundaries, etc.), the extra 4 bytes doesn't cost much, anyway? 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] obj_unique_identifier(oid)
On Sat, Jan 8, 2011 at 12:41 PM, Joel Jacobson j...@gluefinance.com wrote: The query below should not return any rows, because if it does, then there are oids with non-unique descriptions. I don't think your analysis is correct. Each entry in pg_depend represents the fact that one object depends on another object, and an object could easily depend on more than one other object, or be depended upon by more than one other object, or depend on one object and be depended on by another. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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: Range Types
On Sat, Jan 8, 2011 at 3:12 PM, Jeff Davis pg...@j-davis.com wrote: Any ideas? Maybe, with alignment and a flags byte (to hold inclusivity, infinite boundaries, etc.), the extra 4 bytes doesn't cost much, anyway? I'd be really reluctant to bloat the range representation by 4 bytes to support an anyrange type. Better to defer this until the great day when we get a better typmod system, at least IMHO. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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: Range Types
On Sat, 2011-01-08 at 15:47 -0500, Robert Haas wrote: On Sat, Jan 8, 2011 at 3:12 PM, Jeff Davis pg...@j-davis.com wrote: Any ideas? Maybe, with alignment and a flags byte (to hold inclusivity, infinite boundaries, etc.), the extra 4 bytes doesn't cost much, anyway? I'd be really reluctant to bloat the range representation by 4 bytes to support an anyrange type. Better to defer this until the great day when we get a better typmod system, at least IMHO. Can you elaborate? How can we have generic functions without ANYRANGE? And without generic functions, how do we make it easy for users to specify a new range type? 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
[HACKERS] SSI patch(es)
I just finished implementing the SLRU techniques to limit shared memory usage and provide graceful degradation under pessimal loads (as suggested by Heikki), Dan seems to be wrapping up work on preventing non-serializable transactions from being rolled back with a serialization failure if they split a predicate-locked page at the point were we're running out of space to allocate predicate locks (as suggested by Heikki), and John's working on documentation. We've recently committed documentation for new GUCs, modified statements, and the new switch on pg_dump. The main things I see that we still need in documentation are a README.SSI file and some serious work in mvcc.sgml. I'm going through the old emails to see what issues people may have raised that might need to be addressed; besides making the AMs for GIN, GiST, and hash SSI aware (so that they have fewer false positive rollbacks than with the default handling), are there any issues people want to be sure I look at before posting a patch? Then there's the question of whether to submit it in pieces. There are going to be big chunks no matter how I slice it, but here are the ideas I have. (All numbers are for context diff format.) If I cut a patch right now for everything, it would be 7742 lines. Right now a patch of the doc/ changes would be 413 lines. If I split out the src/test/regress/ part it would be 1340 lines, mostly python code for dtester tests. If I split out just the src/bin/pg_dump/ changes it would be 98 lines. Splitting out those three would leave src/backend/ and src/include/ which comes in at a svelte 5891 lines. With a little more work I could split the three new files (predicate.c, predicate.h, and predicate_internals.h) out from the changes scattered around the rest of the code. That's 4346 lines and 1545 lines, respectively. Now, these numbers are likely to change a little in the next few days, but not much as a percentage outside the documentation. Thoughts? -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] obj_unique_identifier(oid)
2011/1/8 Robert Haas robertmh...@gmail.com: I don't think your analysis is correct. Each entry in pg_depend represents the fact that one object depends on another object, and an object could easily depend on more than one other object, or be depended upon by more than one other object, or depend on one object and be depended on by another. What does that have to do with this? Two different oids represents two different objects, right? Two different objects should have two different descriptions, right? Otherwise I cannot see how one can argue the description being unique. The pg_describe_object returns unique descriptions for all object types, except for the 5 types I unexpectedly found. -- 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] DISCARD ALL ; stored procedures
All, Alright, so, the whole fn_extra stuff seems to be unrelated.. I'm not sure when it's used (perhaps multiple calls to the same function in a given query?), but the PLs have their own hash tables that they use for storing functions that have been called. I had assumed that was done through fmgr, but apparently not (or at least, I can't find where..). I'm starting to wonder if we're trying to do too much with this though. If all the PLs have to go through SPI to *get* plans (at least ones we care about), perhaps we could just use SPI to implement the plan invalidation? Consider if we saved the DISCARD's transaction ID and store the last-discard txn (or whenever the function was first prepared) in the result of the SPI prepare and then detect if we need to switch to replanning the query in SPI_execute_plan instead of just executing it. Of course, we'd have to have enough info *to* replan it, but we should be able to manage that. Thoughts? Stephen signature.asc Description: Digital signature
contrib/intarray (was Re: [HACKERS] Fixing GIN for empty/null/full-scan cases)
David E. Wheeler da...@kineticode.com writes: On Jan 7, 2011, at 4:19 PM, Tom Lane wrote: Well, actually, I just committed it. If you want to test, feel free. Note that right now only the anyarray @ @ operators are genuinely fixed ... I plan to hack on tsearch and contrib pretty soon though. Hrm, the queries I wrote for this sort of thing use intarray: I'm going to work on contrib/intarray first (before tsearch etc) so that you can do whatever testing you want sooner. One of the things that first got me annoyed about the whole GIN situation is that intarray's definitions of the @ and @ operators were inconsistent with the core operators of the same names. I believe that the inconsistency has to go away. Really the only reason that intarray has its own versions of these operators at all is that it can be faster than the generic anyarray versions in core. There seem to be three ways in which intarray is simpler/faster than the generic operators: * restricted to integer arrays * restricted to 1-D arrays * doesn't allow nulls in the arrays The first of these is pretty important from a speed perspective, and it's also basically free because of the type system: the parser won't attempt to apply intarray's operators to anything that's not an integer array. The second one seems a little more dubious. AFAICS the code isn't actually exploiting 1-D-ness anywhere; it always uses ArrayGetNItems() to compute the array size, for example. I propose that we just drop that restriction and let it accept arrays that are multidimensional, implicitly linearizing the elements in storage order. (Any created arrays will still be 1-D though.) The third restriction is a bit harder to decide what to do about. If we keep it then intarray's @ etc will throw errors in some cases where core would not have. However, dealing with nulls will make the code significantly uglier and probably slower than it is now; and that's not work that I'm excited about doing right now anyway. So what I propose for the moment is that intarray still have that restriction. Maybe someone else will feel like fixing it later. I will however fix the issue described here: http://archives.postgresql.org/pgsql-bugs/2010-12/msg00032.php that intarray sometimes throws nulls not allowed errors on arrays that once contained nulls but now don't. That can be done with a relatively localized patch --- we just need to look a little harder when the ARR_HASNULL flag is set. Comments, objections? 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: contrib/intarray (was Re: [HACKERS] Fixing GIN for empty/null/full-scan cases)
On Jan 8, 2011, at 1:59 PM, Tom Lane wrote: Hrm, the queries I wrote for this sort of thing use intarray: I'm going to work on contrib/intarray first (before tsearch etc) so that you can do whatever testing you want sooner. No, of course not. One of the things that first got me annoyed about the whole GIN situation is that intarray's definitions of the @ and @ operators were inconsistent with the core operators of the same names. I believe that the inconsistency has to go away. Really the only reason that intarray has its own versions of these operators at all is that it can be faster than the generic anyarray versions in core. There seem to be three ways in which intarray is simpler/faster than the generic operators: * restricted to integer arrays * restricted to 1-D arrays * doesn't allow nulls in the arrays My understanding is that they also perform much better if the values in an integer array are ordered. Does that matter? Best, David -- 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] Wildcard search support for pg_trgm
I updated my patch to make it use full index scan in GIN index which is possible thanks to recent Tom Lane patch. Now wildcard, where no trigram can be extracted from, invokes full index scan, which is slow but correct. test=# explain (analyze, buffers) select * from words where word ilike '%in%'; QUERY PLAN Seq Scan on words (cost=0.00..1703.11 rows=15930 width=9) (actual time=0.333..225.817 rows=16558 loops=1) Filter: (word ~~* '%in%'::text) Buffers: shared read=471 Total runtime: 248.207 ms (4 rows) test=# set enable_seqscan = off; SET test=# explain (analyze, buffers) select * from words where word ilike '%in%'; QUERY PLAN Bitmap Heap Scan on words (cost=2287.46..2957.59 rows=15930 width=9) (actual time=122.239..331.993 rows=16558 loops=1) Recheck Cond: (word ~~* '%in%'::text) Buffers: shared hit=472 read=1185 - Bitmap Index Scan on trgm_idx (cost=0.00..2283.48 rows=15930 width=0) (actual time=122.022..122.022 rows=98569 loops=1) Index Cond: (word ~~* '%in%'::text) Buffers: shared hit=1 read=1185 Total runtime: 354.409 ms (7 rows) As an alternative solution I can propose to extract null item from every string and ivoke scan on that item instead of full index scan. It requires to store additional item per each string but it makes full scan fast. Also I found a segfault when execute the query above and switch enable_seqscan few times on line *searchMode = GIN_SEARCH_MODE_ALL;. Is it a bug in GIN or I'm missing something? Here goes backtrace from gdb: #0 0xb4ead070 in gin_extract_query_trgm (fcinfo=0xbfcd8da8) at trgm_gin.c:112 #1 0x08323a84 in OidFunctionCall5 (functionId=32802, arg1=161269768, arg2=3217920208, arg3=4, arg4=3217920204, arg5=3217920200) at fmgr.c:1687 #2 0x082c5654 in gincostestimate (fcinfo=0xbfcd9148) at selfuncs.c:6466 #3 0x083235d8 in OidFunctionCall9 (functionId=2741, arg1=161270176, arg2=161271296, arg3=161824624, arg4=0, arg5=0, arg6=3217921064, arg7=3217921056, arg8=3217921048, arg9=3217921040) at fmgr.c:1840 #4 0x081f3397 in cost_index (path=0x9a55050, root=0x99cc9a0, index=0x99cce00, indexQuals=0x9a53f70, indexOrderBys=0x0, outer_rel=0x0) at costsize.c:268 #5 0x08216b66 in create_index_path (root=0x99cc9a0, index=0x99cce00, clause_groups=0x9a53f88, indexorderbys=0x0, pathkeys=0x0, indexscandir=NoMovementScanDirection, outer_rel=0x0) at pathnode.c:511 #6 0x081f7ef5 in find_usable_indexes (root=value optimized out, rel=value optimized out, clauses=value optimized out, outer_clauses=0x0, istoplevel=1 '\001', outer_rel=0x0, saop_control=SAOP_FORBID, scantype=ST_ANYSCAN) at indxpath.c:422 #7 0x081f8e38 in create_index_paths (root=0x99cc9a0, rel=0x99ccc30) at indxpath.c:176 #8 0x081eec22 in set_plain_rel_pathlist (root=value optimized out, rel=value optimized out, rti=value optimized out, rte=0x99cc650) at allpaths.c:262 #9 set_rel_pathlist (root=value optimized out, rel=value optimized out, rti=value optimized out, rte=0x99cc650) at allpaths.c:202 #10 0x081efa55 in set_base_rel_pathlists (root=0x99cc9a0, joinlist=0x99ccde8) at allpaths.c:158 #11 make_one_rel (root=0x99cc9a0, joinlist=0x99ccde8) at allpaths.c:94 #12 0x08203ef7 in query_planner (root=0x99cc9a0, tlist=0x99ccb00, tuple_fraction=0, limit_tuples=-1, cheapest_path=0xbfcd98cc, sorted_path=0xbfcd98c8, num_groups=0xbfcd98c0) at planmain.c:271 #13 0x08205b86 in grouping_planner (root=0x99cc9a0, tuple_fraction=0) at planner.c:1182 #14 0x08207609 in subquery_planner (glob=0x99cc910, parse=0x99cc5a0, parent_root=0x0, hasRecursion=0 '\000', tuple_fraction=0, subroot=0xbfcd9a7c) at planner.c:536 #15 0x08207ca6 in standard_planner (parse=0x99cc5a0, cursorOptions=0, boundParams=0x0) at planner.c:201 #16 0x0825db11 in pg_plan_query (querytree=0x99cc5a0, cursorOptions=0, boundParams=0x0) at postgres.c:764 #17 0x0815a824 in ExplainOneQuery (stmt=0x9a258e0, queryString=0x9a24c60 explain (analyze, buffers) select * from words where word ilike '%in%';,---Type return to continue, or q return to quit--- params=0x0, dest=0x9a32330) at explain.c:300 #18 ExplainQuery (stmt=0x9a258e0, queryString=0x9a24c60 explain (analyze, buffers) select * from words where word ilike '%in%';, params=0x0, dest=0x9a32330) at explain.c:209 #19 0x08261266 in PortalRunUtility (portal=0x9a4d6a8, utilityStmt=0x9a258e0, isTopLevel=value optimized out, dest=0x9a32330, completionTag=0xbfcd9bcc ) at pquery.c:1191 #20 0x082622a4 in FillPortalStore (portal=0x9a4d6a8, isTopLevel=32 ' ') at pquery.c:1065 #21 0x0826281a in PortalRun (portal=0x9a4d6a8, count=2147483647, isTopLevel=-56 '\310',
Re: contrib/intarray (was Re: [HACKERS] Fixing GIN for empty/null/full-scan cases)
David E. Wheeler da...@kineticode.com writes: On Jan 8, 2011, at 1:59 PM, Tom Lane wrote: There seem to be three ways in which intarray is simpler/faster than the generic operators: * restricted to integer arrays * restricted to 1-D arrays * doesn't allow nulls in the arrays My understanding is that they also perform much better if the values in an integer array are ordered. Does that matter? Some of the operations sort the array contents as an initial step. I'm not sure how much faster they'll be if the array is already ordered, but in any case they don't *require* presorted input. 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] WIP: Range Types
On Sat, 2011-01-08 at 13:05 -0800, Jeff Davis wrote: On Sat, 2011-01-08 at 15:47 -0500, Robert Haas wrote: On Sat, Jan 8, 2011 at 3:12 PM, Jeff Davis pg...@j-davis.com wrote: Any ideas? Maybe, with alignment and a flags byte (to hold inclusivity, infinite boundaries, etc.), the extra 4 bytes doesn't cost much, anyway? I'd be really reluctant to bloat the range representation by 4 bytes to support an anyrange type. Better to defer this until the great day when we get a better typmod system, at least IMHO. Can you elaborate? How can we have generic functions without ANYRANGE? And without generic functions, how do we make it easy for users to specify a new range type? Another thought: If we use timestamps, then that's 8 bytes each, meaning 16 bytes. Then, there is the VARHDRSZ (now we're at 20), the flag byte (21), and the range type oid (25). With alignment (if it's aligned at all), that's either 28 or 32 bytes, which is starting to seem ridiculous. Making it always varlena is kind of nice, because then if the upper or lower bound is special (NULL or infinity), then we can omit it and save some space. But I'm starting to think that it's not worth it, and we should detect whether the subtype is fixed, and if so, make the range type fixed length. That will save on the varlena header. Any suggestions on how to represent/align these ranges? 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] obj_unique_identifier(oid)
On Sat, 2011-01-08 at 22:21 +0100, Joel Jacobson wrote:
2011/1/8 Robert Haas robertmh...@gmail.com:
I don't think your analysis is correct. Each entry in pg_depend
represents the fact that one object depends on another object, and an
object could easily depend on more than one other object, or be
depended upon by more than one other object, or depend on one object
and be depended on by another.
What does that have to do with this?
Two different oids represents two different objects, right?
Two different objects should have two different descriptions, right?
Otherwise I cannot see how one can argue the description being unique.
The pg_describe_object returns unique descriptions for all object
types, except for the 5 types I unexpectedly found.
I can confirm it has nothing to do with pg_depend, and that it seems to
be a bug with that descriptions do not seem to care about different
amproclefttype and amprocrighttype.
SELECT array_agg(oid), array_agg(amproclefttype) FROM pg_amproc GROUP BY
pg_catalog.pg_describe_object(2603,oid,0) HAVING count(*) 1;
One example row produced by that query.
array_agg | array_agg
---+-
{10608,10612} | {1009,1015}
(1 row)
Regards,
Andreas Karlsson
--
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] obj_unique_identifier(oid)
On Sat, Jan 8, 2011 at 4:21 PM, Joel Jacobson j...@gluefinance.com wrote: 2011/1/8 Robert Haas robertmh...@gmail.com: I don't think your analysis is correct. Each entry in pg_depend represents the fact that one object depends on another object, and an object could easily depend on more than one other object, or be depended upon by more than one other object, or depend on one object and be depended on by another. What does that have to do with this? Oops. I misread your query. I thought the duplicates were because you were feeding pg_describe_object the same classoid, objoid, objsubid pair more than once, but I see now that's not the case (UNION != UNION ALL). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] obj_unique_identifier(oid)
Here is a patch, but I am not sure I am not sure if I like my idea for
format. What do you think?
SELECT pg_describe_object('pg_amproc'::regclass,oid,0)
FROM pg_amproc WHERE oid IN (10608,10612);
pg_describe_object
--
function 1 bttextcmp(text,text) of operator family array_ops for access method
gin for (text[],text[])
function 1 bttextcmp(text,text) of operator family array_ops for access method
gin for (character varying[],character varying[])
(2 rows)
Andreas
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index ec8eb74..795051e 100644
*** a/src/backend/catalog/dependency.c
--- b/src/backend/catalog/dependency.c
*** getObjectDescription(const ObjectAddress
*** 2389,2398
* textual form of the function with arguments, and the second
* %s is the description of the operator family.
*/
! appendStringInfo(buffer, _(function %d %s of %s),
amprocForm-amprocnum,
format_procedure(amprocForm-amproc),
! opfam.data);
pfree(opfam.data);
systable_endscan(amscan);
--- 2389,2400
* textual form of the function with arguments, and the second
* %s is the description of the operator family.
*/
! appendStringInfo(buffer, _(function %d %s of %s for (%s,%s)),
amprocForm-amprocnum,
format_procedure(amprocForm-amproc),
! opfam.data,
! format_type_be(amprocForm-amproclefttype),
! format_type_be(amprocForm-amprocrighttype));
pfree(opfam.data);
systable_endscan(amscan);
--
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] obj_unique_identifier(oid)
2011/1/9 Andreas Karlsson andr...@proxel.se:
Here is a patch, but I am not sure I am not sure if I like my idea for
format. What do you think?
SELECT pg_describe_object('pg_amproc'::regclass,oid,0)
FROM pg_amproc WHERE oid IN (10608,10612);
pg_describe_object
--
function 1 bttextcmp(text,text) of operator family array_ops for access
method gin for (text[],text[])
function 1 bttextcmp(text,text) of operator family array_ops for access
method gin for (character varying[],character varying[])
(2 rows)
Looks great! Many thanks for fixing the bug!
Andreas
--
Best regards,
Joel Jacobson
Glue Finance
E: j...@gluefinance.com
T: +46 70 360 38 01
Postal address:
Glue Finance AB
Box 549
114 11 Stockholm
Sweden
Visiting address:
Glue Finance AB
Birger Jarlsgatan 14
114 34 Stockholm
Sweden
--
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] obj_unique_identifier(oid)
2011/1/9 Robert Haas robertmh...@gmail.com: Oops. I misread your query. I thought the duplicates were because you were feeding pg_describe_object the same classoid, objoid, objsubid pair more than once, but I see now that's not the case (UNION != UNION ALL). Ah, I see, yes, the query should actually be UNION, it would produce the same result, but perhaps it would be a bit faster. -- Best regards, Joel Jacobson Glue Finance -- 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] DISCARD ALL ; stored procedures
On Sat, Jan 8, 2011 at 4:28 PM, Stephen Frost sfr...@snowman.net wrote: Thoughts? Unfortunately, we've officially exceeded my level of knowledge to the point where I can't comment intelligently. Sorry :-( -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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: Range Types
On Sat, Jan 8, 2011 at 4:05 PM, Jeff Davis pg...@j-davis.com wrote: On Sat, 2011-01-08 at 15:47 -0500, Robert Haas wrote: On Sat, Jan 8, 2011 at 3:12 PM, Jeff Davis pg...@j-davis.com wrote: Any ideas? Maybe, with alignment and a flags byte (to hold inclusivity, infinite boundaries, etc.), the extra 4 bytes doesn't cost much, anyway? I'd be really reluctant to bloat the range representation by 4 bytes to support an anyrange type. Better to defer this until the great day when we get a better typmod system, at least IMHO. Can you elaborate? How can we have generic functions without ANYRANGE? And without generic functions, how do we make it easy for users to specify a new range type? Oh, hmm. What generic functions did you have in mind? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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: Range Types
On Sat, Jan 8, 2011 at 6:06 PM, Jeff Davis pg...@j-davis.com wrote: If we use timestamps, then that's 8 bytes each, meaning 16 bytes. Then, there is the VARHDRSZ (now we're at 20), the flag byte (21), and the range type oid (25). With alignment (if it's aligned at all), that's either 28 or 32 bytes, which is starting to seem ridiculous. It'll use the 1-byte varlena header format, which is unaligned. So you'll end up with 8 + 8 + 2 bytes = 18 bytes, unaligned. Maybe you could cram that down to 17 bytes unaligned with sufficient work, but I'm not sure it's worth the complexity. If you end up having to include the type OID though that's pretty horrible; it adds another 4 bytes. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] obj_unique_identifier(oid)
On Sat, Jan 8, 2011 at 8:02 PM, Joel Jacobson j...@gluefinance.com wrote: 2011/1/9 Robert Haas robertmh...@gmail.com: Oops. I misread your query. I thought the duplicates were because you were feeding pg_describe_object the same classoid, objoid, objsubid pair more than once, but I see now that's not the case (UNION != UNION ALL). Ah, I see, yes, the query should actually be UNION, it would produce the same result, but perhaps it would be a bit faster. You did use UNION - I think if you used UNION ALL you'd get spurious results. But maybe I'm still confused. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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: Range Types
On Sat, 2011-01-08 at 20:32 -0500, Robert Haas wrote: On Sat, Jan 8, 2011 at 4:05 PM, Jeff Davis pg...@j-davis.com wrote: On Sat, 2011-01-08 at 15:47 -0500, Robert Haas wrote: On Sat, Jan 8, 2011 at 3:12 PM, Jeff Davis pg...@j-davis.com wrote: Any ideas? Maybe, with alignment and a flags byte (to hold inclusivity, infinite boundaries, etc.), the extra 4 bytes doesn't cost much, anyway? I'd be really reluctant to bloat the range representation by 4 bytes to support an anyrange type. Better to defer this until the great day when we get a better typmod system, at least IMHO. Can you elaborate? How can we have generic functions without ANYRANGE? And without generic functions, how do we make it easy for users to specify a new range type? Oh, hmm. What generic functions did you have in mind? Well, input/output, comparisons, overlaps, intersection, minus, and all the necessary GiST support functions. Without generic functions, the only choices we have are: * force the user to write and specify them all -- which doesn't leave much left of my feature (I think the interface would be all that's left). * somehow generate the functions at type creation time Any other ideas? 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] WIP: Range Types
On Sat, Jan 8, 2011 at 9:12 PM, Jeff Davis pg...@j-davis.com wrote: Oh, hmm. What generic functions did you have in mind? Well, input/output, comparisons, overlaps, intersection, minus, and all the necessary GiST support functions. Without generic functions, the only choices we have are: * force the user to write and specify them all -- which doesn't leave much left of my feature (I think the interface would be all that's left). * somehow generate the functions at type creation time Any other ideas? Do they have to be good ideas? I mean, one semi-obvious possibility is to write one set of C functions that can have multiple SQL-level definitions bound to it. Then when the function is called, it can peek at flinfo-fn_oid to figure out which incarnation was called and then get the typo info from there. That's ugly, though. It'd be really nice if we could just arrange for the info on which type anyrange actually is at the moment to be available in the right place. Storing it on disk to work around that is pretty horrible, but maybe there's no other reasonable option. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] SSI patch(es)
On Sat, Jan 8, 2011 at 4:10 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Splitting out those three would leave src/backend/ and src/include/ which comes in at a svelte 5891 lines. With a little more work I could split the three new files (predicate.c, predicate.h, and predicate_internals.h) out from the changes scattered around the rest of the code. That's 4346 lines and 1545 lines, respectively. Now, these numbers are likely to change a little in the next few days, but not much as a percentage outside the documentation. Thoughts? Well, my first thought is - I'm not sure it's realistic to think we're going to get this committed to 9.1. But that's not a very helpful thought. I just don't know who is going to review 7700 lines of code in the next month, and it doesn't sound like it can be decomposed into independent subfeatures that can be committed independently. Splitting it up by directory isn't really all that helpful. I hope someone will step up to the plate; I'm pretty sure I can't do it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Error code for terminating connection due to conflict with recovery
On Sat, Jan 8, 2011 at 9:52 AM, Tatsuo Ishii is...@postgresql.org wrote: While looking at the backend code, I realized that error code for terminating connection due to conflict with recovery is ERRCODE_ADMIN_SHUTDOWN. I thought the error code is for somewhat a human interruption, such as shutdown command issued by pg_ctl. Is the usage of the error code appropreate? That doesn't sound right to me. I'd have thought something in class 40. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] SSI patch(es)
Robert Haas wrote: Well, my first thought is - I'm not sure it's realistic to think we're going to get this committed to 9.1. But that's not a very helpful thought. I just don't know who is going to review 7700 lines of code in the next month, and it doesn't sound like it can be decomposed into independent subfeatures that can be committed independently. Splitting it up by directory isn't really all that helpful. I hope someone will step up to the plate; I'm pretty sure I can't do it. I hope so, too. FWIW, I submitted this patch with almost 2000 fewer lines in what I hoped was a form suitable for initial commit in the 2010-09 CF, knowing full well there were a number of optimizations and improvements I would like to get in before release. But Heikki felt that it wasn't acceptable without those changes -- and for reasons which I find totally understandable. There's sort of a Catch-22 here for large features like this -- if you submit them in skeletal form they aren't accepted because we don't want code in the official repository which isn't production quality yet. But if you flesh it out to where it is production quality, then it's large enough to be hard to review. I know this isn't the first time this issue has been brought up, but I'm feeling it keenly at the moment. There are three contributors who have already been through the code for this patch in sufficient detail to help advance it -- and I'm most grateful for what they've already done. Hopefully those who have already done that won't find it too hard to digest the patch with its latest improvements, and will have the time and inclination to give it a go. One thing that would help a lot besides code review is performance testing. I did some months ago and I know Dan booked time on MIT benchmarking systems and got good numbers, but with the refactoring it would be good to redo that, and benchmarking properly can be very time consuming. Existing benchmark software might need to be tweaked to retry transactions which fail with SQLSTATE 40001, or at least continue on with out counting those in TPS figures, since applications using this feature will generally have frameworks which automatically do retries for that SQLSTATE. -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] GiST insert algorithm rewrite
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 21.12.2010 20:00, Heikki Linnakangas wrote: One final version, with a bug fix wrt. root page split and some cleanup. I'm planning to commit this before Christmas. It's a big patch, so review would be much appreciated. Committed. Phew! Review testing is of course still appreciated, given how big and complicated this was. I just found out that the benchmark test script in contrib/intarray/bench/ crashes HEAD in gistdoinsert() --- it looks like it's trying to pop to a stack entry that isn't there. Run it per the instructions in the intarray documentation: $ createdb TEST $ psql TEST ../_int.sql ... $ ./create_test.pl | psql TEST CREATE TABLE CREATE TABLE CREATE INDEX CREATE INDEX server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost The script generates randomized data, so possibly it won't fail every time, but it failed three out of three times for me. The changes I'm about to commit in intarray don't seem to make any difference. 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] Fixing GIN for empty/null/full-scan cases
David E. Wheeler da...@kineticode.com writes: On Jan 7, 2011, at 4:19 PM, Tom Lane wrote: Well, actually, I just committed it. If you want to test, feel free. Note that right now only the anyarray @ @ operators are genuinely fixed ... I plan to hack on tsearch and contrib pretty soon though. Hrm, the queries I wrote for this sort of thing use intarray: WHERE blah @@ '(12|14)'::query_int That's not done yet though, right? intarray is done now, feel free to test ... 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
