Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
2010/2/19 Tom Lane t...@sss.pgh.pa.us: Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp writes: David Fetter da...@fetter.org wrote: support both pre-9.0 and post-9.0 PostgreSQLs. David Wheeler has suggested that we special-case PL/pgsql for 9.0 and greater, as it's in template0, where those tests are based. +1 for the CREATE LANGUAGE IF NOT EXISTS behavior. The regression test in the core is targeting only its version, but some external projects have version-independent tests. I think it's more like are under the fond illusion that their tests are version-independent. Are we going to back out the next incompatible change we choose to make as soon as somebody notices that it breaks a third-party test case? I don't think so. Let me point out that choosing to install plpgsql by default has already broken --single restore of practically every pg_dump out there. Nobody batted an eye about that. Why are we suddenly so concerned about its effects on unnamed test suites? Oh yeah, that one is very annoying, can we go fix that one somehow? I think the use of --single has decreased a lot in favor of parallel restore, but it's certainly not all that uncommon... I think the main reason we haven't heard loads of complains is that it isn't the default.. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming Replication docs
Joshua D. Drake wrote: On Fri, 2010-02-12 at 10:22 -0800, Josh Berkus wrote: In addition to the changes you've proposed, one thing our docs could really use is a single reference page which we could go to for all of the .conf files. Right now, you need to rely on postgresql.org doc search in order to find, for example, pg_hba.conf. I think it would be good to put into server administration somewhere a single page called Configuration Files which references: postgresql.conf pg_hba.conf recovery.conf pg_ident.conf ... hmmm, am I missing one? Seems that should go... under Reference Seems like a good idea. Unfortunately my SGML-skills are too weak to do that, so here's a patch to for my original proposal. There's little text changes, mostly just moves sections around. I'm thinking of committing this now; someone else will have to do the above reorganization if we want it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index 2f28da2..02d3765 100644 --- a/doc/src/sgml/backup.sgml +++ b/doc/src/sgml/backup.sgml @@ -955,7 +955,7 @@ SELECT pg_stop_backup(); listitem para Create a recovery command file filenamerecovery.conf/ in the cluster - data directory (see xref linkend=recovery-config-settings). You might + data directory (see xref linkend=recovery-config). You might also want to temporarily modify filenamepg_hba.conf/ to prevent ordinary users from connecting until you are sure the recovery was successful. /para @@ -1076,162 +1076,6 @@ restore_command = 'cp /mnt/server/archivedir/%f %p' WAL data need not be scanned again. /para - -sect3 id=recovery-config-settings xreflabel=Recovery Settings - titleRecovery Settings/title - - para - These settings can only be made in the filenamerecovery.conf/ - file, and apply only for the duration of the recovery. (A sample file, - filenameshare/recovery.conf.sample/, exists in the installation's - filenameshare// directory.) They must be - reset for any subsequent recovery you wish to perform. They cannot be - changed once recovery has begun. - The parameters for streaming replication are described in xref - linkend=replication-config-settings. - /para - - variablelist - - varlistentry id=restore-command xreflabel=restore_command - termvarnamerestore_command/varname (typestring/type)/term - listitem - para -The shell command to execute to retrieve an archived segment of -the WAL file series. This parameter is required for archive recovery, -but optional for streaming replication. -Any literal%f/ in the string is -replaced by the name of the file to retrieve from the archive, -and any literal%p/ is replaced by the copy destination path name -on the server. -(The path name is relative to the current working directory, -i.e., the cluster's data directory.) -Any literal%r/ is replaced by the name of the file containing the -last valid restart point. That is the earliest file that must be kept -to allow a restore to be restartable, so this information can be used -to truncate the archive to just the minimum required to support -restarting from the current restore. literal%r/ is typically only -used by warm-standby configurations -(see xref linkend=warm-standby). -Write literal%%/ to embed an actual literal%/ character. - /para - - para -It is important for the command to return a zero exit status -only if it succeeds. The command emphasiswill/ be asked for file -names that are not present in the archive; it must return nonzero -when so asked. Examples: -programlisting -restore_command = 'cp /mnt/server/archivedir/%f %p' -restore_command = 'copy C:\\server\\archivedir\\%f %p' # Windows -/programlisting - /para - /listitem - /varlistentry - - varlistentry id=recovery-end-command xreflabel=recovery_end_command - termvarnamerecovery_end_command/varname (typestring/type)/term - listitem - para -This parameter specifies a shell command that will be executed once only -at the end of recovery. This parameter is optional. The purpose of the -varnamerecovery_end_command/ is to provide a mechanism for cleanup -following replication or recovery. -Any literal%r/ is replaced by the name of the file -containing the last valid restart point. That is the earliest file that -must be kept to allow a restore to be restartable, so this information -can be used to truncate the archive to just the minimum required to -support restart from the current restore. literal%r/ would -typically be used in a warm-standby configuration -(see xref
Re: [HACKERS] Streaming replication on win32, still broken
Heikki Linnakangas wrote: Magnus Hagander wrote: Well, it's going to make the process that reads the WAL cause actual physical I/O... That'll take a chunk out of your total available I/O, which is likely to push you to the limit of your I/O capacity much quicker. Right, doesn't seem sensible, though it would be nice to see a benchmark on that. Here's a patch to disable O_DIRECT when archiving or streaming is enabled. This is pretty hard to test, so any extra eyeballs would be nice.. Committed. Can you check that this fixed the PANIC you saw? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Introduce WAL records to log reuse of btree pages, allowing
On Thu, 2010-02-18 at 14:17 -0500, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Thu, 2010-02-18 at 14:23 +0200, Heikki Linnakangas wrote: A straightforward way to fix that is to WAL-log the real xid in the XLOG_BTREE_DELETE_PAGE records, instead of resetting it to FrozenTransactionId. An even simpler way would be to reset the value to latestCompletedXid during btree_xlog_delete_page(). That touches less code. I doubt it will make much difference to conflict recovery, since if pages are being deleted then btree delete records are likely to be frequent and will have already killed long running queries. I'm a bit concerned about XID wraparound if the value doesn't get reset to FrozenTransactionId. There's no guarantee the page will get reused promptly ... I'd be very interested for you to have a look at Hot Standby from a transaction wraparound perspective. There was some code in there to handle anti-wraparound in RecordKnownAssignedTransactionId() but it was removed, though I'm a little hazy on that myself. You've got the best nose for corner cases and risks. In this case, I don't see any problem. The xid after recovery will be a same or higher value than if the crash had never taken place, so I can't see any risk that isn't already addressed. Since we now have to handle cases where blocks have been touched in pre-9.0 code and are in a state they could never get into in 9.0, we do still have to handle a value of btpo.xact == FrozenTransactionId. I will add a special case to the handling of XLOG_BTREE_REUSE_PAGE records also to allow for that. Any similar theoretical issues would be most welcome if reported. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing cvs HEAD - HS/SR - PANIC: cannot make new WAL entries during recovery
On Thu, 2010-02-18 at 23:13 +0100, Erik Rijkers wrote: I ran this by accident Good test, thanks for running it. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Merge join and index scan strangeness
Hi! I found something strange with merge join. Let there are two table (http://www.sigaev.ru/misc/ex.sql.gz, 360Kb) t1 and t2, both without indexes. Query is: UPDATE t1 SET f1 = t1.f1 || t2.f1 FROM t2 WHERE t2.f1 = t1.f1 AND t2.f2 = t1.f2 AND t2.f3 = t1.f3 AND t2.f4 = t1.f4 ; I forbid everything except merge join and index scan, so explain gives: set enable_hashjoin=off; set enable_nestloop=off; set enable_seqscan=off; set enable_bitmapscan=off; Merge Join (cost=2035240.26..2388197.90 rows=14024070 width=82) Merge Cond: ((t2.f1 = t1.f1) AND (t2.f2 = t1.f2) AND (t2.f3 = t1.f3) AND (t2.f4 = t1.f4)) - Sort (cost=140.69..142.19 rows=600 width=59) Sort Key: t2.f1, t2.f2, t2.f3, t2.f4 - Seq Scan on t2 (cost=100.00..113.00 rows=600 width=59) - Materialize (cost=1035199.57..1038135.06 rows=234839 width=65) - Sort (cost=1035199.57..1035786.67 rows=234839 width=65) Sort Key: t1.f1, t1.f2, t1.f3, t1.f4 - Seq Scan on t1 (cost=100.00..1005017.39 rows=234839 width=65) All looks good at this point. Create index on suggested by merge join columns: CREATE INDEX i1 ON t1 (f1, f2, f3, f4); CREATE INDEX i2 ON t2 (f1, f2, f3, f4); And explain: Merge Join (cost=49897.68..402855.32 rows=14024070 width=82) Merge Cond: ((t2.f4 = t1.f4) AND (t2.f1 = t1.f1) AND (t2.f2 = t1.f2) AND (t2.f3 = t1.f3)) - Sort (cost=90.81..92.31 rows=600 width=59) Sort Key: t2.f4, t2.f1, t2.f2, t2.f3 - Index Scan using i2 on t2 (cost=0.00..63.13 rows=600 width=59) - Materialize (cost=49806.86..52742.35 rows=234839 width=65) - Sort (cost=49806.86..50393.96 rows=234839 width=65) Sort Key: t1.f4, t1.f1, t1.f2, t1.f3 - Index Scan using i1 on t1 (cost=0.00..19624.68 rows=234839 width=65) Merge join chooses another order of fields! It seems to me that index scan with sort should be slower than pure index scan. Ok, add another indexes with suggested column's order: CREATE INDEX i11 ON t1 (f4, f1, f2, f3); CREATE INDEX i21 ON t2 (f4, f1, f2, f3); Explain: Merge Join (cost=90.81..372665.64 rows=14024070 width=82) Merge Cond: ((t1.f1 = t2.f1) AND (t1.f2 = t2.f2) AND (t1.f3 = t2.f3) AND (t1.f4 = t2.f4)) - Index Scan using i1 on t1 (cost=0.00..19624.68 rows=234839 width=65) - Sort (cost=90.81..92.31 rows=600 width=59) Sort Key: t2.f1, t2.f2, t2.f3, t2.f4 - Index Scan using i21 on t2 (cost=0.00..63.13 rows=600 width=59) Megre join uses index scan but for table t2 it uses wrong index! And again index scan + sort instead of index scan. Am I miss something or misunderstand? -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merge join and index scan strangeness
I found something strange with merge join. Let there are two table Sorry, postgresql's version is 8.4 from today CVS -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
David E. Wheeler wrote: On Feb 18, 2010, at 8:38 PM, Tom Lane wrote: The regression test in the core is targeting only its version, but some external projects have version-independent tests. I think it's more like are under the fond illusion that their tests are version-independent. Are we going to back out the next incompatible change we choose to make as soon as somebody notices that it breaks a third-party test case? I don't think so. Let me point out that choosing to install plpgsql by default has already broken --single restore of practically every pg_dump out there. Nobody batted an eye about that. Why are we suddenly so concerned about its effects on unnamed test suites? Because it's a lot easier for `pg_regress --load-language=plpgsql` to mean ensure the language is installed than it is for 3rd-party test suites to detect what version they're being installed against. Why doesn't the Makefile running the tests simply avoid adding --load-language when the version is higher than 9.0? Shouldn't be a hard test to write. We have $(MAJORVERSION) to help with this. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merge join and index scan strangeness
Teodor Sigaev wrote: I found something strange with merge join. Let there are two table Sorry, postgresql's version is 8.4 from today CVS For what it's worth - 8.4.0 gives as expected. aap=# explain UPDATE t1 SET f1 = t1.f1 || t2.f1 FROM t2 WHERE t2.f1 = t1.f1 AND t2.f2 = t1.f2 AND t2.f3 = t1.f3 AND t2.f4 = t1.f4 ; QUERY PLAN - Merge Join (cost=0.00..28522.60 rows=1 width=142) Merge Cond: ((t1.f1 = t2.f1) AND (t1.f2 = t2.f2) AND (t1.f3 = t2.f3) AND (t1.f4 = t2.f4)) - Index Scan using i1 on t1 (cost=0.00..26090.94 rows=234839 width=110) - Index Scan using i2 on t2 (cost=0.00..77.25 rows=600 width=104) (4 rows) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
On Thu, Feb 18, 2010 at 08:31:05PM -0600, David Christensen wrote: On Feb 18, 2010, at 2:19 PM, Pierre C wrote: What about catching the error in the application and INSERT'ing into the current preprepare.relation table? The aim would be to do that in dev or in pre-prod environments, then copy the table content in production. Yep, but it's a bit awkward and time-consuming, and not quite suited to ORM-generated requests since you got to generate all the plan names, when the SQL query itself would be the most convenient unique identifier... A cool hack would be something like that : pg_execute( SELECT ..., arguments... ) By inserting a hook which calls a user-specified function on non-existing plan instead of raising an error, this could work. However, this wouldn't work as-is since the plan name must be = NAMEDATALEN, but you get the idea ;) How about the SHA1 hash of the query? Hey, it works for git... :-) Regards, David -- David Christensen End Point Corporation da...@endpoint.com Hi David, Not to beat out own drum, but we already include a hashing function that can be used for this purpose and is much faster than SHA-1. We would want to use all 64-bits for this use instead of just the 32-bits we currently use for the internal DB hashing. Here is an article comparing the Jenkins' Hash (the one we use) and SHA-1: http://home.comcast.net/~bretm/hash/ Regards, Ken -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merge join and index scan strangeness
Teodor Sigaev teo...@sigaev.ru writes: I found something strange with merge join. Let there are two table Sorry, postgresql's version is 8.4 from today CVS Can't reproduce it here, either in HEAD or 8.4. Sure you have a clean build with no local modifications? The outright-incorrect last plan you show seems to indicate something rather badly wrong with pathkey matching. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Fwd: Re: [HACKERS] Merge join and index scan strangeness
Can't reproduce it here, either in HEAD or 8.4. Sure you have a clean build with no local modifications? The outright-incorrect last plan Sure, just checkout'ed sources. And it's reproduced on current HEAD right now. More info: postgresql.conf changes: shared_buffers=128MB temp_buffers=16MB work_mem=16MB maintenance_work_mem=256MB effective_cache_size=1024MB autovacuum=on log_destination=syslog % pg_config --configure '--enable-depend' '--enable-cassert' '--enable-debug' '--with-perl' 'CFLAGS=-O0' % gcc -v Using built-in specs. Target: i386-undermydesk-freebsd Configured with: FreeBSD/i386 system compiler Thread model: posix gcc version 4.2.1 20070719 [FreeBSD] % uname -a FreeBSD XXX.XXX 8.0-RELEASE-p1 FreeBSD 8.0-RELEASE-p1 #1: Fri Dec 11 19:03:49 MSK 2009 teo...@xxx.xxx:/usr/obj/usr/src/sys/XOR i386 With gcc version 4.4.3 bug is reproduces too. Could you point me a starting point of investigation? -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing cvs HEAD - HS/SR - PANIC: cannot make new WAL entries during recovery
On Thu, February 18, 2010 23:32, Andres Freund wrote:
On Thursday 18 February 2010 22:25:35 Erik Rijkers wrote:
localhost:55432 = 8.4.2 instance (ssh tunnel)
/tmp:7575 = a 9.0devel standby
time pg_dump -h localhost -p 55432 -t public.tab_jobs --clean --no-owner
--no-privileges ms | psql -q -h /tmp -p 7575 -d replicas
ERROR: transaction is read-only
[...]
PANIC: cannot make new WAL entries during recovery
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
[...]
STATEMENT: ALTER SEQUENCE tab_jobs_id_seq OWNED BY tab_jobs.id;
PANIC: cannot make new WAL entries during recovery
STATEMENT: SELECT pg_catalog.setval('tab_jobs_id_seq', 31907, true);
Hm, yea. setval() simply misses a check there (it was added for nextval
though).
I wonder if there are other functions bypassing the layers like
setval/nextval?
Trivial patch attached.
That worked - and I see has already been committed, thanks.
There is another small issue related to this dumping into a readonly slave:
With multiline columns (at least, I think that's the reason),
there are on STDERR endless occurrences of:
invalid command \N
and
invalid command \
There can be millions of these lines (obviously).
On top of that, the logfile gets appended
with 'syntax error', follwed by the whole dump:
ERROR: syntax error at or near 18726 at character 1
STATEMENT: 18726 16883 [...] [whole dump follows here]
This does maybe not really qualify as a bug, but it does
seem unnecessary to spam a readonly standby with all
this. (come to think of it, 'syntax error' is a bit off in any case)
Thanks,
Erik Rijkers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Feb 19, 2010, at 5:36 AM, Alvaro Herrera wrote: Because it's a lot easier for `pg_regress --load-language=plpgsql` to mean ensure the language is installed than it is for 3rd-party test suites to detect what version they're being installed against. Why doesn't the Makefile running the tests simply avoid adding --load-language when the version is higher than 9.0? Shouldn't be a hard test to write. We have $(MAJORVERSION) to help with this. Usually PGXS loads after setting all the environment variables, though I suspect that it wouldn't have any side effects to set regress_opts afterward. Also, there is no MAJORVERSION in earlier versions, so module authors would have to work around that. Basically though, you're asking all third party module authors who depend on plpgsql in their code and/or tests to modify their makefiles and release new versions to work around something that pg_regress could have fixed internally in 1-2 lines of code and be done with it. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merge join and index scan strangeness
On Fri, 19 Feb 2010, Tom Lane wrote: Teodor Sigaev teo...@sigaev.ru writes: I found something strange with merge join. Let there are two table Sorry, postgresql's version is 8.4 from today CVS Can't reproduce it here, either in HEAD or 8.4. Sure you have a clean build with no local modifications? The outright-incorrect last plan you show seems to indicate something rather badly wrong with pathkey matching. I reproduced on my machine PostgreSQL 8.4.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Ubuntu 4.4.1-4ubuntu9) 4.4.1, 64-bit Notice, plan is different and index scan on t1 uses wrong index. Merge Join (cost=45224.01..251225.22 rows=9760080 width=86) (actual time=1687.545..1687.545 rows=0 loops=1) Merge Cond: ((t2.f1 = t1.f1) AND (t2.f2 = t1.f2) AND (t2.f3 = t1.f3) AND (t2.f4 = t1.f4)) - Index Scan using i2 on t2 (cost=0.00..65.44 rows=600 width=59) (actual time=0.008..0.179 rows=600 loops=1) - Sort (cost=45224.01..45811.10 rows=234839 width=69) (actual time=1612.586..1645.436 rows=161842 loops=1) Sort Key: t1.f1, t1.f2, t1.f3, t1.f4 Sort Method: external sort Disk: 20888kB - Index Scan using i11 on t1 (cost=0.00..24274.83 rows=234839 width=69) (actual time=0.637..137.659 rows=234839 loops=1) Total runtime: 1969.029 ms (8 rows) Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] alpha4 bundled -- please verify
Alpha4 has been bundled and is available at http://developer.postgresql.org/~petere/alpha/ Please check that it is sane. Since I'll be away for the next few days, someone has to take it from here: write announcement, move tarballs, send announcement. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Feb 19, 2010, at 7:43 AM, David E. Wheeler wrote:
Usually PGXS loads after setting all the environment variables, though I
suspect that it wouldn't have any side effects to set regress_opts afterward.
Also, there is no MAJORVERSION in earlier versions, so module authors would
have to work around that.
Basically though, you're asking all third party module authors who depend on
plpgsql in their code and/or tests to modify their makefiles and release new
versions to work around something that pg_regress could have fixed internally
in 1-2 lines of code and be done with it.
I'm sure this is bad C and should do a case-insensitive comparison, but this is
essentially what I mean:
*** a/src/test/regress/pg_regress.c
--- b/src/test/regress/pg_regress.c
*** create_database(const char *dbname)
*** 1795,1802
*/
for (sl = loadlanguage; sl != NULL; sl = sl-next)
{
! header(_(installing %s), sl-str);
! psql_command(dbname, CREATE LANGUAGE \%s\, sl-str);
}
}
--- 1795,1804
*/
for (sl = loadlanguage; sl != NULL; sl = sl-next)
{
! if (sl-str != plpgsql) {
! header(_(installing %s), sl-str);
! psql_command(dbname, CREATE LANGUAGE \%s\, sl-str);
! }
}
}
Does that seem unreasonable?
Best,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Merge join and index scan strangeness
Poking a bit deeper, it *does* think the plan with sorts is cheaper than without. The mergejoin plan it really prefers is: regression=# set enable_hashjoin TO 0; SET regression=# set enable_nestloop TO 0; SET regression=# explain ... QUERY PLAN --- Update (cost=41.69..379234.38 rows=14225817 width=88) - Merge Join (cost=41.69..379234.38 rows=14225817 width=88) Merge Cond: ((t1.f1 = t2.f1) AND (t1.f2 = t2.f2) AND (t1.f3 = t2.f3) AND (t1.f4 = t2.f4)) - Index Scan using i1 on t1 (cost=0.00..21198.88 rows=234839 width=65) - Sort (cost=41.69..43.19 rows=600 width=65) Sort Key: t2.f1, t2.f2, t2.f3, t2.f4 - Seq Scan on t2 (cost=0.00..14.00 rows=600 width=65) (7 rows) but if you force it to use indexscans: regression=# set enable_seqscan TO 0; SET regression=# explain ... QUERY PLAN --- Update (cost=52001.84..410457.60 rows=14225817 width=88) - Merge Join (cost=52001.84..410457.60 rows=14225817 width=88) Merge Cond: ((t1.f3 = t2.f3) AND (t1.f1 = t2.f1) AND (t1.f2 = t2.f2) AND (t1.f4 = t2.f4)) - Sort (cost=51783.56..52370.66 rows=234839 width=65) Sort Key: t1.f3, t1.f1, t1.f2, t1.f4 - Index Scan using i1 on t1 (cost=0.00..21198.88 rows=234839 width=65) - Sort (cost=93.12..94.62 rows=600 width=65) Sort Key: t2.f3, t2.f1, t2.f2, t2.f4 - Index Scan using i2 on t2 (cost=0.00..65.44 rows=600 width=65) (9 rows) and then without sorts: regression=# set enable_sort TO 0; SET regression=# explain ... QUERY PLAN --- Update (cost=0.00..483609.37 rows=14225817 width=88) - Merge Join (cost=0.00..483609.37 rows=14225817 width=88) Merge Cond: ((t2.f1 = t1.f1) AND (t2.f2 = t1.f2) AND (t2.f3 = t1.f3) AND (t2.f4 = t1.f4)) - Index Scan using i2 on t2 (cost=0.00..65.44 rows=600 width=65) - Materialize (cost=0.00..23547.27 rows=234839 width=65) - Index Scan using i1 on t1 (cost=0.00..21198.88 rows=234839 width=65) (6 rows) Note that the join cost is way higher than the sum of the input costs in all three cases. The reason for that is that it's expecting a whole lot of rescanning of the inner relation due to duplicate merge keys. This means that a bare inner indexscan is going to be penalized very heavily for refetches, whereas plans with either sort or materialize in between look better because the refetch cost is very low. So that's how a plan with a sort can be preferred to one without. I think the weird looking choices of sort order may just be randomness because all sort orders cost the same once it decides to sort. However, even given that, it's odd that it prefers a plan with two sorts to a plan with one materialize. Poking around in costsize.c, I think that the reason for this is that the rescan cost of a sort is estimated at cpu_operator_cost per tuple, whereas rescanning a materialize node is being estimated at cpu_tuple_cost per tuple. For a plan where rescan cost is the dominant factor, that matters. We probably ought to make those two estimates the same. Since neither plan node type does any projection or qual checking, the lower number is probably the better choice. BTW, the real bottom line here is that mergejoin is a crummy plan choice when there are so few distinct join key values. The planner would never have picked any of these plans if you hadn't forced it to. So I'm not sure how important this is in the real world. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
Pierre C li...@peufeu.com writes: Yep, but it's a bit awkward and time-consuming, and not quite suited to ORM-generated requests since you got to generate all the plan names, when the SQL query itself would be the most convenient unique identifier... The SHA1 proposal seems better to me. Now you still have to fill the table with statements and names, and I stand on the opinion that having a development-only layer in the ORM to do just that is better. That's supposing you don't generate so many different normalized queries that you can't generate them all in dev or from the unit tests… A cool hack would be something like that : pg_execute( SELECT ..., arguments... ) By inserting a hook which calls a user-specified function on non-existing plan instead of raising an error, this could work. This I'm not helping with. It's definitely not the same skills and time requirements as offering a simple function based alternative to the table lookup in preprepare, in the load them all phase. However, this wouldn't work as-is since the plan name must be = NAMEDATALEN, but you get the idea ;) SHA1 or even MD5 would do, the later having the advantage of being already supported by PostgreSQL core. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER ROLE/DATABASE RESET ALL versus security
Tom Lane wrote: It looks to me like the code in AlterSetting() will allow an ordinary user to blow away all settings for himself. Even those that are for SUSET variables and were presumably set for him by a superuser. Isn't this a security hole? I would expect that an unprivileged user should not be able to change such settings, not even to the extent of reverting to the installation-wide default. Yes, it is, but this is not a new hole. This works just fine in 8.4 too: alvherre=# create role foo; CREATE ROLE alvherre=# alter role foo set lc_messages = 'C'; ALTER ROLE alvherre=# set session AUTHORIZATION foo; SET alvherre= show lc_messages ; lc_messages - es_CL.UTF-8 (1 fila) alvherre= alter role foo reset all; ALTER ROLE alvherre= reset session AUTHORIZATION ; RESET alvherre=# set session AUTHORIZATION foo; SET alvherre= show lc_messages ; lc_messages - es_CL.UTF-8 (1 fila) alvherre= alter role foo set lc_messages to 'C'; ERROR: se ha denegado el permiso para cambiar la opción «lc_messages» So any user is able to reset settings that were set for him by the superuser. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER ROLE/DATABASE RESET ALL versus security
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: It looks to me like the code in AlterSetting() will allow an ordinary user to blow away all settings for himself. Even those that are for SUSET variables and were presumably set for him by a superuser. Isn't this a security hole? I would expect that an unprivileged user should not be able to change such settings, not even to the extent of reverting to the installation-wide default. Yes, it is, but this is not a new hole. This works just fine in 8.4 too: So I'd argue for changing it in 8.4 too. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Thu, Feb 18, 2010 at 11:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp writes: David Fetter da...@fetter.org wrote: support both pre-9.0 and post-9.0 PostgreSQLs. David Wheeler has suggested that we special-case PL/pgsql for 9.0 and greater, as it's in template0, where those tests are based. +1 for the CREATE LANGUAGE IF NOT EXISTS behavior. The regression test in the core is targeting only its version, but some external projects have version-independent tests. I think it's more like are under the fond illusion that their tests are version-independent. Are we going to back out the next incompatible change we choose to make as soon as somebody notices that it breaks a third-party test case? I don't think so. Let me point out that choosing to install plpgsql by default has already broken --single restore of practically every pg_dump out there. Nobody batted an eye about that. Why are we suddenly so concerned about its effects on unnamed test suites? I am still of the opinion that changing this was a bad idea for exactly this reason. We could perhaps ameliorate this problem by implementing CREATE OR REPLACE for languages and emitting that instead; then the command in the dump would be a noop. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Fri, Feb 19, 2010 at 01:34:46PM -0500, Robert Haas wrote:
On Thu, Feb 18, 2010 at 11:38 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp writes:
David Fetter da...@fetter.org wrote:
support both pre-9.0 and post-9.0 PostgreSQLs. David Wheeler has
suggested that we special-case PL/pgsql for 9.0 and greater, as it's
in template0, where those tests are based.
+1 for the CREATE LANGUAGE IF NOT EXISTS behavior.
The regression test in the core is targeting only its version,
but some external projects have version-independent tests.
I think it's more like are under the fond illusion that their
tests are version-independent. Are we going to back out the next
incompatible change we choose to make as soon as somebody notices
that it breaks a third-party test case? I don't think so. Let me
point out that choosing to install plpgsql by default has already
broken --single restore of practically every pg_dump out there.
Nobody batted an eye about that. Why are we suddenly so
concerned about its effects on unnamed test suites?
I am still of the opinion that changing this was a bad idea for
exactly this reason. We could perhaps ameliorate this problem by
implementing CREATE OR REPLACE for languages and emitting that
instead; then the command in the dump would be a noop.
CREATE OR REPLACE LANGUAGE is an even bigger tar pit.
For example:
http://archives.postgresql.org/pgsql-hackers/2009-10/msg00386.php
Please find attached a patch which does this check in pg_regress.
Cheers,
David.
--
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c
index 991bb17..45aad00 100644
--- a/src/test/regress/pg_regress.c
+++ b/src/test/regress/pg_regress.c
@@ -1795,8 +1795,12 @@ create_database(const char *dbname)
*/
for (sl = loadlanguage; sl != NULL; sl = sl-next)
{
- header(_(installing %s), sl-str);
- psql_command(dbname, CREATE LANGUAGE \%s\, sl-str);
+ if ((pg_strncasecmp(sl-str, plpgsql, sizeof(plpgsql)) !=
0)
+ (pg_strncasecmp(sl-str, pl/pgsql,
sizeof(pl/pgsql)) != 0))
+ {
+ header(_(installing %s), sl-str);
+ psql_command(dbname, CREATE LANGUAGE \%s\, sl-str);
+ }
}
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER ROLE/DATABASE RESET ALL versus security
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: It looks to me like the code in AlterSetting() will allow an ordinary user to blow away all settings for himself. Even those that are for SUSET variables and were presumably set for him by a superuser. Isn't this a security hole? I would expect that an unprivileged user should not be able to change such settings, not even to the extent of reverting to the installation-wide default. Yes, it is, but this is not a new hole. This works just fine in 8.4 too: So I'd argue for changing it in 8.4 too. Understood. I'm starting to look at what this requires. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
Robert Haas robertmh...@gmail.com writes: On Thu, Feb 18, 2010 at 11:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: ... Let me point out that choosing to install plpgsql by default has already broken --single restore of practically every pg_dump out there. Nobody batted an eye about that. Why are we suddenly so concerned about its effects on unnamed test suites? I am still of the opinion that changing this was a bad idea for exactly this reason. We could perhaps ameliorate this problem by implementing CREATE OR REPLACE for languages and emitting that instead; then the command in the dump would be a noop. Not really going to help for existing dumps (nor future dumps made with pre-9.0 pg_dump versions). However, the case that is probably going to be the most pressing is pg_upgrade, which last I heard insists on no errors during the restore (and I think that's a good thing). That uses the new version's pg_dump so a fix involving new syntax would cover it. Did we have consensus on exactly what CREATE OR REPLACE LANGUAGE would do? Particularly in cases where the existing definition doesn't match pg_pltemplate? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Fri, Feb 19, 2010 at 1:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: Did we have consensus on exactly what CREATE OR REPLACE LANGUAGE would do? Particularly in cases where the existing definition doesn't match pg_pltemplate? I am of the opinion that any CREATE OR REPLACE command that completes without error should result in exactly the same final state that would have resulted had the object not existed when the command was issued. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
David Fetter da...@fetter.org writes: CREATE OR REPLACE LANGUAGE is an even bigger tar pit. http://archives.postgresql.org/pgsql-hackers/2009-10/msg00386.php The reason that patch got rejected was that it was implementing CREATE IF NOT EXISTS --- under a false name. The problem with that is summarized here: http://archives.postgresql.org/pgsql-patches/2008-03/msg00416.php It wouldn't be that hard to implement actual CREATE OR REPLACE if we decide that's the most useful solution here. The code would need to be prepared to use heap_update instead of heap_insert, and to get rid of old dependencies, but there is plenty of precedent for that. The sticking point for me is still whether or not it's really a good idea for pg_dump to be emitting CREATE OR REPLACE LANGUAGE. It does not do that for any other object type. On the other hand, we've already made languages a special case in pg_dump, since it emits the abbreviated form of CREATE LANGUAGE in most cases rather than trying to duplicate the existing object definition. Maybe there wouldn't be any bad results in practice. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
Tom Lane t...@sss.pgh.pa.us writes: Robert Haas robertmh...@gmail.com writes: I am still of the opinion that changing this was a bad idea for exactly this reason. We could perhaps ameliorate this problem by implementing CREATE OR REPLACE for languages and emitting that instead; then the command in the dump would be a noop. Not really going to help for existing dumps (nor future dumps made with pre-9.0 pg_dump versions). However, the case that is probably going to be the most pressing is pg_upgrade, which last I heard insists on no errors during the restore (and I think that's a good thing). That uses the new version's pg_dump so a fix involving new syntax would cover it. Not sure how helpful I'll be there, but I can't help placing the extension's proposal again. If we had extensions here, plpgsql would be a core maintained extension, made available by CREATE EXTENSION in the database (which initdb would do in templates), then have the language installed by means of issuing an INSTALL EXTENSION command. Now, what would help would be to have that support and have CREATE LANGUAGE foo; be kept for compatibility only and issue INSTALL EXTENSION foo; instead. For those still with me, the choice to have plpgsql available by default would then boil down to have initdb do the CREATE EXTENSION in the template database, the database owner would still have to run the INSTALL EXTENSION. So now --load-language is INSTALL EXTENSION and just works as intended. And older dumps are doing CREATE LANGUAGE plpgsql; which is converted to INSTALL EXTENSION plpgsql;, which just works only because the extension is made available by default. So that if there's a CREATE LANGUAGE plpythonu, say, installing this extension will only succeed when INSTALL EXTENSION plpythonu; has been done either in the template1 database before creating the target database, or in the target database itself. So now we have smart success and failure modes falling from the proposed model. I'll dare not say Hope This Helps as I realize I failed to provide any code for implementing the extension management proposal. But got back to acceptable sleeping patterns and should be able to get back on the topic later this year, unless (please) beaten to it :) Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
Dimitri Fontaine dfonta...@hi-media.com writes: Not sure how helpful I'll be there, but I can't help placing the extension's proposal again. If we had extensions here, plpgsql would be a core maintained extension, made available by CREATE EXTENSION in the database (which initdb would do in templates), then have the language installed by means of issuing an INSTALL EXTENSION command. Well, that isn't really going to help us in terms of what to do for 9.0. But the possibility that something like this might happen in future is one thing that makes me hesitant about extending CREATE LANGUAGE right now --- the more bells and whistles we put on it, the harder it will be to have a clean upgrade to an EXTENSION facility. One thing that strikes me about your proposal is that INSTALL EXTENSION doesn't sound like a CREATE OR REPLACE operation. It sounds like a CREATE IF NOT EXISTS operation, because there simply is not a guarantee that what gets installed is exactly what the user expected --- in particular, for pg_dump, it isn't guaranteeing that the new version's extension is exactly like what was in the old database. And that's not a bad thing, in this context; it's more or less the Whole Point. However it still leaves us with the problem that CINE is underspecified. In particular, since we have already got the notion that languages have owners and ACLs, I'm unsure what the desired state is when pg_dump tries to set the owner and/or ACL for a pre-existing language. I know what is likely to happen if we just drop these concepts into the existing system: restoring a dump will take away ownership from whoever installed the language (extension) previously. That doesn't seem very good, especially if the ownership of any SQL objects contained in the extension doesn't change. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fast or immediate shutdown
Simon Riggs wrote: On Wed, 2009-12-16 at 17:04 +0200, Peter Eisentraut wrote: On tis, 2009-12-15 at 17:19 +, Simon Riggs wrote: running with log_checkpoints = on pg_ctl -D foo -m fast stop log says LOG: received fast shutdown request LOG: aborting any active transactions LOG: shutting down LOG: restartpoint starting: shutdown immediate Some of us know that the immediate word refers to the restartpoint request, though that word causes conceptual conflict with the shutdown mode, which was fast, not immediate. Think we need to change the wording of this LOG: restartpoint starting: shutdown immediate so it is clearer what we mean We *do* we mean? And why are we logging it? The words after the colon refer to options sent to RequestCheckpoint and it is logged because we asked for it by doing log_checkpoints = on. I suggest we say smoothed when checkpoint option is !immediate. So we will remove the word immediate from the message. Did we decide not the change this? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
On Fri, Feb 19, 2010 at 2:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: David Fetter da...@fetter.org writes: CREATE OR REPLACE LANGUAGE is an even bigger tar pit. http://archives.postgresql.org/pgsql-hackers/2009-10/msg00386.php The reason that patch got rejected was that it was implementing CREATE IF NOT EXISTS --- under a false name. The problem with that is summarized here: http://archives.postgresql.org/pgsql-patches/2008-03/msg00416.php It wouldn't be that hard to implement actual CREATE OR REPLACE if we decide that's the most useful solution here. The code would need to be prepared to use heap_update instead of heap_insert, and to get rid of old dependencies, but there is plenty of precedent for that. The sticking point for me is still whether or not it's really a good idea for pg_dump to be emitting CREATE OR REPLACE LANGUAGE. It does not do that for any other object type. On the other hand, we've already made languages a special case in pg_dump, since it emits the abbreviated form of CREATE LANGUAGE in most cases rather than trying to duplicate the existing object definition. Maybe there wouldn't be any bad results in practice. We have all sorts of crufty hacks in pg_dump and the backend to cope with restoration of older dumps. Compared to some of those, this is going to be cleaner than newfallen snow. IMHO, anyway. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
Robert Haas robertmh...@gmail.com writes: On Fri, Feb 19, 2010 at 2:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: The sticking point for me is still whether or not it's really a good idea for pg_dump to be emitting CREATE OR REPLACE LANGUAGE. It does not do that for any other object type. On the other hand, we've already made languages a special case in pg_dump, since it emits the abbreviated form of CREATE LANGUAGE in most cases rather than trying to duplicate the existing object definition. Maybe there wouldn't be any bad results in practice. We have all sorts of crufty hacks in pg_dump and the backend to cope with restoration of older dumps. Compared to some of those, this is going to be cleaner than newfallen snow. IMHO, anyway. What worries me about it is mainly the prospect that restoring a dump would silently change ownership and/or permissions of a pre-existing language. Maybe we can live with that but it's a bit nervous making. One thing we could do that would help limit the damage is have pg_dump only insert OR REPLACE when it's emitting a parameterless CREATE LANGUAGE, ie, it's already depending on there to be a pg_pltemplate entry. This would guarantee that we aren't changing any of the core properties of the pg_language entry (since, because of the way CREATE LANGUAGE already works, any pre-existing entry must match the pg_pltemplate entry). But there's still ownership and ACL to worry about. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGXS: REGRESS_OPTS=--load-language=plpgsql
Tom Lane t...@sss.pgh.pa.us writes: Well, that isn't really going to help us in terms of what to do for 9.0. But the possibility that something like this might happen in future is one thing that makes me hesitant about extending CREATE LANGUAGE right now --- the more bells and whistles we put on it, the harder it will be to have a clean upgrade to an EXTENSION facility. Agreed, but we could still evolve the command with keeping an eye on the future. As of now I intend to implement what's on this page: http://wiki.postgresql.org/wiki/ExtensionPackaging So maybe a quick glance then some early design approval would make it possible to change the CREATE LANGUAGE in an EXTENSION compatible way. One thing that strikes me about your proposal is that INSTALL EXTENSION doesn't sound like a CREATE OR REPLACE operation. It sounds like a CREATE IF NOT EXISTS operation, because there simply is not a guarantee that what gets installed is exactly what the user expected --- in particular, for pg_dump, it isn't guaranteeing that the new version's extension is exactly like what was in the old database. And that's not a bad thing, in this context; it's more or less the Whole Point. In fact it's not either one or the other, because the CREATE EXTENSION is providing the meta data, which includes an optional upgrade function. So if you INSTALL EXTENSION over an existing one, and meantime you've been installing the new version (file system install, PGAN or distro packaged or source level install; then the new CREATE EXTENSION which should be given in the foo.sql for the foo EXTENSION), in this case it's an upgrade, and what INSTALL EXTENSION is meant to do is run the upgrade function with as arguments current and new version numbers. It's when the EXTENSION is not providing this upgrade function that the behavior is more CREATE OR REPLACE, because it'd then run the installation script all over again. In case you provided an upgrade function, we're yet to see how to provide facilities to the extensions authors in order to easily address the columns of their data type and the indexes from their operator classes, etc. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fast or immediate shutdown
On Fri, Feb 19, 2010 at 2:48 PM, Bruce Momjian br...@momjian.us wrote: Simon Riggs wrote: On Wed, 2009-12-16 at 17:04 +0200, Peter Eisentraut wrote: On tis, 2009-12-15 at 17:19 +, Simon Riggs wrote: running with log_checkpoints = on pg_ctl -D foo -m fast stop log says LOG: received fast shutdown request LOG: aborting any active transactions LOG: shutting down LOG: restartpoint starting: shutdown immediate Some of us know that the immediate word refers to the restartpoint request, though that word causes conceptual conflict with the shutdown mode, which was fast, not immediate. Think we need to change the wording of this LOG: restartpoint starting: shutdown immediate so it is clearer what we mean We *do* we mean? And why are we logging it? The words after the colon refer to options sent to RequestCheckpoint and it is logged because we asked for it by doing log_checkpoints = on. I suggest we say smoothed when checkpoint option is !immediate. So we will remove the word immediate from the message. Did we decide not the change this? Personally, my opinion is that if we're going to print the message at all, the names used for the message should match the names used in the code. So -1 from me on calling it immediate in the code but smoothed in the message. On the other hand, I have no personal attachment to that message, so if other people feel it's not needed at all, I could see removing it. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] lock_timeout GUC patch
Hi, Tom Lane írta: Boszormenyi Zoltan z...@cybertec.at writes: You expressed stability concerns coming from this patch. Were these concerns because of locks timing out making things fragile or because of general feelings about introducing such a patch at the end of the release cycle? I was thinking about the former, hence this modification. Indeed, I am *very* concerned about the stability implications of this patch. I just don't believe that arbitrarily restricting which processes the GUC applies to will make it any safer. regards, tom lane Okay, here is the rewritten lock_timeout GUC patch that uses setitimer() to set the timeout for lock timeout. I removed the GUC assignment/validation function. I left the current statement timeout vs deadlock timeout logic mostly intact in enable_sig_alarm(), because it's used by a few places. The only change is that statement_fin_time is always computed there because the newly introduced function (enable_sig_alarm_for_lock_timeout()) checks it to see whether the lock timeout triggers earlier then the deadlock timeout. As it was discussed before, this is 9.1 material. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ diff -dcrpN pgsql.orig/doc/src/sgml/config.sgml pgsql/doc/src/sgml/config.sgml *** pgsql.orig/doc/src/sgml/config.sgml 2010-02-17 10:05:40.0 +0100 --- pgsql/doc/src/sgml/config.sgml 2010-02-19 11:29:18.0 +0100 *** COPY postgres_log FROM '/full/path/to/lo *** 4240,4245 --- 4240,4269 /listitem /varlistentry + varlistentry id=guc-lock-timeout xreflabel=lock_timeout + termvarnamelock_timeout/varname (typeinteger/type)/term + indexterm +primaryvarnamelock_timeout/ configuration parameter/primary + /indexterm + listitem +para + Abort any statement that tries to acquire a heavy-weight lock (e.g. rows, + pages, tables, indices or other objects) and the lock has to wait more + than the specified number of milliseconds, starting from the time the + command arrives at the server from the client. + If varnamelog_min_error_statement/ is set to literalERROR/ or lower, + the statement that timed out will also be logged. A value of zero + (the default) turns off the limitation. +/para + +para + Setting varnamelock_timeout/ in + filenamepostgresql.conf/ is not recommended because it + affects all sessions. +/para + /listitem + /varlistentry + varlistentry id=guc-vacuum-freeze-table-age xreflabel=vacuum_freeze_table_age termvarnamevacuum_freeze_table_age/varname (typeinteger/type)/term indexterm diff -dcrpN pgsql.orig/doc/src/sgml/ref/lock.sgml pgsql/doc/src/sgml/ref/lock.sgml *** pgsql.orig/doc/src/sgml/ref/lock.sgml 2009-09-18 08:26:40.0 +0200 --- pgsql/doc/src/sgml/ref/lock.sgml 2010-02-19 11:29:18.0 +0100 *** LOCK [ TABLE ] [ ONLY ] replaceable cla *** 39,46 literalNOWAIT/literal is specified, commandLOCK TABLE/command does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an !error is emitted. Once obtained, the lock is held for the !remainder of the current transaction. (There is no commandUNLOCK TABLE/command command; locks are always released at transaction end.) /para --- 39,49 literalNOWAIT/literal is specified, commandLOCK TABLE/command does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an !error is emitted. If varnamelock_timeout/varname is set to a value !higher than 0, and the lock cannot be acquired under the specified !timeout value in milliseconds, the command is aborted and an error !is emitted. Once obtained, the lock is held for the remainder of !the current transaction. (There is no commandUNLOCK TABLE/command command; locks are always released at transaction end.) /para diff -dcrpN pgsql.orig/doc/src/sgml/ref/select.sgml pgsql/doc/src/sgml/ref/select.sgml *** pgsql.orig/doc/src/sgml/ref/select.sgml 2010-02-13 19:44:33.0 +0100 --- pgsql/doc/src/sgml/ref/select.sgml 2010-02-19 11:29:18.0 +0100 *** FOR SHARE [ OF replaceable class=param *** 1160,1165 --- 1160,1173 /para para + If literalNOWAIT/ option is not specified and varnamelock_timeout/varname + is set to a value higher than 0, and the lock needs to wait
Re: [HACKERS] Merge join and index scan strangeness
I wrote: However, even given that, it's odd that it prefers a plan with two sorts to a plan with one materialize. Poking around in costsize.c, I think that the reason for this is that the rescan cost of a sort is estimated at cpu_operator_cost per tuple, whereas rescanning a materialize node is being estimated at cpu_tuple_cost per tuple. For a plan where rescan cost is the dominant factor, that matters. We probably ought to make those two estimates the same. Since neither plan node type does any projection or qual checking, the lower number is probably the better choice. I've done that in HEAD. I'm loath to touch it in the back branches, though, because the logic in that area now is quite different from what it was in 8.4 and earlier. As I said before, I think this isn't too important in cases where you're not forcing a mergejoin, so it seems better to not risk destabilizing plans in released branches. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Directory fsync and other fun
Hi all,
I started setting up some halfway automated method of simulating hard crashes
and even while setting those up I found some pretty unsettling results...
Now its not unlikely that my testing is flawed but unfortunately I don't see
where right now (its 3am now and I have a 8h trainride behind me, so ...)
The simple testsetup I have till now:
Serverscript:
* setup disk
* start pg
* wait for getting killed
* setup disk
* start pg
Clientside:
* CREATE DATABASE ... TEMPLATE crashtemplate
* CHECKPOINT
* make device readonly not allowing any cache flushes or such (using
devicemapper)
kill server
* connect to database (some of the time it errors here
* select * from $every_table (some time here)
At first pg survived that nicely without any problems. Then I got to my senses
and started adding some background io. Like:
dd if=/dev/zero of=/mnt/test/foobar bs=10M count=1000
Thats where things started failing. All are logs from after the crash:
1:
FATAL: could not read relation mapping file base/140883/pg_filenode.map:
Interrupted system call
DEBUG: autovacuum: processing database postgres
FATAL: could not read relation mapping file base/140883/pg_filenode.map:
Success
DEBUG: autovacuum: processing database postgres
...
FATAL: could not read relation mapping file base/58963/pg_filenode.map: No
such file or directory
2:
FATAL: base/165459 is not a valid data directory
DETAIL: File base/165459/PG_VERSION does not contain valid data.
HINT: You might need to initdb.
3:
You are now connected to database test.
test=# SELECT execute('SELECT * FROM table_'||g.i) FROM generate_series(1,
3000) g(i);
ERROR: XX001: could not read block 0 in file base/124499/11652: read only 0
of 8192 bytes
LOCATION: mdread, md.c:656
(that one I did not see with -o data=ordered,barrier=1,commit=300)
I tried the following mount options/filesystems so far:
-t ext4 -o data=writeback,barrier=1,commit=300,noauto_da_alloc
-t ext4 -o data=writeback,barrier=1,commit=300
-t ext4 -o data=writeback,barrier=0,commit=300
-t ext4 -o data=ordered,barrier=0,commit=300,noauto_da_alloc
-t ext4 -o data=ordered,barrier=1,commit=300,noauto_da_alloc
-t ext4 -o data=ordered,barrier=1,commit=300
The same with s/ext4/ext3/ and with a commit=5. With the latter the errors
were way much harder to reproduce (not that surprisingly) but still occured.
I attached my preliminary scripts/hacks... They even contain a comment or two.
Note though that they are a bit of a loaded gun...
I guess it would be sensible trying to do some more extensive tests on a setup
like that... All I tested till now was create database :-(
Andres
pg_crashtest_client.sh
Description: application/shellscript
pg_crashtest_server.sh
Description: application/shellscript
pg_createtemplate.sh
Description: application/shellscript
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] explain and PARAM_EXEC
Consider the following (rather lame) query: rhaas=# explain (verbose) select (select oid from pg_class a where a.oid = b.relfilenode) from pg_class b; QUERY PLAN Seq Scan on pg_catalog.pg_class b (cost=0.00..2250.22 rows=271 width=4) Output: (SubPlan 1) SubPlan 1 - Index Scan using pg_class_oid_index on pg_catalog.pg_class a (cost=0.00..8.27 rows=1 width=4) Output: a.oid Index Cond: (a.oid = $0) It seems odd to me that we don't display any information about where $0 comes from or how it's initialized. Should we? I believe what's happening is that the sequential scan of b kicks out b.oid, and that then gets yanked into $0 when we invoke the subplan. But you can't really see what's happening. Interestingly, if you contrive to make the sequential scan not the toplevel plan node, then you actually do get to see what it's kicking out: rhaas=# explain (verbose) select (select oid from pg_class a where a.oid = b.oid::integer) from pg_class b, generate_series(1,5); QUERY PLAN Nested Loop (cost=0.00..2245943.89 rows=271000 width=4) Output: (SubPlan 1) - Seq Scan on pg_catalog.pg_class b (cost=0.00..9.71 rows=271 width=4) Output: b.oid - Function Scan on pg_catalog.generate_series (cost=0.00..10.00 rows=1000 width=0) Output: generate_series.generate_series SubPlan 1 - Index Scan using pg_class_oid_index on pg_catalog.pg_class a (cost=0.00..8.27 rows=1 width=4) Output: a.oid Index Cond: (a.oid = ($0)::oid) (10 rows) We can even make it kick out two things: rhaas=# explain (verbose) select (select oid from pg_class a where a.oid = b.oid::integer + b.relfilenode::integer) from pg_class b, generate_series(1,5); QUERY PLAN Nested Loop (cost=0.00..2246621.39 rows=271000 width=8) Output: (SubPlan 1) - Seq Scan on pg_catalog.pg_class b (cost=0.00..9.71 rows=271 width=8) Output: b.oid, b.relfilenode - Function Scan on pg_catalog.generate_series (cost=0.00..10.00 rows=1000 width=0) Output: generate_series.generate_series SubPlan 1 - Index Scan using pg_class_oid_index on pg_catalog.pg_class a (cost=0.00..8.27 rows=1 width=4) Output: a.oid Index Cond: (a.oid = ((($0)::integer + ($1)::integer))::oid) (10 rows) But if we drop the generate_series call we're back in the dark - where's the node that's emitting oid and relfilenode? rhaas=# explain (verbose) select (select oid from pg_class a where a.oid = b.oid::integer + b.relfilenode::integer) from pg_class b; QUERY PLAN Seq Scan on pg_catalog.pg_class b (cost=0.00..2250.90 rows=271 width=8) Output: (SubPlan 1) SubPlan 1 - Index Scan using pg_class_oid_index on pg_catalog.pg_class a (cost=0.00..8.27 rows=1 width=4) Output: a.oid Index Cond: (a.oid = ((($0)::integer + ($1)::integer))::oid) (6 rows) So I guess there are two issues here: (1) somehow I feel like we should be telling the user what expression is being used to initialize $0, $1, etc. when they are PARAM_EXEC parameters; and (2) where does the output list for the sequential scan go when there's only one table involved? This is when you all start explaining to me why I'm woefully confused... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] explain and PARAM_EXEC
Robert Haas robertmh...@gmail.com writes: So I guess there are two issues here: (1) somehow I feel like we should be telling the user what expression is being used to initialize $0, $1, etc. when they are PARAM_EXEC parameters; Maybe, but the only reasonable place to put it would be within the (SubPlan N) reference, which is not a place where verboseness would be appreciated, I think. In principle it could look something like (SubPlan N ($0 := b.oid)) but with a few parameters and a bunch of other stuff on the same line that would get out of hand. and (2) where does the output list for the sequential scan go when there's only one table involved? The (SubPlan N) reference is meant to be understood as an expression element yielding the output of the subplan. One of us is confused, because I don't see any material difference between your examples; they're all calling the subplan in the same way. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] explain and PARAM_EXEC
On Fri, Feb 19, 2010 at 10:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: So I guess there are two issues here: (1) somehow I feel like we should be telling the user what expression is being used to initialize $0, $1, etc. when they are PARAM_EXEC parameters; Maybe, but the only reasonable place to put it would be within the (SubPlan N) reference, which is not a place where verboseness would be appreciated, I think. In principle it could look something like (SubPlan N ($0 := b.oid)) but with a few parameters and a bunch of other stuff on the same line that would get out of hand. I thought maybe it could do something like this: SubPlan 1 Parameters: $0 := b.oid - Index Scan etc. ...but maybe that doesn't work if it can be called with different parameters from different places? Not sure if that's possible. and (2) where does the output list for the sequential scan go when there's only one table involved? The (SubPlan N) reference is meant to be understood as an expression element yielding the output of the subplan. One of us is confused, because I don't see any material difference between your examples; they're all calling the subplan in the same way. I don't think it's a stretch to say that I'm the one who is confused. I am under the (perhaps faulty) impression that when evaluating an expression there can only ever be three tuples in score: inner, outer, and scan. So when we go to evaluate the expression whose result will be assigned to $0, where do we get those inner and/or outer and/or scan tuples from? IOW, I understand where the subplan is putting its OUTPUT, what I don't understand is what context is being used to set its input parameters. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] explain and PARAM_EXEC
On Fri, Feb 19, 2010 at 11:05 PM, Robert Haas robertmh...@gmail.com wrote: I don't think it's a stretch to say that I'm the one who is confused. I am under the (perhaps faulty) impression that when evaluating an expression there can only ever be three tuples in score: inner, outer, s/score/scope. and scan. So when we go to evaluate the expression whose result will be assigned to $0, where do we get those inner and/or outer and/or scan tuples from? IOW, I understand where the subplan is putting its OUTPUT, what I don't understand is what context is being used to set its input parameters. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] explain and PARAM_EXEC
Robert Haas robertmh...@gmail.com writes: On Fri, Feb 19, 2010 at 10:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Maybe, but the only reasonable place to put it would be within the (SubPlan N) reference, I thought maybe it could do something like this: SubPlan 1 Parameters: $0 := b.oid - Index Scan etc. No, that's the wrong end of the stick --- that's like trying to annotate a function definition with the actual parameter values being passed to it from somewhere else. You haven't got the info there, and even if you did, it's assuming that there is exactly one call site for any subplan. I am under the (perhaps faulty) impression that when evaluating an expression there can only ever be three tuples in score: inner, outer, and scan. So when we go to evaluate the expression whose result will be assigned to $0, where do we get those inner and/or outer and/or scan tuples from? IOW, I understand where the subplan is putting its OUTPUT, what I don't understand is what context is being used to set its input parameters. Consider this small mod on your example: regression=# explain (verbose) select oid::int + 1,(select oid from pg_class a where a.oid = b.relfilenode) from pg_class b; QUERY PLAN Seq Scan on pg_catalog.pg_class b (cost=0.00..5573.04 rows=671 width=8) Output: ((b.oid)::integer + 1), (SubPlan 1) SubPlan 1 - Index Scan using pg_class_oid_index on pg_catalog.pg_class a (cost=0.00..8.27 rows=1 width=4) Output: a.oid Index Cond: (a.oid = $0) (6 rows) When we are evaluating the output targetlist of the seqscan node, we have a scan tuple of pg_class b in scope. We can fetch that tuple's oid and use it in the first expression. We can also fetch that tuple's relfilenode and pass it to the subplan, which we do by setting the $0 Param value before invoking the subplan. The subplan runs an indexscan and returns a single scalar value (to wit, a.oid from some row of pg_class a), which becomes the value of the (SubPlan 1) reference back at the evaluation of the seqscan's targetlist. It's really not much different from a function call with subplans as functions. The PARAM_EXEC stuff looks just like 1950's era non-reentrant function parameter passing mechanisms, back before anybody had thought of recursive functions and they passed a function's parameters in fixed storage locations. It's okay for this because subplan trees are never recursive ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] explain and PARAM_EXEC
On Fri, Feb 19, 2010 at 11:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Feb 19, 2010 at 10:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Maybe, but the only reasonable place to put it would be within the (SubPlan N) reference, I thought maybe it could do something like this: SubPlan 1 Parameters: $0 := b.oid - Index Scan etc. No, that's the wrong end of the stick --- that's like trying to annotate a function definition with the actual parameter values being passed to it from somewhere else. You haven't got the info there, and even if you did, it's assuming that there is exactly one call site for any subplan. OK. Will have to think this one over. I am under the (perhaps faulty) impression that when evaluating an expression there can only ever be three tuples in score: inner, outer, and scan. So when we go to evaluate the expression whose result will be assigned to $0, where do we get those inner and/or outer and/or scan tuples from? IOW, I understand where the subplan is putting its OUTPUT, what I don't understand is what context is being used to set its input parameters. Consider this small mod on your example: regression=# explain (verbose) select oid::int + 1,(select oid from pg_class a where a.oid = b.relfilenode) from pg_class b; QUERY PLAN Seq Scan on pg_catalog.pg_class b (cost=0.00..5573.04 rows=671 width=8) Output: ((b.oid)::integer + 1), (SubPlan 1) SubPlan 1 - Index Scan using pg_class_oid_index on pg_catalog.pg_class a (cost=0.00..8.27 rows=1 width=4) Output: a.oid Index Cond: (a.oid = $0) (6 rows) When we are evaluating the output targetlist of the seqscan node, we have a scan tuple of pg_class b in scope. We can fetch that tuple's oid and use it in the first expression. OK. We can also fetch that tuple's relfilenode and pass it to the subplan, which we do by setting the $0 Param value before invoking the subplan. Are the same tuples in scope when evaluating the expression that sets $0 as were in scope when evaluating ((b.oid)::integer + 1)? The subplan runs an indexscan and returns a single scalar value (to wit, a.oid from some row of pg_class a), which becomes the value of the (SubPlan 1) reference back at the evaluation of the seqscan's targetlist. This part I get, 100%. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] explain and PARAM_EXEC
Robert Haas robertmh...@gmail.com writes: On Fri, Feb 19, 2010 at 11:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: We can also fetch that tuple's relfilenode and pass it to the subplan, which we do by setting the $0 Param value before invoking the subplan. Are the same tuples in scope when evaluating the expression that sets $0 as were in scope when evaluating ((b.oid)::integer + 1)? Yes, exactly the same. The parameter-value expression is just like any other scalar expression that could appear where the SubPlan reference is. It doesn't know anything about the subplan, really. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
