Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Zoltan Boszormenyi <[EMAIL PROTECTED]> writes: > Tom Lane írta: >> Zoltan Boszormenyi <[EMAIL PROTECTED]> writes: >>> Yes. Plain SERIALs can be updated with given values >>> whereas IDENTITY columns cannot. >> >> Really? How is pg_dump going to deal with that? > It emits ALTER TABLE ... SET GENERATED AS IDENTITY > after ALTER SEQUENCE OWNED BY and if any of the > columns is IDENTITY or GENERATED then it emits > COPY OVERRIDING SYSTEM VALUE in my patch already. And you fail to see the irony in that? You might as well just admit that it's OK to update an identity column. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Tom Lane írta: Zoltan Boszormenyi <[EMAIL PROTECTED]> writes: Tom Lane írta: The latter would treat GENERATED BY DEFAULT AS IDENTITY the same as SERIAL. Is there any good reason to distinguish the two? Yes. Plain SERIALs can be updated with given values whereas IDENTITY columns cannot. Really? How is pg_dump going to deal with that? regards, tom lane It emits ALTER TABLE ... SET GENERATED AS IDENTITY after ALTER SEQUENCE OWNED BY and if any of the columns is IDENTITY or GENERATED then it emits COPY OVERRIDING SYSTEM VALUE in my patch already. -- -- Zoltán Böszörményi Cybertec Geschwinde & Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support
"Bruce Momjian" <[EMAIL PROTECTED]> writes: > Uh, shouldn't CREATE TABLE LIKE INCLUDING CONSTRAINTS already be including > any indexes in the parent table? You could argue it should for unique indexes since our unique indexes are how we implement unique constraints. But I see no particular reason to expect it to copy random other indexes. At least its name doesn't lead one to expect it to. I also thought it was sort of strange to have a command that otherwise is just copying definitions suddenly start building whole new objects. I think I was thinking it would be a long slow operation but I suppose creating an empty index isn't really noticeably slow. It could print a NOTICE similar to what's printed when you create a primary key or unique constraint. It does mean that users would be unable to create a partition, load data, then build indexes. Perhaps it would be nice to have an ALTER TABLE foo LIKE bar INCLUDING CONSTRAINTS as well. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] CREATE TABLE LIKE INCLUDING INDEXES support
Uh, shouldn't CREATE TABLE LIKE INCLUDING CONSTRAINTS already be including
any indexes in the parent table?
---
Trevor Hardcastle wrote:
> Greetings all,
>
> I wrote this patch about a week ago to introduce myself to coding on
> PostgreSQL. I wasn't entirely sure what the 'INCLUDING INDEXES' option
> was meant to do, so I held off submitting it until I could get around to
> asking about that and tweaking the documentation to reflect the patch.
> By useful coincidence the thread "Auto creation of Partitions" had this
> post in it, which made the intent of the option clear enough for me to
> go ahead and see what people think of this.
>
> Gregory Stark wrote:
> > "NikhilS" <[EMAIL PROTECTED]> writes:
> >
> >
> >> the intention is to use this information from the parent and make it a
> >> property of the child table. This will avoid the step for the user having
> >> to
> >> manually specify CREATE INDEX and the likes on all the children tables
> >> one-by-one.
> >>
> >
> > Missed the start of this thread. A while back I had intended to add WITH
> > INDEXES to CREATE TABLE LIKE. That would let you create a tale LIKE parent
> > WITH CONSTRAINTS WITH INDEXES and get pretty much a perfect table ready for
> > adding to the inheritance structure.
> >
> >
> >
> So, that's what this patch does. When a table is created with 'CREATE
> TABLE ... LIKE parent INCLUDING INDEXES' this iterates over the parent
> table indexes looking for constraint indexes, and alters the
> CreateStmtContext to include equivalent indexes on the child table.
>
> This is probably a somewhat naive implementation, being a first attempt.
> I wasn't sure what sort of lock to place on the parent indexes or what
> tablespace the new indexes should be created in. Any help improving it
> would be appreciated.
>
> Thank you,
> -Trevor Hardcastle
>
> Index: src/backend/parser/analyze.c
> ===
> RCS file: /projects/cvsroot/pgsql/src/backend/parser/analyze.c,v
> retrieving revision 1.361
> diff -c -r1.361 analyze.c
> *** src/backend/parser/analyze.c 20 Feb 2007 17:32:16 - 1.361
> --- src/backend/parser/analyze.c 7 Mar 2007 01:43:12 -
> ***
> *** 14,19
> --- 14,20
> #include "postgres.h"
>
> #include "access/heapam.h"
> + #include "access/genam.h"
> #include "catalog/heap.h"
> #include "catalog/index.h"
> #include "catalog/namespace.h"
> ***
> *** 40,45
> --- 41,47
> #include "utils/acl.h"
> #include "utils/builtins.h"
> #include "utils/lsyscache.h"
> + #include "utils/relcache.h"
> #include "utils/syscache.h"
>
>
> ***
> *** 1345,1355
> }
> }
>
> - if (including_indexes)
> - ereport(ERROR,
> - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> - errmsg("LIKE INCLUDING INDEXES is not
> implemented")));
> -
> /*
>* Insert the copied attributes into the cxt for the new table
>* definition.
> --- 1347,1352
> ***
> *** 1448,1453
> --- 1445,1519
> }
>
> /*
> + * Clone constraint indexes if requested.
> + */
> + if (including_indexes && relation->rd_rel->relhasindex)
> + {
> + List *parent_index_list = RelationGetIndexList(relation);
> + ListCell *parent_index_scan;
> +
> + foreach(parent_index_scan, parent_index_list)
> + {
> + Oidparent_index_oid =
> lfirst_oid(parent_index_scan);
> + Relation parent_index;
> +
> + parent_index = index_open(parent_index_oid,
> AccessShareLock);
> +
> + /*
> + * Create new unique or primary key indexes on the
> child.
> + */
> + if (parent_index->rd_index->indisunique ||
> parent_index->rd_index->indisprimary)
> + {
> + IndexInfo *parent_index_info;
> + Constraint *n = makeNode(Constraint);
> + AttrNumber parent_attno;
> +
> + parent_index_info =
> BuildIndexInfo(parent_index);
> +
> + if (parent_index->rd_index->indisprimary)
> + {
> + n->contype = CONSTR_PRIMARY;
> + }
> + else
> + {
> + n->contype = CONSTR_UNIQUE;
> + }
> + /* Let DefineIndex name it */
> + n->name = NULL;
> + n->raw_expr = NULL;
> +
Re: [PATCHES] autovacuum multiworkers, patch 5
Joshua D. Drake wrote: > Alvaro Herrera wrote: > >ITAGAKI Takahiro wrote: > >>Alvaro Herrera <[EMAIL PROTECTED]> wrote: > >> > >>>Here is the autovacuum patch I am currently working with. This is > >>>basically the same as the previous patch; I have tweaked the database > >>>list management so that after a change in databases (say a new database > >>>is created or a database is dropped), the list is recomputed to account > >>>for the change, keeping the ordering of the previous list. > >>I'm interested in your multiworkers autovacuum proposal. > >> > >>I'm researching the impact of multiworkers with > >>autovacuum_vacuum_cost_limit. > >>Autovacuum will consume server resources up to autovacuum_max_workers > >>times > >>as many as before. I think we might need to change the semantics of > >>autovacuum_vacuum_cost_limit when we have multiworkers. > > > >Yes, that's correct. Per previous discussion, what I actually wanted to > >do was to create a GUC setting to simplify the whole thing, something > >like "autovacuum_max_mb_per_second" or "autovacuum_max_io_per_second". > >Then, have each worker use up to (max_per_second/active workers) as much > >IO resources. This way, the maximum use of IO resources by vacuum can > >be easily determined and limited by the DBA; certainly much simpler than > >the vacuum cost limiting feature. > > +1 One thing I forgot to mention is that this is unlikely to be implemented in 8.3. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] pgbench transaction timestamps
> Tatsuo, would you please comment on this patch too? No problem. I will come up with a comment by the end of this week. -- Tatsuo Ishii SRA OSS, Inc. Japan > --- > > Greg Smith wrote: > > This patch changes the way pgbench outputs its latency log files so that > > every transaction gets a timestamp and notes which transaction type was > > executed. It's a one-line change that just dumps some additional > > information that was already sitting in that area of code. I also made a > > couple of documentation corrections and clarifications on some of the more > > confusing features of pgbench. > > > > It's straightforward to parse log files in this format to analyze what > > happened during the test at a higher level than was possible with the > > original format. You can find some rough sample code to convert this > > latency format into CVS files and then into graphs at > > http://www.westnet.com/~gsmith/content/postgresql/pgbench.htm which I'll > > be expanding on once I get all my little patches sent in here. > > > > If you recall the earlier version of this patch I submitted, it added a > > cleanup feature that did a vacuum and checkpoint after the test was > > finished and reported two TPS results. The idea was to quantify how much > > of a hit the eventual table maintenance required to clean up after the > > test would take. While those things do influence results and cause some > > of the run-to-run variation in TPS (checkpoints are particularly visible > > in the graphs), after further testing I concluded running a VACUUM VERBOSE > > and CHECKPOINT in a script afterwards and analyzing the results was more > > useful than integrating something into pgbench itself. > > > > -- > > * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD > Content-Description: > > [ Attachment, skipping... ] > > > > > ---(end of broadcast)--- > > TIP 6: explain analyze is your friend > > -- > Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us > EnterpriseDB http://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Optimized pgbench for 8.3
> Tatsuo, would you please comment on this patch? Sure. I will come up with a comment by the end of this week. -- Tatsuo Ishii SRA OSS, Inc. Japan > --- > > ITAGAKI Takahiro wrote: > > The attached is a patch to optimize contrib/pgbench using new 8.3 features. > > > > - Use DROP IF EXISTS to suppress errors for initial loadings. > > - Use a combination of TRUNCATE and COPY to reduce WAL on creating > > the accounts table. > > > > Also, there are some cosmetic changes. > > > > - Change the output of -v option from "starting full vacuum..." > > to "starting vacuum accounts..." in reflection of the fact. > > - Shape duplicated error checks into executeStatement(). > > > > > > There is a big performance win in "COPY with no WAL" feature. > > Thanks for the efforts! > > > > Regards, > > --- > > ITAGAKI Takahiro > > NTT Open Source Software Center > > [ Attachment, skipping... ] > > > > > ---(end of broadcast)--- > > TIP 5: don't forget to increase your free space map settings > > -- > Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us > EnterpriseDB http://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] pgbench transaction timestamps
Tatsuo, would you please comment on this patch too? --- Greg Smith wrote: > This patch changes the way pgbench outputs its latency log files so that > every transaction gets a timestamp and notes which transaction type was > executed. It's a one-line change that just dumps some additional > information that was already sitting in that area of code. I also made a > couple of documentation corrections and clarifications on some of the more > confusing features of pgbench. > > It's straightforward to parse log files in this format to analyze what > happened during the test at a higher level than was possible with the > original format. You can find some rough sample code to convert this > latency format into CVS files and then into graphs at > http://www.westnet.com/~gsmith/content/postgresql/pgbench.htm which I'll > be expanding on once I get all my little patches sent in here. > > If you recall the earlier version of this patch I submitted, it added a > cleanup feature that did a vacuum and checkpoint after the test was > finished and reported two TPS results. The idea was to quantify how much > of a hit the eventual table maintenance required to clean up after the > test would take. While those things do influence results and cause some > of the run-to-run variation in TPS (checkpoints are particularly visible > in the graphs), after further testing I concluded running a VACUUM VERBOSE > and CHECKPOINT in a script afterwards and analyzing the results was more > useful than integrating something into pgbench itself. > > -- > * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD Content-Description: [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Optimized pgbench for 8.3
Tatsuo, would you please comment on this patch? --- ITAGAKI Takahiro wrote: > The attached is a patch to optimize contrib/pgbench using new 8.3 features. > > - Use DROP IF EXISTS to suppress errors for initial loadings. > - Use a combination of TRUNCATE and COPY to reduce WAL on creating > the accounts table. > > Also, there are some cosmetic changes. > > - Change the output of -v option from "starting full vacuum..." > to "starting vacuum accounts..." in reflection of the fact. > - Shape duplicated error checks into executeStatement(). > > > There is a big performance win in "COPY with no WAL" feature. > Thanks for the efforts! > > Regards, > --- > ITAGAKI Takahiro > NTT Open Source Software Center [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] autovacuum multiworkers, patch 5
Alvaro Herrera wrote: ITAGAKI Takahiro wrote: Alvaro Herrera <[EMAIL PROTECTED]> wrote: Here is the autovacuum patch I am currently working with. This is basically the same as the previous patch; I have tweaked the database list management so that after a change in databases (say a new database is created or a database is dropped), the list is recomputed to account for the change, keeping the ordering of the previous list. I'm interested in your multiworkers autovacuum proposal. I'm researching the impact of multiworkers with autovacuum_vacuum_cost_limit. Autovacuum will consume server resources up to autovacuum_max_workers times as many as before. I think we might need to change the semantics of autovacuum_vacuum_cost_limit when we have multiworkers. Yes, that's correct. Per previous discussion, what I actually wanted to do was to create a GUC setting to simplify the whole thing, something like "autovacuum_max_mb_per_second" or "autovacuum_max_io_per_second". Then, have each worker use up to (max_per_second/active workers) as much IO resources. This way, the maximum use of IO resources by vacuum can be easily determined and limited by the DBA; certainly much simpler than the vacuum cost limiting feature. +1 Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] autovacuum multiworkers, patch 5
ITAGAKI Takahiro wrote: > Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > > Here is the autovacuum patch I am currently working with. This is > > basically the same as the previous patch; I have tweaked the database > > list management so that after a change in databases (say a new database > > is created or a database is dropped), the list is recomputed to account > > for the change, keeping the ordering of the previous list. > > I'm interested in your multiworkers autovacuum proposal. > > I'm researching the impact of multiworkers with autovacuum_vacuum_cost_limit. > Autovacuum will consume server resources up to autovacuum_max_workers times > as many as before. I think we might need to change the semantics of > autovacuum_vacuum_cost_limit when we have multiworkers. Yes, that's correct. Per previous discussion, what I actually wanted to do was to create a GUC setting to simplify the whole thing, something like "autovacuum_max_mb_per_second" or "autovacuum_max_io_per_second". Then, have each worker use up to (max_per_second/active workers) as much IO resources. This way, the maximum use of IO resources by vacuum can be easily determined and limited by the DBA; certainly much simpler than the vacuum cost limiting feature. > BTW, I found an unwitting mistake in the foreach_worker() macro. > These two operations are same in C. > - worker + 1 > - (WorkerInfo *) (((char *) worker) + sizeof(WorkerInfo)) Ah, thanks. I had originally coded the macro like you suggest, but then during the development I needed to use the "i" variable as well, so I added it. Apparently later I removed that usage; I see that there are no such uses left in the current code. The "+ sizeof(WorkerInfo)" part is just stupidity on my part, sorry about that. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [HACKERS] --enable-xml instead of --with-libxml?
I have applied the following patch which adds documentation and an
improved error message for an installation that does not use
--with-libxml.
---
Nikolay Samokhvalov wrote:
> On 4/5/07, Bruce Momjian <[EMAIL PROTECTED]> wrote:
> >
> > Nikolay Samokhvalov wrote:
> > [...]
> > > If I am wrong and it's better to leave libxml2-free capabilities, then
> > IMHO
> > > we need to reflect it explicitly in the docs, what requires libxml2, and
> > > what doesn't
> >
> > Agreed, let's do the later and update the documentation.
>
>
> So, you are agreed that I am wrong :-) Well... I would be happy hear some
> arguments, but I cannot insist on it :-)
>
>
> Also, do we
> > output a helpful message if someone tries to use a libxml2 function that
> > isn't available.
>
>
> Yep, here it is:
>
> INSERT INTO xmltest VALUES (1, 'one');
> ERROR: no XML support in this installation
>
> I suppose we should change it to "no libxml2 support in this installation",
> shouldn't we?
>
>
>
> --
> Best regards,
> Nikolay
--
Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/datatype.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v
retrieving revision 1.193
diff -c -c -r1.193 datatype.sgml
*** doc/src/sgml/datatype.sgml 2 Apr 2007 15:27:02 - 1.193
--- doc/src/sgml/datatype.sgml 5 Apr 2007 01:42:38 -
***
*** 3202,3212
advantage over storing XML data in a text field is that it
checks the input values for well-formedness, and there are support
functions to perform type-safe operations on it; see .
! In particular, the xml type can store well-formed
documents, as defined by the XML standard, as well
as content fragments, which are defined by the
production XMLDecl? content in the XML
--- 3202,3214
advantage over storing XML data in a text field is that it
checks the input values for well-formedness, and there are support
functions to perform type-safe operations on it; see . Use of this data type requires the
! installation to have been built with configure
! --with-libxml.
! The xml type can store well-formed
documents, as defined by the XML standard, as well
as content fragments, which are defined by the
production XMLDecl? content in the XML
Index: doc/src/sgml/func.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.374
diff -c -c -r1.374 func.sgml
*** doc/src/sgml/func.sgml 2 Apr 2007 15:27:02 - 1.374
--- doc/src/sgml/func.sgml 5 Apr 2007 01:42:42 -
***
*** 7511,7517
linkend="datatype-xml"> for information about the xml
type. The function-like expressions xmlparse
and xmlserialize for converting to and from
!type xml are not repeated here.
--- 7511,7519
linkend="datatype-xml"> for information about the xml
type. The function-like expressions xmlparse
and xmlserialize for converting to and from
!type xml are not repeated here. Use of many of these
!xml functions requires the installation to have been built
!with configure --with-libxml.
Index: src/backend/utils/adt/xml.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/xml.c,v
retrieving revision 1.39
diff -c -c -r1.39 xml.c
*** src/backend/utils/adt/xml.c 2 Apr 2007 03:49:39 - 1.39
--- src/backend/utils/adt/xml.c 5 Apr 2007 01:42:44 -
***
*** 112,118
#define NO_XML_SUPPORT() \
ereport(ERROR, \
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED), \
! errmsg("no XML support in this installation")))
#define _textin(str) DirectFunctionCall1(textin, CStringGetDatum(str))
--- 112,118
#define NO_XML_SUPPORT() \
ereport(ERROR, \
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED), \
! errmsg("feature not supported; no libxml support in this installation")))
#define _textin(str) DirectFunctionCall1(textin, CStringGetDatum(str))
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PATCHES] autovacuum multiworkers, patch 5
Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Here is the autovacuum patch I am currently working with. This is > basically the same as the previous patch; I have tweaked the database > list management so that after a change in databases (say a new database > is created or a database is dropped), the list is recomputed to account > for the change, keeping the ordering of the previous list. I'm interested in your multiworkers autovacuum proposal. I'm researching the impact of multiworkers with autovacuum_vacuum_cost_limit. Autovacuum will consume server resources up to autovacuum_max_workers times as many as before. I think we might need to change the semantics of autovacuum_vacuum_cost_limit when we have multiworkers. BTW, I found an unwitting mistake in the foreach_worker() macro. These two operations are same in C. - worker + 1 - (WorkerInfo *) (((char *) worker) + sizeof(WorkerInfo)) #define foreach_worker(_i, _worker) \ _worker = (WorkerInfo *) (AutoVacuumShmem + \ offsetof(AutoVacuumShmemStruct, av_workers)); \ for (_i = 0; _i < autovacuum_max_workers; _i++, _worker += sizeof(WorkerInfo)) should be: #define foreach_worker(_worker) \ for ((_worker) = AutoVacuumShmem->av_workers; \ (_worker) < AutoVacuumShmem->av_workers + autovacuum_max_workers; \ (_worker)++) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] HOT WIP Patch - version 6.3
> On 4/3/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: > > > > > > Your patch has been added to the PostgreSQL unapplied patches list at: > > > > > Thanks Bruce. I would like to submit atleast one more revision > which would include couple of TODOs mentioned in my last mail. > I would also like to do some cleanup and commenting to make > review process easier. I hope to do this before the weekend, > but if someone wants to start reviewing it before, please let me > know. > > Is there something more that I can do to help the review process ? > I've posted almost all the design ideas as and when I was posting > patch revisions. Would it help to consolidate them in a single > document ? That would help me lot. Also it would be nice it is place in a source directory as a README file. I have a design document of HOT posted by Simon on 2007/02/07. I wonder what have changed since the proposal. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [GENERAL] dropping role w/dependent objects
On Wednesday April 4 2007 5:37 pm, Bruce Momjian wrote: > > Perhaps this could be added to the TODO list? I won't get > > to it anytime soon. > > Yes. What should the TODO text be? See if the attached patch is acceptable. If not, perhaps the TODO text should be: Enable end user to identify dependent objects when the following error is encountered: ERROR: role "mygroup" cannot be dropped because some objects depend on it DETAIL: 227 objects in this database Index: ./src/backend/catalog/pg_shdepend.c === RCS file: /projects/cvsroot/pgsql/src/backend/catalog/pg_shdepend.c,v retrieving revision 1.17 diff -C1 -r1.17 pg_shdepend.c *** ./src/backend/catalog/pg_shdepend.c 3 Mar 2007 19:32:54 - 1.17 --- ./src/backend/catalog/pg_shdepend.c 5 Apr 2007 00:05:56 - *** *** 484,488 * We try to limit the number of reported dependencies to something sane, ! * both for the user's sake and to avoid blowing out memory. */ ! #define MAX_REPORTED_DEPS 100 --- 484,497 * We try to limit the number of reported dependencies to something sane, ! * both for the user's sake and to avoid blowing out memory. But since ! * this is the only way for an end user to easily identify the dependent ! * objects, make the limit pretty big. Generously assuming each object ! * description is 64 chars long, and assuming we add some commentary of ! * up to 15 chars in storeObjectDescription(), that's ~80 chars per ! * object. If we allow 2000, that's 160Kb, which is reasonable. If the ! * installer gets wild and uses 128 character names, that's still only ! * 320Kb. These sorts of high numbers of dependencies are reached quite ! * easily when a sizeable schema of hundreds of tables has specific grants ! * on each relation. */ ! #define MAX_REPORTED_DEPS 2000 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] autovacuum multiworkers, patch 5
Alvaro Herrera wrote: Hi, uhmmm patch? Here is the autovacuum patch I am currently working with. This is basically the same as the previous patch; I have tweaked the database list management so that after a change in databases (say a new database is created or a database is dropped), the list is recomputed to account for the change, keeping the ordering of the previous list. Modulo two low probability failure scenarios, I feel this patch is ready to be applied; I will do so on Friday unless there are objections. The failure scenarios are detailed in the comment pasted below. I intend to attack these problems next, but as the first one should be fairly low probability, I don't think it should bar the current patch from being applied. (The second problem, which seems to me to be the most serious, should be easily fixable by checking launch times and -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] autovacuum multiworkers, patch 5
Alvaro Herrera wrote:
> Hi,
>
> Here is the autovacuum patch I am currently working with.
Obviously I forgot to attach the patch, sorry.
--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
"Para tener más hay que desear menos"
Index: src/backend/postmaster/autovacuum.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.40
diff -c -p -r1.40 autovacuum.c
*** src/backend/postmaster/autovacuum.c 28 Mar 2007 22:17:12 - 1.40
--- src/backend/postmaster/autovacuum.c 4 Apr 2007 23:34:15 -
***
*** 52,57
--- 52,58
#include "utils/syscache.h"
+ static volatile sig_atomic_t got_SIGUSR1 = false;
static volatile sig_atomic_t got_SIGHUP = false;
static volatile sig_atomic_t avlauncher_shutdown_request = false;
*** static volatile sig_atomic_t avlauncher_
*** 59,64
--- 60,66
* GUC parameters
*/
bool autovacuum_start_daemon = false;
+ int autovacuum_max_workers;
int autovacuum_naptime;
int autovacuum_vac_thresh;
double autovacuum_vac_scale;
*** int autovacuum_freeze_max_age;
*** 69,75
int autovacuum_vac_cost_delay;
int autovacuum_vac_cost_limit;
! /* Flag to tell if we are in the autovacuum daemon process */
static bool am_autovacuum_launcher = false;
static bool am_autovacuum_worker = false;
--- 71,77
int autovacuum_vac_cost_delay;
int autovacuum_vac_cost_limit;
! /* Flags to tell if we are in an autovacuum process */
static bool am_autovacuum_launcher = false;
static bool am_autovacuum_worker = false;
*** static int default_freeze_min_age;
*** 82,95
/* Memory context for long-lived data */
static MemoryContext AutovacMemCxt;
! /* struct to keep list of candidate databases for vacuum */
! typedef struct autovac_dbase
{
! Oid ad_datid;
! char *ad_name;
! TransactionId ad_frozenxid;
! PgStat_StatDBEntry *ad_entry;
! } autovac_dbase;
/* struct to keep track of tables to vacuum and/or analyze, in 1st pass */
typedef struct av_relation
--- 84,105
/* Memory context for long-lived data */
static MemoryContext AutovacMemCxt;
! /* struct to keep track of databases in launcher */
! typedef struct avl_dbase
{
! Oid adl_datid; /* hash key -- must be first */
! TimestampTz adl_next_worker;
! int adl_score;
! } avl_dbase;
!
! /* struct to keep track of databases in worker */
! typedef struct avw_dbase
! {
! Oid adw_datid;
! char *adw_name;
! TransactionId adw_frozenxid;
! PgStat_StatDBEntry *adw_entry;
! } avw_dbase;
/* struct to keep track of tables to vacuum and/or analyze, in 1st pass */
typedef struct av_relation
*** typedef struct autovac_table
*** 110,123
int at_vacuum_cost_limit;
} autovac_table;
typedef struct
{
! Oid process_db; /* OID of database to process */
! int worker_pid; /* PID of the worker process, if any */
} AutoVacuumShmemStruct;
static AutoVacuumShmemStruct *AutoVacuumShmem;
#ifdef EXEC_BACKEND
static pid_t avlauncher_forkexec(void);
static pid_t avworker_forkexec(void);
--- 120,169
int at_vacuum_cost_limit;
} autovac_table;
+ /*-
+ * This struct holds information about a single worker's whereabouts. We keep
+ * an array of these in shared memory, sized according to
+ * autovacuum_max_workers.
+ *
+ * wi_dboid OID of the database this worker is supposed to work on
+ * wi_tableoid OID of the table currently being vacuumed
+ * wi_workerpid PID of the running worker, 0 if not yet started
+ * wi_finished True when the worker is done and about to exit
+ *
+ * The locking for this is a bit weird: all fields except wi_tableoid are
+ * protected by AutovacuumLock, and wi_tableoid is protected by
+ * AutovacuumScheduleLock.
+ *-
+ */
+ typedef struct
+ {
+ Oid wi_dboid;
+ Oid wi_tableoid;
+ int wi_workerpid;
+ bool wi_finished;
+ } WorkerInfo;
+
typedef struct
{
! pid_t av_launcherpid;
! WorkerInfo av_workers[1];
! /* VARIABLE LENGTH STRUCT */
} AutoVacuumShmemStruct;
+ /* Macro to iterate over all workers. Beware multiple evaluation of args! */
+ #define foreach_worker(_i, _worker) \
+ _worker = (WorkerInfo *) (AutoVacuumShmem + \
+ offsetof(AutoVacuumShmemStruct, av_workers)); \
+ for (_i = 0; _i < autovacuum_max_workers; _i++, _worker += sizeof(WorkerInfo))
+
static AutoVacuumShmemStruct *AutoVacuumShmem;
+ /* number of currently free worker slots; only valid in the launcher */
+ static int free_workers;
+ /* the database list in the launcher, and the context that contains it */
+ static Dllist *DatabaseList = NULL;
+ static MemoryContext DatabaseListCxt = NULL;
+
#ifdef EXEC_BACKEND
static pid_t avlauncher_forkexec(void);
static pid_t avworker_forkexec(void);
*** static pid_t
[PATCHES] autovacuum multiworkers, patch 5
Hi, Here is the autovacuum patch I am currently working with. This is basically the same as the previous patch; I have tweaked the database list management so that after a change in databases (say a new database is created or a database is dropped), the list is recomputed to account for the change, keeping the ordering of the previous list. Modulo two low probability failure scenarios, I feel this patch is ready to be applied; I will do so on Friday unless there are objections. The failure scenarios are detailed in the comment pasted below. I intend to attack these problems next, but as the first one should be fairly low probability, I don't think it should bar the current patch from being applied. (The second problem, which seems to me to be the most serious, should be easily fixable by checking launch times and "aborting" processes that took longer than autovacuum_naptime to start). /* * Main loop for the autovacuum launcher process. * * The signalling between launcher and worker is as follows: * * When the worker has finished starting up, it stores its PID in wi_workerpid * and sends a SIGUSR1 signal to the launcher. The launcher then knows that * the postmaster is ready to start a new worker. We do it this way because * otherwise we risk calling SendPostmasterSignal() when the postmaster hasn't * yet processed the last one, in which case the second signal would be lost. * This is only useful when two workers need to be started close to one * another, which should be rare but it's possible. * * Additionally, when the worker is finished with the vacuum work, it sets the * wi_finished flag and sends a SIGUSR1 signal to the launcher. Upon receipt * of this signal, the launcher then clears the entry for future use and may * start another worker right away, if need be. * * There is at least one race condition here: if the workers are all busy, a * database needs immediate attention and a worker finishes just after the * launcher started a worker and sent the signal to postmaster, but before * postmaster processes the signal; at this point, the launcher receives a * signal from the finishing process, sees the empty slot, and sends the * signal to postmaster again to start another worker. But the postmaster * SendPostmasterSignal() flag was already set, so the signal is lost. To * avoid this problem, the launcher should not try to start a new worker until * all WorkerInfo entries that have the wi_dboid field set have a PID assigned. * FIXME someday. The problem is that if we have workers failing to start for * some reason, holding the start of new workers will worsen the starvation by * disabling the start of a new worker as soon as one worker fails to start. * So it's important to be able to distinguish a worker that has failed * starting from a worker that is just taking its little bit of time to do so. * * There is another potential problem if, for some reason, a worker starts and * is not able to finish correctly. It will not be able to set its finished * flag, so the launcher will believe that it's still starting up. To prevent * this problem, we should check the PGPROCs of worker processes, and clean * them up if we find they are not actually running (or they correspond to * processes that are not autovacuum workers.) FIXME someday. */ -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] HOT WIP Patch - version 6.3
Pavan Deolasee wrote: > On 4/3/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: > > > > > > Your patch has been added to the PostgreSQL unapplied patches list at: > > > > > Thanks Bruce. I would like to submit atleast one more revision > which would include couple of TODOs mentioned in my last mail. > I would also like to do some cleanup and commenting to make > review process easier. I hope to do this before the weekend, > but if someone wants to start reviewing it before, please let me > know. > > Is there something more that I can do to help the review process ? > I've posted almost all the design ideas as and when I was posting > patch revisions. Would it help to consolidate them in a single > document ? No, I think you have taken it very far already. I think HOT is similar to a group of patches we are looking at for 8.3 where the implementation and side-effects are complicated, and are going to take a while to resolve. For HOT, I think the basic idea of using UPDATE chains in a single page is a clear win, but we have to make sure that assumptions made subsystems are not broken by this. CREATE INDEX is only one case. Before committing it, we have to be sure we have everything covered. It is also good you have done performance testing, so we are probably OK in that area. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] build/install xml2 when configured with libxml
Peter Eisentraut wrote: And if it is, then you have several options: . don't configure with libxml, or . don't build contrib modules from the contrib Makefile (use the individual module Makefiles instead), or . change the xml2 Makefile Any of these could be worth considering, but it needs to be thought through first. Well, I'm happy to receive suggestions. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] build/install xml2 when configured with libxml
Andrew Dunstan wrote: > Well, how often is libxslt missing when libxml is present, in > practice? A local sample shows a probability of 100%. > And if it is, then you have several options: > . don't configure with libxml, or > . don't build contrib modules from the contrib Makefile (use the > individual module Makefiles instead), or > . change the xml2 Makefile Any of these could be worth considering, but it needs to be thought through first. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Re: [HACKERS] [COMMITTERS] pgsql: Add GUC temp_tablespaces to provide a default location for
Jaime Casanova wrote: > On 4/2/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: > > > > This has been saved for the 8.4 release: > > > >http://momjian.postgresql.org/cgi-bin/pgpatches_hold > > > > mmm... sorry, i have been busy... how many time we have? i can send > something for friday... Yes. Friday is fine, or even Monday. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Zoltan Boszormenyi <[EMAIL PROTECTED]> writes: > Tom Lane írta: >>> The latter would treat GENERATED BY DEFAULT AS IDENTITY >>> the same as SERIAL. >> Is there any good reason to distinguish the two? > Yes. Plain SERIALs can be updated with given values > whereas IDENTITY columns cannot. Really? How is pg_dump going to deal with that? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] build/install xml2 when configured with libxml
Peter Eisentraut wrote: Andrew Dunstan wrote: Since contrib/xml2 seems to be staying with us at least for now, this small patch enables it to be built and installed from the contrib Makefile when --with-libxml is given to configure. contrib/xml2 also requires libxslt. Well, how often is libxslt missing when libxml is present, in practice? And if it is, then you have several options: . don't configure with libxml, or . don't build contrib modules from the contrib Makefile (use the individual module Makefiles instead), or . change the xml2 Makefile My main purpose is to complete buildfarm build coverage. cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Tom Lane írta: Zoltan Boszormenyi <[EMAIL PROTECTED]> writes: So, I should allow DROP DEFAULT, implement SET DEFAULT GENERATED ALWAYS AS and modify the catalog so the GENERATED property is part of pg_attrdef. Sounds good. What about IDENTITY? Should it also be part of pg_attrdef? There are two ways to implement it: have or don't have a notion of it. The latter would treat GENERATED BY DEFAULT AS IDENTITY the same as SERIAL. Is there any good reason to distinguish the two? Yes. Plain SERIALs can be updated with given values whereas IDENTITY columns cannot. And there is the difference between GENERATED and GENERATED IDENTITY: GENERATED columns can updated with DEFAULT values, IDENTITY columns cannot. I strictly have to distinguish IDENTITY from both GENERATED and plain SERIALs. regards, tom lane -- -- Zoltán Böszörményi Cybertec Geschwinde & Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] build/install xml2 when configured with libxml
Andrew Dunstan wrote: > Since contrib/xml2 seems to be staying with us at least for now, this > small patch enables it to be built and installed from the contrib > Makefile when --with-libxml is given to configure. contrib/xml2 also requires libxslt. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Zoltan Boszormenyi <[EMAIL PROTECTED]> writes: > So, I should allow DROP DEFAULT, implement > SET DEFAULT GENERATED ALWAYS AS > and modify the catalog so the GENERATED property > is part of pg_attrdef. Sounds good. > What about IDENTITY? > Should it also be part of pg_attrdef? There are two ways > to implement it: have or don't have a notion of it. > The latter would treat GENERATED BY DEFAULT AS IDENTITY > the same as SERIAL. Is there any good reason to distinguish the two? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Tom Lane írta: Zoltan Boszormenyi <[EMAIL PROTECTED]> writes: I have two questions about the dependency system. 1. Is there a built-in defense to avoid circular dependencies? It doesn't have a problem with them, if that's what you mean. 2. If I register dependencies between column, is there a way to retrieve all table/column type dependencies for a depender column? You can scan pg_depend. What I would like to achieve is to lift the limit that a GENERATED column cannot reference another one. I would counsel not doing that, mainly because then you will have to solve an evaluation-order problem at runtime. OK. Point taken. So, just like with SET / DROP IDENTITY, I should implement SET GENERATED ALWAYS and DROP GENERATED. If you think of it as a property of the default expression, then DROP DEFAULT covers both cases, you don't need DROP GENERATED... So, I should allow DROP DEFAULT, implement SET DEFAULT GENERATED ALWAYS AS and modify the catalog so the GENERATED property is part of pg_attrdef. What about IDENTITY? Should it also be part of pg_attrdef? There are two ways to implement it: have or don't have a notion of it. The latter would treat GENERATED BY DEFAULT AS IDENTITY the same as SERIAL. regards, tom lane -- -- Zoltán Böszörményi Cybertec Geschwinde & Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] build/install xml2 when configured with libxml
Since contrib/xml2 seems to be staying with us at least for now, this small patch enables it to be built and installed from the contrib Makefile when --with-libxml is given to configure. If there's no objection I'll apply in a day or two. cheers andrew Index: configure === RCS file: /cvsroot/pgsql/configure,v retrieving revision 1.540 diff -c -r1.540 configure *** configure 29 Mar 2007 15:30:51 - 1.540 --- configure 4 Apr 2007 19:05:01 - *** *** 314,320 # include #endif" ! ac_subst_vars='SHELL PATH_SEPARATOR PACKAGE_NAME PACKAGE_TARNAME PACKAGE_VERSION PACKAGE_STRING PACKAGE_BUGREPORT exec_prefix prefix program_transform_name bindir sbindir libexecdir datadir sysconfdir sharedstatedir localstatedir libdir includedir oldincludedir infodir mandir build_alias host_alias target_alias DEFS ECHO_C ECHO_N ECHO_T LIBS configure_args build build_cpu build_vendor build_os host host_cpu host_vendor host_os PORTNAME docdir enable_nls WANTED_LANGUAGES default_port enable_shared enable_rpath enable_debug enable_profiling DTRACE DTRACEFLAGS enable_dtrace CC CFLAGS LDFLAGS CPPFLAGS ac_ct_CC EXEEXT OBJEXT CPP GCC TAS autodepend INCLUDES enable_thread_safety with_tcl with_perl with_python with_krb5 krb_srvtab with_pam with_ldap with_bonjour with_openssl XML2_CONFIG with_zlib EGREP ELF_SYS LDFLAGS_SL AWK FLEX FLEXFLAGS LN_S LD with_gnu_ld ld_R_works RANLIB ac_ct_RANLIB TAR STRIP ac_ct_STRIP STRIP_STATIC_LIB STRIP_SHARED_LIB YACC YFLAGS PERL perl_archlibexp perl_privlibexp perl_useshrplib perl_embed_ldflags PYTHON python_version python_configdir python_includespec python_libdir python_libspec python_additional_libs HAVE_IPV6 LIBOBJS acx_pthread_config PTHREAD_CC PTHREAD_LIBS PTHREAD_CFLAGS LDAP_LIBS_FE LDAP_LIBS_BE HAVE_POSIX_SIGNALS MSGFMT MSGMERGE XGETTEXT localedir TCLSH TCL_CONFIG_SH TCL_INCLUDE_SPEC TCL_LIB_FILE TCL_LIBS TCL_LIB_SPEC TCL_SHARED_BUILD TCL_SHLIB_LD_LIBS NSGMLS JADE have_docbook DOCBOOKSTYLE COLLATEINDEX SGMLSPL vpath_build LTLIBOBJS' ac_subst_files='' # Initialize some variables set by options. --- 314,320 # include #endif" ! ac_subst_vars='SHELL PATH_SEPARATOR PACKAGE_NAME PACKAGE_TARNAME PACKAGE_VERSION PACKAGE_STRING PACKAGE_BUGREPORT exec_prefix prefix program_transform_name bindir sbindir libexecdir datadir sysconfdir sharedstatedir localstatedir libdir includedir oldincludedir infodir mandir build_alias host_alias target_alias DEFS ECHO_C ECHO_N ECHO_T LIBS configure_args build build_cpu build_vendor build_os host host_cpu host_vendor host_os PORTNAME docdir enable_nls WANTED_LANGUAGES default_port enable_shared enable_rpath enable_debug enable_profiling DTRACE DTRACEFLAGS enable_dtrace CC CFLAGS LDFLAGS CPPFLAGS ac_ct_CC EXEEXT OBJEXT CPP GCC TAS autodepend INCLUDES enable_thread_safety with_tcl with_perl with_python with_krb5 krb_srvtab with_pam with_ldap with_bonjour with_openssl XML2_CONFIG with_libxml with_zlib EGREP ELF_SYS LDFLAGS_SL AWK FLEX FLEXFLAGS LN_S LD with_gnu_ld ld_R_works RANLIB ac_ct_RANLIB TAR STRIP ac_ct_STRIP STRIP_STATIC_LIB STRIP_SHARED_LIB YACC YFLAGS PERL perl_archlibexp perl_privlibexp perl_useshrplib perl_embed_ldflags PYTHON python_version python_configdir python_includespec python_libdir python_libspec python_additional_libs HAVE_IPV6 LIBOBJS acx_pthread_config PTHREAD_CC PTHREAD_LIBS PTHREAD_CFLAGS LDAP_LIBS_FE LDAP_LIBS_BE HAVE_POSIX_SIGNALS MSGFMT MSGMERGE XGETTEXT localedir TCLSH TCL_CONFIG_SH TCL_INCLUDE_SPEC TCL_LIB_FILE TCL_LIBS TCL_LIB_SPEC TCL_SHARED_BUILD TCL_SHLIB_LD_LIBS NSGMLS JADE have_docbook DOCBOOKSTYLE COLLATEINDEX SGMLSPL vpath_build LTLIBOBJS' ac_subst_files='' # Initialize some variables set by options. *** *** 4331,4336 --- 4331,4337 fi + # # Zlib # *** *** 24091,24096 --- 24092,24098 s,@with_bonjour@,$with_bonjour,;t t s,@with_openssl@,$with_openssl,;t t s,@XML2_CONFIG@,$XML2_CONFIG,;t t + s,@with_libxml@,$with_libxml,;t t s,@with_zlib@,$with_zlib,;t t s,@EGREP@,$EGREP,;t t s,@ELF_SYS@,$ELF_SYS,;t t Index: configure.in === RCS file: /cvsroot/pgsql/configure.in,v retrieving revision 1.507 diff -c -r1.507 configure.in *** configure.in 29 Mar 2007 15:30:51 - 1.507 --- configure.in 4 Apr 2007 19:05:05 - *** *** 577,582 --- 577,583 fi fi + AC_SUBST(with_libxml) # # Zlib Index: contrib/Makefile === RCS file: /cvsroot/pgsql/contrib/Makefile,v retrieving revision 1.71 diff -c -r1.71 Makefile *** contrib/Makefile 8 Feb 2007 15:09:47 - 1.71 --- contrib/Makefile 4 Apr 2007 19:05:05 - *** *** 37,45 WANTED_DIRS += sslinfo endif # Missing: # start-scripts \ (does not have a makefile) - # xml2 \ (requires libxml installed) all install installdirs
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Zoltan Boszormenyi <[EMAIL PROTECTED]> writes: > I have two questions about the dependency system. > 1. Is there a built-in defense to avoid circular dependencies? It doesn't have a problem with them, if that's what you mean. > 2. If I register dependencies between column, is there a way > to retrieve all table/column type dependencies for a depender column? You can scan pg_depend. > What I would like to achieve is to lift the limit that > a GENERATED column cannot reference another one. I would counsel not doing that, mainly because then you will have to solve an evaluation-order problem at runtime. > Point taken. So, just like with SET / DROP IDENTITY, > I should implement SET GENERATED ALWAYS > and DROP GENERATED. If you think of it as a property of the default expression, then DROP DEFAULT covers both cases, you don't need DROP GENERATED... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Tom Lane írta: Zoltan Boszormenyi <[EMAIL PROTECTED]> writes: Before you get too excited about making generated columns disappear automatically in all these cases, consider that dropping a column is not something to be done lightly --- it might contain irreplaceable data. The standard says that the GENERATED column should be dropped silently if either of the referenced columns is dropped. [ itch... ] I think a pretty good case could be made for ignoring that provision, on the grounds that it's a foot-gun, and that it's not very important to follow the standard slavishly on this point because it's hard to conceive of any application actually relying on that behavior. You could probably implement the auto-drop behavior with some combination of (a) AUTO rather than NORMAL dependencies from the default expression to the stuff it depends on and (b) INTERNAL rather than AUTO dependency from the default expression to its column. But I really question whether this is a good idea. So, all dependency should be NORMAL to require manual CASCADE to avoid accidental data loss. I have two questions about the dependency system. 1. Is there a built-in defense to avoid circular dependencies? 2. If I register dependencies between column, is there a way to retrieve all table/column type dependencies for a depender column? What I would like to achieve is to lift the limit that a GENERATED column cannot reference another one. Only self-referencing should be disallowed. The standard says somewhere that GENERATED columns can only be added to and dropped from a table. This argument carries no weight at all --- there is plenty of stuff in PG that is an extension of the capabilities listed in the spec. Point taken. So, just like with SET / DROP IDENTITY, I should implement SET GENERATED ALWAYS and DROP GENERATED. regards, tom lane -- -- Zoltán Böszörményi Cybertec Geschwinde & Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Zoltan Boszormenyi <[EMAIL PROTECTED]> writes: >> Before you get too excited about making generated columns disappear >> automatically in all these cases, consider that dropping a column >> is not something to be done lightly --- it might contain irreplaceable >> data. > The standard says that the GENERATED column should be > dropped silently if either of the referenced columns is dropped. [ itch... ] I think a pretty good case could be made for ignoring that provision, on the grounds that it's a foot-gun, and that it's not very important to follow the standard slavishly on this point because it's hard to conceive of any application actually relying on that behavior. You could probably implement the auto-drop behavior with some combination of (a) AUTO rather than NORMAL dependencies from the default expression to the stuff it depends on and (b) INTERNAL rather than AUTO dependency from the default expression to its column. But I really question whether this is a good idea. > The standard says somewhere that GENERATED columns > can only be added to and dropped from a table. This argument carries no weight at all --- there is plenty of stuff in PG that is an extension of the capabilities listed in the spec. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Tom Lane írta: I wrote: I see another problem with this patch: the code added to ATExecDropColumn is a crude hack. It doesn't work anyway since this is not the only possible way for columns to be dropped (another one that comes to mind immediately is DROP TYPE ... CASCADE). The only correct way to handle things is to let the dependency mechanism do it. I will try that. Actually, the whole question of dependencies for generated columns probably needs some thought. What should happen if a function or operator used in a GENERATED expression gets dropped? The result for a normal column's default expression is that the default expression goes away, but the column is still there. I suspect we don't want that for a GENERATED column --- it would then be effectively a plain column. No, I would want the DROP FUNCTION to be cancelled if used in a GENERATED, but a DROP ... CASCADE would drop it, too. So, DEPENDENCY_NORMAL will keep the referencing object but DEPENDENCY_AUTO would drop it too if the referenced object is dropped? Along the same lines, is ALTER COLUMN DROP DEFAULT a legal operation on a generated column? What about just replacing the expression with ALTER COLUMN SET DEFAULT? Neither of these options are legal for GENERATED columns, AFAIK I prohibited them already. Before you get too excited about making generated columns disappear automatically in all these cases, consider that dropping a column is not something to be done lightly --- it might contain irreplaceable data. The standard says that the GENERATED column should be dropped silently if either of the referenced columns is dropped. I haven't seen anything about the expression, though. On second thought maybe the right approach is just to allow the default expression to be dropped the same as it would be for an ordinary column, and to make sure that if a GENERATED column doesn't (currently) have a default, it is treated the same as an ordinary column. This leads to the further thought that maybe GENERATED is not a property of a column at all, but of its default (ie, it should be stored in pg_attrdef not pg_attribute, which would certainly make the patch less messy). AFAICS plain GENERATED merely indicates that the default expression can depend on other columns, which is certainly a property of the default --- you could imagine ALTER COLUMN SET DEFAULT GENERATED AS ... to make a formerly plain column into a GENERATED one. I'm not entirely sure about ALWAYS though. The standard says somewhere that GENERATED columns can only be added to and dropped from a table. My observation is: I deleted my hack from ATExecDropColumn() and now I cannot drop the referenced column without CASCADE. The comment in StoreAttrDefault() says the objects in the expression will have dependencies registered. I guess "objects" also means functions? This way, if I do explicit recordDependencyOn(, DEPENDENCY_AUTO) on referenced columns then the standard requirement will be satisfied, i.e. dropping columns will drop GENERATED columns silently that reference the said column but . Am I right? Or how about using recordDependencyOnSingleRelExpr(... , DEP_NORMAL, DEP_AUTO ) ? regards, tom lane -- -- Zoltán Böszörményi Cybertec Geschwinde & Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] HOT WIP Patch - version 6.3
On 4/3/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: Your patch has been added to the PostgreSQL unapplied patches list at: Thanks Bruce. I would like to submit atleast one more revision which would include couple of TODOs mentioned in my last mail. I would also like to do some cleanup and commenting to make review process easier. I hope to do this before the weekend, but if someone wants to start reviewing it before, please let me know. Is there something more that I can do to help the review process ? I've posted almost all the design ideas as and when I was posting patch revisions. Would it help to consolidate them in a single document ? Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
I wrote: > I see another problem with this patch: the code added to > ATExecDropColumn is a crude hack. It doesn't work anyway since this is > not the only possible way for columns to be dropped (another one that > comes to mind immediately is DROP TYPE ... CASCADE). The only correct > way to handle things is to let the dependency mechanism do it. Actually, the whole question of dependencies for generated columns probably needs some thought. What should happen if a function or operator used in a GENERATED expression gets dropped? The result for a normal column's default expression is that the default expression goes away, but the column is still there. I suspect we don't want that for a GENERATED column --- it would then be effectively a plain column. Along the same lines, is ALTER COLUMN DROP DEFAULT a legal operation on a generated column? What about just replacing the expression with ALTER COLUMN SET DEFAULT? Before you get too excited about making generated columns disappear automatically in all these cases, consider that dropping a column is not something to be done lightly --- it might contain irreplaceable data. On second thought maybe the right approach is just to allow the default expression to be dropped the same as it would be for an ordinary column, and to make sure that if a GENERATED column doesn't (currently) have a default, it is treated the same as an ordinary column. This leads to the further thought that maybe GENERATED is not a property of a column at all, but of its default (ie, it should be stored in pg_attrdef not pg_attribute, which would certainly make the patch less messy). AFAICS plain GENERATED merely indicates that the default expression can depend on other columns, which is certainly a property of the default --- you could imagine ALTER COLUMN SET DEFAULT GENERATED AS ... to make a formerly plain column into a GENERATED one. I'm not entirely sure about ALWAYS though. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Zoltan Boszormenyi <[EMAIL PROTECTED]> writes: > Here's the new version with the modifications you requested. I see another problem with this patch: the code added to ATExecDropColumn is a crude hack. It doesn't work anyway since this is not the only possible way for columns to be dropped (another one that comes to mind immediately is DROP TYPE ... CASCADE). The only correct way to handle things is to let the dependency mechanism do it. I think you would get the behavior you want if you make the generated columns have AUTO rather than NORMAL dependencies on the columns they reference. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Tom Lane írta: Zoltan Boszormenyi <[EMAIL PROTECTED]> writes: The idea wouldn't have considered to cross my mind if Tom didn't mention the action-at-a-distance behaviour. AFAIR, that remark had to do with NEXT VALUE FOR, which SQL2003 defines in a very weird way (it's not equivalent to nextval() as one would wish). I don't see anything strange in the spec for GENERATED. regards, tom lane Thanks for clarifying. Please review the version I sent you. -- -- Zoltán Böszörményi Cybertec Geschwinde & Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Zoltan Boszormenyi <[EMAIL PROTECTED]> writes: > The idea wouldn't have considered to cross my mind > if Tom didn't mention the action-at-a-distance behaviour. AFAIR, that remark had to do with NEXT VALUE FOR, which SQL2003 defines in a very weird way (it's not equivalent to nextval() as one would wish). I don't see anything strange in the spec for GENERATED. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Andrew Dunstan írta: Zoltan Boszormenyi wrote: Tom Lane írta: - unique index checks are done in two steps to avoid inflating the sequence if a unique index check is failed that doesn't reference the IDENTITY column This is just not acceptable --- there is nothing in the standard that requires such behavior, But also there is nothing that would say not to do it. :-) And this way, there is be nothing that would separate IDENTITY from regular SERIALs only the slightly later value generation. The behaviour I proposed would be a big usability plus over the standard with less possible skipped values. and I dislike the wide-ranging kluges you introduced to support it. Can you see any other way to avoid skipping sequence values as much as possible? This doesn't strike me as a sensible design goal. Why not just live with skipped values? cheers andrew The idea wouldn't have considered to cross my mind if Tom didn't mention the action-at-a-distance behaviour. If you look back in the archives, my first working IDENTITY was nothing more than the syntactic sugar over the regular SERIAL. -- -- Zoltán Böszörményi Cybertec Geschwinde & Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] xpath_array with namespaces support
On 4/4/07, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote:
So, choosing between two inefficient approaches:
1. mine, which in some cases use dummy element wrapping, that we could
escape;
2. proposed by you, which leads to +1 parsing.
... I'd definitely choose the first one.
I'd make it a bit more clear.
We have different cases for XML value as input of xpath():
a. document with prolog ('
Re: [PATCHES] xpath_array with namespaces support
Am Mittwoch, 4. April 2007 15:20 schrieb Nikolay Samokhvalov: > > To determine if an XML datum is a document, call xml_is_document(). The > > implementation of that function is probably not the best possible one, > > but what the xpath() code does it totally wrong nevertheless. > > You are proposing 2-3 (depends on the case) parsing times for the one XML > value instead of current 1-2 I know it's bad, and something like adding a bit (byte) to mark this in the value would be good, but that doesn't change the fact that (xmlStrncmp((xmlChar *) VARDATA(data), (xmlChar *) "bar' IS DOCUMENT; ?column? -- t (1 row) pei=# select xml 'barbar' IS DOCUMENT; ?column? -- f (1 row) pei=# select xml 'bar' IS DOCUMENT; ?column? -- t (1 row) pei=# select xml 'barbar' IS DOCUMENT; ?column? -- f (1 row) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] xpath_array with namespaces support
Am Mittwoch, 4. April 2007 14:43 schrieb Nikolay Samokhvalov: > > Why do we even need to support xpath on fragments? > > Why not? I find it useful and convenient. Well, rather than inventing bogus root wrapper elements, why not let users call xmlelement() to produce the wrapper element themselves? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Zoltan Boszormenyi wrote: Tom Lane írta: - unique index checks are done in two steps to avoid inflating the sequence if a unique index check is failed that doesn't reference the IDENTITY column This is just not acceptable --- there is nothing in the standard that requires such behavior, But also there is nothing that would say not to do it. :-) And this way, there is be nothing that would separate IDENTITY from regular SERIALs only the slightly later value generation. The behaviour I proposed would be a big usability plus over the standard with less possible skipped values. and I dislike the wide-ranging kluges you introduced to support it. Can you see any other way to avoid skipping sequence values as much as possible? This doesn't strike me as a sensible design goal. Why not just live with skipped values? cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] xpath_array with namespaces support
Am Mittwoch, 4. April 2007 14:42 schrieb Nikolay Samokhvalov: > > Why is the function not strict? > > Because in case of 3rd argument (NS mappings) being NULL, we shouldn't > return NULL immediately: If the namespace mapping is NULL then it is unknown, and therefore the result of the XPath expression cannot be evaluated with certainty. If no namespace mapping is to be passed, then you should pass a list(/array/...) of length zero. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] xpath_array with namespaces support
Am Mittwoch, 4. April 2007 14:42 schrieb Nikolay Samokhvalov: > Maybe it's worth to start keeping additional information in xml datum (i.e. > bit IS_DOCUMENT and, what is more important for xpath() function, a bit > indicating that XML value has only one root and can be considered as a tree > => there is no need to wrap with .. ). But this change requires > additional time to design xml datum structure and to rework the code > (macros, I/O functions...). To determine if an XML datum is a document, call xml_is_document(). The implementation of that function is probably not the best possible one, but what the xpath() code does it totally wrong nevertheless. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: IDENTITY/GENERATED v36 Re: [PATCHES] Final version of IDENTITY/GENERATED patch
Tom Lane írta: Zoltan Boszormenyi <[EMAIL PROTECTED]> writes: [ IDENTITY/GENERATED patch ] I got around to reviewing this today. Thanks for the review. Sorry for the bit late reply, I was ill and then occupied with some other work. - unique index checks are done in two steps to avoid inflating the sequence if a unique index check is failed that doesn't reference the IDENTITY column This is just not acceptable --- there is nothing in the standard that requires such behavior, But also there is nothing that would say not to do it. :-) And this way, there is be nothing that would separate IDENTITY from regular SERIALs only the slightly later value generation. The behaviour I proposed would be a big usability plus over the standard with less possible skipped values. and I dislike the wide-ranging kluges you introduced to support it. Can you see any other way to avoid skipping sequence values as much as possible? Please get rid of that and put the behavior back into ordinary DEFAULT-substitution where it belongs. You mean put the IDENTITY generation into rewriteTargetList()? And what about the "action at a distance" behaviour you praised so much before? (Which made the less-skipping behaviour implementable...) Anyway, I put it back. But it brought the consequence that GENERATED fields may reference IDENTITY columns, too, so I removed this limitation as well. - to minimize runtime impact of checking whether an index references the IDENTITY column and skipping it in the first step in ExecInsertIndexTuples(), I introduced a new attribute in the pg_index catalog. This is likewise unreasonably complex and fragile ... but it goes away anyway if you remove the above, no? Yes. The patch appears to believe that OVERRIDING SYSTEM VALUE should be restricted to the table owner, but I don't actually see any support for that in the SQL2003 spec ... where did you get that from? Somehow it felt wrong to allow everybody to use it. Limit removed. I'm pretty dubious about the kluges in aclchk.c to automatically grant/revoke on dependent sequences --- particularly the "revoke" part. The problem with that is that it breaks if the same sequence is being used to feed multiple tables. OK, removed. User-facing errors need to be ereport() not elog() so that they can be translated and have appropriate SQLSTATEs reported. elog is only for internal errors. OK, changed. One other thought is that the field names based on force_default seemed confusing. I'd suggest that maybe "generated" would be a better name choice. I modified the names. force_default -> is_identity, attforceddef -> attgenerated I also fixed COPY without OVERRIDING SYSTEM VALUE regarding IDENTITY and GENERATED fields and modified the docs and the testcase according to your requested modifications. Please fix and resubmit. regards, tom lane Thanks again for the review. Here's the new version with the modifications you requested. -- -- Zoltán Böszörményi Cybertec Geschwinde & Schönig GmbH http://www.postgresql.at/ psql-serial-38.diff.gz Description: Unix tar archive ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] COPY-able sql log outputs
FAST PostgreSQL wrote: On Wed, 4 Apr 2007 02:26, Andrew Dunstan wrote: I am currently doing the following modifications to the patch as per the review comments. 1. Changing all references to 'sqllog' or 'sql' to 'csvlog' and 'csv' respectively. 2. Escaping the username and databasename 3. Constructing the csvlog filename using log_filename instead of strcat. 4. General code optimization. Any other changes required ? ? Function additions like get_timestamp need more comments, or better comments "returns the timestamp", which timestamp? Will soon submit the updated patch. Rgds, Arul Shaji FAST PostgreSQL wrote: Am Dienstag, 3. April 2007 20:33 schrieb FAST PostgreSQL: Attached is the completed patch for the COPY-able sql log outputs. Could you please remove random whitespace changes from this patch? Done and attached. Brief review of the CSV aspect only: a. username and databasename should probably be quoted and escaped in case they contain dangerous characters (even though that's unlikely) b. calling pg_get_client_encoding() inside the loop in escape_string_literal seems suboptimal. At the very least it should be moved outside the loop so it's only called once per string. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
