[HACKERS] max_locks_per_transactions ...
Right now max_locks_per_transactions defines the average number of locks taken by a transaction. thus, shared memory is limited to max_locks_per_transaction * (max_connections + max_prepared_transactions). this is basically perfect. however, recently we have seen a couple of people having trouble with this. partitioned tables are becoming more and more popular so it is very likely that a single transaction can eat up a great deal of shared memory. some people having a lot of data create daily tables. if done for 3 years we already lost 1000 locks per inheritance-structure. i wonder if it would make sense to split max_locks_per_transaction into two variables: max_locks (global size) and max_transaction_locks (local size). if set properly this would prevent good short running transactions from running out of shared memory when some evil long running transactions start to suck up shared memory. if people find this useful we would glady implement this new feature for 8.3. many thanks, hans -- Cybertec Geschwinde Schönig GmbH Schöngrabern 134; A-2020 Hollabrunn Tel: +43/1/205 10 35 / 340 www.postgresql.at, www.cybertec.at ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] May, can, might
I have made these adjustments to the documentation. Do people want the error message strings also updated? It will probably make the translation easier/clearer in the future, but it does involve some error message wording churn. CVS HEAD only, of course. I think most translations will have the intended meaning translated correctly. So I think we can leave the translations unchanged in most cases, and only change the english original. Maybe this can be automated ? But since it only seems to be very few this might not be necessary. (e.g. I only see 1 wrong may in psql) Andreas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PL/pgSQL RENAME functionality in TODOs
On 2/1/07, Tom Lane [EMAIL PROTECTED] wrote: imad [EMAIL PROTECTED] writes: OK, so renaming does not work in the same block. You can rename a vairable in a nested block and thats why it works for OLD/NEW. BTW, what is the purpose behind it? Declaring a variable in a block and quickly renaming it does not make sense to me. I agree it's pretty useless; but if we're gonna forbid it then we should throw a more sensible error than syntax error. Actually, it seems to me that it works in the nested-block case only for rather small values of work: regression=# create function foo() returns int as $$ regression$# declare regression$# x int := 1; regression$# begin regression$# x := 2; regression$# declare regression$# rename x to y; regression$# begin regression$#y := 3; regression$# end; regression$# return x; regression$# end$$ language plpgsql; CREATE FUNCTION regression=# select foo(); ERROR: column x does not exist LINE 1: SELECT x ^ QUERY: SELECT x CONTEXT: PL/pgSQL function foo line 10 at return regression=# Surely the variable's name should be x again after we're out of the nested block? Yes, seems to be the only possible reason of renaming a variable. --Imad www.EnterpriseDB.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] fixing Makefile.shlib for solaris/gcc with -m64 flag
I dont think we solved this.. But I think the way to put -m64 should be same as in Linux and Solaris and not different. Thanks. Regards, Jignesh Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Am Mittwoch, 17. Januar 2007 17:12 schrieb Tom Lane: Jignesh K. Shah [EMAIL PROTECTED] writes: simple if I use -m64 for 64 bit then all end binaries are generated 64-bit and the shared libraries are generated 32-bit and the compilation fails (ONLY ON SOLARIS) since that particular line is only for the condition Solaris AND gcc. If I use the COMPILER which is CC + CFLAGS it passes -m64 properly to it and generates shared libraries 64-bit and the compile continues.. Hmm ... I see we're doing it that way already for some other platforms, but I can't help thinking it's a kluge. Wouldn't the correct answer be that -m64 needs to be in LDFLAGS? The correct answer may be to put -m64 into CC. Did we conclude that that was a satisfactory solution, or is this still a live patch proposal? If -m64 in CC is the right solution, it should probably be mentioned in FAQ_Solaris. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Estimation error in n_dead_tuples
AFAICS, the statistics information the number of dead tuples (n_dead_tuples) has an estimation error. VACUUM sends a message to stats collector process when it has swept a table. The stats collector receives the message and sets n_dead_tuples of the table to zero. However, we can update or delete tuples even if a concurrent vacuum is running through the table. There might be some dead tuples that were created after start of the vacuum. Therefore, it's not always correct to set n_dead_tuples to zero at the end of vacuum. Especially, the error will be worse when a vacuum takes long time. The autovacuum sees the stats information to decide when to vacuum. The error in n_dead_tuples misleads it and vacuum starvation might occur because we always underestimate the number of dead tuples. I'm thinking to add the number of vacuumed tuples to the message from vacuum. The stats collector will subtract the value from n_dead_tuples instead of setting it to zero. This is also needed if we want to make some kinds of partial vacuum methods. Thoughts? Is this worth doing, I'd like to implement it. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(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] PL/pgSQL RENAME functionality in TODOs
Hello, std. use rename only for triggers and variables new and old. It has sense. I don't see sense for rename in clasic plpgsql functions. There was one reason, rename unnamed $params. But currently plpgsql support named params and this reason is obsolete. Regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(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] Data archiving/warehousing idea
On 2/1/07, Chris Dunlop wrote:
In maillist.postgres.dev, you wrote:
On Thu, 1 Feb 2007, Chris Dunlop wrote:
The main idea is that, there might be space utilisation and
performance advantages if postgres had hard read-only
tables, i.e. tables which were guaranteed (by postgres) to
never have their data changed (insert/update/delete).
This could potentially save disk space by allowing book
keeping elements in the page/tuple headers to be removed,
e.g. visibility information etc. Also, some indexes could
potentially be packed tighter if we know the data will never
change (of course this is already available using the
fillfactor control).
Well, there is also CPU overhead doing MVCC but there are a
few fundamental problems that must be overcome. The most
significant is that no useful table is always read only,
otherwise you could never load it.
Yes, that's why I was proposing ALTER TABLE... SET ARCHIVE
rather than CREATE TABLE... ARCHIVE. (Although, for
consistency, perhaps the CREATE TABLE would be allowed, it's
just that you couldn't load anything into it until you did a
ALTER TABLE... DROP ARCHIVE.)
If you want to squeeze the most out of it, CLUSTER would need to be
able to set the archive option too. (Otherwise you first rewrite the
table when you cluster it, and then rewrite it again when you archive
it.)
Rather than writing in-place, perhaps the SET ARCHIVE would
create a on-disk copy of the table.
Just like CLUSTER does now: create an on-disk copy first and swap the
relfilenodes of the files and flush the relcache.
Of course this would demand
you have twice the disk space available which may be prohibitive
in a large warehouse. On the other hand, I'm not sure if you
would have a single humongous table that you'd SET ARCHIVE on,
you might be as likely to archive on a weekly or yearly or
whatever is manageable basis, along the lines of:
begin;
select * into foo_2006 from foo
where date_trunc('year', timestamp) = '2006-01-01';
delete from foo
where date_trunc('year', timestamp) = '2006-01-01';
alter table foo_2006 set archive;
alter table foo_2006 inherit foo;
commit;
Ideally you let most of it run outside a transaction:
create table foo_2006 ();
insert into foo_2006 SELECT * from foo where ;
cluster foo_2006 on ... ARCHIVE;
begin;
delete from foo where PK in select PK from foo_2006;
alter table foo_2006 inherit foo;
commit;
You're talking about the no-WAL concept? Not quite the same
thing I think, but perhaps complimentary to the ARCHIVE idea: I
wouldn't expect an ARCHIVE table to need to generate any WAL
entries as it would be read only.
The WAL gains come automatically when data isn't changed. But there
are additional advantages that can be obtained with archive tables:
- no need to vacuum them, not even for XID rollover (but you do need
to vacuum their entries in the catalogs)
- index entries are always valid so no need to check the heap (unless
the index is lossy)
- you can force the fillfactor to 100% regardless of the setting
- more agressive compression of toast tables
Jochem
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] May, can, might
Bruce Momjian schrieb: I have made these adjustments to the documentation. Do people want the error message strings also updated? It will probably make the translation easier/clearer in the future, but it does involve some error message wording churn. CVS HEAD only, of course. I still think logging localized error message is a bad idea anayway. Nothing wrong with a frontend client to respond with localized messages but logfiles with localized errors are hard or next to impossible to parse. (Let allone quoting it on mailing lists) So, changes of the wording could break such applications anyway but not unexpected :-) Regards Tino ---(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] pg_restore fails with a custom backup file
Still sitting on my TODO. I have a working solution for MSVC, but it didn't run on MingW. Andreas had a working solution on his MingW, but it didn't work on my MingW. I need to merge them together for something that works on all three. I hope to have this done for 8.3, and possibly a 8.2.x, but will most likely no thave time to do it before the next 8.2.x to come out. But perhaps the one after that. //Magnus On Wed, Jan 31, 2007 at 11:41:09PM -0500, Bruce Momjian wrote: Where are we on this? --- Magnus Hagander wrote: On Tue, Dec 19, 2006 at 04:58:22PM +0100, Zeugswetter Andreas ADI SD wrote: MinGW has fseeko64 and ftello64 with off64_t. Maybe we need separate macros for MSVC and MinGW. Given the other You mean something quick and dirty like this ? That would work. Yes, except does that actually work? If so you found the place in the headers to stick it without breaking things that I couldn't find ;-) Compiles clean without warnings on MinGW, but not tested, sorry also no time. Does not compile on my MinGW - errors in the system headers (unistd.h, io.h) due to changing the argument format for chsize(). The change of off_t propagated into parts of the system headers, thus chaos was ensured. I still think we need to use a pgoff_t. Will look at combining these two approaches. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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: [HACKERS] Data archiving/warehousing idea
On Thu, 2007-02-01 at 15:03 +1100, Chris Dunlop wrote: A different approach discussed earlier involves greatly restricting the way in which the table is used. This table could only be written to if an exclusive lock is held; on error or ABORT, the table is truncated. You're talking about the no-WAL concept? Not quite the same thing I think, but perhaps complimentary to the ARCHIVE idea: I wouldn't expect an ARCHIVE table to need to generate any WAL entries as it would be read only. Setting an option to reduce the size of the row headers needs to be done before its loaded, not after. If you mark a table as APPEND-only it would be possible to save 4 bytes off the row header. Saving a further 8-bytes off the row header by dropping the xmin and commandid fields isn't a very workable solution since those fields provide visibility within a transaction and across transactions. You'd end up with a table that could only be loaded by one transaction and only touched by one command within that transaction. That would require bookkeeping on the relation itself (in pg_class) and an AccessExclusiveLock. In those circumstances you would be able to save on writing WAL as well. You'd be basically saying that it could only be loaded by a special utility and would be read-only once loaded. I'm not crazy about those ideas, even though I think I suggested them some time back. They're very special case and would probably require many functions to handle multiple cases, so that additional complexity and cost would effect all users. My main doubt relates to the data you're storing. If the events you're interested in have so little information associated with them that they are a thin table then storing them at all is questionable. I've been involved at the design stage of a number of data warehouses and the amount of data eventually stored is typically 10% of the number people first thought of, with number of rows decreasing drastically and the number of columns increasing slightly as people try to gain additional value from their data storage. So row overhead should be less of a concern. It is possible to save on WAL by doing COPY LOCK or using a table with different resilience characteristics, both ideas are already on the TODO. There are also better, less intrusive ways of reducing data volume and improving load performance. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] max_locks_per_transactions ...
On Thu, 2007-02-01 at 09:15 +0100, Hans-Juergen Schoenig wrote: Right now max_locks_per_transactions defines the average number of locks taken by a transaction. thus, shared memory is limited to max_locks_per_transaction * (max_connections + max_prepared_transactions). this is basically perfect. however, recently we have seen a couple of people having trouble with this. partitioned tables are becoming more and more popular so it is very likely that a single transaction can eat up a great deal of shared memory. some people having a lot of data create daily tables. if done for 3 years we already lost 1000 locks per inheritance-structure. i wonder if it would make sense to split max_locks_per_transaction into two variables: max_locks (global size) and max_transaction_locks (local size). if set properly this would prevent good short running transactions from running out of shared memory when some evil long running transactions start to suck up shared memory. Do partitioned tables use a lock even when they are removed from the plan as a result of constraint_exclusion? I thought not. So you have lots of concurrent multi-partition scans. I'm not sure I understand your suggestion. It sounds like you want to limit the number of locks an individual backend can take, which simply makes the partitioned queries fail, no? Perhaps we should just set the default higher? -- 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
Re: [HACKERS] stack usage in toast_insert_or_update()
On 1/31/07, Tom Lane [EMAIL PROTECTED] wrote: We can't change TOAST_MAX_CHUNK_SIZE without forcing an initdb, but I think that it would be safe to remove the MAXALIGN'ing of the tuple size in the tests in heapam.c, that is That would mean that the tuple size in the heap may exceed TOAST_TUPLE_THRESHOLD which should be OK, but we may want to have a comment explaining that. We should do the same for heap_update() as well. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Improving NOT IN
On Tue, 2007-01-30 at 17:34 -0500, Tom Lane wrote: I think the NOT IN optimization that *would* be of use is to automatically transform the NOT IN representation to an outer-join-with-null-test type of operation, so as to give us a wider choice of join methods. However, I'm not sure about correct handling of NULLs on the RHS in such a scenario. The existing hashed-IN code has to jump through some really ugly hoops to give spec-compliant answers with NULLs. ISTM that we can handle this neatly by looking for a WHERE clause that specifically excludes NULLs in the NOT IN. i.e. a query of the form select ... from LHS where key NOT IN (select key from RHS where key is not null) can be optimised to select ... from LHS left outer join RHS on LHS.key = RHS.key where RHS.key IS NULL; This rewards people that understand the spec-compliant behaviour and ensure there coding is watertight in the presence of NULLs. We can extend that behaviour later when we have plan invalidation to make it also pick up NOT NULL constraints on the keys of the RHS table. Doing it this way round is much more useful, since not all tables have NOT NULL constraints on their join columns so is slightly wider in its usefulness than just checking constraints. It's also very annoying in this specific case to not have any way for the SQL developer to pass information to the planner - and I don't mean hints. This would be similar to pull_up_IN_clauses() -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
A more general approach (Re: [HACKERS] Data archiving/warehousing idea)
Ühel kenal päeval, N, 2007-02-01 kell 13:24, kirjutas Gavin Sherry: A different approach discussed earlier involves greatly restricting the way in which the table is used. This table could only be written to if an exclusive lock is held; on error or ABORT, the table is truncated. The problem is that a lot of this looks like a hack and I haven't seen a very clean approach which has gone beyond basic brain dump. A more radical variation of the restricted-use archive table approach is storing all tuple visibility info in a separate file. At first it seems to just add overhead, but for lots (most ? ) usecases the separately stored visibility should be highly compressible, so for example for bulk-loaded tables you could end up with one bit per page saying that all tuples on this page are visible. Also this could be used to speed up vacuums, as only the visibility table needs to be scanned duting phase 1 of vacuum, and so tables with localised/moving hotspots can be vacuumed withoutd scanning lots of static data. Also, storing the whole visibility info, but in a separate heap, lifts all restrictions of the restricted-use archive table variant. And the compression of visibility info (mostly replacing per-tuple info with per-page info) can be carried out by a separate vacuum-like process. And it has many of the benefits of static/RO tables, like space saving and index-only queries. Index-only will of course need to get the visibility info from visibility heap, but if it is mostly heavily compressed, it will be a lot cheaper than random access to data heap. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: A more general approach (Re: [HACKERS] Data archiving/warehousing idea)
Ühel kenal päeval, N, 2007-02-01 kell 14:38, kirjutas Hannu Krosing: Ühel kenal päeval, N, 2007-02-01 kell 13:24, kirjutas Gavin Sherry: A different approach discussed earlier involves greatly restricting the way in which the table is used. This table could only be written to if an exclusive lock is held; on error or ABORT, the table is truncated. The problem is that a lot of this looks like a hack and I haven't seen a very clean approach which has gone beyond basic brain dump. A more radical variation of the restricted-use archive table approach is storing all tuple visibility info in a separate file. At first it seems to just add overhead, but for lots (most ? ) usecases the separately stored visibility should be highly compressible, so for example for bulk-loaded tables you could end up with one bit per page saying that all tuples on this page are visible. Also this could be used to speed up vacuums, as only the visibility table needs to be scanned duting phase 1 of vacuum, and so tables with localised/moving hotspots can be vacuumed withoutd scanning lots of static data. Also, storing the whole visibility info, but in a separate heap, lifts all restrictions of the restricted-use archive table variant. And the compression of visibility info (mostly replacing per-tuple info with per-page info) can be carried out by a separate vacuum-like process. And it has many of the benefits of static/RO tables, like space saving and index-only queries. Index-only will of course need to get the visibility info from visibility heap, but if it is mostly heavily compressed, it will be a lot cheaper than random access to data heap. For tables with fixed-width tuples it can probably be extended to support vertical fragmentation as well, to get DWH benefits similar to http://monetdb.cwi.nl/ . -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: A more general approach (Re: [HACKERS] Dataarchiving/warehousing idea)
On Thu, 2007-02-01 at 14:38 +0200, Hannu Krosing wrote: Ühel kenal päeval, N, 2007-02-01 kell 13:24, kirjutas Gavin Sherry: A different approach discussed earlier involves greatly restricting the way in which the table is used. This table could only be written to if an exclusive lock is held; on error or ABORT, the table is truncated. The problem is that a lot of this looks like a hack and I haven't seen a very clean approach which has gone beyond basic brain dump. A more radical variation of the restricted-use archive table approach is storing all tuple visibility info in a separate file. At first it seems to just add overhead, but for lots (most ? ) usecases the separately stored visibility should be highly compressible, so for example for bulk-loaded tables you could end up with one bit per page saying that all tuples on this page are visible. Also this could be used to speed up vacuums, as only the visibility table needs to be scanned duting phase 1 of vacuum, and so tables with localised/moving hotspots can be vacuumed withoutd scanning lots of static data. Also, storing the whole visibility info, but in a separate heap, lifts all restrictions of the restricted-use archive table variant. And the compression of visibility info (mostly replacing per-tuple info with per-page info) can be carried out by a separate vacuum-like process. And it has many of the benefits of static/RO tables, like space saving and index-only queries. Index-only will of course need to get the visibility info from visibility heap, but if it is mostly heavily compressed, it will be a lot cheaper than random access to data heap. I like that idea, as a non-default option, since in-line visibility is important for OLTP applications. This idea is sufficiently flexible to allow a range of use cases without requiring a complete removal of functionality. Read-mostly is an important use case for very large databases. This is essentially the same thing as PhantomCommandId, just for the whole tuple header. It's something that would go on pg_class easily, just as WITH/WITHOUT OIDS has done. -- 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
Re: [HACKERS] May, can, might
Peter Eisentraut wrote: Bruce Momjian wrote: I have made these adjustments to the documentation. Do people want the error message strings also updated? I have no problem with that. They seem to be in pretty good shape already, so the changes should be few. Yea, I see only a few. I will update those. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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: [HACKERS] max_locks_per_transactions ...
Simon Riggs wrote: On Thu, 2007-02-01 at 09:15 +0100, Hans-Juergen Schoenig wrote: Right now max_locks_per_transactions defines the average number of locks taken by a transaction. thus, shared memory is limited to max_locks_per_transaction * (max_connections + max_prepared_transactions). this is basically perfect. however, recently we have seen a couple of people having trouble with this. partitioned tables are becoming more and more popular so it is very likely that a single transaction can eat up a great deal of shared memory. some people having a lot of data create daily tables. if done for 3 years we already lost 1000 locks per inheritance-structure. i wonder if it would make sense to split max_locks_per_transaction into two variables: max_locks (global size) and max_transaction_locks (local size). if set properly this would prevent good short running transactions from running out of shared memory when some evil long running transactions start to suck up shared memory. Do partitioned tables use a lock even when they are removed from the plan as a result of constraint_exclusion? I thought not. So you have lots of concurrent multi-partition scans. maybe i was a bit unprecise before - let me clarify. the application we are talking about contains 1 tb of data. the main table (about 90% of the data) is partitioned into about 3.700 subtables. for this kind of application this makes perfect sense as subsets of data (= subtable) change frequently. two types of queries are executed by the system: - short OLTP operations adding data to the huge tables - a large set of analysis stuff which tortures the database with more complex queries. the main issue is that to a large extend those analysis queries have to run concurrently. the thing now is: if there are many concurrent operations which need this partitioned structure the amount of locks is growing quite fast (in this +3700 locks per transaction). so, it can happen that we run out of shared memory inside some OLTP transaction just because too many background processes are sucking up shared memory. of course it would be simple to pump max_locks_per_transaction - this is not the point. the idea is rather: max_locks_per_transaction is a somehow obscure way of putting things. many people are simply misleaded. most people assume that this is indeed a per transaction limit and then they are surprised when a transaction which hardly needs locks fails. i would suggest to replace the existing parameter but something else: - a switch to define the global size of the lock pool (e.g. max_locks) - a switch which defines the upper limit for the current backend / transaction we could make a transaction fail which takes too many locks. the advantage would be that the transaction causes the problem and not some other innocent small operation. best regards, hans -- Cybertec Geschwinde Schönig GmbH Schöngrabern 134; A-2020 Hollabrunn Tel: +43/1/205 10 35 / 340 www.postgresql.at, www.cybertec.at ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] stack usage in toast_insert_or_update()
On 1/31/2007 12:41 PM, Tom Lane wrote: Pavan Deolasee [EMAIL PROTECTED] writes: On 1/31/07, Tom Lane [EMAIL PROTECTED] wrote: The toast code takes pains to ensure that the tuples it creates won't be subject to re-toasting. Else it'd be an infinite recursion. I think I found it. The toast_insert_or_update() function gets into an unnecessary recursion because of alignment issues. It thus toasts already toasted data. This IMHO might be causing unnecessary overheads for each toast operation. Interesting --- I'd never seen this because both of my usual development machines have MAXALIGN 8, and it works out that that makes TOAST_MAX_CHUNK_SIZE 1986, which makes the actual toasted tuple size 2030, which maxaligns to 2032, which is still less than TOAST_TUPLE_THRESHOLD. I think the coding was implicitly assuming that TOAST_TUPLE_THRESHOLD would itself be a maxalign'd value, but it's not necessarily (and in fact not, with the current page header size --- I wonder whether the bug was originally masked because the page header size was different??) We can't change TOAST_MAX_CHUNK_SIZE without forcing an initdb, but I think that it would be safe to remove the MAXALIGN'ing of the tuple size in the tests in heapam.c, that is if (HeapTupleHasExternal(tup) || (MAXALIGN(tup-t_len) TOAST_TUPLE_THRESHOLD)) heaptup = toast_insert_or_update(relation, tup, NULL); else heaptup = tup; becomes if (HeapTupleHasExternal(tup) || (tup-t_len TOAST_TUPLE_THRESHOLD)) heaptup = toast_insert_or_update(relation, tup, NULL); else heaptup = tup; which'll save a cycle or two as well as avoid this corner case. It seems like a number of the uses of MAXALIGN in tuptoaster.c are useless/bogus as well. Comments? Can't we maxalign the page header in the calculations? Jan regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Estimation error in n_dead_tuples
ITAGAKI Takahiro [EMAIL PROTECTED] writes: I'm thinking to add the number of vacuumed tuples to the message from vacuum. The stats collector will subtract the value from n_dead_tuples instead of setting it to zero. This is also needed if we want to make some kinds of partial vacuum methods. This seems awfully dangerous to me, because then you are operating on dead reckoning forever: there will be nothing that can correct an inaccurate rowcount estimate, and in practice that means it will diverge arbitrarily far from reality :-(, because of the inherent inaccuracies of the stats system. I think the risk of that is far worse than the relatively small (or at least bounded) error arising from tuples not seen by vacuum. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] stack usage in toast_insert_or_update()
Jan Wieck [EMAIL PROTECTED] writes: On 1/31/2007 12:41 PM, Tom Lane wrote: We can't change TOAST_MAX_CHUNK_SIZE without forcing an initdb, but I think that it would be safe to remove the MAXALIGN'ing of the tuple size in the tests in heapam.c, that is Can't we maxalign the page header in the calculations? Actually, the page header size *is* maxaligned. The problem is that TOAST_TUPLE_THRESHOLD isn't. We could make it so, but that would change TOAST_MAX_CHUNK_SIZE thus forcing an initdb. I think simply removing the MAXALIGN operations in the code is a better answer, as it eliminates some rather pointless overhead. There's no particular reason why the threshold needs to be maxaligned ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Why is ecpg segfaulting on buildfarm member clownfish?
Has anyone looked into $SUBJECT? I just today realized that the ECPG-Check failures on that machine are not the run-of-the-mill small difference in the expected results. Rather, most of the tests are actually dumping core on the client side: testing connect/test1.pgc ... skipped testing connect/test2.pgc ... Segmentation Fault - core dumped FAILED (log) testing connect/test3.pgc ... Segmentation Fault - core dumped FAILED (log) testing connect/test4.pgc ... ok testing connect/test5.pgc ... ok testing compat_informix/charfuncs.pgc ... ok testing compat_informix/dec_test.pgc ... ok testing compat_informix/rfmtdate.pgc ... ok testing compat_informix/rfmtlong.pgc ... ok testing compat_informix/rnull.pgc ... Segmentation Fault - core dumped FAILED (log, output) testing compat_informix/test_informix.pgc ... Segmentation Fault - core dumped FAILED (log, output) testing compat_informix/test_informix2.pgc ... Segmentation Fault - core dumped FAILED (log, output) testing preproc/comment.pgc... ok testing preproc/define.pgc ... Segmentation Fault - core dumped FAILED (log, output) etc etc BTW, this is a perfect example of why it's not a good idea to allow minor regression failures to go unfixed --- people become desensitized. I know I've been completely ignoring ECPG-Check buildfarm results for awhile now. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Why is ecpg segfaulting on buildfarm member clownfish?
Tom Lane wrote: [...] BTW, this is a perfect example of why it's not a good idea to allow minor regression failures to go unfixed --- people become desensitized. I know I've been completely ignoring ECPG-Check buildfarm results for awhile now. I already reported that a while ago: http://archives.postgresql.org/pgsql-hackers/2007-01/msg00319.php there was also some discussion off-list last week with Michael - I have arranged for an account on that box for him but I'm not sure if he already found time to investigate. Stefan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: A more general approach (Re: [HACKERS] Data archiving/warehousing idea)
Hannu Krosing [EMAIL PROTECTED] writes: A more radical variation of the restricted-use archive table approach is storing all tuple visibility info in a separate file. At first it seems to just add overhead, but for lots (most ? ) usecases the separately stored visibility should be highly compressible, so for example for bulk-loaded tables you could end up with one bit per page saying that all tuples on this page are visible. The more you compress, the slower and more complicated it will be to access the information. I'd put my money on this being a net loss in the majority of scenarios. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] max_locks_per_transactions ...
Hans-Juergen Schoenig [EMAIL PROTECTED] writes: i would suggest to replace the existing parameter but something else: - a switch to define the global size of the lock pool (e.g. max_locks) - a switch which defines the upper limit for the current backend / transaction The problem with that is that it's pretty much guaranteed to break pg_dump, as pg_dump always needs a lot of locks. We could perhaps change pg_dump to increase its limit value (assuming that that's not a privileged operation), but the fact that a counterexample is so handy makes me doubt that this is a better design than what we have. 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] A more general approach (Re: Data archiving/warehousing idea)
Hannu Krosing wrote: ...is storing all tuple visibility info in a separate file. At first it seems to just add overhead, but for lots (most ? ) usecases the separately stored visibility should be highly compressible, so for example for bulk-loaded tables you could end up with one bit per page saying that all tuples on this page are visible. Seems you could do one bit per page compression even with visibility data stored in the pages themselves. I could imagine a table re-writing vacuum freeze that finds pages with all data visible to everyone and packs them with a single bit saying everything here's frozen. The penalty would be an expensive splitting of the page (and who knows what evil locks would be needed) if an update is ever done on those wholly frozen pages -- but we're talking about read-mostly tables here so that tradeoff might not be bad. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_restore fails with a custom backup file
Thread URL added to TODO item: o Add long file support for binary pg_dump output --- Magnus Hagander wrote: On Fri, Dec 15, 2006 at 12:57:50AM +0900, Hiroshi Saito wrote: Win32 does not implement fseeko() and ftello(). So I think it limit to handle a 2GB file. Is this a specification? Yes, Magnus-san suggested the problem. It is present TODO. The entire adjustment was still difficult though I had tried it. SetFilePointer might be able to be saved. However, I think it might be an attempt of 8.3... I've been looking at a fix for this, and I think I have it. The solution looks to be to redefine off_t to 64-bit (the standard headers *always* define it as 32-bit, and there is no way to change that - at least not that I can find). I have the fix made for just bin/pg_dump for now (in pg_dump.h), and I'm testing that. (So far only on MSVC builds) A question though - is there any *gain* from using 64-bit offsets in the actual backend? The change could of course be done in port.h, but that will affect the whole backend (and require a few more functions than just fseeko/ftello to be redefined) which could have larger consequences. So - provided that this works after my test is completed, is the better place to do this for just pg_dump/pg_restore, or attempt to do it for the whole backend? //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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
[HACKERS] The may/can/might business
3606c3606 errmsg(aggregate function calls cannot be nested))); --- errmsg(aggregate function calls may not be nested))); I don't think that this is an improvement, or even correct English. You have changed a message that states that an action is logically impossible into one that implies we are arbitrarily refusing to let the user do something that *could* be done, if only we'd let him. There is relevant material in the message style guidelines, section 45.3.8: it says that cannot open file %s ... indicates that the functionality of opening the named file does not exist at all in the program, or that it's conceptually impossible. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] The may/can/might business
Tom Lane wrote: 3606c3606 errmsg(aggregate function calls cannot be nested))); --- errmsg(aggregate function calls may not be nested))); I don't think that this is an improvement, or even correct English. You have changed a message that states that an action is logically impossible into one that implies we are arbitrarily refusing to let the user do something that *could* be done, if only we'd let him. There is relevant material in the message style guidelines, section 45.3.8: it says that cannot open file %s ... indicates that the functionality of opening the named file does not exist at all in the program, or that it's conceptually impossible. Uh, I think you might be reading the diff backwards. The current CVS wording is cannot. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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: [HACKERS] The may/can/might business
On Thu, 1 Feb 2007, Bruce Momjian wrote: From: Bruce Momjian [EMAIL PROTECTED] Tom Lane wrote: 3606c3606 errmsg(aggregate function calls cannot be nested))); --- errmsg(aggregate function calls may not be nested))); I don't think that this is an improvement, or even correct English. You have changed a message that states that an action is logically impossible into one that implies we are arbitrarily refusing to let the user do something that *could* be done, if only we'd let him. There is relevant material in the message style guidelines, section 45.3.8: it says that cannot open file %s ... indicates that the functionality of opening the named file does not exist at all in the program, or that it's conceptually impossible. Uh, I think you might be reading the diff backwards. The current CVS wording is cannot. No, Bruce, he got it exactly right: cannot indicates, as Tom put it, logical impossibility, whereas may not suggests that something could happen but it's being prevented. His parsing of the english was spot-on. RT -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 [EMAIL PROTECTED], http://ScienceTools.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] The may/can/might business
Richard Troy wrote: On Thu, 1 Feb 2007, Bruce Momjian wrote: From: Bruce Momjian [EMAIL PROTECTED] Tom Lane wrote: 3606c3606 errmsg(aggregate function calls cannot be nested))); --- errmsg(aggregate function calls may not be nested))); I don't think that this is an improvement, or even correct English. You have changed a message that states that an action is logically impossible into one that implies we are arbitrarily refusing to let the user do something that *could* be done, if only we'd let him. There is relevant material in the message style guidelines, section 45.3.8: it says that cannot open file %s ... indicates that the functionality of opening the named file does not exist at all in the program, or that it's conceptually impossible. Uh, I think you might be reading the diff backwards. The current CVS wording is cannot. No, Bruce, he got it exactly right: cannot indicates, as Tom put it, logical impossibility, whereas may not suggests that something could happen but it's being prevented. His parsing of the english was spot-on. Right, but the changes was from may not (permission) to cannot (logical impossibility), which I think is what he wanted. Is there an open source grammar award we can win? :-) -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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: [HACKERS] The may/can/might business
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: 3606c3606 errmsg(aggregate function calls cannot be nested))); --- errmsg(aggregate function calls may not be nested))); I don't think that this is an improvement, or even correct English. Uh, I think you might be reading the diff backwards. The current CVS wording is cannot. Er ... duh. Sorry about that; got confused while merging with some work-in-progress. emily litellaNever mind./emily litella regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] writing new regexp functions
I am wanting to write some new C functions which leverage postgresql's existing regexp code in an extension module. I notice that the functions RE_compile_and_cache and RE_compile_and_execute in src/backend/util/regexp.c contain the code necessary to connect the regexp code in src/backend/regex with the postgresql string conversion, error reporting, and memory management infrastructure, as well as providing caching of regexes which would probably be a win to any regex function in postgres. It would seem that these functions would be useful to any C function dealing with regexp matching in postgresql, but they are static functions, so they cannot be used outside of src/backend/utils/adt/regexp.c. Since all of the core regexp functions are in this file, this has been ok, but it is my opinion that these functions should be made visible and added to a header file so that extensions can make use of them, because any add-on functions that want to use the regex code in postgres in some new way would need to basically duplicate that same code in order to do so. Is there some specific reason that these functions are static, or would it be ok to make them non-static and add them to a header (say, src/include/utils/regexp.h) so that extensions could use them as well? I could put together a patch for this if desired, or it seems simple enough that someone could just do it... -- I can't decide whether to commit suicide or go bowling. -- Florence Henderson ---(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] [PATCHES] Full page writes improvement
Koichi Suzuki [EMAIL PROTECTED] writes: Here's an idea and a patch for full page writes improvement. Idea: (1) keep full page writes for ordinary WAL, make them available during the crash recovery, - recovery from inconsistent pages which can be made at the crash, (2) Remove them from the archive log except for those written during online backup (between pg_start_backup and pg_stop_backup) - small size archive log. Doesn't this break crash recovery on PITR slaves? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Bitmap index thoughts
Where are we on this patch? Does it have performance tests to show where it is beneificial? Is it ready to be reviewed? --- Heikki Linnakangas wrote: I've been skimming through the bitmap index patch... A scan needs to access at least five pages: 1. B-tree index (root+others, depending on depth) 2. The auxiliary heap page 3. bitmap index meta page 4. LOV page 5. bitmap page That seems like a lot of indirection. A high startup cost is probably ok for typical bitmap index use cases and most of the needed pages should stay in memory, but could we simplify this? Why do we need the auxiliary heap, couldn't we just store the blk+offset of the LOV item directly in the b-tree index item? And instead of having separate LOV pages that store a number of LOV items, how about storing each LOV item on a page of it's own, and using the rest of the page to store the last chunk of the bitmap. That would eliminate one page access, but more importantly, maybe we could then get rid of all the bm_last_* attributes in BMLOVItemData that complicate the patch quite a bit, while preserving the performance. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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: [HACKERS] Bitmap index thoughts
On Thu, 1 Feb 2007, Bruce Momjian wrote: Where are we on this patch? Does it have performance tests to show where it is beneificial? Is it ready to be reviewed? I've got an updated patch which adds significant performance improvements for worse case data distributions. It also contains a rewrite of the WAL code to handle incomplete actions. I haven't worked on the stuff discussed below with Heikki. It's a lot of work and probably more suitable for a second generation. I've just got to finish testing the merge of Tom's operator family stuff and then I'll send off the patch and performance figures. Thanks, Gavin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] writing new regexp functions
Jeremy Drake [EMAIL PROTECTED] writes: Is there some specific reason that these functions are static, Yeah: not cluttering the global namespace. I'm not excited about exporting everything that anybody could possibly want access to; that just makes it harder to maintain the code. When you see a static function, you know that you don't have to look further than the current file to understand how it's used. When you see a global function, the difficulty of knowing how it's used is an order of magnitude higher, maybe more. What's more, if you want to change it then you have to worry about the possible side-effects on unknown non-core code that might be calling it. Is there a reason for not putting your new code itself into regexp.c? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Full page writes improvement
Tom Lane wrote: Koichi Suzuki [EMAIL PROTECTED] writes: Here's an idea and a patch for full page writes improvement. Idea: (1) keep full page writes for ordinary WAL, make them available during the crash recovery, - recovery from inconsistent pages which can be made at the crash, (2) Remove them from the archive log except for those written during online backup (between pg_start_backup and pg_stop_backup) - small size archive log. Doesn't this break crash recovery on PITR slaves? Compressed archive log contains the same data as full_page_writes off case. So the influence to PITR slaves is the same as full_page_writes off. K.Suzuki regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Koichi Suzuki ---(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] [PATCHES] Full page writes improvement
Koichi Suzuki [EMAIL PROTECTED] writes: Tom Lane wrote: Doesn't this break crash recovery on PITR slaves? Compressed archive log contains the same data as full_page_writes off case. So the influence to PITR slaves is the same as full_page_writes off. Right. So what is the use-case for running your primary database with full_page_writes on and the slaves with it off? It doesn't seem like a very sensible combination to me. Also, it seems to me that some significant performance hit would be taken by having to grovel through the log files to remove and re-add the full-page data. Plus you are actually writing *more* WAL data out of the primary, not less, because you have to save both the full-page images and the per-tuple data they normally replace. Do you have numbers showing that there's actually any meaningful savings overall? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] writing new regexp functions
On Thu, 1 Feb 2007, Tom Lane wrote: Jeremy Drake [EMAIL PROTECTED] writes: Is there some specific reason that these functions are static, Yeah: not cluttering the global namespace. Is there a reason for not putting your new code itself into regexp.c? Not really, I just figured it would be cleaner/easier to write it as an extension. I also figure that it is unlikely that every regexp function that anyone could possibly want will be implemented in core in that one file. If anyone writes an extension like this, they would need to duplicate a good amount of code in order to do so, that would make more difficulty in maintaining the code if it should need to change. It also makes developing a new function a lot easier, no need to re-initdb to add the function, no need to relink the postmaster and restart it every time the function changes. Anyway, the particular thing I was writing was a function like substring(str FROM pattern) which instead of returning just the first match group, would return an array of text containing all of the match groups. I exported the functions in my sandbox, and wrote a module with a function that does this. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Calling J-Man Kink. Calling J-Man Kink. Hash missile sighted, target Los Angeles. Disregard personal feelings about city and intercept. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Function proposal to find the type of a datum
Hi all, I found the need to determine the type of a datum at runtime. David Fetter and Elein have already written about this: http://www.varlena.com/varlena/GeneralBits/117.php (My scenario is similar to the article there; I was writing a procedure which unit-tests other procedures. It needs to know the type of arguments passed so that when constructing queries, it may avoid quoting (for example) integers, so that the appropiate function is selected for those which are overloaded. As I did not want to assume that a user has permission to create PL functions in C, I ended up casting to an integer and catching exceptions to determine failure!) I think this would be a useful function to expose by default. David suggested pg_catalog.pg_type_of(ANYELEMENT). If this is viewed as a good idea, I'd like to try to implement this. Suggestions welcome, -- Kate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] About PostgreSQL certification
Joshua D. Drake wrote: To cure the shortage of experienced Postgres folks there is only one solution - err, more experience! So the need is for good training courses (not necessarily certification and all the IMHO nonsense that comes with that), and a willingness on the part of employers to invest in upskilling their staff. You know its funny. Command Prompt, OTG-Inc, SRA and Big Nerd Ranch *all* offer training. Last I checked, OTG had to cancel classes because of lack of demand (please verify Chander). We tried to offer classes in Santa Clara, CA (and may do so again), but didn't have sufficient demand to run them. There were 2 people that had expressed interest in that class. However, I will say that in 2006 that was the only class we canceled (keep in mind though, that we run courses at our headquarters *regardless* of the number of students enrolled - it's our policy to not cancel classes here...) On another note, I will say that we're doing well enough to support the project through SPI... Command Prompt currently only trains corps with 12+ people per class so we are a bit different. Sinerely, Joshua D. Drake -- Chander Ganesan The Open Technology Group One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com
Re: [HACKERS] About PostgreSQL certification
Ivo, Iannsp wrote: Hello, I did like to know what you think about the postgresql certifications provided for PostgreSQL CE http://www.sraoss.co.jp/postgresql-ce/news_en.html CertFirst http://www.certfirst.com/postgreSql.htm My question is about the validate of this certification for the clients. Make difference to be certified? IMHO, the SRA certification has been around for awhile, and I believe it has some credibility in Japan...While I'm not sure what its credibility is like here in the US - the fact that it has credibility in Japan is a big plus . The CertFirst certification (examsonline.com), seems to be administered online (as opposed to SRA's which is at a PearsonVUE test center) - which basically means that it's open book, open note, call your friend, copy the questions, etc. It also seems that CertFirst runs the certification themselves under what appears to be a shell company called examsonline. It looks to be more of a marketing ploy than anything else Based on the fact that they are a provider of training under the WIA act in IL, I'd suspect that they need a certification so that they can sell their programs to the unemployed folks that are getting free training on the gov'ts dime. thanks for advanced. Ivo Nascimento. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Chander Ganesan The Open Technology Group One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] About PostgreSQL certification
Chander Ganesan wrote: Ivo, Iannsp wrote: Hello, I did like to know what you think about the postgresql certifications provided for PostgreSQL CE http://www.sraoss.co.jp/postgresql-ce/news_en.html CertFirst http://www.certfirst.com/postgreSql.htm My question is about the validate of this certification for the clients. Make difference to be certified? IMHO, the SRA certification has been around for awhile, and I believe it has some credibility in Japan...While I'm not sure what its credibility is like here in the US - the fact that it has credibility in Japan is a big plus . The CertFirst certification (examsonline.com), seems to be administered online (as opposed to SRA's which is at a PearsonVUE test center) - which basically means that it's open book, open note, call your friend, copy the questions, etc. It also seems that CertFirst runs the certification themselves under what appears to be a shell company called examsonline. It looks to be more of a marketing ploy than anything else Correction...I just checked and it looks like they've updated their web site and no longer refer to the examsonline online exam...so I'm not sure where/what their exam entails now. Their site used to refer to an exam through examsonline.com ... You'll have to contact them for details... Based on the fact that they are a provider of training under the WIA act in IL, I'd suspect that they need a certification so that they can sell their programs to the unemployed folks that are getting free training on the gov'ts dime. thanks for advanced. Ivo Nascimento. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Chander Ganesan The Open Technology Group One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Full page writes improvement
Tom Lane wrote: Koichi Suzuki [EMAIL PROTECTED] writes: Tom Lane wrote: Doesn't this break crash recovery on PITR slaves? Compressed archive log contains the same data as full_page_writes off case. So the influence to PITR slaves is the same as full_page_writes off. Right. So what is the use-case for running your primary database with full_page_writes on and the slaves with it off? It doesn't seem like a very sensible combination to me. Also, it seems to me that some significant performance hit would be taken by having to grovel through the log files to remove and re-add the full-page data. Plus you are actually writing *more* WAL data out of the primary, not less, because you have to save both the full-page images and the per-tuple data they normally replace. Do you have numbers showing that there's actually any meaningful savings overall? Yes, I have some evaluations to show that we're writing less and using overall less resources. Please give me a couple of days to translate. In the case of PITR slave, because archive logs are read in a short period, amount of archive log may not be an issue. In the case where online backup and archive logs must be kept for (relatively) long period, archive log size is a major issue. K.Suzuki regards, tom lane -- Koichi Suzuki ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Estimation error in n_dead_tuples
Tom Lane [EMAIL PROTECTED] wrote: I'm thinking to add the number of vacuumed tuples to the message from vacuum. The stats collector will subtract the value from n_dead_tuples instead of setting it to zero. This seems awfully dangerous to me, because then you are operating on dead reckoning forever Ah, I see. We need 'reset' of statistics sometimes. Now I'll propose to save the n_dead_tuples value at the beginning of vacuum, and send the value with a stat message to subtract. The added number to n_dead_tuples during vacuum will be left. (This is true for now, but will be incorrect after Recalculating OldestXmin in a long-running vacuum patch is commited.) I'll send a patch that adds the above to patches. This is also needed if we want to make some kinds of partial vacuum methods. We can adjust the number of vacuumed tuples for patial vacuums that scan a part of heap pages. For example, n_dead_duples_at_start * scanned_pages / rel_pages . We can also use the actually removed tuples in vacuum for adjustments. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(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] writing new regexp functions
On Thu, Feb 01, 2007 at 05:11:30PM -0800, Jeremy Drake wrote: On Thu, 1 Feb 2007, Tom Lane wrote: Jeremy Drake [EMAIL PROTECTED] writes: Is there some specific reason that these functions are static, Yeah: not cluttering the global namespace. Is there a reason for not putting your new code itself into regexp.c? Not really, I just figured it would be cleaner/easier to write it as an extension. I also figure that it is unlikely that every regexp function that anyone could possibly want will be implemented in core in that one file. If anyone writes an extension like this, they would need to duplicate a good amount of code in order to do so, that would make more difficulty in maintaining the code if it should need to change. It also makes developing a new function a lot easier, no need to re-initdb to add the function, no need to relink the postmaster and restart it every time the function changes. Anyway, the particular thing I was writing was a function like substring(str FROM pattern) which instead of returning just the first match group, would return an array of text containing all of the match groups. That'd be great! People who use dynamic languages like Perl would feel much more at home having access to all the matches. While you're at it, could you could make pre-match and post-match (optionally--I know it's expensive) available? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(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: column ordering, was Re: [HACKERS] [PATCHES] Enums patch v2
Added to TODO: o Allow column display reordering by recording a display, storage, and permanent id for every column? http://archives.postgresql.org/pgsql-hackers/2006-12/msg00782.php --- Jim C. Nasby wrote: On Thu, Dec 21, 2006 at 11:43:27AM -0500, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: You could make a case that we need *three* numbers: a permanent column ID, a display position, and a storage position. Could this not be handled by some catalog fixup after an add/drop? If we get the having 3 numbers you will almost have me convinced that this might be too complicated after all. Actually, the more I think about it the more I think that 3 numbers might be the answer. 99% of the code would use only the permanent ID. Display position would be used in *exactly* one place, namely while expanding SELECT foo.* --- I can't think of any other part of the backend that would care about it. (Obviously, client-side code such as psql's \d would use it too.) Use of storage position could be localized into a few low-level tuple access functions, probably. The problems we've been having with the concept stem precisely from trying to misuse either display or storage position as a permanent ID. That's fine as long as it actually is permanent, but as soon as you want to change it then you have problems. We should all understand this perfectly well from a database theory standpoint: pg_attribute has to have a persistent primary key. (attrelid, attnum) is that key, and we can't go around altering a column's attnum without creating problems for ourselves. Is there enough consensus on this to add it to the TODO? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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: [HACKERS] writing new regexp functions
On Thu, 1 Feb 2007, David Fetter wrote: On Thu, Feb 01, 2007 at 05:11:30PM -0800, Jeremy Drake wrote: Anyway, the particular thing I was writing was a function like substring(str FROM pattern) which instead of returning just the first match group, would return an array of text containing all of the match groups. If you are subscribed to -patches, I sent my code to date there earlier this evening. I also said that I wanted to make a function that split on a pattern (like perl split) and returned setof text. That'd be great! People who use dynamic languages like Perl would feel much more at home having access to all the matches. While you're at it, could you could make pre-match and post-match (optionally--I know it's expensive) available? I could, but I'm not sure how someone would go about accessing such a thing. What I just wrote would be most like this perl: @foo = ($str=~/pattern/); Where would pre and post match fit into this? Are you talking about a different function? Or sticking prematch at the beginning of the array and postmatch at the end? I could also put the whole match somewhere also, but I did not in this version. The code I wrote returns a text[] which is one-dimensional, has a lower bound of 1 (as most postgres arrays do), where if there are n capture groups, ra[1] has the first capture group and ra[n] has the last one. Since postgres has an option to make different lower bounds, I suppose I could have an option to put the prematch in [-1], the entire match in [0], and the postmatch in [n+1]. This seems to be odd to me though. I guess I'm saying, I agree that the entire match, prematch, and postmatch would be helpful, but how would you propose to present these to the user? Cheers, D -- To err is human, to forgive, beyond the scope of the Operating System. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Function proposal to find the type of a datum
Hello, you can identify type via operator OF. like: IF a IS OF INTEGER THEN RAISE NOTICE 'Parametr a is numeric'; ELSIF a IS OF varchar THEN RAISE NOTICE 'Parametr a is string'; END IF; Regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] writing new regexp functions
On Thu, Feb 01, 2007 at 10:16:54PM -0800, Jeremy Drake wrote: On Thu, 1 Feb 2007, David Fetter wrote: On Thu, Feb 01, 2007 at 05:11:30PM -0800, Jeremy Drake wrote: Anyway, the particular thing I was writing was a function like substring(str FROM pattern) which instead of returning just the first match group, would return an array of text containing all of the match groups. If you are subscribed to -patches, I sent my code to date there earlier this evening. I also said that I wanted to make a function that split on a pattern (like perl split) and returned setof text. That'd be great! People who use dynamic languages like Perl would feel much more at home having access to all the matches. While you're at it, could you could make pre-match and post-match (optionally--I know it's expensive) available? I could, but I'm not sure how someone would go about accessing such a thing. What I just wrote would be most like this perl: @foo = ($str=~/pattern/); Where would pre and post match fit into this? Are you talking about a different function? Yes, although it might have the same name, as in regex_match(pattern TEXT, string TEXT, return_pre_and_post BOOL). Or sticking prematch at the beginning of the array and postmatch at the end? I could also put the whole match somewhere also, but I did not in this version. The data structure could be something like TYPE matches ( prematch TEXT, matchTEXT[], postmatch TEXT ) The code I wrote returns a text[] which is one-dimensional, has a lower bound of 1 (as most postgres arrays do), where if there are n capture groups, ra[1] has the first capture group and ra[n] has the last one. Since postgres has an option to make different lower bounds, I suppose I could have an option to put the prematch in [-1], the entire match in [0], and the postmatch in [n+1]. This seems to be odd to me though. Odd == bad. I think the pre- and post-matches should be different in essence, not just in index :) I guess I'm saying, I agree that the entire match, prematch, and postmatch would be helpful, but how would you propose to present these to the user? See above :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Function proposal to find the type of a datum
Pavel Stehule [EMAIL PROTECTED] writes: you can identify type via operator OF. IF a IS OF INTEGER THEN RAISE NOTICE 'Parametr a is numeric'; Yeah, that is the SQL-standard syntax, but I think our implementation doesn't work the way Kate would like: if a is an ANYELEMENT function parameter then (I think) only IS OF ANYELEMENT would succeed. Would it conform to the SQL spec for the test to look through ANYELEMENT to the actual parameter type? Would it be reasonably implementable to do that? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Function proposal to find the type of a datum
On Fri, Feb/ 2/07 02:17:51AM -0500, Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: you can identify type via operator OF. IF a IS OF INTEGER THEN RAISE NOTICE 'Parametr a is numeric'; Yeah, that is the SQL-standard syntax, but I think our implementation doesn't work the way Kate would like: if a is an ANYELEMENT function parameter then (I think) only IS OF ANYELEMENT would succeed. Would it conform to the SQL spec for the test to look through ANYELEMENT to the actual parameter type? Would it be reasonably implementable to do that? Well, actually, I discussed this on IRC with Pavel, and for the above I have syntax error at or near INTEGER. My guess was that the grammar changed as an unintentional side-effect of some other change. He corrected his suggestion: OF takes a list of types; it needs parentheses: IF a IS OF (INTEGER) THEN This operator would look wonderful for my particular needs, were it not that it has completely different semantics! It appears to test to see what a datum actually *is* (in my case TEXT), rather than what it may be parsed as (in my case, INTEGER). Meanwhile, I still think the function David proposed is a worthy addition (and I still have a user-case for it!), as using just the OF operator for something similar, one would have to explictly test against every type required. Thank you, -- Kate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Function proposal to find the type of a datum
Kate F [EMAIL PROTECTED] writes: ... OF takes a list of types; it needs parentheses: IF a IS OF (INTEGER) THEN Oh, right, minor detail. Meanwhile, I still think the function David proposed is a worthy addition (and I still have a user-case for it!), as using just the OF operator for something similar, one would have to explictly test against every type required. Um, but what would you do differently with an OID result? The nice thing about the SQL-spec syntax is that it avoids depending on anything so implementation-specific as a pg_type OID. So if it covers the territory then I'd be inclined to go that way. What useful functionality does it lack? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] ToDo: add documentation for operator IS OF
Hello, I miss doc for this operator Regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Function proposal to find the type of a datum
On Fri, Feb/ 2/07 02:41:15AM -0500, Tom Lane wrote: Meanwhile, I still think the function David proposed is a worthy addition (and I still have a user-case for it!), as using just the OF operator for something similar, one would have to explictly test against every type required. Um, but what would you do differently with an OID result? The nice thing about the SQL-spec syntax is that it avoids depending on anything so implementation-specific as a pg_type OID. So if it covers the territory then I'd be inclined to go that way. What useful functionality does it lack? There are two differences here. I did try to explain these in my previous mail, but perhaps I wasn't clear enough. Firstly: Substitute the name of the type (as TEXT) if you think that's more appropiate. The difference between OF and this function is that this function is pulling the type from the datum, rather than explicitly testing it against types the user suggests. If I wanted to find the type of x using OF, I would have to check it for all types which interest me: IF a IS OF (INTEGER) THEN t := 'INTEGER'; ELSE IF a IS OF (TEXT) THEN t := 'TEXT'; ELSE IF a IS OF (REAL) THEN t := 'REAL'; ... and so on. Versus: t := pg_type_of(a); Secondly, the semantics are different: OF yields the type the datum currently is; pg_type_of() (perhaps it should be renamed?) returns the most appropiate type to which the datum may be cast, if I understand get_fn_expr_argtype() correctly. Regards, -- Kate ---(end of broadcast)--- TIP 6: explain analyze is your friend
