Re: [HACKERS] WAL, xl_heap_insert and tuple oid mystry
On 11.04.2011 23:35, jagan wrote: Hi, Suppose I create a table as follows: CREATE TABLE test2 (name TEXT, age INTEGER) WITH oids; Now, for every tuple in this table is associated with a unique oid, which I can retrieve by: SELECT oid, name, age FROM test2; which works great. So far so good. Now, if look at the corresponding WAL entry for any insert into this relation, it creates the following WAL entry which I can decode. XLogRecord ---> Followed by --> xl_heap_insert struct -->followed by --> xl_heap_header --> Followed by --> tuple data If I use the macro HeapTupleHeaderGetOid([xl_heap_header struct]) Yeah, that won't work because xl_heap_header is not a HeapTupleHeader. or equivalently if I test ([xl_heap_header struct]->t_infomask& HEAP_HASOID), it tells me that the tuple oid is not stored with this record. That's strange, the flag should be set. Where is the OID of tuple stored in a WAL record of a tuple? If not with xl_heap_header, where is it stored? Is it stored at all? It's stored in the "tuple data" portion. See the code that writes the WAL record, here: http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/backend/access/heap/heapam.c;h=1fbd8b39b4a73f6aee337fccf2dcce548d0850bb;hb=HEAD#l1987 Maybe the WAL record you're looking at is a full-page image? A record with a full-page image includes a verbatim copy of the page, and the individual tuple is omitted in that case. -- 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] Locking when concurrent updated of foreign references
On 2011-04-11 23:30, Alvaro Herrera wrote: Excerpts from Jesper Krogh's message of lun abr 11 17:07:33 -0300 2011: But when the locking is done "row-level" then it is correct to do it that way. It would allthough be nice with a weaker locklevel for that kind of updates (I have no clue if that is a hard problem). http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/ That looks exactly what I have been seeing. Naive suggestion (at least to part of the problem): Would it be possible to identify updates that never can violate any constraints and not do any verification of foreign keys on the update and only pick a lock that block concurrent updates of the same tuple? UPDATE table set ; would all be of that type. Would allthough require the database to examine the UPDATE statement and in comparison with the table definition figure out which of the column are "safe" to update. There might actually be a potential speedup since the update would require to go visit the foreign table at all. Jesper -- Jesper -- Jesper -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Calling Matlab function from Postgres
I'm trying to call MATLAB functions from PostgreSQL. I was trying to use Joshua Kaplan's java MATLAB control. I install my jar file which in turn calls MATLAB to run my algorithm, but get the error message "ERROR: java.lang.NoClassDefFoundError: matlabcontrol/RemoteMatlabProxyFactory" when I call the java function. I saw where DerTech LLC developed a MATLAB interface for PostgreSQL, but their web site seems to be gone. Does anyone either have an idea of how to solve my error or have a copy of the code that DerTech developed for the interface? Thank you, Susan -- 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] POSIX shared memory redux
On Apr 11, 2011, at 7:13 PM, Tom Lane wrote: > Robert Haas writes: >> On Mon, Apr 11, 2011 at 3:11 PM, A.M. wrote: >>> What do you mean by "leakier"? The goal here is to extinguish SysV shared >>> memory for portability and convenience benefits. The mini-SysV proposal was >>> implemented and shot down by Tom Lane. > >> I mean I'm not convinced that fcntl() locking will be as reliable. > > I'm not either. Particularly not on NFS. (Although on NFS you have > other issues to worry about too, like postmasters on different machines > being able to reach the same data directory. I wonder if we should do > both SysV and fcntl locking ...) Is there an example of a recent system where fcntl is broken (ignoring NFS)? I believe my patch addresses all potential race conditions and uses the APIs properly to guarantee single-postmaster data directory usage and I tested on Darwin and a two-year-old Linux kernel. In the end, fcntl locking relies on the same kernel which provides the SysV user count, so I'm not sure what makes it less "reliable", but I have heard that twice now, so I am open to hearing about your experiences. >> I know Tom shot that down before, but I still think it's probably the >> best way forward. > > Did I? I think I pointed out that there's zero gain in portability as > long as we still depend on SysV shmem to work. However, if you're doing > it for other reasons than portability, it might make sense anyway. The > question is whether there are adequate other reasons. I provided an example of postmaster-failover relying on F_SETLKW in the email with the patch. Also, as you point out above, fcntl locking at least has a chance of working over NFS. > >> The advantage I see is that we would be able to >> more easily allocate larger chunks of shared memory with changing >> kernel parameters, > > Yes, getting out from under the SHMMAX bugaboo would be awfully nice. Yes, please! That is my primary motivation for this patch. > >> and perhaps even to dynamically resize shared memory chunks. > > This I don't really believe will ever work reliably, especially not in > 32-bit machines. Whatever your kernel API is, you still have the > problem of finding address space contiguous to what you were already > using. Even if expanding shmem involves copying large regions of memory, it could be at least useful to adjust buffer sizes live without a restart. Cheers, M -- 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] POSIX shared memory redux
On Apr 11, 2011, at 7:25 PM, Tom Lane wrote: > Robert Haas writes: >> On Sun, Apr 10, 2011 at 5:03 PM, A.M. wrote: >>> To ensure that no two postmasters can startup in the same data directory, I >>> use fcntl range locking on the data directory lock file, which also works >>> properly on (properly configured) NFS volumes. Whenever a postmaster or >>> postmaster child starts, it acquires a read (non-exclusive) lock on the >>> data directory's lock file. When a new postmaster starts, it queries if >>> anything would block a write (exclusive) lock on the lock file which >>> returns a lock-holding PID in the case when other postgresql processes are >>> running. > >> This seems a lot leakier than what we do now (imagine, for example, >> shared storage) and I'm not sure what the advantage is. > > BTW, the above-described solution flat out doesn't work anyway, because > it has a race condition. Postmaster children have to reacquire the lock > after forking, because fcntl locks aren't inherited during fork(). And > that means you can't tell whether there's a just-started backend that > hasn't yet acquired the lock. It's really critical for our purposes > that SysV shmem segments are inherited at fork() and so there's no > window where a just-forked backend isn't visible to somebody checking > the state of the shmem segment. Then you haven't looked at my patch because I address this race condition by ensuring that a lock-holding violator is the postmaster or a postmaster child. If such as condition is detected, the child exits immediately without touching the shared memory. POSIX shmem is inherited via file descriptors. Cheers, M -- 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] POSIX shared memory redux
Robert Haas writes: > On Sun, Apr 10, 2011 at 5:03 PM, A.M. wrote: >> To ensure that no two postmasters can startup in the same data directory, I >> use fcntl range locking on the data directory lock file, which also works >> properly on (properly configured) NFS volumes. Whenever a postmaster or >> postmaster child starts, it acquires a read (non-exclusive) lock on the data >> directory's lock file. When a new postmaster starts, it queries if anything >> would block a write (exclusive) lock on the lock file which returns a >> lock-holding PID in the case when other postgresql processes are running. > This seems a lot leakier than what we do now (imagine, for example, > shared storage) and I'm not sure what the advantage is. BTW, the above-described solution flat out doesn't work anyway, because it has a race condition. Postmaster children have to reacquire the lock after forking, because fcntl locks aren't inherited during fork(). And that means you can't tell whether there's a just-started backend that hasn't yet acquired the lock. It's really critical for our purposes that SysV shmem segments are inherited at fork() and so there's no window where a just-forked backend isn't visible to somebody checking the state of the shmem segment. 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] POSIX shared memory redux
Robert Haas writes: > On Mon, Apr 11, 2011 at 3:11 PM, A.M. wrote: >> What do you mean by "leakier"? The goal here is to extinguish SysV shared >> memory for portability and convenience benefits. The mini-SysV proposal was >> implemented and shot down by Tom Lane. > I mean I'm not convinced that fcntl() locking will be as reliable. I'm not either. Particularly not on NFS. (Although on NFS you have other issues to worry about too, like postmasters on different machines being able to reach the same data directory. I wonder if we should do both SysV and fcntl locking ...) > I know Tom shot that down before, but I still think it's probably the > best way forward. Did I? I think I pointed out that there's zero gain in portability as long as we still depend on SysV shmem to work. However, if you're doing it for other reasons than portability, it might make sense anyway. The question is whether there are adequate other reasons. > The advantage I see is that we would be able to > more easily allocate larger chunks of shared memory with changing > kernel parameters, Yes, getting out from under the SHMMAX bugaboo would be awfully nice. > and perhaps even to dynamically resize shared memory chunks. This I don't really believe will ever work reliably, especially not in 32-bit machines. Whatever your kernel API is, you still have the problem of finding address space contiguous to what you were already using. 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] Postgre inner work question
Hi! Does postgre execute the queries following a execution plan tree, where the leafs are table scans, and the nodes are joins? I'm looking for a database where I can get a cardinality from a partial result of the execution... for example, print the cardinality of the results until the next join operator use this result... Thanks!
[HACKERS] Postgre inner work question
Hi! Does postgre execute the queries following a execution plan tree, where the leafs are table scans, and the nodes are joins? I'm looking for a database where I can get a cardinality from a partial result of the execution... for example, print the cardinality of the results until the next join operator use this result... Thanks!
Re: [HACKERS] lowering privs in SECURITY DEFINER function
On Apr 8, 2011, at 6:17 PM, Alvaro Herrera wrote: >> In other words, if you wrap an unprivileged operation inside of >> privileged operations, it seems like the unprivileged operation then >> becomes privileged. Right? > > Well, it's in the hands of the creator of the overall wrapper function > to ensure that the before/after functions are "safe" in that sense. How do you do that in a safe way though? The problem you run into is if you have a pair of operations that need to be done as a superuser, and something else you want to do in the middle as a non-super user. The goal here is to ensure that you MUST perform both operations out of the pair. The problem is: how do you enforce that the cleanup will actually happen? Right now, we're doing this through a single function that performs the first SU action, does whatever the user asked, and then performs the second SU action. I don't think there's any other way to do that, at least not in 8.3. To make this robust, you can't just provide secdef functions that wrap your operations that require SU: that would mean that anyone could still call them, which means they could potentially call the 1st operation and not the 2nd. I suspect there might be clever ways around this issue, but ISTM that there should be some reasonable way to handle this. BTW, Alvaro did some digging and discovered that the SQL spec allows you to drop to a lower privilege state, but then there's no way you can regain your higher-level privileges until the code block that requested lower privileges exits. That would actually work fine here, so long as you defined a sub-transaction (ie: an embedded BEGIN; END; block in plpgsql as a code block. With such a facility, you could do: CREATE FUNCTION () SECURITY DEFINER AS $$ BEGIN; privileged operation... BEGIN; SET ROLE original_user; UNprivileged operation... END; privileged operation... END; $$; -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] POSIX shared memory redux
On Mon, Apr 11, 2011 at 3:11 PM, A.M. wrote: > > On Apr 11, 2011, at 6:06 PM, Robert Haas wrote: > >> On Sun, Apr 10, 2011 at 5:03 PM, A.M. wrote: >>> To ensure that no two postmasters can startup in the same data directory, I >>> use fcntl range locking on the data directory lock file, which also works >>> properly on (properly configured) NFS volumes. Whenever a postmaster or >>> postmaster child starts, it acquires a read (non-exclusive) lock on the >>> data directory's lock file. When a new postmaster starts, it queries if >>> anything would block a write (exclusive) lock on the lock file which >>> returns a lock-holding PID in the case when other postgresql processes are >>> running. >> >> This seems a lot leakier than what we do now (imagine, for example, >> shared storage) and I'm not sure what the advantage is. I was >> imagining keeping some portion of the data in sysv shm, and moving the >> big stuff to a POSIX shm that would operate alongside it. > > What do you mean by "leakier"? The goal here is to extinguish SysV shared > memory for portability and convenience benefits. The mini-SysV proposal was > implemented and shot down by Tom Lane. I mean I'm not convinced that fcntl() locking will be as reliable. I know Tom shot that down before, but I still think it's probably the best way forward. The advantage I see is that we would be able to more easily allocate larger chunks of shared memory with changing kernel parameters, and perhaps even to dynamically resize shared memory chunks. That'd be worth the price of admission even if we didn't get all those benefits in one commit. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] POSIX shared memory redux
On Apr 11, 2011, at 6:06 PM, Robert Haas wrote: > On Sun, Apr 10, 2011 at 5:03 PM, A.M. wrote: >> To ensure that no two postmasters can startup in the same data directory, I >> use fcntl range locking on the data directory lock file, which also works >> properly on (properly configured) NFS volumes. Whenever a postmaster or >> postmaster child starts, it acquires a read (non-exclusive) lock on the data >> directory's lock file. When a new postmaster starts, it queries if anything >> would block a write (exclusive) lock on the lock file which returns a >> lock-holding PID in the case when other postgresql processes are running. > > This seems a lot leakier than what we do now (imagine, for example, > shared storage) and I'm not sure what the advantage is. I was > imagining keeping some portion of the data in sysv shm, and moving the > big stuff to a POSIX shm that would operate alongside it. What do you mean by "leakier"? The goal here is to extinguish SysV shared memory for portability and convenience benefits. The mini-SysV proposal was implemented and shot down by Tom Lane. Cheers, M -- 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] POSIX shared memory redux
On Sun, Apr 10, 2011 at 5:03 PM, A.M. wrote: > To ensure that no two postmasters can startup in the same data directory, I > use fcntl range locking on the data directory lock file, which also works > properly on (properly configured) NFS volumes. Whenever a postmaster or > postmaster child starts, it acquires a read (non-exclusive) lock on the data > directory's lock file. When a new postmaster starts, it queries if anything > would block a write (exclusive) lock on the lock file which returns a > lock-holding PID in the case when other postgresql processes are running. This seems a lot leakier than what we do now (imagine, for example, shared storage) and I'm not sure what the advantage is. I was imagining keeping some portion of the data in sysv shm, and moving the big stuff to a POSIX shm that would operate alongside it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Locking when concurrent updated of foreign references
Excerpts from Jesper Krogh's message of lun abr 11 17:07:33 -0300 2011: > But when the locking is done "row-level" then it is correct > to do it that way. It would allthough be nice with a weaker > locklevel for that kind of updates (I have no clue if that is > a hard problem). http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/ -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. 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
[HACKERS] WAL, xl_heap_insert and tuple oid mystry
Hi, Suppose I create a table as follows: CREATE TABLE test2 (name TEXT, age INTEGER) WITH oids; Now, for every tuple in this table is associated with a unique oid, which I can retrieve by: SELECT oid, name, age FROM test2; which works great. So far so good. Now, if look at the corresponding WAL entry for any insert into this relation, it creates the following WAL entry which I can decode. XLogRecord ---> Followed by --> xl_heap_insert struct -->followed by --> xl_heap_header --> Followed by --> tuple data If I use the macro HeapTupleHeaderGetOid([xl_heap_header struct]) or equivalently if I test ([xl_heap_header struct]->t_infomask & HEAP_HASOID), it tells me that the tuple oid is not stored with this record. Where is the OID of tuple stored in a WAL record of a tuple? If not with xl_heap_header, where is it stored? Is it stored at all? Thanks for any responses. Jagan -- 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] Windows build issues
On tor, 2011-04-07 at 16:20 -0400, Robert Haas wrote:
> It sure would be nice if someone would write a doc patch, or at least
> a wiki page, explaining all the permutations here... I get the
> impression it's not that hard to set up if you are reasonable
> comfortable working in a Windows environment, but it's pretty
> intimidating if you aren't.
Here's a patch I came up with that matches my experience, but evidently
there could be many others.
diff --git i/doc/src/sgml/install-windows.sgml w/doc/src/sgml/install-windows.sgml
index f6d38c1..d13a161 100644
--- i/doc/src/sgml/install-windows.sgml
+++ w/doc/src/sgml/install-windows.sgml
@@ -19,11 +19,11 @@
There are several different ways of building PostgreSQL on
Windows. The simplest way to build with
- Microsoft tools is to install a modern version of the
- Microsoft Platform SDK and use use the included
+ Microsoft tools is to install
+ Microsoft Visual C++ 2008 Express Edition and use use the included
compiler. It is also possible to build with the full
Microsoft Visual C++ 2005 or 2008. In some cases
- that requires the installation of the Platform SDK
+ that requires the installation of the Windows SDK
in addition to the compiler.
@@ -68,17 +68,21 @@
- Building with Visual C++ or the
- Platform SDK
+ Building with Visual C++
PostgreSQL can be built using the Visual C++ compiler suite from Microsoft.
These compilers can be either from Visual Studio,
- Visual Studio Express or recent versions of the
- Platform SDK. If you do not already have a
- Visual Studio environment set up, the easiest
- way us to use the compilers in the Platform SDK,
- which is a free download from Microsoft.
+ Visual Studio Express or the
+ Platform SDK. (The Platform
+ SDK approach is obsolescent. The SDK has been renamed
+ to Windows SDK and is included
+ in Visual Studio Express). If you do not
+ already have a
+ Visual Studio environment set up, the
+ easiest way us to use the Visual C++
+ Express component from Visual Studio
+ Express, which is a free download from Microsoft.
@@ -86,7 +90,9 @@
Visual Studio 2005 and
Visual Studio 2008. When using the Platform SDK
only, or when building for 64-bit Windows, only
- Visual Studio 2008 is supported.
+ Visual Studio 2008 is
+ supported. Visual Studio 2010 is not yet
+ supported and will not work.
@@ -94,13 +100,16 @@
are in the src/tools/msvc directory. When building,
make sure there are no tools from MinGW or
Cygwin present in your system PATH. Also, make
- sure you have all the required Visual C++ tools available in the PATH. In
- Visual Studio, start the
- Visual Studio Command Prompt. In the
+ sure you have all the required Visual C++ tools available in the PATH. The easiest way to accomplish that, when using
+ Visual Studio, is to start the
+ Visual Studio Command Prompt that is installed in the Start menu. When using the
Platform SDK, start the
CMD shell listed under the SDK on the Start Menu.
If you wish to build a 64-bit version, you must use the 64-bit version of
the command, and vice versa.
+
+
+
All commands should be run from the src\tools\msvc
directory.
@@ -140,18 +149,20 @@ $ENV{PATH}=$ENV{PATH} . ';c:\some\where\bison\bin';
- Microsoft Platform SDK
+ Microsoft Visual C++
- It is recommended that you upgrade to the latest available version
- of the Microsoft Platform SDK, available
- for download from http://www.microsoft.com/downloads/";>.
+ It is recommended that you use Microsoft Visual
+ Studio 2008 Express, available for download
+ from http://www.microsoft.com/downloads/";>, if
+ you don't already have another version installed.
- You must always include the
- Windows Headers and Libraries part of the SDK.
- If you install the Platform SDK
- including the Visual C++ Compilers,
- you don't need Visual Studio to build.
+ If you use an older version, you might also need to install
+ the Platform SDK. You must always
+ include the
+ Windows Headers and Libraries part of
+ the SDK. Recent versions of Visual Studio
+ (Express) contain the necessary SDK components.
@@ -176,9 +187,9 @@ $ENV{PATH}=$ENV{PATH} . ';c:\some\where\bison\bin';
- ActiveState TCL
+ ActiveState Tcl
- Required for building PL/TCL (Note: version
+ Required for building PL/Tcl (Note: version
8.4 is required, the free Standard Distribution is sufficient).
@@ -193,7 +204,18 @@ $ENV{PATH}=$ENV{PATH} . ';c:\some\where\bison\bin';
Bison can be downloaded from http://gnuwin32.sourceforge.net";>.
Flex can be downloaded from
http://www.postgresql.org/ftp/misc/winflex/";>.
-
+
+
+
+
+The Bison distribution from GnuWin appears to have a bug that
+causes B
Re: [HACKERS] lowering privs in SECURITY DEFINER function
Excerpts from Robert Haas's message of dom abr 10 13:37:46 -0300 2011: > It's maybe worth noting here that what's being asked for is roughly > what you get from UNIX's distinction between euid and ruid. Many > programs that run setuid root perform a few operations that require > root privileges up front, and then drop privs. To what degree that > model applies in an SQL environment I'm not sure, but it might be > worth looking at some of the parallels, as well as some of the ways > that the UNIX mechanism has managed to cause all sorts of privilege > escalation bugs over the years, to make sure we don't repeat those > mistakes. Thanks for mentioning that. It made me recall a couple of articles I read some time ago, http://lwn.net/Articles/416494/ and http://www.cis.upenn.edu/~KeyKOS/ConfusedDeputy.html -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. 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] Locking when concurrent updated of foreign references
On 2011-04-11 20:18, Jesper Krogh wrote: Hi. This seem a bit strange to me. In short: Not any more I.. I guess what made me a bit confused was that a "update table set key = value" would acually block out changes on tables referencing this tuple even if the referenced column wasn't effected by the update. But when the locking is done "row-level" then it is correct to do it that way. It would allthough be nice with a weaker locklevel for that kind of updates (I have no clue if that is a hard problem). -- Jesper -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] fn_collation in FmgrInfo considered harmful
The fact that the collations patch put fn_collation into FmgrInfo, rather than FunctionCallInfo, has been bothering me for awhile. The collation is really a kind of argument, not a property of the function, so FmgrInfo is logically the wrong place for it. But I'd not found a concrete reason not to do it that way. Now I think I have. Bug #5970 points out that record_cmp() needs to set up collations for the comparison functions it calls. Since record_cmp relies on FmgrInfo structs that belong to the typcache, this is problematic. I see three choices: 1. Scribble on fn_collation of the FmgrInfo, even though it's in a cache entry that may be used by other calls. This is only safe if you assume that record_cmp (and array_cmp, which is already doing this) need not be re-entrant, ie the cache entry won't be used for another purpose before we're done with the comparison. Considering that the comparison function can be user-defined code, I don't find that assumption safe in the slightest. 2. Copy the FmgrInfo struct to local storage in record_cmp (ick). Since these FmgrInfo structs advertise that they belong to CacheMemoryContext, that doesn't seem very safe either. A function could allocate fn_extra workspace in CacheMemoryContext, and then do it over again on the next call, lather rinse repeat. Maybe we could fix that by copying the fn_extra pointer *back* to the typcache afterwards, but double ick. (And that doesn't seem very safe if the typcache entry could get used re-entrantly, anyway.) 3. Don't store fn_collation in FmgrInfo. A short look around the code suggests that #3 may not be inordinately painful. We'd need to add a collation field to ScanKey to make up for the lack of one in the contained FmgrInfo, but that would make the code cleaner not dirtier. I can see a couple of places where the index AMs assume that the index's collation is available from index_getprocinfo, but it doesn't look too terribly hard to get them to consult index->rd_indcollation[] instead. So, unless there's a really good reason why fn_collation should be in FmgrInfo and not FunctionCallInfo, I'm going to see about moving it. 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] Global variables in plpgsql
Hello 2011/4/11 Nick Raj : > Hi, > Can anyone know how to define global variable in plpgsql? > Thanks > > Regards, > Raj > plpgsql doesn't support global or session variables. There are a few techniques - you can emulate it http://www.postgresql.org/docs/8.3/static/plperl-global.html Regards Pavel Stehule -- 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] how to keep/lock/ hide pg_hba.conf ?
On mån, 2011-04-11 at 07:35 -0700, john.cheng wrote: > I found that,if user modified the pg_hba.conf, modified the > "METHOD"field from md5 to "password" then,user can find out the > password by some the TCP/IP peep tool Don't do that then. Are you concerned that your users would do this? Well, if you install software on their machine, they can do whatever they want with it. That's not an easy issue to solve. -- 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] how to keep/lock/ hide pg_hba.conf ?
On Mon, Apr 11, 2011 at 9:35 AM, john.cheng wrote: > I found that,if user modified the pg_hba.conf, modified the "METHOD"field > from md5 to "password" if it's a client/server app the user shouldn't have access to the server, so how could him to make the change? Also the directory in which the pg_hba.conf is is only visible/writable for the database cluster owner and the system administrator, so that means you're allowing your user to connect to the server as one of those users? or is windows uncapable of enforce those restrictions? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- 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] switch UNLOGGED to LOGGED
On Mon, Apr 11, 2011 at 10:29 AM, Noah Misch wrote: > On Mon, Apr 11, 2011 at 11:41:18AM +0100, Leonardo Francalanci wrote: >> > > But re-reading it, I don't understand: what's the difference in creating >> > > a new "regular" table and crashing before emitting the abort record, >> > > and converting an unlogged table to logged and crashing before >> > > emitting the abort record? How do the standby servers handle a >> > > "CREATE TABLE" followed by a ROLLBACK if the master crashes >> > > before writing the abort record? I thought that too would "leave a >> > > stray file around on a standby". >> > >> > I've been thinking about the same thing. And AFAICS, your analysis is >> > correct, though there may be some angle to it I'm not seeing. >> >> Anyone else? I would like to know if what I'm trying to do is, in fact, >> possible... otherwise starting with thewal_level=minimal case first >> will be wasted effort in case the other cases can't be integrated >> somehow... > > If the master crashes while a transaction that used CREATE TABLE is > unfinished, > both the master and the standby will indefinitely retain identical, stray (not > referenced by pg_class) files. The catalogs do reference the relfilenode of > each unlogged relation; currently, that relfilenode never exists on a standby > while that standby is accepting connections. By the time the startup process > releases the AccessExclusiveLock acquired by the proposed UNLOGGED -> normal > conversion process, that relfilenode needs to be either fully copied or > unlinked > all over again. (Alternately, find some other way to make sure queries don't > read the half-copied file.) In effect, the problem is that the relfilenode is > *not* stray, so its final state does need to be well-defined. Oh, right. Maybe we should just put in a rule that a server in Hot Standby mode won't ever try to read from an unlogged table (right now we count on the fact that there will be nothing to read). If we crash before copying the whole file, it won't matter, because the catalogs won't have been updated, so we'll refuse to look at it anyway. And we have to reinitialize on entering normal running anyway, so we can clean it up then. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] how to keep/lock/ hide pg_hba.conf ?
Dear all: I am ready to release a client/server software (in windows xp),sure it's postgresql based application but I have to hide the password for sensitive data. I found that,if user modified the pg_hba.conf, modified the "METHOD"field from md5 to "password" then,user can find out the password by some the TCP/IP peep tool --such as LayerViewer (we don't plan to install SSL in server/client) I think as a newbie as I know this bug,this solution should be released already Thanks for any advice/suggestion Regards john from Taiwan -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-keep-lock-hide-pg-hba-conf-tp4296068p4296068.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Global variables in plpgsql
On Mon, Apr 11, 2011 at 7:33 AM, Nick Raj wrote: > Can anyone know how to define global variable in plpgsql? I expect you should consult the manual page on the command CREATE TABLE. That's what would be the nearest SQL equivalent to a "global variable." http://www.postgresql.org/docs/9.0/static/sql-createtable.html -- http://linuxfinances.info/info/postgresql.html -- 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] workaround for expensive KNN?
price has a problem :(.
"iphone" can be a 20 cents bag or a sticker or a 900 euro thing signed by
whoever ...
so, words and the sort-number / price are not related in anyway. price is in
this case no way to narrow down the problem (e.g. evaluate first or so).
many thanks,
hans
On Apr 8, 2011, at 5:25 PM, Oleg Bartunov wrote:
> Hans,
>
> what if you create index (price,title) ?
>
>
> On Fri, 8 Apr 2011, PostgreSQL - Hans-J?rgen Sch?nig wrote:
>
>> hello ...
>>
>> i got that one ...
>>
>> "idx_product_t_product_titleprice" gist (to_tsvector('german'::regconfig,
>> title), int_price)
>>
>> so, i have a combined index on text + number.
>> to me the plan seems fine ... it looks like a prober KNN traversal.
>> the difference between my plan and your plan seems to be the fact that i
>> have, say, 1 mio rows which have "handy" or so in it (1 mio out of 11 mio or
>> so). you are moving out from one specific place.
>>
>> my maths is like that:
>> 11 mio in total
>> 1 mio matching "iphone"
>> cheapest / most expensive 10 out of this mio needed.
>>
>> operator classes are all nice and in place:
>>
>> SELECT 10 <-> 4 as distance;
>> distance
>> --
>> 6
>> (1 row)
>>
>> what does "buffers true" in your case say?
>>
>> many thanks,
>>
>> hans
>>
>>
>> On Apr 8, 2011, at 3:22 PM, Oleg Bartunov wrote:
>>
>>> Probably, you miss two-columnt index. From my early post:
>>> http://www.sai.msu.su/~megera/wiki/knngist
>>>
>>> =# CREATE INDEX spots_idx ON spots USING knngist (coordinates,
>>> to_tsvector('french',address));
>>> =# SELECT id, address, (coordinates <->
>>> '(2.29470491409302,48.858263472125)'::point) AS dist FROM spots WHERE
>>> coordinates >< '(2.29470491409302,48.858263472125)'::point AND
>>> to_tsvector('french',address) @@ to_tsquery('french','mars') LIMIT 10;
>>> id| address |
>>>dist
>>> -+-+-
>>> 366096 | 1st Floor Tour Eiffel | Champs de Mars, Paris 75007, France |
>>> 2.32488941293945e-05
>>> 4356328 | r Champ de Mars 75007 PARIS |
>>> 0.00421854756964406
>>> 5200167 | Champ De Mars 75007 Paris |
>>> 0.00453564562587288
>>> 9301676 | Champ de Mars, 75007 Paris, |
>>> 0.00453564562587288
>>> 2152213 | 16, ave Rapp, Champ de Mars, Tour Eiffel, Paris, France |
>>> 0.00624152097590896
>>> 1923818 | Champ de Mars Paris, France |
>>> 0.00838214733539654
>>> 5165953 | 39 Rue Champ De Mars Paris, France |
>>> 0.00874410234569529
>>> 7395870 | 39 Rue Champ De Mars Paris, France |
>>> 0.00874410234569529
>>> 4358671 | 32 Rue Champ De Mars Paris, France |
>>> 0.00876089659276339
>>> 1923742 | 12 rue du Champ de Mars Paris, France |
>>> 0.00876764731845995
>>> (10 rows)
>>>
>>> Time: 7.859 ms
>>>
>>> =# EXPLAIN (COSTS OFF) SELECT id, address FROM spots WHERE coordinates ><
>>> '(2.29470491409302,48.858263472125)'::point
>>> AND to_tsvector('french',address) @@ to_tsquery('french','mars') LIMIT 10;
>>>
>>> QUERY PLAN
>>> --
>>> Limit
>>> -> Index Scan using spots_idx on spots
>>>Index Cond: ((coordinates ><
>>> '(2.29470491409302,48.858263472125)'::point) AND
>>> (to_tsvector('french'::regconfig, address) @@ '''mar'''::tsquery))
>>> (3 rows)
>>>
>>>
>>> On Fri, 8 Apr 2011, PostgreSQL - Hans-J?rgen Sch?nig wrote:
>>>
hello all ...
given oleg's posting before i also wanted to fire up some KNN related
question.
let us consider a simple example. i got some million lines and i want all
rows matching a tsquery sorted by price.
i did some tests:
test=# explain (analyze true, buffers true, costs true) SELECT id FROM
product.t_product WHERE to_tsvector('german', title) @@
to_tsquery('german', 'iphone') ORDER BY int_price <-> 0 LIMIT 10;
QUERY PLAN
-
--
Limit (cost=0.00..41.11 rows=10 width=16) (actual
time=36391.717..45542.590 rows=10 loops=1)
Buffers: shared hit=9 read=5004
-> Index Scan using idx_product_t_product_titleprice on t_product
(cost=0.00..13251.91 rows=3224 width=16) (actual time=
36391.715..45542.573 rows=10 loops=1)
Index Cond: (to_tsvector('german'::regconfig, title) @@
'''iphon'''::
[HACKERS] Global variables in plpgsql
Hi, Can anyone know how to define global variable in plpgsql? Thanks Regards, Raj
Re: [HACKERS] SSI bug?
Heikki Linnakangas wrote: > I finally got around to look at this. Attached patch adds a > HASH_FIXED_SIZE flag, which disables the allocation of new entries > after the initial allocation. I believe we have consensus to make > the predicate lock hash tables fixed-size, so that there's no > competition of the slack shmem space between predicate lock > structures and the regular lock maanager. OK, I can see why you preferred this -- the existing exchange of slack space with the HW lock tables remains unchanged this way, and only the new tables for predicate locking have the stricter limits. This makes it very unlikely to break current apps which might be unknowingly relying on existing allocation behavior in the HW locking area. Smart. I hadn't picked up on your intent that the new flag would only be used for the new tables, which is why it wasn't quite making sense to me before. Thanks! -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Locking when concurrent updated of foreign references
Hi.
This seem a bit strange to me. In short:
2 tables, one with has a foreign key to the other one;
CREATE TABLE test (id SERIAL primary key, data text);
CREATE TABLE testref(id SERIAL primary key, test_id integer references
test(id) not null, data text);
INSERT INTO test(data) values('something');
INSERT INTO testref(test_id,data) values(1,'something else');
CREATE OR REPLACE FUNCTION upd(data text) RETURNS text as $$ BEGIN
PERFORM pg_sleep(3); return data; END; $$ LANGUAGE 'plpgsql' immutable;
Then start 2 transactions (from different psql prompts):
TXN1: BEGIN;
TXN1: update test set data = upd('something');
TXN2: BEGIN;
TXN2: update testref set data = upd('something'); (within 3 seconds of
the other update, so they are overlapping in execution)
other command is done>
Wether this is correct or not I'm not sure, but if you remove the
"upd()" calls
in both above so the update isn't exactly executed at the same time
then both commands succeed without interfering with each other.
While waiting on TXN2's update, pg_locks shows that there is a ShareLock
that
cannot be granted, which will first get further when TXN1 commits or
rolls back.
It seems as the lock isn't released if some other process is actually
waiting
for it?
It may be related to the plpgsql function, I have a similar one (doing more
clever things than sleep) in the production system, but I cannot figure
out how
to get the updates overlapping in execution in other ways.
A hugely trimmed down example of something I currently see in a production
system. (8.4.7) but above is on 9.1HEAD
--
Jesper
--
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] Feature request: pg_basebackup --force
Heikki Linnakangas wrote: > That's exactly what pg_basebackup does. Once you move into more > complicated scenarios with multiple standbys and WAL archiving, > it's inevitably going to be more complicated to set up. > > That doesn't mean that we can't make it easier - we can and we > should - but I don't think the common complaint that replication > is hard to set up is true anymore. Getting back to the rsync-like behavior, which is what led the conversation in this direction, I think -- the point of that seemed to be to allow similar ease of use for those activating a replicated node as the master, without requiring that the entire data directory be sent over a slow WAN or Internet path when the delta needed to modify what was already at the remote end to match the new master might be orders of magnitude less than data than that. The intelligence to support that would be a fraction of what is in rsync. In fact, since we might want to ignore hint bit differences where possible, rsync might not work nearly as well as a home-grown solution. -Kevin -- 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] SSI bug?
On 03.04.2011 09:16, Dan Ports wrote: I think I see what is going on now. We are sometimes failing to set the commitSeqNo correctly on the lock. In particular, if a lock assigned to OldCommittedSxact is marked with InvalidSerCommitNo, it will never be cleared. The attached patch corrects this: TransferPredicateLocksToNewTarget should initialize a new lock entry's commitSeqNo to that of the old one being transferred, or take the minimum commitSeqNo if it is merging two lock entries. Also, CreatePredicateLock should initialize commitSeqNo for to InvalidSerCommitSeqNo instead of to 0. (I don't think using 0 would actually affect anything, but we should be consistent.) I also added a couple of assertions I used to track this down: a lock's commitSeqNo should never be zero, and it should be InvalidSerCommitSeqNo if and only if the lock is not held by OldCommittedSxact. Thanks, committed this. -- 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] SSI bug?
On 11.04.2011 11:33, Heikki Linnakangas wrote: On 31.03.2011 22:06, Kevin Grittner wrote: Heikki Linnakangas wrote: That's not enough. The hash tables can grow beyond the maximum size you specify in ShmemInitHash. It's just a hint to size the directory within the hash table. We'll need to teach dynahash not to allocate any more entries after the preallocation. A new HASH_NO_GROW flag to hash_create() seems like a suitable interface. OK. If we're doing that, is it worth taking a look at the "safety margin" added to the size calculations, and try to make the calculations more accurate? Would you like me to code a patch for this? I finally got around to look at this. Attached patch adds a HASH_FIXED_SIZE flag, which disables the allocation of new entries after the initial allocation. I believe we have consensus to make the predicate lock hash tables fixed-size, so that there's no competition of the slack shmem space between predicate lock structures and the regular lock maanager. Ok, committed that. I left the safety margins in the size calculations alone for now. -- 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] switch UNLOGGED to LOGGED
On Mon, Apr 11, 2011 at 11:41:18AM +0100, Leonardo Francalanci wrote: > > > But re-reading it, I don't understand: what's the difference in creating > > > a new "regular" table and crashing before emitting the abort record, > > > and converting an unlogged table to logged and crashing before > > > emitting the abort record? How do the standby servers handle a > > > "CREATE TABLE" followed by a ROLLBACK if the master crashes > > > before writing the abort record? I thought that too would "leave a > > > stray file around on a standby". > > > > I've been thinking about the same thing. And AFAICS, your analysis is > > correct, though there may be some angle to it I'm not seeing. > > > Anyone else? I would like to know if what I'm trying to do is, in fact, > possible... otherwise starting with thewal_level=minimal case first > will be wasted effort in case the other cases can't be integrated > somehow... If the master crashes while a transaction that used CREATE TABLE is unfinished, both the master and the standby will indefinitely retain identical, stray (not referenced by pg_class) files. The catalogs do reference the relfilenode of each unlogged relation; currently, that relfilenode never exists on a standby while that standby is accepting connections. By the time the startup process releases the AccessExclusiveLock acquired by the proposed UNLOGGED -> normal conversion process, that relfilenode needs to be either fully copied or unlinked all over again. (Alternately, find some other way to make sure queries don't read the half-copied file.) In effect, the problem is that the relfilenode is *not* stray, so its final state does need to be well-defined. nm -- 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] Transforming IN (...) to ORs, volatility
Heikki Linnakangas writes: > Does anyone object to making BETWEEN and IN more strict about the data > types? At the moment, you can do this: > postgres=# SELECT '1234' BETWEEN '10001'::text AND 10002::int4; > ?column? > -- > t > (1 row) > I'm thinking that it should throw an error. Same with IN, if the values > in the IN-list can't be coerced to a common type. You *will* get push-back on that ... maybe from people with badly coded applications, but I guarantee there will be complaints. 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] pgfoundry down?
Apologies ... everything should be back up and running now ... On Mon, 11 Apr 2011, Tatsuo Ishii wrote: Does anybody know what's going on? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- 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] Transforming IN (...) to ORs, volatility
On 11.04.2011 19:06, Kevin Grittner wrote: Heikki Linnakangas wrote: On 05.04.2011 18:42, Heikki Linnakangas wrote: On 05.04.2011 13:19, Marti Raudsepp wrote: On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas wrote: We sometimes transform IN-clauses to a list of ORs: postgres=# explain SELECT * FROM foo WHERE a IN (b, c); QUERY PLAN Seq Scan on foo (cost=0.00..39.10 rows=19 width=12) Filter: ((a = b) OR (a = c)) But what if you replace "a" with a volatile function? It doesn't seem legal to do that transformation in that case, but we do it: postgres=# explain SELECT * FROM foo WHERE (random()*2)::integer IN (b, c); QUERY PLAN Seq Scan on foo (cost=0.00..68.20 rows=19 width=12) Filter: random() * 2::double precision))::integer = b) OR (((random() * 2::double precision))::integer = c)) Is there a similar problem with the BETWEEN clause transformation into AND expressions? marti=> explain verbose select random() between 0.25 and 0.75; Result (cost=0.00..0.02 rows=1 width=0) Output: ((random()>= 0.25::double precision) AND (random()<= 0.75::double precision)) Yes, good point. Hmm, the SQL specification explicitly says that X BETWEEN Y AND Z is equal to X>= Y AND X<= Z It doesn't say anything about side-effects of X. Seems like an oversight in the specification. I would not expect X to be evaluated twice, and I think we should change BETWEEN to not do that. Does the SQL spec explicitly say anything about how many times X should be evaluated if you were to code it as?: X>= Y AND X<= Z Not explicitly. However, it does say that: " NOTE 258 — Since is an ordering operation, the Conformance Rules of Subclause 9.12, “Ordering operations”, also apply. " If I'm reading those ordering operation conformance rules correctly, it only allows the operand to be a simple column or an expression that's specified in the ORDER BY or similar, not an arbitrary expression. Which seems quite restrictive, but it would dodge the whole issue.. The spec also has that: “X BETWEEN SYMMETRIC Y AND Z” is equivalent to “((X BETWEEN ASYMMETRIC Y AND Z) OR (X BETWEEN ASYMMETRIC Z AND Y))”. So if you take that into account too, X is evaluated four times. The SQL standard can be funny sometimes, but I can't believe that they intended that. -- 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: Don't make "replication" magical as a user name, only as a datab
On 04/10/2011 10:17 PM, Andrew Dunstan wrote: On 04/10/2011 09:47 PM, Fujii Masao wrote: On Mon, Apr 11, 2011 at 3:53 AM, Andrew Dunstan wrote: Don't make "replication" magical as a user name, only as a database name, in pg_hba.conf. Is it worth backporting this change to 9.0? I didn't because it's a behaviour change, but arguably it's just us being ever so slightly more permissive, and nothing that now works would change in any way, so we possibly could. Well, nobody seems to be very fussed about this idea, so unless someone objects I'll do this in 24 hours. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pgfoundry down?
Does anybody know what's going on? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Transforming IN (...) to ORs, volatility
Heikki Linnakangas wrote: > On 05.04.2011 18:42, Heikki Linnakangas wrote: >> On 05.04.2011 13:19, Marti Raudsepp wrote: >>> On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas >>> wrote: We sometimes transform IN-clauses to a list of ORs: postgres=# explain SELECT * FROM foo WHERE a IN (b, c); QUERY PLAN Seq Scan on foo (cost=0.00..39.10 rows=19 width=12) Filter: ((a = b) OR (a = c)) But what if you replace "a" with a volatile function? It doesn't seem legal to do that transformation in that case, but we do it: postgres=# explain SELECT * FROM foo WHERE (random()*2)::integer IN (b, c); QUERY PLAN Seq Scan on foo (cost=0.00..68.20 rows=19 width=12) Filter: random() * 2::double precision))::integer = b) OR (((random() * 2::double precision))::integer = c)) >>> >>> Is there a similar problem with the BETWEEN clause >>> transformation into AND expressions? >>> >>> marti=> explain verbose select random() between 0.25 and 0.75; >>> Result (cost=0.00..0.02 rows=1 width=0) >>> Output: ((random()>= 0.25::double precision) AND (random()<= >>> 0.75::double precision)) >> >> Yes, good point. > > Hmm, the SQL specification explicitly says that > > X BETWEEN Y AND Z > > is equal to > > X >= Y AND X <= Z > > It doesn't say anything about side-effects of X. Seems like an > oversight in the specification. I would not expect X to be > evaluated twice, and I think we should change BETWEEN to not do > that. Does the SQL spec explicitly say anything about how many times X should be evaluated if you were to code it as?: X >= Y AND X <= Z If it does, evaluating it a different number of times for BETWEEN would seem to be a deviation from standard. Evaluating it once seem less surprising, but if we're going to deviate from the standard in doing that, it at least deserves a clear note to that effect in the docs. Evaluating X once for BETWEEN seems better from a POLA perspective, unless you happen to be massaging a query to another form and trusting that the equivalence defined in the standard will always hold. > Does anyone object to making BETWEEN and IN more strict about the > data types? At the moment, you can do this: > > postgres=# SELECT '1234' BETWEEN '10001'::text AND 10002::int4; > ?column? > -- > t > (1 row) > > I'm thinking that it should throw an error. Same with IN, if the > values in the IN-list can't be coerced to a common type. That will > probably simplify the code a lot, and is what the SQL standard > assumes anyway AFAICS. +1 for more strict. -Kevin -- 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] SSI bug?
On 31.03.2011 22:06, Kevin Grittner wrote:
Heikki Linnakangas wrote:
That's not enough. The hash tables can grow beyond the maximum
size you specify in ShmemInitHash. It's just a hint to size the
directory within the hash table.
We'll need to teach dynahash not to allocate any more entries
after the preallocation. A new HASH_NO_GROW flag to hash_create()
seems like a suitable interface.
OK. If we're doing that, is it worth taking a look at the "safety
margin" added to the size calculations, and try to make the
calculations more accurate?
Would you like me to code a patch for this?
I finally got around to look at this. Attached patch adds a
HASH_FIXED_SIZE flag, which disables the allocation of new entries after
the initial allocation. I believe we have consensus to make the
predicate lock hash tables fixed-size, so that there's no competition of
the slack shmem space between predicate lock structures and the regular
lock maanager.
I also noticed that there's a few hash_search(HASH_ENTER) calls in
predicate.c followed by check for a NULL result. But with HASH_ENTER,
hash_search never returns NULL, it throws an "out of shared memory"
error internally. I changed those calls to use HASH_ENTER_NULL, so you
now get the intended error message with the hint to raise
max_pred_locks_per_transaction.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
diff --git a/src/backend/storage/lmgr/predicate.c b/src/backend/storage/lmgr/predicate.c
index 401acdb..6ff41fc 100644
--- a/src/backend/storage/lmgr/predicate.c
+++ b/src/backend/storage/lmgr/predicate.c
@@ -959,17 +959,15 @@ InitPredicateLocks(void)
{
HASHCTL info;
int hash_flags;
- long init_table_size,
-max_table_size;
+ long max_table_size;
Size requestSize;
bool found;
/*
- * Compute init/max size to request for predicate lock target hashtable.
+ * Compute size of predicate lock target hashtable.
* Note these calculations must agree with PredicateLockShmemSize!
*/
max_table_size = NPREDICATELOCKTARGETENTS();
- init_table_size = max_table_size / 2;
/*
* Allocate hash table for PREDICATELOCKTARGET structs. This stores
@@ -980,17 +978,16 @@ InitPredicateLocks(void)
info.entrysize = sizeof(PREDICATELOCKTARGET);
info.hash = tag_hash;
info.num_partitions = NUM_PREDICATELOCK_PARTITIONS;
- hash_flags = (HASH_ELEM | HASH_FUNCTION | HASH_PARTITION);
+ hash_flags = (HASH_ELEM | HASH_FUNCTION | HASH_PARTITION | HASH_FIXED_SIZE);
PredicateLockTargetHash = ShmemInitHash("PREDICATELOCKTARGET hash",
- init_table_size,
+ max_table_size,
max_table_size,
&info,
hash_flags);
/* Assume an average of 2 xacts per target */
max_table_size *= 2;
- init_table_size *= 2;
/*
* Reserve an entry in the hash table; we use it to make sure there's
@@ -1011,18 +1008,17 @@ InitPredicateLocks(void)
info.entrysize = sizeof(PREDICATELOCK);
info.hash = predicatelock_hash;
info.num_partitions = NUM_PREDICATELOCK_PARTITIONS;
- hash_flags = (HASH_ELEM | HASH_FUNCTION | HASH_PARTITION);
+ hash_flags = (HASH_ELEM | HASH_FUNCTION | HASH_PARTITION | HASH_FIXED_SIZE);
PredicateLockHash = ShmemInitHash("PREDICATELOCK hash",
- init_table_size,
+ max_table_size,
max_table_size,
&info,
hash_flags);
/*
- * Compute init/max size to request for serializable transaction
- * hashtable. Note these calculations must agree with
- * PredicateLockShmemSize!
+ * Compute size for serializable transaction hashtable.
+ * Note these calculations must agree with PredicateLockShmemSize!
*/
max_table_size = (MaxBackends + max_prepared_xacts);
@@ -1093,7 +1089,7 @@ InitPredicateLocks(void)
info.keysize = sizeof(SERIALIZABLEXIDTAG);
info.entrysize = sizeof(SERIALIZABLEXID);
info.hash = tag_hash;
- hash_flags = (HASH_ELEM | HASH_FUNCTION);
+ hash_flags = (HASH_ELEM | HASH_FUNCTION | HASH_FIXED_SIZE);
SerializableXidHash = ShmemInitHash("SERIALIZABLEXID hash",
max_table_size,
@@ -2045,7 +2041,7 @@ CreatePredicateLock(const PREDICATELOCKTARGETTAG *targettag,
target = (PREDICATELOCKTARGET *)
hash_search_with_hash_value(PredicateLockTargetHash,
targettag, targettaghash,
- HASH_ENTER, &found);
+ HASH_ENTER_NULL, &found);
if (!target)
ereport(ERROR,
(errcode(ERRCODE_OUT_OF_MEMORY),
@@ -2060,7 +2056,7 @@ CreatePredicateLock(const PREDICATELOCKTARGETTAG *targettag,
lock = (PREDICATELOCK *)
hash_search_with_hash_value(PredicateLockHash, &locktag,
PredicateLockHashCodeFromTargetHashCode(&locktag, targettaghash),
- HASH_ENTER, &found);
+ HASH_ENTER_NULL, &found);
if (!lock)
ereport(ERROR,
(errcode(ERRCODE_OUT_OF_MEMORY),
@@ -3251,7 +3247,7 @@ ReleaseOneSerializableXact(SERIALIZABLEXACT *sxact, bool partial,
predlock = hash_search_with_hash_value(PredicateLockHash, &tag,
PredicateLockHashCodeFromT
Re: [HACKERS] developer.postgresql.org down
Everything should be back up and running now ... sorry for delay ... On Mon, 11 Apr 2011, Albert Cervera i Areny wrote: Maybe already known or in scheduled maintenance but developer.postgresql.org seems to be down right now. -- Albert Cervera i Areny http://www.NaN-tic.com OpenERP Partners Tel: +34 93 553 18 03 skype: nan-oficina http://twitter.com/albertnan http://www.nan-tic.com/blog Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] developer.postgresql.org down
Maybe already known or in scheduled maintenance but developer.postgresql.org seems to be down right now. -- Albert Cervera i Areny http://www.NaN-tic.com OpenERP Partners Tel: +34 93 553 18 03 skype: nan-oficina http://twitter.com/albertnan http://www.nan-tic.com/blog
Re: [HACKERS] switch UNLOGGED to LOGGED
> > But re-reading it, I don't understand: what's the difference in creating > > a new "regular" table and crashing before emitting the abort record, > > and converting an unlogged table to logged and crashing before > > emitting the abort record? How do the standby servers handle a > > "CREATE TABLE" followed by a ROLLBACK if the master crashes > > before writing the abort record? I thought that too would "leave a > > stray file around on a standby". > > I've been thinking about the same thing. And AFAICS, your analysis is > correct, though there may be some angle to it I'm not seeing. Anyone else? I would like to know if what I'm trying to do is, in fact, possible... otherwise starting with thewal_level=minimal case first will be wasted effort in case the other cases can't be integrated somehow... Leonardo -- 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] Transforming IN (...) to ORs, volatility
On 05.04.2011 18:42, Heikki Linnakangas wrote: On 05.04.2011 13:19, Marti Raudsepp wrote: On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas wrote: We sometimes transform IN-clauses to a list of ORs: postgres=# explain SELECT * FROM foo WHERE a IN (b, c); QUERY PLAN Seq Scan on foo (cost=0.00..39.10 rows=19 width=12) Filter: ((a = b) OR (a = c)) But what if you replace "a" with a volatile function? It doesn't seem legal to do that transformation in that case, but we do it: postgres=# explain SELECT * FROM foo WHERE (random()*2)::integer IN (b, c); QUERY PLAN Seq Scan on foo (cost=0.00..68.20 rows=19 width=12) Filter: random() * 2::double precision))::integer = b) OR (((random() * 2::double precision))::integer = c)) Is there a similar problem with the BETWEEN clause transformation into AND expressions? marti=> explain verbose select random() between 0.25 and 0.75; Result (cost=0.00..0.02 rows=1 width=0) Output: ((random()>= 0.25::double precision) AND (random()<= 0.75::double precision)) Yes, good point. Hmm, the SQL specification explicitly says that X BETWEEN Y AND Z is equal to X >= Y AND X <= Z It doesn't say anything about side-effects of X. Seems like an oversight in the specification. I would not expect X to be evaluated twice, and I think we should change BETWEEN to not do that. Does anyone object to making BETWEEN and IN more strict about the data types? At the moment, you can do this: postgres=# SELECT '1234' BETWEEN '10001'::text AND 10002::int4; ?column? -- t (1 row) I'm thinking that it should throw an error. Same with IN, if the values in the IN-list can't be coerced to a common type. That will probably simplify the code a lot, and is what the SQL standard assumes anyway AFAICS. -- 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] SSI bug?
On 11.04.2011 11:33, Heikki Linnakangas wrote: I also noticed that there's a few hash_search(HASH_ENTER) calls in predicate.c followed by check for a NULL result. But with HASH_ENTER, hash_search never returns NULL, it throws an "out of shared memory" error internally. I changed those calls to use HASH_ENTER_NULL, so you now get the intended error message with the hint to raise max_pred_locks_per_transaction. Oops, those were already fixed. Never mind. -- 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] SSI bug?
hi, > hi, > >> I think I see what is going on now. We are sometimes failing to set the >> commitSeqNo correctly on the lock. In particular, if a lock assigned to >> OldCommittedSxact is marked with InvalidSerCommitNo, it will never be >> cleared. >> >> The attached patch corrects this: >> TransferPredicateLocksToNewTarget should initialize a new lock >> entry's commitSeqNo to that of the old one being transferred, or take >> the minimum commitSeqNo if it is merging two lock entries. >> >> Also, CreatePredicateLock should initialize commitSeqNo for to >> InvalidSerCommitSeqNo instead of to 0. (I don't think using 0 would >> actually affect anything, but we should be consistent.) >> >> I also added a couple of assertions I used to track this down: a >> lock's commitSeqNo should never be zero, and it should be >> InvalidSerCommitSeqNo if and only if the lock is not held by >> OldCommittedSxact. >> >> Takashi, does this patch fix your problem with leaked SIReadLocks? > > i'm currently running bf6848bc8c82e82f857d48185554bc3e6dcf1013 with this > patch applied. i haven't seen the symptom yet. i'll keep it running for > a while. i haven't seen the symptom since them. so i guess it was fixed by your patch. thanks! YAMAMOTO Takashi > > btw, i've noticed the following message in the server log. is it normal? > > LOG: could not truncate directory "pg_serial": apparent wraparound > > YAMAMOTO Takashi > >> >> Dan >> >> >> -- >> Dan R. K. Ports MIT CSAILhttp://drkp.net/ > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
