[PATCHES] New version of GENERATED/IDENTITY, was Re: parser dilemma
Hi, here's the patch with the modifications suggested by Tom Lane. The postfix rule was deleted from b_expr and the reverse parsing in ruleutils.c::get_oper_expr() always puts parentheses around postfix operators. Other changes: - OVERRIDING SYSTEM VALUE in COPY can appear at any place in the option list. - pg_dump was modified accordingly - \copy built-in in psql now also accepts OVERRIDING SYSTEM VALUE - documentation and testcase updates Please, review. Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Geschwinde Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] New version of GENERATED/IDENTITY, was Re: parser dilemma
And here it is attached. Sorry. Zoltan Boszormenyi írta: Hi, here's the patch with the modifications suggested by Tom Lane. The postfix rule was deleted from b_expr and the reverse parsing in ruleutils.c::get_oper_expr() always puts parentheses around postfix operators. Other changes: - OVERRIDING SYSTEM VALUE in COPY can appear at any place in the option list. - pg_dump was modified accordingly - \copy built-in in psql now also accepts OVERRIDING SYSTEM VALUE - documentation and testcase updates Please, review. Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Geschwinde Schönig GmbH http://www.postgresql.at/ psql-serial-43.diff.gz Description: Unix tar archive ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] New version of GENERATED/IDENTITY, was Re: parser dilemma
Hi,
some last changes. Really. :-)
I made ALTER TABLE symmetric with CREATE TABLE
so the grammar now has:
ALTER TABLE tabname ALTER colname SET GENERATED
{ ALWAYS | BY DEFAULT} AS IDENTITY [ ( sequence options )]
This works intuitively the same as in CREATE TABLE, i.e.
- it creates an OWNED sequence (if the column doesn't already have one)
- it creates or alters the sequence with the given options
- adds the DEFAULT expression with the proper generation behaviour
in one go. I extended the documentation and modified the test case
accordingly.
I also tested that an IDENTITY column can't be created with a type that
cannot be cast from bigint i.e. box. I added it to the test case.
Please, review.
Best regards,
Zoltán Böszörményi
Zoltan Boszormenyi írta:
And here it is attached. Sorry.
Zoltan Boszormenyi írta:
Hi,
here's the patch with the modifications suggested by Tom Lane.
The postfix rule was deleted from b_expr and the reverse parsing
in ruleutils.c::get_oper_expr() always puts parentheses around
postfix operators.
Other changes:
- OVERRIDING SYSTEM VALUE in COPY can appear
at any place in the option list.
- pg_dump was modified accordingly
- \copy built-in in psql now also accepts OVERRIDING SYSTEM VALUE
- documentation and testcase updates
Please, review.
Best regards,
Zoltán Böszörményi
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
--
Zoltán Böszörményi
Cybertec Geschwinde Schönig GmbH
http://www.postgresql.at/
psql-serial-44.diff.gz
Description: Unix tar archive
---(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] New version of GENERATED/IDENTITY, was Re: parser dilemma
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---
Zoltan Boszormenyi wrote:
Hi,
some last changes. Really. :-)
I made ALTER TABLE symmetric with CREATE TABLE
so the grammar now has:
ALTER TABLE tabname ALTER colname SET GENERATED
{ ALWAYS | BY DEFAULT} AS IDENTITY [ ( sequence options )]
This works intuitively the same as in CREATE TABLE, i.e.
- it creates an OWNED sequence (if the column doesn't already have one)
- it creates or alters the sequence with the given options
- adds the DEFAULT expression with the proper generation behaviour
in one go. I extended the documentation and modified the test case
accordingly.
I also tested that an IDENTITY column can't be created with a type that
cannot be cast from bigint i.e. box. I added it to the test case.
Please, review.
Best regards,
Zolt?n B?sz?rm?nyi
Zoltan Boszormenyi ?rta:
And here it is attached. Sorry.
Zoltan Boszormenyi ?rta:
Hi,
here's the patch with the modifications suggested by Tom Lane.
The postfix rule was deleted from b_expr and the reverse parsing
in ruleutils.c::get_oper_expr() always puts parentheses around
postfix operators.
Other changes:
- OVERRIDING SYSTEM VALUE in COPY can appear
at any place in the option list.
- pg_dump was modified accordingly
- \copy built-in in psql now also accepts OVERRIDING SYSTEM VALUE
- documentation and testcase updates
Please, review.
Best regards,
Zolt?n B?sz?rm?nyi
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
--
Zolt?n B?sz?rm?nyi
Cybertec Geschwinde Sch?nig GmbH
http://www.postgresql.at/
[ application/x-tar is not supported, skipping... ]
--
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] [BUGS] BUG #3245: PANIC: failed to re-find shared lock object
Attached is the complete patch against HEAD to prevent hashtable bucket
splits during hash_seq_search. Any comments before I start
back-porting? I suppose we had better patch this all the way back,
even though AtPrepare_Locks() is the only known trouble spot.
regards, tom lane
*** src/backend/access/transam/xact.c.orig Tue Apr 3 12:34:35 2007
--- src/backend/access/transam/xact.c Wed Apr 25 20:32:00 2007
***
*** 1631,1636
--- 1631,1637
/* smgrcommit already done */
AtEOXact_Files();
AtEOXact_ComboCid();
+ AtEOXact_HashTables(true);
pgstat_clear_snapshot();
pgstat_count_xact_commit();
pgstat_report_txn_timestamp(0);
***
*** 1849,1854
--- 1850,1856
/* smgrcommit already done */
AtEOXact_Files();
AtEOXact_ComboCid();
+ AtEOXact_HashTables(true);
pgstat_clear_snapshot();
CurrentResourceOwner = NULL;
***
*** 2003,2008
--- 2005,2011
smgrabort();
AtEOXact_Files();
AtEOXact_ComboCid();
+ AtEOXact_HashTables(false);
pgstat_clear_snapshot();
pgstat_count_xact_rollback();
pgstat_report_txn_timestamp(0);
***
*** 3716,3721
--- 3719,3725
s-parent-subTransactionId);
AtEOSubXact_Files(true, s-subTransactionId,
s-parent-subTransactionId);
+ AtEOSubXact_HashTables(true, s-nestingLevel);
/*
* We need to restore the upper transaction's read-only state, in case
the
***
*** 3827,3832
--- 3831,3837
s-parent-subTransactionId);
AtEOSubXact_Files(false, s-subTransactionId,
s-parent-subTransactionId);
+ AtEOSubXact_HashTables(false, s-nestingLevel);
}
/*
*** src/backend/commands/prepare.c.orig Mon Apr 16 14:21:07 2007
--- src/backend/commands/prepare.c Thu Apr 26 15:00:40 2007
***
*** 21,27
#include catalog/pg_type.h
#include commands/explain.h
#include commands/prepare.h
! #include funcapi.h
#include parser/analyze.h
#include parser/parse_coerce.h
#include parser/parse_expr.h
--- 21,27
#include catalog/pg_type.h
#include commands/explain.h
#include commands/prepare.h
! #include miscadmin.h
#include parser/analyze.h
#include parser/parse_coerce.h
#include parser/parse_expr.h
***
*** 743,834
Datum
pg_prepared_statement(PG_FUNCTION_ARGS)
{
! FuncCallContext *funcctx;
! HASH_SEQ_STATUS *hash_seq;
! PreparedStatement *prep_stmt;
! /* stuff done only on the first call of the function */
! if (SRF_IS_FIRSTCALL())
! {
! TupleDesc tupdesc;
! MemoryContext oldcontext;
! /* create a function context for cross-call persistence */
! funcctx = SRF_FIRSTCALL_INIT();
! /*
!* switch to memory context appropriate for multiple function
calls
!*/
! oldcontext =
MemoryContextSwitchTo(funcctx-multi_call_memory_ctx);
! /* allocate memory for user context */
! if (prepared_queries)
{
! hash_seq = (HASH_SEQ_STATUS *)
palloc(sizeof(HASH_SEQ_STATUS));
! hash_seq_init(hash_seq, prepared_queries);
! funcctx-user_fctx = (void *) hash_seq;
! }
! else
! funcctx-user_fctx = NULL;
! /*
!* build tupdesc for result tuples. This must match the
definition of
!* the pg_prepared_statements view in system_views.sql
!*/
! tupdesc = CreateTemplateTupleDesc(5, false);
! TupleDescInitEntry(tupdesc, (AttrNumber) 1, name,
! TEXTOID, -1, 0);
! TupleDescInitEntry(tupdesc, (AttrNumber) 2, statement,
! TEXTOID, -1, 0);
! TupleDescInitEntry(tupdesc, (AttrNumber) 3, prepare_time,
! TIMESTAMPTZOID, -1, 0);
! TupleDescInitEntry(tupdesc, (AttrNumber) 4, parameter_types,
! REGTYPEARRAYOID, -1, 0);
! TupleDescInitEntry(tupdesc, (AttrNumber) 5, from_sql,
! BOOLOID, -1, 0);
!
! funcctx-tuple_desc = BlessTupleDesc(tupdesc);
! MemoryContextSwitchTo(oldcontext);
! }
!
! /* stuff done on every call of the function */
! funcctx = SRF_PERCALL_SETUP();
! hash_seq =
Re: [PATCHES] Auto Partitioning Patch - WIP version 1
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- NikhilS wrote: Hi, Please find attached the WIP version 1 of the auto partitioning patch. There was discussion on this a while back on -hackers at: http://archives.postgresql.org/pgsql-hackers/2007-03/msg00375.php Please note that this patch tries to automate the activities that currently are carried out manually. It does nothing fancy beyond that for now. There were a lot of good suggestions, I have noted them down but for now I have tried to stick to the initial goal of automating existing steps for providing partitioning. Things that this patch does: i) Handle new syntax to provide partitioning: CREATE TABLE tabname ( ... ) PARTITION BY RANGE(ColId) | LIST(ColId) ( PARTITION partition_name CHECK(...), PARTITION partition_name CHECK(...) ... ); ii) Create master table. iii) Create children tables based on the number of partitions specified and make them inherit from the master table. The following things are TODOs: iv) Auto generate rules using the checks mentioned for the partitions, to handle INSERTs/DELETEs/UPDATEs to navigate them to the appropriate child. Note that checks specified directly on the master table will get inherited automatically. v) Based on the PRIMARY, UNIQUE information specified, pass it on to the children tables. vi) [stretch goal] Support HASH partitions Will try to complete the above mentioned TODOs as soon as is possible. Comments, feedback appreciated. Thanks and Regards, Nikhils -- EnterpriseDB http://www.enterprisedb.com [ 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Preliminary GSSAPI Patches
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Henry B. Hotz wrote: These patches have been reasonably tested (and cross-tested) on Solaris 9 (SPARC) and MacOS 10.4 (both G4 and Intel) with the native GSSAPI libraries. They implement the gss-np and (incompletely) the gss authentication methods. Unlike the current krb5 method gssapi has native support in Java and (with the SSPI) on Windows. I still have bugs in the security layer for the gss method. Hopefully will finish getting them ironed out today or tomorrow. Documentation is in the README.GSSAPI file. Make sure you get it created when you apply the patches. [ Attachment, skipping... ] The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- 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: [PATCHES] Re: [HACKERS] [COMMITTERS] pgsql: Add GUC temp_tablespaces to provide a default location for
I think we will have to wait for 8.4 for this. --- 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... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook -- 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] [GENERAL] dropping role w/dependent objects
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Ed L. wrote: 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 [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- 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] Reviewers Guide to Deferred Transactions/Transaction Guarantee
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Simon Riggs wrote: transaction_guarantee.v11.patch - keep current, cleanup, more comments and docs Brief Performance Analysis -- I've tested 3 scenarios: 1. normal 2. wal_writer_delay = 100ms 3. wal_writer_delay = 100ms and transaction_guarantee = off On my laptop, with a scale=1 pgbench database with 1 connection I consistently get around 85 tps in mode (1), with a slight performance drop in mode (2). In mode (3) I get anywhere from 200tps - 900 tps, depending upon how well cached everything is, with 700 tps being fairly typical. fsync = on gives around 900tps. Also good speedups with multiple session tests. make installcheck passes in 120 sec in mode (3), though 155 sec in mode (1) and 158 sec in mode (2). Basic Implementation xact.c xact.h The basic implementation simply records the LSN of the xlog commit record in a shared memory area, the deferred fsync cache. ipci.c The cache is protected by an LWlock called DeferredFsyncLock. lwlock.h A WALWriter process wakes up regularly to perform a background flush of WAL up to the point of the highest LSN in the deferred fsync cache. walwriter.c walwriter.h postmaster.c WALWriter can be enabled only at server start. (All above same as March 11 version) Correctness --- postgres.c Only certain code paths can execute transaction_guarantee = off transactions, though the main code paths for OLTP allow it. xlog.c CreateCheckpoint() must protect against starting a checkpoint when commits are not yet flushed, so an additional flush must occur here. vacuum.c VACUUM FULL cannot move tuples until their states are all known, so this command triggers a background flush also. clog.c clog.h slru.c slru.h Changes to Clog and SLRU enforce the basic rule of WAL-before-data, which otherwise might allow the record of a commit to reach disk before the flush of the WAL. This is implemented by storing an LSN for each clog page. transam.c transam.h twophase.c xact.c The above files have API changes that allow the LSN at transaction commit to be passed through to the Clog. tqual.c tqual.h multixact.c multixact.h Visibility hint bits must also not be set before the transaction is flushed, so other changes are required to ensure we store the LSN of each transaction, not just the maximum LSN. Changes to tqual.c appear extensive, though this is just refactoring to allow us to make additional function calls before setting bits - there are no functional changes to any HeapTupleSatisfies... functions. xact.c Contains the module for the Deferred Transaction functions and in particular the deferred transaction cache. This could be a separate module, since there is only a slight link with the other xact.c code. User Interface -- guc.c postgresql.conf.sample guc_table.h New parameters have been added, with a new parameter grouping of WAL_COMMITS created to control the various commit parameters. Performance Tuning -- The WALWriter wakes up each eal_writer_delay milliseconds. There are two protections against mis-setting this parameter. pmsignal.h The WALWriter will also be woken by a signal if the DF cache has nearly filled and flushing would be desirable. The WALWriter will also loop without any delay if the number of transactions committed while it was writing WAL is above a threshold value. Docs The fsync parameter has been removed from postgresql.conf.sample and the docs, though it still exists in this patch to allow performance testing during Beta. It is suggested that fsync=on should mean the same thing as transaction_guarantee = off, wal_writer_delay = 100ms, if it is specified in postgresql.conf or on the server command line. A new section in wal.sgml willd escribe this in more detail, later. Open Questions -- 1. Should the DFC use a standard hash table? Custom code allows both additional speed and the ability to signal when it fills. 2. Should tqual.c update the LSN of a heap page with the LSN of the transaction commit that it can read from the DF cache? 3. Should the WALWriter also do the wal_buffers half-full write at the start of XLogInsert() ? 4. The recent changes to remove CheckpointStartLock haven't changed the code path for deferred transactions, so a similar solution might be possible there also. 5. Is it correct to do WAL-before-flush for clog only, or should this be multixact also? All of the above are fairly minor changes. Any other thoughts/comments/tests welcome. -- Simon
Re: [PATCHES] [HACKERS] Full page writes improvement, code update
Josh, Josh Berkus wrote: Koichi, Andreas, 1) To deal with partial/inconsisitent write to the data file at crash recovery, we need full page writes at the first modification to pages after each checkpoint. It consumes much of WAL space. We need to find a way around this someday. Other DBs don't do this; it may be becuase they're less durable, or because they fixed the problem. Maybe both. Fixing the problem may need some means to detect partial/inconsistent writes to the data files, which may needs additional CPU resource. I don't think there should be only one setting. It depend on how database is operated. Leaving wal_add_optiomization_info = off default does not bring any change in WAL and archive log handling. I understand some people may not be happy with additional 3% or so increase in WAL size, especially people who dosn't need archive log at all. So I prefer to leave the default off. Except that, is there any reason to turn this off if we are archiving? Maybe it should just be slaved to archive_command ... if we're not using PITR, it's off, if we are, it's on. Hmm, this sounds to work. On the other hand, existing users, who are happy with the current archiving and would not like to change current archiving command to pg_compresslog or archive log size will increase a bit. I'd like to hear some more on this. 1) is there any throughput benefit for platforms with fast CPU but contrained I/O (e.g. 2-drive webservers)? Any penalty for servers with plentiful I/O? I've only run benchmarks with archive process running, because wal_add_optimization_info=on does not make sense if we don't archive WAL. In this situation, total I/O decreases because writes to archive log decreases. Because of 3% or so increase in WAL size, there will be increase in WAL write, but decrease in archive writes makes it up. Yeah, I was just looking for a way to make this a performance feature. I see now that it can't be. ;-) As to the performance feature, I tested the patch against 8.3HEAD. With pgbench, throughput was as follows: Case1. Archiver: cp command, wal_add_optimization_info = off, full_page_writes=on Case2. Archiver: pg_compresslog, wal_add_optimization_info = on, full_page_writes=on DB Size: 1.65GB, Total transaction:1,000,000 Throughput was: Case1: 632.69TPS Case2: 653.10TPS ... 3% gain. Archive Log Size: Case1: 1.92GB Case2: 0.57GB (about 30% of the Case1)... Before compression, the size was 1.92GB. Because this is based on the number of WAL segment file size, there will be at most 16MB error in the measurement. If we count this, the increase in WAL I/O will be less than 1%. 3) How is this better than command-line compression for log-shipping? e.g. why do we need it in the database? I don't fully understand what command-line compression means. Simon suggested that this patch can be used with log-shipping and I agree. If we compare compression with gzip or other general purpose compression, compression ratio, CPU usage and I/O by pg_compresslog are all quite better than those in gzip. OK, that answered my question. This is why I don't like Josh's suggested name of wal_compressable eighter. WAL is compressable eighter way, only pg_compresslog would need to be more complex if you don't turn off the full page optimization. I think a good name would tell that you are turning off an optimization. (thus my wal_fullpage_optimization on/off) Well, as a PG hacker I find the name wal_fullpage_optimization quite baffling and I think our general user base will find it even more so. Now that I have Koichi's explanation of the problem, I vote for simply slaving this to the PITR settings and not having a separate option at all. Could I have more specific suggestion on this? Regards; -- - Koichi Suzuki ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Reviewers Guide to Deferred Transactions/TransactionGuarantee
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Simon Riggs wrote: On Thu, 2007-04-05 at 22:56 +0100, Simon Riggs wrote: transaction_guarantee.v11.patch correct files attached Open Questions -- 1. Should the DFC use a standard hash table? Custom code allows both additional speed and the ability to signal when it fills. 2. Should tqual.c update the LSN of a heap page with the LSN of the transaction commit that it can read from the DF cache? I now think we should update the LSN of the page, but not changed yet. 3. Should the WALWriter also do the wal_buffers half-full write at the start of XLogInsert() ? Not that important 4. The recent changes to remove CheckpointStartLock haven't changed the code path for deferred transactions, so a similar solution might be possible there also. Some further discussion required here, I think. That change may actually have introduced a slight risk into the patch. Will raise at review. 5. Is it correct to do WAL-before-flush for clog only, or should this be multixact also? Not necessary -- Simon Riggs EnterpriseDB http://www.enterprisedb.com [ 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] Reviewers Guide to Deferred Transactions/TransactionGuarantee
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- ITAGAKI Takahiro wrote: Simon Riggs [EMAIL PROTECTED] wrote: transaction_guarantee.v11.patch correct files attached This is a small fix to transaction_guarantee patch. WAL writer needs PGSharedMemoryReAttach() on EXEC_BACKEND platforms. Other changes are only for suppressing warnings. We might also need to increase NUM_AUXILIARY_PROCS (=3) for WAL writer, but I didn't change it in the patch. (I don't know why the value is 3 -- bgwriter, autovacuum launcher and ... what?) BTW, the following TODO item comes to my mind: | Allow WAL traffic to be streamed to another server for stand-by replication We have to open sockets to another server when we want to stream WAL. If there were WAL writer, we can save the number of those sockets. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center [ Attachment, skipping... ] ---(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 -- 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 6: explain analyze is your friend
Re: [PATCHES] non-recursive WITH clause support
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Gregory Stark wrote: David Fetter [EMAIL PROTECTED] writes: On Mon, Apr 09, 2007 at 06:43:08PM -0400, Tom Lane wrote: So this does not really add any new functionality, it's just variant syntax for something you can do about as easily without it, right? Not totally as easily. For example, you can do some kinds of aggregation with a few fewer keystrokes. I think fewer keystrokes is exactly what Tom meant by a variant syntax without new functionality. That's an accurate description. I suppose it depends in part on how important we think it is to add variant syntaxes just because they're blessed by the ANSI standard. If this were a syntax we were creating just for our user's convenience it would be a pretty weak justification for an incompatibility. But if there are users who expect this syntax to work because it's standard then it could be considered an omission in our standards compliance. I'm actually not too sure what the answer is. I hadn't heard of it before the discussion about recursive queries myself. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- 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 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] CIC and deadlocks
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Pavan Deolasee wrote: On 4/11/07, Tom Lane [EMAIL PROTECTED] wrote: [ itch... ] The problem is with time-extended execution of GetSnapshotData; what happens if the other guy lost the CPU for a good long time while in the middle of GetSnapshotData? He might set his xmin based on info you saw as long gone. You might be correct that it's safe, but the argument would have to hinge on the OldestXmin process being unable to commit because of someone holding shared ProcArrayLock; a point you are definitely not making above. (Study the comments in GetSnapshotData for awhile, also those in xact.c's commit-related code.) My argument was based on what you said above, but I obviously did not state it well :) Anyways, I think its better to be safe and we agree that its not such a bad thing to take exclusive lock on procarray because CIC is not something that happens very often. Attached is a revised patch which takes exclusive lock on the procarray, rest remaining the same. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- 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] UPDATE using sub selects
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- NikhilS wrote: Hi, As per discussion on -hackers, a patch which allows updates to use subselects is attached with this mail. As per discussion with Tom, I have adopted the following approach: * Introduce ROWEXPR_SUBLINK type for subqueries that allows multiple column outputs. * Populate the targetList with PARAM_SUBLINK entries dependent on the subselects. * Modify the targets in-place into PARAM_EXEC entries in the make_subplan phase. The above does not require any kluges in the targetList processing code path at all. UPDATEs seem to work fine using subselects with this patch. I have modified the update.sql regression test to include possible variations . No documentation changes are present in this patch. Feedback, comments appreciated. Regards, Nikhils -- EnterpriseDB http://www.enterprisedb.com [ Attachment, skipping... ] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- 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] CREATE TABLE LIKE INCLUDING INDEXES support
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---
Trevor Hardcastle wrote:
NikhilS wrote:
Hi Trevor,
+
+ parent_index_info =
BuildIndexInfo(parent_index);
The above is not used anywhere else in the code and seems redundant.
Yep, pulled that out.
+
+ ereport(NOTICE,
+
(errmsg(Index \%s\ cloned.,
+
RelationGetRelationName(parent_index;
DefineIndex will give out a message anyways for unique/primary keys.
The above seems additional to it.
The original reason for this was the support for copying all indexes,
but it doesn't make much sense now. I've pulled it too.
Thanks for pointing those out. An updated patch is attached.
-Trevor Hardcastle
Index: src/backend/parser/analyze.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.362
diff -c -r1.362 analyze.c
*** src/backend/parser/analyze.c 13 Mar 2007 00:33:41 - 1.362
--- src/backend/parser/analyze.c 13 Apr 2007 16:41:46 -
***
*** 28,33
--- 28,34
#include postgres.h
#include access/heapam.h
+ #include access/genam.h
#include catalog/heap.h
#include catalog/index.h
#include catalog/namespace.h
***
*** 54,59
--- 55,61
#include utils/acl.h
#include utils/builtins.h
#include utils/lsyscache.h
+ #include utils/relcache.h
#include utils/syscache.h
***
*** 1331,1338
}
/*
! * Copy CHECK constraints if requested, being careful to adjust
! * attribute numbers
*/
if (including_constraints tupleDesc-constr)
{
--- 1333,1340
}
/*
! * Copy CHECK based constraints if requested, being careful to adjust
! * attribute numbers. Also duplicate unique index constraints.
*/
if (including_constraints tupleDesc-constr)
{
***
*** 1355,1360
--- 1357,1424
n-indexspace = NULL;
cxt-ckconstraints = lappend(cxt-ckconstraints, (Node
*) n);
}
+
+ /*
+ * Clone constraint indexes if any exist.
+ */
+ if (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)
+ {
+ Constraint *n = makeNode(Constraint);
+ AttrNumber parent_attno;
+
+ 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;
+ n-cooked_expr = NULL;
+
+ /*
+ * Search through the possible index
keys, and append
+ * the names of simple columns to the
new index key list.
+ */
+ for (parent_attno = 1; parent_attno =
parent_index-rd_att-natts;
+
Re: [PATCHES] HOT + MVCC-safe cluster conflict fix
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Heikki Linnakangas wrote: Hi Pavan, Here's a little patch against CVS HEAD + NewHOT-v7.0.patch to fix the conflict between MVCC-safe cluster and HOT. index_getnext is modified to return all tuples in a HOT chain when called with SnapshotAny. Cluster will insert them all as normal cold updates. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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 -- 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
[PATCHES] Re: actualised forgotten Magnus's patch for plpgsql MOVE statement
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Pavel Stehule wrote: Hello I refreshed Magnus's patch http://archives.postgresql.org/pgsql-patches/2007-02/msg00275.php from februar. Regards Pavel Stehule p.s. scrollable cursors in plpgsql need little work still. I forgot for nonstandard (postgresql extension) direction forward all, forward n, backward n. Forward all propably hasn't sense. _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ [ Attachment, skipping... ] -- 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 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] autovacuum does not start in HEAD
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- ITAGAKI Takahiro wrote: I wrote: I found that autovacuum launcher does not launch any workers in HEAD. The attached autovacuum-fix.patch could fix the problem. I changed to use 'greater or equal' instead of 'greater' at the decision of next autovacuum target. The point was in the resolution of timer; There is a platform that timer has only a resolution of milliseconds. We initialize adl_next_worker with current_time in rebuild_database_list(), but we could use again the same value in do_start_worker(), because there is no measurable difference in those low-resolution-platforms. Another attached patch, autovacuum-debug.patch, is just for printf-debug. I got the following logs without fix -- autovacuum never works. # SELECT oid, datname FROM pg_database ORDER BY oid; oid | datname ---+--- 1 | template1 11494 | template0 11495 | postgres 16384 | bench (4 rows) # pgbench bench -s1 -c1 -t10 [with configurations of autovacuum_naptime = 10s and log_min_messages = debug1] LOG: do_start_worker skip : 230863399.25, 230863399.25, 230863409.25 LOG: rebuild_database_list: db=11495, time=230863404.25 LOG: rebuild_database_list: db=16384, time=230863409.25 DEBUG: autovacuum: processing database bench LOG: do_start_worker skip : 230863404.25, 230863404.25, 230863414.25 LOG: do_start_worker skip : 230863404.25, 230863409.25, 230863414.25 LOG: rebuild_database_list: db=11495, time=230863409.25 LOG: rebuild_database_list: db=16384, time=230863414.25 LOG: do_start_worker skip : 230863409.25, 230863409.25, 230863419.25 LOG: do_start_worker skip : 230863409.25, 230863414.25, 230863419.25 LOG: rebuild_database_list: db=11495, time=230863414.25 LOG: rebuild_database_list: db=16384, time=230863419.25 ... (no autovacuum activities forever) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center [ Attachment, skipping... ] [ 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 6: explain analyze is your friend
Re: [PATCHES] updated SORT/LIMIT patch
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Gregory Stark wrote: Updated patch against cvs update in case it makes applying easier. One minor change: . Added #include limits.h in tuplesort.h to pull in UINT_MAX (thanks to dpage for noticing this is necessary on OSX) [ Attachment, skipping... ] -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- 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
