[HACKERS] system info functions
(1) The docs claim that pg_get_viewdef() returns the "CREATE VIEW
command for view", but that is clearly not the case:
postgres=# create view v1 as select 1;
CREATE VIEW
postgres=# select pg_get_viewdef('v1'::regclass::oid);
pg_get_viewdef
SELECT 1;
(1 row)
Should we change the documentation, or the implementation of
pg_get_viewdef()?
(2) pg_get_indexdef() and pg_get_triggerdef() don't include a
terminating semi-colon, but pg_get_ruledef() does (as does the SELECT
statement returned by pg_get_viewdef()). Is there a good reason for this
inconsistency?
-Neil
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[HACKERS] binds only for s,u,i,d?
Why are only select, insert, update, and delete supported for $X binds? Why can't preparation be used as a global anti-injection facility? Example using the backend protocol for binds: PREPARE TRANSACTION $1; bind $1 ['text'] -->syntax error at $1 Why am I able to prepare statements with the backend protocol that I can't prepare with PREPARE: agentm=# prepare gonk as prepare transaction $1; ERROR: syntax error at or near "prepare" at character 17 LINE 1: prepare gonk as prepare transaction $1; whereas the backend protocol only emits an error when the statement is executed [and the binds are ignored]. -M ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Transaction and table partitioning
"Dragan Zubac" <[EMAIL PROTECTED]> writes: > Hello > > Is it possible to use transactions with 'per sub table' locks? What I > mean,if I partition a table and use transaction on that table with > constraint,will the database lock the master table (and all subtables),or > only sub table where the data is ? Unless you're doing ALTER TABLE or something like that Postgres never locks tables. Two backends can insert into the same table at the same time and neither has to wait until the other is done. If you are updating the same record then of course one has to wait but then partitioning isn't going to help in that case. -- greg ---(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: [HACKERS] mysterious nbtree.c comment
Greg Stark <[EMAIL PROTECTED]> writes: > I don't see how the lack of deletions is relevant to needing vacuum-cycle-ID. > AFAICT there's still a risk that someone will come along and do a page split > underneath this scan and if the page is to the left of the scan it will be > missed. Well, if there are active insertions or deletions happening in parallel with the scan, the tuple count is going to be at best approximate anyway, no? So there's no need to be tense about ensuring we visit every single index tuple. We do want to hit all the pages so we can clean up any recyclable pages, but that's not a problem. 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
[HACKERS] buildfarm stats
Sometime in late June the buildfarm passed 50,000 builds reported on. Here are stats over the lifetime. cheers andrew ym| builds | reporting_members ++--- 2004-10 |181 | 6 2004-11 | 1533 |12 2004-12 | 2468 |27 2005-01 | 2432 |30 2005-02 | 1367 |25 2005-03 | 1970 |28 2005-04 | 2297 |28 2005-05 | 2348 |28 2005-06 | 2543 |34 2005-07 | 3038 |43 2005-08 | 2748 |41 2005-09 | 2104 |36 2005-10 | 2597 |36 2005-11 | 2214 |35 2005-12 | 2534 |38 2006-01 | 3338 |41 2006-02 | 2843 |40 2006-03 | 2536 |43 2006-04 | 2978 |45 2006-05 | 2816 |45 2006-06 | 3338 |50 ---(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: [HACKERS] mysterious nbtree.c comment
On Mon, 2006-07-03 at 16:34 -0400, Greg Stark wrote: > In nbtree.c there's a path that calls btvacuumscan to gather statistics if > there aren't already statistics. I'm not exactly clear how this code path is > reached but that's not my question. VACUUM calls access/index/index_vacuum_cleanup() which is part of the btree index access method API. In the case of a btree index this is a function pointer to access/nbtree/btvacuumcleanup() That is important to your question. > There's a comment that "there's no need to > go through all the vacuum-cycle-ID pushups" in this case because no deletions > are being performed. > > I don't see how the lack of deletions is relevant to needing vacuum-cycle-ID. > AFAICT there's still a risk that someone will come along and do a page split > underneath this scan and if the page is to the left of the scan it will be > missed. Read the comments in btvacuumscan. It is only important to scan all the pages of an index when deleting leaf items. The btvacuumscan called from btvacuumcleanup only gets called when stats are NULL. That only happens when the VACUUM returns no rows for cleanup, so the scan need only perform one of its three functions: remove pages already marked as deleted that can now be recycled into the FSM. You're right - it will be missed but its not crucial to the scan when called in that way since we'll pick it up next time around. -- Simon Riggs 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
[HACKERS] mysterious nbtree.c comment
In nbtree.c there's a path that calls btvacuumscan to gather statistics if
there aren't already statistics. I'm not exactly clear how this code path is
reached but that's not my question. There's a comment that "there's no need to
go through all the vacuum-cycle-ID pushups" in this case because no deletions
are being performed.
I don't see how the lack of deletions is relevant to needing vacuum-cycle-ID.
AFAICT there's still a risk that someone will come along and do a page split
underneath this scan and if the page is to the left of the scan it will be
missed.
Datum
btvacuumcleanup(PG_FUNCTION_ARGS)
{
IndexVacuumInfo *info = (IndexVacuumInfo *) PG_GETARG_POINTER(0);
IndexBulkDeleteResult *stats = (IndexBulkDeleteResult *)
PG_GETARG_POINTER(1);
/*
* If btbulkdelete was called, we need not do anything, just return
* the stats from the latest btbulkdelete call. If it wasn't called,
* we must still do a pass over the index, to recycle any
newly-recyclable
* pages and to obtain index statistics.
*
* Since we aren't going to actually delete any leaf items, there's no
* need to go through all the vacuum-cycle-ID pushups.
*/
if (stats == NULL)
{
stats = (IndexBulkDeleteResult *)
palloc0(sizeof(IndexBulkDeleteResult));
btvacuumscan(info, stats, NULL, NULL, 0);
}
--
greg
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Transaction and table partitioning
On Mon, Jul 03, 2006 at 14:59:49 +0200, Dragan Zubac <[EMAIL PROTECTED]> wrote: > > Is it possible to use transactions with 'per sub table' locks? What I > mean,if I partition a table and use transaction on that table with > constraint,will the database lock the master table (and all subtables),or > only sub table where the data is ? Are you sure locking will be a problem? MVCC avoids a lot of typical conflicts. If you are doing something that is locking the tables in a way that causes problems, I think you will need to be more specific about what you are doing to get useful suggestions. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Transaction and table partitioning
On Mon, 2006-07-03 at 14:59 +0200, Dragan Zubac wrote: > Is it possible to use transactions with 'per sub table' locks? What I > mean,if I partition a table and use transaction on that table with > constraint,will the database lock the master table (and all > subtables),or only sub table where the data is ? If you reference only the sub-table then there will be no locks on the master or other partition tables, assuming you mean to use just DML rather than DDL. The tables are more loosely coupled than they are in other RDBMS implementations. -- Simon Riggs 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
[HACKERS] Transaction and table partitioning
Hello Is it possible to use transactions with 'per sub table' locks? What I mean,if I partition a table and use transaction on that table with constraint,will the database lock the master table (and all subtables),or only sub table where the data is ? Sincerely Dragan Zubac
Re: [HACKERS] Auto selection of internal representation for integer NUMERIC
ITAGAKI Takahiro <[EMAIL PROTECTED]> writes: > In particular, this is useful for oracle users. Oracle recommends to use > NUMBER(n, p) for all the case where numerics are required. So they try to > use NUMERIC on PostgreSQL instead of NUMBER. But NUMERIC is not the best > alternative to a short integer NUMBER. I think the correct answer to that is user education. Anything along the lines you are suggesting would be convoluted and would probably introduce unexpected behaviors (eg, overflow of intermediate results that wouldn't have overflowed if the data was really NUMERIC). 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: [HACKERS] CVS mirror, was Re: [PATCHES] ADD/DROPS INHERIT (actually INHERIT
19 minutes past the hour, every hour ... On Mon, 3 Jul 2006, Andrew Dunstan wrote: [redirecting to -hackers] context: a buildfarm member apparently failed through getting a partial update from CVS, possibly because the anonymous mirror was also partially updated. Tom Lane wrote: "Andrew Dunstan" <[EMAIL PROTECTED]> writes: Since CVS updates are not atomic, it's hard to see how mirroring could be, unless you did something like disallow updates, mirror, allow updates. I suspect such a cure would be worse than the disease. This is such a rare event that I don't think it's worth the trouble. Buildfarm members are doing 200 builds a day or more, and I can't recall having seen this before. Yeah, I don't remember having seen it before either, but on the other hand I haven't been paying super close attention. One easy low-tech fix would be for Marc to publish the exact times at which the mirror syncs run (I think it might be something like 20 past the hour but I'm not sure), and then we could tell buildfarm owners not to schedule their CVS pulls to start in that particular five-minute window, and committers could try to avoid committing many-file patches right then either. Yuck. I think if it gets that far we would have discovered a compelling reason to abandon CVS, as many bystanders have urged us to do. But I think we can live with an occasional hiccup. cheers andrew Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] MultiXactID Wrap-Around
paolo romano <[EMAIL PROTECTED]> writes: > My doubts now concern MultixactID wrap-around management. > Afaics, it is possible to spawn multixactids so quickly to have a > wrap-around and to start overwriting the data stored in the offset > slru (but analogous considerations apply to the member slru as > well). I looked into this when the multixact code was written. There is a theoretical risk but I think it's entirely theoretical. MXIDs are unlikely to be consumed faster than XIDs over the long term, and also can be recycled sooner. So you'd run up against XID wraparound (which we do defend against) first. 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
[HACKERS] CVS mirror, was Re: [PATCHES] ADD/DROPS INHERIT (actually INHERIT / NO INHERIT)
[redirecting to -hackers] context: a buildfarm member apparently failed through getting a partial update from CVS, possibly because the anonymous mirror was also partially updated. Tom Lane wrote: "Andrew Dunstan" <[EMAIL PROTECTED]> writes: Since CVS updates are not atomic, it's hard to see how mirroring could be, unless you did something like disallow updates, mirror, allow updates. I suspect such a cure would be worse than the disease. This is such a rare event that I don't think it's worth the trouble. Buildfarm members are doing 200 builds a day or more, and I can't recall having seen this before. Yeah, I don't remember having seen it before either, but on the other hand I haven't been paying super close attention. One easy low-tech fix would be for Marc to publish the exact times at which the mirror syncs run (I think it might be something like 20 past the hour but I'm not sure), and then we could tell buildfarm owners not to schedule their CVS pulls to start in that particular five-minute window, and committers could try to avoid committing many-file patches right then either. Yuck. I think if it gets that far we would have discovered a compelling reason to abandon CVS, as many bystanders have urged us to do. But I think we can live with an occasional hiccup. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] MultiXactID Wrap-Around
paolo romano wrote: ops, i did forget to update the e-mail subject, sorry. I am reposting it with an appropriate one. Please do NOT create a post on a new subject by using an MUA's reply mechanism, even if you replace the subject. The MUA will create an in-reply-to header which will be totally inappropriate and confuse other MUAs and achive processors that use such headers for thread construction. Only use a reply facility when you are genuinely replying on the same subject. This goes for webmail MUAs too (yahoo, squirrelmail etc.) The right way to get the address is to put it in your address book or as a last resort cut and paste it. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] MultiXactID Wrap-Around
ops, i did forget to update the e-mail subject, sorry. I am reposting it with an appropriate one. I'm keeping on studying multixact.c and log management, and I hope you can help me, as usual, in clearing my doubts.My doubts now concern MultixactID wrap-around management. Afaics, it is possible to spawn multixactids so quickly to have a wrap-around and to start overwriting the data stored in the offset slru (but analogous considerations apply to the member slru as well). This would cause corruption, if the overwritten info was still needed, e.g., by a (very) long-running transaction. This is of course very unlikely in practice, but yet still possible in theory.In GetNewMultiXactId() wrap-around of MultiXactId seems to be simply handled this way: 00780 _/* Handle wraparound of the nextMXact counter */00781 if (MultiXactState->nextMXact < FirstMultiXactId)00782 MultiXactState->nextMXact = FirstMultiXactId; I cannot see how this may avoid possible overwriting of still needed data. To address such an issue shouldn't one need to check against OldestMemberMXactId, OldestVisibleMXactId? Or, alternatively, rely on an approach similar to the one taken to handle standard XID generation (xidWarnLimit, see GetNewTransactionId)?Is it me who's missing something or is it just that such a case has been considered so unlikely not to motivate additional overheads/checks?Thanks in advance! Paolo Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
[HACKERS] update/insert, delete/insert efficiency WRT vacuum and MVCC
Is there a difference in PostgreSQL performance between these two
different strategies:
if(!exec("update foo set bar='blahblah' where name = 'xx'"))
exec("insert into foo(name, bar) values('xx','blahblah'");
or
exec("delete from foo where name = 'xx'");
exec("insert into foo(name, bar) values('xx','blahblah'");
In my session handler code I can do either, but am curious if it makes any
difference. Yes, "name" is unique.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Do a pass of code review for the ALTER TABLE
I'm keeping on studying multixact.c and log management, and I hope you can help me, as usual, in clearing my doubts.My doubts now concern MultixactID wrap-around management. Afaics, it is possible to spawn multixactids so quickly to have a wrap-around and to start overwriting the data stored in the offset slru (but analogous considerations apply to the member slru as well). This would cause corruption, if the overwritten info was still needed, e.g., by a (very) long-running transaction. This is of course very unlikely in practice, but yet still possible in theory.In GetNewMultiXactId() wrap-around of MultiXactId seems to be simply handled this way: 00780 _/* Handle wraparound of the nextMXact counter */00781 if (MultiXactState->nextMXact < FirstMultiXactId)00782 MultiXactState->nextMXact = FirstMultiXactId; I cannot see how this may avoid possible overwriting of still needed data. To address such an issue shouldn't one need to check against OldestMemberMXactId, OldestVisibleMXactId? Or, alternatively, rely on an approach similar to the one taken to handle standard XID generation (xidWarnLimit, see GetNewTransactionId)?Is it me who's missing something or is it just that such a case has been considered so unlikely not to motivate additional overheads/checks?Thanks in advance! Paolo Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
Re: [HACKERS] patch postgresql for AMD64 (Opteron)
Hi Tom, I remember that you provide a small SQL script to force the context switch storm. Can you provide a similar script for Pg 8.1.4? It looks to me that you get context switch storm if you access with SELECT one table from multiple clients. I have a customer which has an current XEON MP DualCore and we get 15+ context switches/sec. We have around 30 clients. We use RHEL 4 in 32-bit (i368) mode. I didn't use the patch for the Opteron-specific behavior. Cheers Sven. Tom Lane schrieb: > Sven Geisler <[EMAIL PROTECTED]> writes: >> I created a patch for PostgreSQL and x86 architecture. >> This patch address a Opteron-specific >> behavior regarding some assembler statements. > > AFAICT this patch essentially proposes that we should allow the single > case of an Opteron running in 32-bit mode to determine our optimization > strategy for all 32-bit Intel. Tail wagging dog, no? > > As the comment notes, it's not real clear that the separate cmpb is a > win on average, but this case is not the average case I'm interested in. > If you want to make an argument for removing the cmpb you need to > provide numbers on mainstream 32-bit platforms. > > regards, tom lane -- /This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient, you should not copy it, re-transmit it, use it or disclose its contents, but should return it to the sender immediately and delete your copy from your system. Thank you for your cooperation./ Sven Geisler <[EMAIL PROTECTED]> Tel +49.30.5362.1627 Fax .1638 Senior Developer,AEC/communications GmbHBerlin, Germany ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] odd 7.4 build failure on new sparc machine
Tom Lane said: > Andrew Dunstan <[EMAIL PROTECTED]> writes: >> I am seeing a strange failure on the new box Sun donated, when trying >> to > >> ccache gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes >> -Wmissing-declarations -c tas.s /usr/ccs/bin/ld -r -o SUBSYS.o >> dynloader.o pg_sema.o pg_shmem.o tas.o ld: fatal: relocation error: >> R_SPARC_32: file tas.o: symbol : offset 0xec1 is non-aligned >> [etc] > >> What is odd is that the identical file seems to succeeed on the later >> 8.0 and 8.1 branches. > > The solaris_sparc.s file seems identical in these branches up to CVS > label ... but are the compilation options the same? The critical fix > might be somewhere in the configure/Makefile chain. Yes - see later email where I concluded that. > > Another thing to try is whether it works without ccache. We've seen > plenty of trouble from that tool :-( I am still waiting to see a smoking gun on it. So far there has been some smoke but no gun or fire (sorry for mixed metaphor). What I am thinking of doing is having buildfarm blow away the cache on failure, so that ccache would be forced to recompile fropm scratch unless the last case was a success. Thoughts? >> Why do we have "mov 1,%o0" immediately followed by "mov 0,%o0"? > > Better read up on branch delay slots... > Yes, ok, I understand. I must have forgotten that one had to write the assembler in that non-linear fashion to get the benefit of saving an instruction cycle. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Auto selection of internal representation for integer NUMERIC
Hi Hackers, I think about mapping NUMERIC(n) to fixed size integers corresponding to n: - n < 4 to int2 - 5 <= n < 9 to int4 - 10 <= n < 18 to int8 - 19 <= n to original numeric NUMERIC is not so efficient datatypes compared to fixed size integers. If the auto selection is avaliable, users don't have to care about integer types; they can always use NUMERICs. In particular, this is useful for oracle users. Oracle recommends to use NUMBER(n, p) for all the case where numerics are required. So they try to use NUMERIC on PostgreSQL instead of NUMBER. But NUMERIC is not the best alternative to a short integer NUMBER. Is this worth trying and acceptable? This thought is not wanted if users choise int2/4/8 carefully -- but it is rare case from my experience :-( In addition, treating NaN value is an issue. NaN is supported on NUMERIC, but there are complexities to do the same on fixed size integers. Comments and suggestions are welcome. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
