Re: [HACKERS] mingw check hung
Andrew Dunstan wrote: Magnus Hagander wrote: Andrew Dunstan wrote: Magnus Hagander wrote: Specifically, it's the SetEnvironmentVariable() call from pgwin32_putenv() called from pgwin32_unsetenv(). When this is disabled things work just fine. That's strange :( What arguments are it sent to the function? Since this is an API function, it really shouldn't behave differently between mingw and msvc, so it must be something that goes wrong with the arguments. Also, Tom mentioned earlier that we may be including *two* replacements for unsetenv(), which could be what's causing the problem. Can you check if that is happening and try to disable the one in port/unsetenv.c and see if that changes things? I've already ruled out that hypothesis by forcing the call direct to pgwin32_unsetenv() instead of relying on the macro, in initdb.c. There are only two such calls in initdb.c: the arguments are LC_ALL and PGCLIENTENCODING. I wonder if this version of SetEnvironmentVariable is sufficiently dumb that it fails badly if given a NULL second argument for a value that is not in fact in the environment (as I would normally expect of these on Windows)? But that should be a win32 API call. It's not a runtime call. So it should be identical between mingw and msvc! Try removing the code that sets it to NULL if it's empty string. Having it as empty string made it fail on MSVC, and the API documentation says it should be NULL, but maybe mingw is somehow intercepting the call and breaking it... Mingw is just passing the call on. You're right. When I comment out the NULL assignment, it all works. MSDN says this (http://msdn.microsoft.com/en-us/library/z46c489x.aspx): If the value parameter is not empty and the environment variable named by the variable parameter does not exist, the environment variable is created and assigned the contents of value. Solely for purposes of this operation, value is considered empty if it is a null reference (Nothing in Visual Basic), contains a zero-length string, or contains an initial hexadecimal zero character (0x00). If variable contains a non-initial hexadecimal zero character, the characters before the zero character are considered the environment variable name and all subsequent characters are ignored. If value contains a non-initial hexadecimal zero character, the characters before the zero character are assigned to the environment variable and all subsequent characters are ignored. If value is empty and the environment variable named by variable exists, the environment variable is deleted. If variable does not exist, no error occurs even though the operation cannot be performed. So it looks like we could remove that NULL assignment happily and expect the right thing to be done. I'm doing training all day today, but I can hopefully look at it this weekend if you haven't already. However, I do recall *adding* that part specifically for MSVC compatibility - I got a crash without it. Perhaps we need to #ifdef it on mingw, but I'd like to understand *why*, since it's just an API call... Are we *sure*, btw, that this is actually a mingw issue, and not something else in the environment? Could you try a MSVC compiled binary on the same machine? //Magnus -- 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] using composite types in insert/update
Merlin Moncure mmonc...@gmail.com wrote: however no such workaround exists for update. ideally, UPDATE foo SET foo = foo; +1. UPDATE foo SET (*) = (foo.*) would be another candidate of syntax. I want to use this kind of queries to apply a changeset log to another table. It is just like log application in Slony-I. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Commitfest infrastructure (was Re: [HACKERS] 8.4 release planning)
Stefan Kaltenbrunner píše v čt 29. 01. 2009 v 18:29 +0100: Peter Eisentraut wrote: On Thursday 29 January 2009 11:40:48 Stefan Kaltenbrunner wrote: well from a quick glance there is the bugzilla demo install as well as pieces of reviewboard and patchwork on the trackerdemo jail. So what's the URL and where can we sign up? note the pieces part of my mail :-) As far as I recall the patchworks install somehow collided with the reviewboard one so it was disabled because Zdenek was still actively using reviewboard. I don't use it at this moment. You can disable reviewboard if you want. Zdenek -- 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] Hot standby, recovery infra
I just realized that the new minSafeStartPoint is actually exactly the same concept as the existing minRecoveryPoint. As the recovery progresses, we could advance minRecoveryPoint just as well as the new minSafeStartPoint. Perhaps it's a good idea to keep them separate anyway though, the original minRecoveryPoint might be a useful debugging aid. Or what do you think? -- 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] Hot standby, recovery infra
On Thu, 2009-01-29 at 20:35 +0200, Heikki Linnakangas wrote: Hmm, another point of consideration is how this interacts with the pause/continue. In particular, it was suggested earlier that you could put an option into recovery.conf to start in paused mode. If you pause recovery, and then stop and restart the server, and have that option in recovery.conf, I would expect that when you enter consistent recovery you're at the exact same paused location as before stopping the server. The upshot of that is that we need to set minSafeStartPoint to that exact location, at least when you pause stop in a controlled fashion. OK, makes sense. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Hot standby, recovery infra
On Thu, 2009-01-29 at 19:20 +0200, Heikki Linnakangas wrote: Heikki Linnakangas wrote: It looks like if you issue a fast shutdown during recovery, postmaster doesn't kill bgwriter. Hmm, seems like we haven't thought through how shutdown during consistent recovery is supposed to behave in general. Right now, smart shutdown doesn't do anything during consistent recovery, because the startup process will just keep going. And fast shutdown will simply ExitPostmaster(1), which is clearly not right. That whole area was something I was leaving until last, since immediate shutdown doesn't work either, even in HEAD. (Fujii-san and I discussed this before Christmas, briefly). I'm thinking that in both smart and fast shutdown, the startup process should exit in a controlled way as soon as it's finished with the current WAL record, and set minSafeStartPoint to the current point in the replay. That makes sense, though isn't required. I wonder if bgwriter should perform a restartpoint before exiting? You'll have to start with recovery on the next startup anyway, but at least we could minimize the amount of WAL that needs to be replayed. That seems like extra work for no additional benefit. I think we're beginning to blur the lines between review and you just adding some additional stuff in this area. There's nothing to stop you doing further changes after this has been committed. We can also commit what we have with some caveats also, i.e. commit in pieces. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Hot standby, recovery infra
On Fri, 2009-01-30 at 11:33 +0200, Heikki Linnakangas wrote: I just realized that the new minSafeStartPoint is actually exactly the same concept as the existing minRecoveryPoint. As the recovery progresses, we could advance minRecoveryPoint just as well as the new minSafeStartPoint. Perhaps it's a good idea to keep them separate anyway though, the original minRecoveryPoint might be a useful debugging aid. Or what do you think? I think we've been confusing ourselves substantially. The patch already has everything it needs, but there is a one-line-fixable bug where Fujii-san says. The code comments already explain how this works * There are two points in the log that we must pass. The first * is minRecoveryPoint, which is the LSN at the time the * base backup was taken that we are about to rollforward from. * If recovery has ever crashed or was stopped there is also * another point also: minSafeStartPoint, which we know the * latest LSN that recovery could have reached prior to crash. The later message FATAL WAL ends before end time of backup dump was originally triggered if if (XLByteLT(EndOfLog, ControlFile-minRecoveryPoint)) and I changed that. Now I look at it again, I see that the original if test, shown above, is correct and should not have been changed. Other than that, I don't see the need for further change. Heikki's suggestions to write a new minSafeStartPoint are good ones and fit within the existing mechanisms and meanings of these variables. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Hot standby, recovery infra
On Thu, 2009-01-29 at 14:21 +0200, Heikki Linnakangas wrote: It looks like if you issue a fast shutdown during recovery, postmaster doesn't kill bgwriter. Thanks for the report. I'm thinking to add a new function that will allow crash testing easier. pg_crash_standby() will issue a new xlog record, XLOG_CRASH_STANDBY, which when replayed will just throw a FATAL error and crash Startup process. We won't be adding that to the user docs... This will allow us to produce tests that crash the server at specific places, rather than trying to trap those points manually. Seems that reaper() needs to be taught that bgwriter can be active during consistent recovery. I'll take a look at how to do that. BTW, the message terminating connection ... is a bit misleading. It's referring to the startup process, which is hardly a connection. We have that in CVS HEAD too, so it's not something introduced by the patch, but seems worth changing in HS, since we then let real connections in while startup process is still running. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Hot standby, recovery infra
Simon Riggs wrote: I'm thinking to add a new function that will allow crash testing easier. pg_crash_standby() will issue a new xlog record, XLOG_CRASH_STANDBY, which when replayed will just throw a FATAL error and crash Startup process. We won't be adding that to the user docs... This will allow us to produce tests that crash the server at specific places, rather than trying to trap those points manually. Heh, talk about a footgun ;-). I don't think including that in CVS is a good idea. -- 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] Hot standby, recovery infra
Simon Riggs wrote: On Thu, 2009-01-29 at 19:20 +0200, Heikki Linnakangas wrote: Hmm, seems like we haven't thought through how shutdown during consistent recovery is supposed to behave in general. Right now, smart shutdown doesn't do anything during consistent recovery, because the startup process will just keep going. And fast shutdown will simply ExitPostmaster(1), which is clearly not right. That whole area was something I was leaving until last, since immediate shutdown doesn't work either, even in HEAD. (Fujii-san and I discussed this before Christmas, briefly). We must handle shutdown gracefully, can't just leave bgwriter running after postmaster exit. Hmm, why does pg_standby catch SIGQUIT? Seems it could just let it kill the process. I wonder if bgwriter should perform a restartpoint before exiting? You'll have to start with recovery on the next startup anyway, but at least we could minimize the amount of WAL that needs to be replayed. That seems like extra work for no additional benefit. I think we're beginning to blur the lines between review and you just adding some additional stuff in this area. There's nothing to stop you doing further changes after this has been committed. Sure. I think the shutdown restartpoint might actually fall out of the way the code is structured anyway: bgwriter normally performs a checkpoint before exiting. We can also commit what we have with some caveats also, i.e. commit in pieces. This late in the release cycle, I don't want to commit anything that we would have to rip out if we run out of time. There is no difference from review or testing point of view whether the code is in CVS or not. -- 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
[HACKERS] Synch Replication - Synch rep 0114
Hi, I have been testing in recent, the Synch Replication(Synch rep 0114 (Jan 14, 2009) ) on PostgreSQL version 8.4 (postgresql-8.4devel_20081229.tar.bz2) I followed the steps in Readme as well used the test script provided in patch for the setup. As per wiki, I am able to bring up the walsender and the walreceiver process in a single server as well when primary and seconday are setup on different nodes(making necessary changes to the test script) Then I am able to see the walsender and walreceiver process are in progress. Then I try to insert some records into the table created (within the script) as below: ./psql psql (8.4devel) Type help for help. postgres=# insert into temp values(5,'e'); I get the following output : Standby 6820 FATAL: unexpected EOF on replication connection: lost synchronization with server: got message type c, length -805175295 Primary 6821 LOG: unexpected EOF on replication connection Primary 6821 LOG: replication done at: write 0/100 (file 0001), flush 0/100 (file 0001) Standby 6820 LOG: replication done at: write 0/100 (file 0001), flush 0/100 (file 0001) Standby 6812 LOG: could not open file pg_xlog/00010001 (log file 0, segment 1): No such file or directory Standby 6812 LOG: redo done at 0/4A983C Standby 6812 PANIC: could not open file pg_xlog/0001 (log file 0, segment 0): No such file or directory Standby 6809 LOG: startup process (PID 6812) was terminated by signal 6: Aborted Standby 6809 LOG: aborting startup due to startup process failure INSERT 0 1 After this, I see both walsender and walreceiver are down and writer process is still running. Is this because, there is no provision of replication between primary and secondary? Or is it because write transactions are not supported? In case where primary and standby are run on two different nodes, I am able to bring up the walsender and walreceiver process. But atleast read transactions( records inserted in primary ) are not getting reflected in the standby node. In such cases I would like to know about what exact features are working with this patch? Because, in the Readme section of Synch Replication wiki, it is mentioned to check whether the walsender and walreceiver process are in progress. How about replication and read - write transactions? Also with the latest patch Synch rep 0128 (Jan 28, 2009), Am getting compilation errors. Please let me about the correct status of the Synch Replication about what features are working properly. Regards, Smita Patil -- View this message in context: http://www.nabble.com/Synch-Replication---Synch-rep-0114-tp21745603p21745603.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
[HACKERS] Synch Replication - Synch rep 0114
Hi, I have been testing in recent, the Synch Replication(Synch rep 0114 (Jan 14, 2009) ) on PostgreSQL version 8.4 (postgresql-8.4devel_20081229.tar.bz2) I followed the steps in Readme as well used the test script provided in patch for the setup. As per wiki, I am able to bring up the walsender and the walreceiver process in a single server as well when primary and seconday are setup on different nodes(making necessary changes to the test script) Then I am able to see the walsender and walreceiver process are in progress. Then I try to insert some records into the table created (within the script) as below: ./psql psql (8.4devel) Type help for help. postgres=# insert into temp values(5,'e'); I get the following output : Standby 6820 FATAL: unexpected EOF on replication connection: lost synchronization with server: got message type c, length -805175295 Primary 6821 LOG: unexpected EOF on replication connection Primary 6821 LOG: replication done at: write 0/100 (file 0001), flush 0/100 (file 0001) Standby 6820 LOG: replication done at: write 0/100 (file 0001), flush 0/100 (file 0001) Standby 6812 LOG: could not open file pg_xlog/00010001 (log file 0, segment 1): No such file or directory Standby 6812 LOG: redo done at 0/4A983C Standby 6812 PANIC: could not open file pg_xlog/0001 (log file 0, segment 0): No such file or directory Standby 6809 LOG: startup process (PID 6812) was terminated by signal 6: Aborted Standby 6809 LOG: aborting startup due to startup process failure INSERT 0 1 After this, I see both walsender and walreceiver are down and writer process is still running. Is this because, there is no provision of replication between primary and secondary? Or is it because write transactions are not supported? In case where primary and standby are run on two different nodes, I am able to bring up the walsender and walreceiver process. But atleast read transactions( records inserted in primary ) are not getting reflected in the standby node. In such cases I would like to know about what exact features are working with this patch? Because, in the Readme section of Synch Replication wiki, it is mentioned to check whether the walsender and walreceiver process are in progress. How about replication and read - write transactions? Also with the latest patch Synch rep 0128 (Jan 28, 2009), Am getting compilation errors. Please let me about the correct status of the Synch Replication about what features are working properly. Regards, Smita Patil -- View this message in context: http://www.nabble.com/Synch-Replication---Synch-rep-0114-tp21745679p21745679.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
[HACKERS] Synch Replication - Synch rep 0114
Hi, I have been testing in recent, the Synch Replication(Synch rep 0114 http://wiki.postgresql.org/wiki/Image:Synch_rep_0114.tgz (Jan 14, 2009) ) on PostgreSQL version 8.4 ( postgresql-8.4devel_20081229.tar.bz2 http://yum.pgsqlrpms.org/srpms/8.4/postgresql-8.4devel_20081229.tar.bz2 ) I followed the steps in Readme as well used the test script provided in patch for the setup. As per wiki, I am able to bring up the walsender and the walreceiver process in a single server as well when primary and seconday are setup on different nodes(making necessary changes to the test script) Then I am able to see the walsender and walreceiver process are in progress. Then I try to insert some records into the table created (within the script) as below: ./psql psql (8.4devel) Type help for help. postgres=# insert into temp values(5,'e'); I get the following output : Standby 6820 FATAL: unexpected EOF on replication connection: lost synchronization with server: got message type c, length -805175295 Primary 6821 LOG: unexpected EOF on replication connection Primary 6821 LOG: replication done at: write 0/100 (file 0001), flush 0/100 (file 0001) Standby 6820 LOG: replication done at: write 0/100 (file 0001), flush 0/100 (file 0001) Standby 6812 LOG: could not open file pg_xlog/00010001 (log file 0, segment 1): No such file or directory Standby 6812 LOG: redo done at 0/4A983C Standby 6812 PANIC: could not open file pg_xlog/0001 (log file 0, segment 0): No such file or directory Standby 6809 LOG: startup process (PID 6812) was terminated by signal 6: Aborted Standby 6809 LOG: aborting startup due to startup process failure INSERT 0 1 After this, I see both walsender and walreceiver are down and writer process is still running. Is this because, there is no provision of replication between primary and secondary? Or is it because write transactions are not supported? In case where primary and standby are run on two different nodes, I am able to bring up the walsender and walreceiver process. But atleast read transactions( records inserted in primary ) are not getting reflected in the standby node. In such cases I would like to know about what exact features are working with this patch? Because, in the Readme section of Synch Replication wiki, it is mentioned to check whether the walsender and walreceiver process are in progress. How about replication and read - write transactions? Also with the latest patch Synch rep 0128 http://wiki.postgresql.org/wiki/Image:Synch_rep_0128.tgz (Jan 28, 2009), Am getting compilation errors. Please let me about the correct status of the Synch Replication about what features are working properly. Regards, Smita Patil image002.jpg
Re: [HACKERS] Synch Replication - Synch rep 0114
Hi, On Fri, Jan 30, 2009 at 8:05 PM, Patil, Smita (NSN - IN/Bangalore) smita.pa...@nsn.com wrote: Hi, I have been testing in recent, the Synch Replication(Synch rep 0114 (Jan 14, 2009) ) on PostgreSQL version 8.4 ( postgresql-8.4devel_20081229.tar.bz2) Thanks for your testing and report! I'm afraid that the base HEAD version (postgresql-8.4devel_20081229.tar.bz2) is old, which might have caused the following error. So, please try to apply synch-rep v0128 patch to the latest HEAD, and test it. If you can use cvs, the following document might be helpful for you to get the latest HEAD. http://www.postgresql.org/docs/8.3/static/anoncvs.html As per wiki, I am able to bring up the walsender and the walreceiver process in a single server as well when primary and seconday are setup on different nodes(making necessary changes to the test script) What kind of change was required? Then I am able to see the walsender and walreceiver process are in progress. Good! Then I try to insert some records into the table created (within the script) as below: ./psql psql (8.4devel) Type help for help. postgres=# insert into temp values(5,'e'); Please let me know the DDL of creating temp table. I'll test it also on my machine. After this, I see both walsender and walreceiver are down and writer process is still running. Is this because, there is no provision of replication between primary and secondary? Yes, it's because unexpected error terminated replication (ie. walsender and walreceiver). But, such termination of replication doesn't affect the primary's normal processing, so walwriter was still running on the primary. Or is it because write transactions are not supported? Write transactions are also supported like original postgres. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] using composite types in insert/update
On Wed, Jan 28, 2009 at 12:03:56PM -0500, Merlin Moncure wrote:
IMO, composite types on insert/update should work as they do on select:
INSERT INTO foo VALUES '(something)'::foo -- fails,
The VALUES command is just a convenient way of getting lots of tuples
into PG isn't it? If the above was valid, PG would have to support
similar syntax elsewhere, which seems independent of the feature you're
really asking for.
but we have a workaround:
INSERT INTO foo SELECT ('(something)'::foo).* -- expands foo into foo columns
Or if you wanted to insert multiple rows:
INSERT INTO foo SELECT (txt::foo).* FROM
(VALUES ('(something)'), ('(something else)')) x(txt);
however no such workaround exists for update. ideally,
UPDATE foo SET foo = foo;
would be valid.
Sounds useful, but seems to break existing syntax (imagine if the table
foo had a column called foo). Takahiro suggests using a * to
indicate what you're asking for and this seems to have nicer semantics
to me.
There seem to be two different improvements needed; the first would be
in allowing composite values on the RHS, the second in allowing the
column list on the LHS to be replaced with a *. E.g. we start with the
following code:
CREATE TEMP TABLE foo ( a INT, b TEXT );
INSERT INTO foo ( 1, 'a' );
the following is currently valid:
UPDATE foo SET (a,b) = (x.a,x.b) FROM (SELECT ('(2,c)'::foo).*) x;
The first step would allow you to do:
UPDATE foo SET (a,b) = x FROM (SELECT ('(2,c)'::foo).*) x;
and the second step allow you to do:
UPDATE foo SET (*) = x FROM (SELECT ('(2,c)'::foo).*) x;
Aside from fixing a surprising behavior
Or have I missed the point and you mean the surprising behavior is
that you expect PG to generate WHERE clauses for you automatically.
This seems impossible in the general case.
, it would
greatly aid in writing triggers that do things like ship updates over
dblink _much_ easier (in fact...the dblink_build_xxx family would
become obsolete).
e.g.
perform dblink.dblink('UPDATE foo SET foo = \'' || new || '\'::foo);
I call the existing behavior of insert/update of composite types
broken to the point of almost being a bug. Fixing the above to work
would close the loop on a broad new set of things you can do with
composite types.
How well would something like this work in practice? If for some reason
foo had been created with the columns in a different order in the two
databases then you'd end up with things breaking pretty quickly. One
naive way out seems to be to include the column names in serialized
tuples. This has advantages (i.e. we're always told not to rely on
column order and this would be one less place we implicitly had to) as
well as disadvantages (i.e. the size of the resulting serialized value
would go up and well as the complexity of the serialization routine).
--
Sam http://samason.me.uk/
--
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] mingw check hung
Magnus Hagander wrote: Are we *sure*, btw, that this is actually a mingw issue, and not something else in the environment? Could you try a MSVC compiled binary on the same machine? My MSVC buildfarm animal runs on the same machine, and does not suffer the same problem. 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] How to learn all information on the user of a database?
How to learn all information on the user of a database? (user name, host name, sleep or running,connect or disconnect) Thanks Regards, Mr. St -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] fflush
Hello, I'm coding some functions in C language on Windows for Postgres server (calling from PG). After dll generation and put it to the correct postgres directory the problem is as follow : Postgres stop running when i call fflush() function on a file stream and no data are in my file. The behavior is already the same, Postgres process dies. Is it a know problem ?, or there is a way to protect the calling function, or to implement with caution the soft. Best regards Jean-Michel
Re: [HACKERS] mingw check hung
Andrew Dunstan wrote: Magnus Hagander wrote: Are we *sure*, btw, that this is actually a mingw issue, and not something else in the environment? Could you try a MSVC compiled binary on the same machine? My MSVC buildfarm animal runs on the same machine, and does not suffer the same problem. Meh. Stupid mingw :-) So how about we #ifdef out that NULL setting based on WIN32_ONLY_COMPILER, does that seem reasonable? //Magnus -- 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] fflush
Jean-Michel Riet wrote: Hello, I'm coding some functions in C language on Windows for Postgres server (calling from PG). After dll generation and put it to the correct postgres directory the problem is as follow : Postgres stop running when i call /fflush() / function on a file stream and no data are in my file. The behavior is already the same, Postgres process dies. Is it a know problem ?, or there is a way to protect the calling function, or to implement with caution the soft. That seems very odd. I think you'll need to show us the whole function code. 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
Re: [HACKERS] mingw check hung
Magnus Hagander wrote: Andrew Dunstan wrote: Magnus Hagander wrote: Are we *sure*, btw, that this is actually a mingw issue, and not something else in the environment? Could you try a MSVC compiled binary on the same machine? My MSVC buildfarm animal runs on the same machine, and does not suffer the same problem. Meh. Stupid mingw :-) So how about we #ifdef out that NULL setting based on WIN32_ONLY_COMPILER, does that seem reasonable? The odd thing is that it doesn't seem to affect Vista, only XP. Anyway, yes, I think that would be OK. How do we then test to see if the original problem is still fixed? 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
Re: [HACKERS] [PATCH] Space reservation v02
Heikki Linnakangas wrote: Bruce Momjian wrote: The patch has two space reservations, one per page, another per tuple. Now, thinking back, what types of changes have we made that increase storage size. The one that I can think of first is where we made a data type require larger storage. (I think inet/cidr.) This could not be handled by this patch because if a row had _two_ values of that type, there would be no way to specify this using the two supplied parameters. Well, I believe the idea was that the pre-upgrade script that sets the space reservation would look at the catalogs to decide the right reservation for each table. Interesting --- so you set the reservation per table --- that seems much better than a GUC, certainly. I assume we would still need a per-page GUC that affects all tables? Or one for heap and one for index pages? One thing I think would help would be a pg_class column that says whether the table is ready for upgrading. This is something we can't easily backpatch and would be helpful so people could do their upgrade preparation in a staged manner, rather than having to do it all at once, and would give the upgrade scripts confidence that the backpatch had done everying needed. The backpatched code would set this pg_class column value when it was done making sure the table is ready for upgrade (probably via vacuum). I recommend an int2 column to store PG_VERSION_NUM / 100. I think that being able to stop and restart the pre-upgrade process is a luxury we can add later. Also note that the pre-upgrade tool can use a flat file in the data directory to store state in a more free-form fashion. To implement restartability, for example, you could dump a list of relfilenodes not yet scanned to the file at start, and strike them out as you go. Well, I was thinking the new pg_class column would allow the upgrade to verify the pre-upgrade script was run properly, but a flat file works just as well if we assume we are going to pre-upgrade in one pass. However, I am afraid requiring this pre-upgrade to run while the server is basically in single-user mode will make upgrade-in-place be a long process for many users, and if it takes a significant time compared to dump/reload, they might as well dump/reload. But again, all this is trying to handle cases where the data size increases, which is a rare event for us. -- 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] [PATCH] Space reservation v02
Bruce Momjian píše v pá 30. 01. 2009 v 10:41 -0500: Heikki Linnakangas wrote: Bruce Momjian wrote: The patch has two space reservations, one per page, another per tuple. Now, thinking back, what types of changes have we made that increase storage size. The one that I can think of first is where we made a data type require larger storage. (I think inet/cidr.) This could not be handled by this patch because if a row had _two_ values of that type, there would be no way to specify this using the two supplied parameters. Well, I believe the idea was that the pre-upgrade script that sets the space reservation would look at the catalogs to decide the right reservation for each table. Interesting --- so you set the reservation per table --- that seems much better than a GUC, certainly. I assume we would still need a per-page GUC that affects all tables? Or one for heap and one for index pages? Each access methods has different requirements and it heavily depends on specific relations. Also TOAST tables has different requirements. GUC variable is not good option. One thing I think would help would be a pg_class column that says whether the table is ready for upgrading. This is something we can't easily backpatch and would be helpful so people could do their upgrade preparation in a staged manner, rather than having to do it all at once, and would give the upgrade scripts confidence that the backpatch had done everying needed. The backpatched code would set this pg_class column value when it was done making sure the table is ready for upgrade (probably via vacuum). I recommend an int2 column to store PG_VERSION_NUM / 100. I think that being able to stop and restart the pre-upgrade process is a luxury we can add later. Also note that the pre-upgrade tool can use a flat file in the data directory to store state in a more free-form fashion. To implement restartability, for example, you could dump a list of relfilenodes not yet scanned to the file at start, and strike them out as you go. Well, I was thinking the new pg_class column would allow the upgrade to verify the pre-upgrade script was run properly, but a flat file works just as well if we assume we are going to pre-upgrade in one pass. Flat file or special table for pg_upgrade will work fine. However, I am afraid requiring this pre-upgrade to run while the server is basically in single-user mode will make upgrade-in-place be a long process for many users, and if it takes a significant time compared to dump/reload, they might as well dump/reload. pre_upgrade script should be run during normal operation. There will be some limitation. For example CREATE/ALTER TABLE can cause problems. Zdenek -- 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] reloptions with a namespace
Euler Taveira de Oliveira wrote: Alvaro Herrera escreveu: Okay, so I've changed things so that the transformRelOptions' caller is now in charge of passing an array of valid option namespaces. This is working A-OK. I'm now going to figure out appropriate pg_dump support and commit as soon as possible. I don't like the spreading validnsps' approach. Isn't there a way to centralize those variables in one place, i.e., reloption.h ? Also, remove an obsolete comment about toast tables at reloption.h. No, that doesn't work, because we don't know centrally what's the allowed list of namespaces. In fact that's precisely the problem: for example, there's no point in having a toast namespace for index reloptions. And for a user-defined access method, we don't know what the valid namespaces are. Of course, the easiest way is to just state that there are no valid namespaces other than NULL, and only allow toast for heap, but I think that's not thinking far enough ahead. The other option I considered was to have another AM entry point that returns the list of valid namespaces, but that seems to be way overkill, particularly considering that the current arrays are all NULL. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 La gente vulgar solo piensa en pasar el tiempo; el que tiene talento, en aprovecharlo -- 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] using composite types in insert/update
On 1/30/09, Sam Mason s...@samason.me.uk wrote:
On Wed, Jan 28, 2009 at 12:03:56PM -0500, Merlin Moncure wrote:
IMO, composite types on insert/update should work as they do on select:
The VALUES command is just a convenient way of getting lots of tuples
into PG isn't it? If the above was valid, PG would have to support
similar syntax elsewhere, which seems independent of the feature you're
really asking for.
You are missing the point, using the composite type allows you to
build the insert without knowing the specific layout of the
table...only the table itself and the fields that comprise the key for
update statements.
but we have a workaround:
INSERT INTO foo SELECT ('(something)'::foo).* -- expands foo into foo
columns
Or if you wanted to insert multiple rows:
INSERT INTO foo SELECT (txt::foo).* FROM
(VALUES ('(something)'), ('(something else)')) x(txt);
however no such workaround exists for update. ideally,
UPDATE foo SET foo = foo;
would be valid.
Sounds useful, but seems to break existing syntax (imagine if the table
foo had a column called foo). Takahiro suggests using a * to
indicate what you're asking for and this seems to have nicer semantics
to me.
I don't think it would...right now select statements work the way I
want. If there is table and column with the same name, the column
name is assumed. It's an issue of symmetry...why can't you insert the
same way you select?
By the way, record types are virtually first class objects starting with 8.4:
create index foo_idx on foo(foo);
select (1,2)::foo = (3,4)::foo;
select foo from foo order by foo;
select foo::text::foo; -- got this in 8.3
are all valid.
There seem to be two different improvements needed; the first would be
in allowing composite values on the RHS, the second in allowing the
column list on the LHS to be replaced with a *. E.g. we start with the
following code:
CREATE TEMP TABLE foo ( a INT, b TEXT );
INSERT INTO foo ( 1, 'a' );
the following is currently valid:
UPDATE foo SET (a,b) = (x.a,x.b) FROM (SELECT ('(2,c)'::foo).*) x;
The first step would allow you to do:
UPDATE foo SET (a,b) = x FROM (SELECT ('(2,c)'::foo).*) x;
and the second step allow you to do:
UPDATE foo SET (*) = x FROM (SELECT ('(2,c)'::foo).*) x;
Hm. IMO, set (*) is a completely new invention of what '*' means. I
guess it's ok though, but I think the composite type is more natural.
I think if you went this route you should think about other places
that this syntax might be valid. I'm not arguing against what you're
saying, but the composite type should work too.
Aside from fixing a surprising behavior
Or have I missed the point and you mean the surprising behavior is
that you expect PG to generate WHERE clauses for you automatically.
This seems impossible in the general case.
The surprising behavior is that 'select foo from foo' works, but
'update foo set foo = x::foo' does not.
, it would
greatly aid in writing triggers that do things like ship updates over
dblink _much_ easier (in fact...the dblink_build_xxx family would
become obsolete).
e.g.
perform dblink.dblink('UPDATE foo SET foo = \'' || new || '\'::foo);
I call the existing behavior of insert/update of composite types
broken to the point of almost being a bug. Fixing the above to work
would close the loop on a broad new set of things you can do with
composite types.
How well would something like this work in practice? If for some reason
foo had been created with the columns in a different order in the two
databases then you'd end up with things breaking pretty quickly. One
That's a separate application specific issue that applies only to
dblink style replication (and I don't think keeping to schemas similar
is really all that difficult). Composite type insertion has
usefulness far beyond dblink triggers. Imagine a global trigger that
captures record to text and logs to text table. Now it's trivial if
you want to render the text back into the table, since you don't have
to look up the field list to generate the statement.
naive way out seems to be to include the column names in serialized
tuples. This has advantages (i.e. we're always told not to rely on
column order and this would be one less place we implicitly had to) as
well as disadvantages (i.e. the size of the resulting serialized value
would go up and well as the complexity of the serialization routine).
That's nice as well, but should be considered separately from powering
composite types.
merlin
--
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] mingw check hung
Andrew Dunstan wrote: Magnus Hagander wrote: Andrew Dunstan wrote: Magnus Hagander wrote: Are we *sure*, btw, that this is actually a mingw issue, and not something else in the environment? Could you try a MSVC compiled binary on the same machine? My MSVC buildfarm animal runs on the same machine, and does not suffer the same problem. Meh. Stupid mingw :-) So how about we #ifdef out that NULL setting based on WIN32_ONLY_COMPILER, does that seem reasonable? The odd thing is that it doesn't seem to affect Vista, only XP. Anyway, yes, I think that would be OK. How do we then test to see if the original problem is still fixed? Further proof that this is a Windows version issue: I took the problem build from my XP and put it on my Vista box: the same build that causes a problem on XP runs perfectly on Vista. Go figure. Maybe we need a version check at runtime? That would be icky. 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
Re: [HACKERS] using composite types in insert/update
Merlin Moncure mmonc...@gmail.com writes: You are missing the point, using the composite type allows you to build the insert without knowing the specific layout of the table... Surely at *some* level you have to know that. 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] [PATCH] Space reservation v02
Zdenek Kotala wrote: Bruce Momjian píše v pá 30. 01. 2009 v 10:41 -0500: Well, I was thinking the new pg_class column would allow the upgrade to verify the pre-upgrade script was run properly, but a flat file works just as well if we assume we are going to pre-upgrade in one pass. Flat file or special table for pg_upgrade will work fine. Right, there's no difference in what you can achieve, whether you store the additional info in a flat file, special table or extra pg_class columns. If you can store something in pg_class, you can store it elsewhere just as well. However, I am afraid requiring this pre-upgrade to run while the server is basically in single-user mode will make upgrade-in-place be a long process for many users, and if it takes a significant time compared to dump/reload, they might as well dump/reload. pre_upgrade script should be run during normal operation. There will be some limitation. Right. That's the whole point of having a pre-upgrade script. Otherwise you might as well run the conversion in the new version. For example CREATE/ALTER TABLE can cause problems. Yeah, if the pre-upgrade script determines the amount of reserved space for each table, and sets it in pg_class or reloptions or whatever, it's not clear how mwhat to do with tables created after the script is run. I guess we need quick scan of pg_class before the actual upgrade to check that you don't have newly-created tables, and refuse the upgrade if there is. However, if we have the logic to determine how much space to reserve for a table in the backend, as a back-ported patch, then we can invoke it for new tables just as well. -- 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] mingw check hung
Andrew Dunstan wrote: Anyway, yes, I think that would be OK. How do we then test to see if the original problem is still fixed? Further proof that this is a Windows version issue: I took the problem build from my XP and put it on my Vista box: the same build that causes a problem on XP runs perfectly on Vista. Go figure. Maybe we need a version check at runtime? That would be icky. At a minimum we need to document this behavior in a source code comment. -- 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] [PATCH] Space reservation v02
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Zdenek Kotala wrote: Bruce Momjian píše v pá 30. 01. 2009 v 10:41 -0500: Well, I was thinking the new pg_class column would allow the upgrade to verify the pre-upgrade script was run properly, but a flat file works just as well if we assume we are going to pre-upgrade in one pass. Flat file or special table for pg_upgrade will work fine. Right, there's no difference in what you can achieve, whether you store the additional info in a flat file, special table or extra pg_class columns. If you can store something in pg_class, you can store it elsewhere just as well. Well having a column in pg_class does have some advantages. Like, you could look at the value from an sql session more easily. And if there are operations which we know are unsafe -- such as adding columns -- we could clear it from the server side easily. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- 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] [PATCH] Space reservation v02
Heikki Linnakangas wrote: For example CREATE/ALTER TABLE can cause problems. Yeah, if the pre-upgrade script determines the amount of reserved space for each table, and sets it in pg_class or reloptions or whatever, it's not clear how mwhat to do with tables created after the script is run. I guess we need quick scan of pg_class before the actual upgrade to check that you don't have newly-created tables, and refuse the upgrade if there is. This is where a pg_class column would be useful. You default the column value to -1. The pre-upgrade script sets the proper reserved space, and new tables get a -1 and you check for those just before the upgrade. -- 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] [PATCH] Space reservation v02
Bruce Momjian wrote: Heikki Linnakangas wrote: For example CREATE/ALTER TABLE can cause problems. Yeah, if the pre-upgrade script determines the amount of reserved space for each table, and sets it in pg_class or reloptions or whatever, it's not clear how mwhat to do with tables created after the script is run. I guess we need quick scan of pg_class before the actual upgrade to check that you don't have newly-created tables, and refuse the upgrade if there is. This is where a pg_class column would be useful. You default the column value to -1. The pre-upgrade script sets the proper reserved space, and new tables get a -1 and you check for those just before the upgrade. You can do that with a flat file too. If there's any tables in the database that were not present when pre-upgrade script was started, throw an error. It might be a bit simpler with a pg_class column, but if we don't know what exactly we need to store there, and might need to resort to different storage anyway, it doesn't seem worth it. An extra table as Zdenek suggested in the passing might give the best of both worlds. The pre-upgrade script can create it when it's run, so we don't need to decide beforehand what columns we need, and it's a table so you can query it etc. -- 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] [PATCH] Space reservation v02
Heikki Linnakangas wrote: Bruce Momjian wrote: Heikki Linnakangas wrote: For example CREATE/ALTER TABLE can cause problems. Yeah, if the pre-upgrade script determines the amount of reserved space for each table, and sets it in pg_class or reloptions or whatever, it's not clear how mwhat to do with tables created after the script is run. I guess we need quick scan of pg_class before the actual upgrade to check that you don't have newly-created tables, and refuse the upgrade if there is. This is where a pg_class column would be useful. You default the column value to -1. The pre-upgrade script sets the proper reserved space, and new tables get a -1 and you check for those just before the upgrade. You can do that with a flat file too. If there's any tables in the database that were not present when pre-upgrade script was started, throw an error. It might be a bit simpler with a pg_class column, but if we don't know what exactly we need to store there, and might need to resort to different storage anyway, it doesn't seem worth it. An extra table as Zdenek suggested in the passing might give the best of both worlds. The pre-upgrade script can create it when it's run, so we don't need to decide beforehand what columns we need, and it's a table so you can query it etc. Yep, makes sense. I had forgotten that idea; sorry. -- 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] [PATCH] Space reservation v02
Gregory Stark st...@enterprisedb.com writes: Well having a column in pg_class does have some advantages. Like, you could look at the value from an sql session more easily. And if there are operations which we know are unsafe -- such as adding columns -- we could clear it from the server side easily. Why would there be any unsafe operations? Surely the patch would add sufficient logic to prevent the old version from de-fixing any page that had already been fixed. If this is not so, the entire concept is broken, because you're still going to have to go to single-user mode for a long time to make sure that the whole database is in good shape. On the whole I agree with Heikki's earlier criticism: this is all about guessing the future, and the odds seem high that the actual requirements will not be what you designed for anyway. 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] using composite types in insert/update
On Fri, Jan 30, 2009 at 11:01:53AM -0500, Merlin Moncure wrote:
On 1/30/09, Sam Mason s...@samason.me.uk wrote:
The VALUES command is just a convenient way of getting lots of tuples
into PG isn't it? If the above was valid, PG would have to support
similar syntax elsewhere, which seems independent of the feature you're
really asking for.
You are missing the point, using the composite type allows you to
build the insert without knowing the specific layout of the
table...only the table itself and the fields that comprise the key for
update statements.
But this has nothing to do with the VALUES command! Going back to what
I interpret as your original point, I find myself wanting to write:
SELECT *
FROM foo f, (VALUES 1, 5, 7, 23, 47) v
WHERE f.id = v;
but end up having to write the following:
SELECT *
FROM foo f, (VALUES (1), (5), (7), (23), (47)) x(v)
WHERE f.id = x.v;
quite often (i.e. a VALUES command with many singletons). This seems
a bit annoying and appears to be what you were suggesting you wanted
before (although you killed the relevant bit of context, making me think
we may be talking about different things).
ideally,
UPDATE foo SET foo = foo;
would be valid.
Sounds useful, but seems to break existing syntax (imagine if the table
foo had a column called foo). Takahiro suggests using a * to
indicate what you're asking for and this seems to have nicer semantics
to me.
I don't think it would...right now select statements work the way I
want. If there is table and column with the same name, the column
name is assumed. It's an issue of symmetry...why can't you insert the
same way you select?
For several reasons; mainly because SQL is an abortion of a language,
it's got no regularity and attempts to justify requirements because of
symmetry will end up causing more headaches.
Another way of saying what you seem to be saying above is: I want things
to work correctly, unless I happen to have a column name that happens to
be the same as the table at which point I want everything to break.
By the way, record types are virtually first class objects starting with 8.4:
create index foo_idx on foo(foo);
select (1,2)::foo = (3,4)::foo;
select foo from foo order by foo;
select foo::text::foo; -- got this in 8.3
are all valid.
Record *types* are most definitely not first class objects;
record/composite *values* on the other hand have been gaining support
for a while. There are a few weirdo's left, like VALUES commands only
working with records, but the dichotomy between record and non-record
types is slowly vanishing.
UPDATE foo SET (*) = x FROM (SELECT ('(2,c)'::foo).*) x;
Hm. IMO, set (*) is a completely new invention of what '*' means.
In my head, * has always meant all the columns associated with some
record. This is just putting it in a new place in the grammar. It's
nice because it doesn't introduce any ambiguities, whereas using the
table name does.
I'm not sure if the brackets are needed, but I thought it safer to leave
them in.
Aside from fixing a surprising behavior
Or have I missed the point and you mean the surprising behavior is
that you expect PG to generate WHERE clauses for you automatically.
This seems impossible in the general case.
The surprising behavior is that 'select foo from foo' works, but
'update foo set foo = x::foo' does not.
Then blame the original designers of SQL; they optimized the syntax for
a different set of use cases! Is the symmetry more obvious when you
compare:
SELECT * FROM foo;
with
UPDATE foo SET * = x;
?
--
Sam http://samason.me.uk/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Psql List Languages
Hi,,
My job, I maintainer some postgres server for clients. We have
many PL/(Java, Perl, Ruby, Python, R) and to more easy
administration, I worked new little psql attribute to list languages
com shorcurt/function \dL.
postg...@darkside:/media/disk/devel/pg$ bin/psql -U postgres test
psql (8.4devel)
Type help for help.
test=# \dL
List of languages
Name | Owner | Procedural Language | Trusted |Call
Handler |Validator
--+--+-++-+-
c| postgres | No | Unstrusted |
| fmgr_c_validator
internal | postgres | No | Unstrusted |
| fmgr_internal_validator
plperl | postgres | Yes | Trusted|
plperl_call_handler | plperl_validator
sql | postgres | No | Trusted|
| fmgr_sql_validator
(4 rows)
test=#
I know that this moment is inappropriate to submit patch, with the
discussions about features for 8.4. But, if can added for commitfest
to 8.5 version. I'm appreciate.
Regards,
--
Fernando Ike
http://www.midstorm.org/~fike/weblog
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
***
*** 375,380 exec_command(const char *cmd,
--- 375,383
case 'l':
success = do_lo_list();
break;
+ case 'L':
+ success = listLanguages(pattern, show_verbose);
+ break;
case 'n':
success = listSchemas(pattern, show_verbose);
break;
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***
*** 2018,2023 listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
--- 2018,2081
return true;
}
+ /*
+ * \dL
+ *
+ * Describes Languages.
+ */
+ bool
+ listLanguages(const char *pattern, bool verbose)
+ {
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ initPQExpBuffer(buf);
+
+ printfPQExpBuffer(buf,
+ SELECT l.lanname as \%s\,\n
+ pg_catalog.pg_get_userbyid(l.lanowner) as \%s\,\n
+ CASE WHEN l.lanispl = 't' THEN 'Trusted' WHEN l.lanispl = 'f' THEN 'Untrusted' END AS \%s\,\n
+ CASE WHEN l.lanpltrusted='t' THEN 'Trusted' WHEN lanpltrusted='f' THEN 'Unstrusted' END AS \%s\,\n
+ CASE WHEN p.oid = 0 THEN NULL ELSE p.proname END AS \%s\,\n
+ CASE WHEN q.oid = 0 THEN NULL ELSE q.proname END AS \%s\\n,
+ gettext_noop(Name),
+ gettext_noop(Owner),
+ gettext_noop(Procedural Language),
+ gettext_noop(Trusted),
+ gettext_noop(Call Handler),
+ gettext_noop(Validator));
+
+ if (verbose)
+ {
+ appendPQExpBuffer(buf, ,\n);
+ printACLColumn(buf, l.lanacl);
+ }
+
+ appendPQExpBuffer(buf, FROM pg_catalog.pg_language l\n);
+ appendPQExpBuffer(buf, LEFT JOIN pg_catalog.pg_proc p on l.lanplcallfoid = p.oid\n);
+ appendPQExpBuffer(buf, LEFT JOIN pg_catalog.pg_proc q on l.lanvalidator = q.oid\n);
+
+ processSQLNamePattern(pset.db, buf, pattern, false, false,
+ NULL, l.lanname, NULL, NULL);
+
+ appendPQExpBuffer(buf, ORDER BY 1;);
+
+ res = PSQLexec(buf.data, false);
+ termPQExpBuffer(buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _(List of languages);
+ myopt.translate_header = true;
+
+ printQuery(res, myopt, pset.queryFout, pset.logfile);
+
+ PQclear(res);
+ return true;
+
+ }
/*
* \dD
*** a/src/bin/psql/describe.h
--- b/src/bin/psql/describe.h
***
*** 75,79 extern bool listForeignServers(const char *pattern, bool verbose);
--- 75,81
/* \deu */
extern bool listUserMappings(const char *pattern, bool verbose);
+ /* \dL */
+ extern bool listLanguages(const char *pattern, bool verbose);
#endif /* DESCRIBE_H */
*** a/src/bin/psql/help.c
--- b/src/bin/psql/help.c
***
*** 215,220 slashUsage(unsigned short int pager)
--- 215,221
fprintf(output, _( \\dg [PATTERN]list roles (groups)\n));
fprintf(output, _( \\di[S+] [PATTERN]list indexes\n));
fprintf(output, _( \\dl list large objects, same as \\lo_list\n));
+ fprintf(output, _( \\dL list (procedural) languages\n));
fprintf(output, _( \\dn[+] [PATTERN]list schemas\n));
fprintf(output, _( \\do[S] [PATTERN]list operators\n));
fprintf(output, _( \\dp [PATTERN]list table, view, and sequence access privileges\n));
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] array_map not SQL accessible?
Hi,
I'm wondering why don't we expose the array_map() function to the SQL
level. As it happens, I need to use it in the pg_dump support for TOAST
reloptions.
Why? Well, TOAST reloptions are stored in the pg_class tuple of the
TOAST table, so when I extract them directly, it looks like a simple
array of normally-named reloptions. Like this:
alvherre=# select c.oid,c.relname, c.reloptions, tc.reloptions
alvherre-# from pg_class c join pg_class tc on c.reltoastrelid = tc.oid
alvherre-# where c.relname = 'foo';
oid | relname | reloptions| reloptions
---+-+-+-
48372 | foo | {fillfactor=10} | {fillfactor=15}
(1 fila)
So I need the second array to look like this instead:
toast.fillfactor=15
The easiest way to do that that I can see is using array_map and a
function that prepends toast. to each element.
So, can I just go ahead and try to expose it for this usage?
--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
You liked Linux a lot when he was just the gawky kid from down the block
mowing your lawn or shoveling the snow. But now that he wants to date
your daughter, you're not so sure he measures up. (Larry Greenemeier)
--
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 learn all information on the user of a database?
I think you might want to ask this question on pgsql-novice or pgsql-general. You might also want to take a look at the pg_user and pg_stat_activity views. ...Robert On Fri, Jan 30, 2009 at 7:16 AM, mmf.stave...@gmail.com wrote: How to learn all information on the user of a database? (user name, host name, sleep or running,connect or disconnect) Thanks Regards, Mr. St -- 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
Re: [HACKERS] Should IS DISTINCT FROM work with ANY()?
On Jan 29, 2009, at 5:50 PM, Tom Lane wrote: I don't think we want it to come true. If we treat IS DISTINCT FROM as a weirdly-named operator then we have to provide an implementation for every datatype (oh, and another one for IS NOT DISTINCT FROM). The PITA factor is enormous. Much better to handle it the way we are now, where it's a specialized expression node type. To get it to work with ANY/ALL you'd probably need some special hack to create new sublink types, or something like that. Also a PITA, but a lot more localized ... Okay, I don't know much about the internals, so of course it may be a PITA, but the documentation doesn't really sound like it. Maybe the docs need updating? For example, the documentation for ANY and SOME says: expression operator ANY (array expression) expression operator SOME (array expression) Which makes me think that it will work with any comparison operator. Conveniently, IS (NOT)? DISTINCT FROM is listed on the comparison operators page, which says: expression IS DISTINCT FROM expression expression IS NOT DISTINCT FROM expression Since `ANY(ARRAY['foo'])` is an expression, I had expected it to work. Furthermore, the docs for ANY and SOME say: For non-null inputs, IS DISTINCT FROM is the same as the operator. However, when both inputs are null it will return false, and when just one input is null it will return true. Reading this, I assumed that IS DISTINCT FROM should work with any two operands to which applies. Meaning the underlying function would check for NULL values and return the proper value as appropriate, and simply re-dispatch to the function for the operator if neither operand is NULL. If that's the case, based on the docs, I'd just expect IS DISTINCT FROM ANY() to be supported, and we just have a parsing problem. So maybe this isn't accurate? Should IS DISTINCT FROM *not* be documented as a binary operator? Or maybe it should be documented that it somehow doesn't rely on the = operator internally? IOW, I get that you say it'd be a PITA to support this in in the code, Tom, so maybe the docs should be updated to explain what operands IS DISTINCT FROM can and cannot apply to? Thanks, 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] array_map not SQL accessible?
Alvaro Herrera alvhe...@alvh.no-ip.org writes: I'm wondering why don't we expose the array_map() function to the SQL level. It requires some notion of reference to function, which doesn't really exist in SQL. (Please don't say you're going to pass it a function OID.) As it happens, I need to use it in the pg_dump support for TOAST reloptions. Maybe something involving ARRAY(SELECT foo(x) FROM UNNEST(arrayvariable) x) 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] array_map not SQL accessible?
Tom Lane wrote: Alvaro Herrera alvhe...@alvh.no-ip.org writes: I'm wondering why don't we expose the array_map() function to the SQL level. It requires some notion of reference to function, which doesn't really exist in SQL. (Please don't say you're going to pass it a function OID.) regproc maybe? As it happens, I need to use it in the pg_dump support for TOAST reloptions. Maybe something involving ARRAY(SELECT foo(x) FROM UNNEST(arrayvariable) x) Hmm, I'll have a look at this. -- Alvaro Herrera http://www.PlanetPostgreSQL.org/ Linux transformó mi computadora, de una `máquina para hacer cosas', en un aparato realmente entretenido, sobre el cual cada día aprendo algo nuevo (Jaime Salinas) -- 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] array_map not SQL accessible?
Alvaro Herrera wrote:
Tom Lane wrote:
As it happens, I need to use it in the pg_dump support for TOAST
reloptions.
Maybe something involving
ARRAY(SELECT foo(x) FROM UNNEST(arrayvariable) x)
Hmm, I'll have a look at this.
It seems there's something wrong here.
alvherre=# select c.oid,tc.oid,c.relname, c.reloptions, array(select 'toast.'
|| x from unnest(tc.reloptions) x) from pg_class c join pg_class tc on
c.reltoastrelid = tc.oid where c.relname = 'foo';
-[ RECORD 1
]
oid| 16395
oid| 16398
relname| foo
reloptions | {fillfactor=10,bogusopt=15}
?column? |
{toast.fillfactor=20,toast.\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F}
To reproduce easily:
create table text (a text[]);
insert into text values ('{fillfactor=10,bogusval=20}');
select array(select 'foobar.' || x from unnest(a) x) from text;
--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4
I can see support will not be a problem. 10 out of 10.(Simon Wittber)
(http://archives.postgresql.org/pgsql-general/2004-12/msg00159.php)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ecpg grammar in CVS is annoying
Hi, Is it only me, or having the generated ecpg grammar is a bit obnoxious? I am getting troubled because whenever I touch gram.y it gets updated when I run make, and then it shows in cvs diff. I'm not sure what's the solution -- maybe something is missing in a .cvsignore file somewhere? Oh, another thing -- ecpg has a dependency on libpq, but it is not declared in Makefiles, so if you build ecpg without first building libpq, it errors out. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 Siempre hay que alimentar a los dioses, aunque la tierra esté seca (Orual) -- 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] ecpg grammar in CVS is annoying
Alvaro Herrera wrote: Hi, Is it only me, or having the generated ecpg grammar is a bit obnoxious? I am getting troubled because whenever I touch gram.y it gets updated when I run make, and then it shows in cvs diff. Hmm, and then weird things happen anyway; I just built it and it errors out with: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -DECPG_COMPILE -I../include -I/pgsql/source/00orig/src/interfaces/ecpg/include -I/pgsql/source/00orig/src/interfaces/ecpg/preproc -DMAJOR_VERSION=4 -DMINOR_VERSION=5 -DPATCHLEVEL=0 -I../../../../src/include -I/pgsql/source/00orig/src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o preproc.o /pgsql/source/00orig/src/interfaces/ecpg/preproc/preproc.c -MMD -MP -MF .deps/preproc.Po In file included from /pgsql/source/00orig/src/interfaces/ecpg/preproc/preproc.y:7128: /pgsql/source/00orig/src/interfaces/ecpg/preproc/pgc.l: In function ‘base_yylex’: /pgsql/source/00orig/src/interfaces/ecpg/preproc/pgc.l:484: error: ‘UCONST’ undeclared (first use in this function) /pgsql/source/00orig/src/interfaces/ecpg/preproc/pgc.l:484: error: (Each undeclared identifier is reported only once /pgsql/source/00orig/src/interfaces/ecpg/preproc/pgc.l:484: error: for each function it appears in.) /pgsql/source/00orig/src/interfaces/ecpg/preproc/pgc.l:573: error: ‘UIDENT’ undeclared (first use in this function) make[1]: *** [preproc.o] Error 1 make[1]: se sale del directorio `/home/alvherre/Code/CVS/pgsql/build/00orig/src/interfaces/ecpg/preproc' make: *** [all] Error 2 This is on a clean checkout, so I'm not sure what's going on ... -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 Para tener más hay que desear menos -- 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] ecpg grammar in CVS is annoying
Alvaro Herrera wrote: Oh, another thing -- ecpg has a dependency on libpq, but it is not declared in Makefiles, so if you build ecpg without first building libpq, it errors out. This seems to fix the problem, but I'd prefer a rule that declared the dependency without recursing ... is there a way to do that? -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ The West won the world not by the superiority of its ideas or values or religion but rather by its superiority in applying organized violence. Westerners often forget this fact, non-Westerners never do. (Samuel P. Huntington) Index: src/interfaces/ecpg/ecpglib/Makefile === RCS file: /home/alvherre/cvs/pgsql/src/interfaces/ecpg/ecpglib/Makefile,v retrieving revision 1.62 diff -c -p -r1.62 Makefile *** src/interfaces/ecpg/ecpglib/Makefile 14 Jan 2009 14:54:35 - 1.62 --- src/interfaces/ecpg/ecpglib/Makefile 30 Jan 2009 19:18:12 - *** ifeq ($(PORTNAME), win32) *** 42,48 SHLIB_LINK += -lshfolder endif ! all: all-lib # Shared library stuff include $(top_srcdir)/src/Makefile.shlib --- 42,54 SHLIB_LINK += -lshfolder endif ! all: libpq pgtypeslib all-lib ! ! libpq: ! $(MAKE) -C $(top_builddir)/src/interfaces/libpq all ! ! pgtypeslib: ! $(MAKE) -C $(top_builddir)/src/interfaces/ecpg/pgtypeslib all # Shared library stuff include $(top_srcdir)/src/Makefile.shlib -- 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] parallel restore
Andrew Dunstan and...@dunslane.net writes: Latest patch is attached. Starting to look at this now. One thing that is bothering me is that if the connection parameters are such as to cause prompts for passwords, it's going to be broken beyond usability (multiple threads all trying to read the terminal at once). Is there anything we can do about that? If not, we've at least got to warn people to avoid it in the manual. Also, how does this interact with single_txn mode? I suspect that's just not very sane at all and we should forbid the combination. 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] ecpg grammar in CVS is annoying
Alvaro Herrera alvhe...@alvh.no-ip.org writes: Is it only me, or having the generated ecpg grammar is a bit obnoxious? I am getting troubled because whenever I touch gram.y it gets updated when I run make, and then it shows in cvs diff. Huh? preproc.y was cvs removed two months ago. 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] using composite types in insert/update
On 1/30/09, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: You are missing the point, using the composite type allows you to build the insert without knowing the specific layout of the table... Surely at *some* level you have to know that. You don't (if I understand your meaning) ...you just have to make sure the destination of the insert is the same as the source. With 'tables as composite types', this is trivially easy as long as you make sure the destination schema matches (basically, the whole point of ad-hoc dblink based replication). Fix up the composite types, and you can now make context free triggers that ship records around without exposing any detail of the record except a candidate key, which can be solved by convention (foo-foo_id). merlin -- 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] ecpg grammar in CVS is annoying
Tom Lane wrote: Alvaro Herrera alvhe...@alvh.no-ip.org writes: Is it only me, or having the generated ecpg grammar is a bit obnoxious? I am getting troubled because whenever I touch gram.y it gets updated when I run make, and then it shows in cvs diff. Huh? preproc.y was cvs removed two months ago. Doh! I was missing --delete to rsync (again -- I fixed it on my workstation months ago and had forgotten the laptop ...) Thanks. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 This is a foot just waiting to be shot(Andrew Dunstan) -- 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] reloptions with a namespace
New patch attached, with pg_dump support (thanks to Tom for the SQL
heads-up).
Euler Taveira de Oliveira wrote:
I don't like the spreading validnsps' approach. Isn't there a way to
centralize those variables in one place, i.e., reloption.h ?
Maybe one option is to create a #define with the options valid for
heaps?
Also, remove an obsolete comment about toast tables at reloption.h.
I'm not sure about that one -- maybe one day we'll want to separate the
options for toast tables and those for plain tables (for example, surely
we don't need per-row default security in toast tables, or stuff like
that).
--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
The West won the world not by the superiority of its ideas or values
or religion but rather by its superiority in applying organized violence.
Westerners often forget this fact, non-Westerners never do.
(Samuel P. Huntington)
Index: src/backend/access/common/reloptions.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/common/reloptions.c,v
retrieving revision 1.19
diff -c -p -r1.19 reloptions.c
*** src/backend/access/common/reloptions.c 26 Jan 2009 19:41:06 - 1.19
--- src/backend/access/common/reloptions.c 30 Jan 2009 19:42:38 -
***
*** 390,397
}
/*
! * Transform a relation options list (list of DefElem) into the text array
! * format that is kept in pg_class.reloptions.
*
* This is used for three cases: CREATE TABLE/INDEX, ALTER TABLE SET, and
* ALTER TABLE RESET. In the ALTER cases, oldOptions is the existing
--- 390,399
}
/*
! * Transform a relation options list (list of ReloptElem) into the text array
! * format that is kept in pg_class.reloptions, including only those options
! * that are in the passed namespace. The output values do not include the
! * namespace.
*
* This is used for three cases: CREATE TABLE/INDEX, ALTER TABLE SET, and
* ALTER TABLE RESET. In the ALTER cases, oldOptions is the existing
***
*** 402,415
* in the list (it will be or has been handled by interpretOidsOption()).
*
* Note that this is not responsible for determining whether the options
! * are valid.
*
* Both oldOptions and the result are text arrays (or NULL for default),
* but we declare them as Datums to avoid including array.h in reloptions.h.
*/
Datum
! transformRelOptions(Datum oldOptions, List *defList,
! bool ignoreOids, bool isReset)
{
Datum result;
ArrayBuildState *astate;
--- 404,420
* in the list (it will be or has been handled by interpretOidsOption()).
*
* Note that this is not responsible for determining whether the options
! * are valid, but it does check that namespaces for all the options given are
! * listed in validnsps. The NULL namespace is always valid and needs not be
! * explicitely listed. Passing a NULL pointer means that only the NULL
! * namespace is valid.
*
* Both oldOptions and the result are text arrays (or NULL for default),
* but we declare them as Datums to avoid including array.h in reloptions.h.
*/
Datum
! transformRelOptions(Datum oldOptions, List *defList, char *namspace,
! char *validnsps[], bool ignoreOids, bool isReset)
{
Datum result;
ArrayBuildState *astate;
***
*** 444,454
/* Search for a match in defList */
foreach(cell, defList)
{
! DefElem*def = lfirst(cell);
! int kw_len = strlen(def-defname);
if (text_len kw_len text_str[kw_len] == '='
! pg_strncasecmp(text_str, def-defname, kw_len) == 0)
break;
}
if (!cell)
--- 449,471
/* Search for a match in defList */
foreach(cell, defList)
{
! ReloptElem *def = lfirst(cell);
! int kw_len;
+ /* ignore if not in the same namespace */
+ if (namspace == NULL)
+ {
+ if (def-nmspc != NULL)
+ continue;
+ }
+ else if (def-nmspc == NULL)
+ continue;
+ else if (pg_strcasecmp(def-nmspc, namspace) != 0)
+ continue;
+
+ kw_len = strlen(def-optname);
if (text_len kw_len text_str[kw_len] == '='
! pg_strncasecmp(text_str, def-optname, kw_len) == 0)
break;
}
if (!cell)
***
*** 468,474
*/
foreach(cell, defList)
{
! DefElem*def = lfirst(cell);
if (isReset)
{
--- 485,492
*/
foreach(cell, defList)
{
! ReloptElem*def = lfirst(cell);
!
if (isReset)
{
***
*** 483,504
const char *value;
Size len;
! if (ignoreOids pg_strcasecmp(def-defname, oids) == 0)
continue;
/*
! * Flatten the DefElem into a text string like name=arg. If we
! * have just name, assume name=true is meant.
*/
if (def-arg != NULL)
! value = defGetString(def);
else
value = true;
! len = VARHDRSZ +
Re: [HACKERS] using composite types in insert/update
On 1/30/09, Sam Mason s...@samason.me.uk wrote: quite often (i.e. a VALUES command with many singletons). This seems a bit annoying and appears to be what you were suggesting you wanted before (although you killed the relevant bit of context, making me think we may be talking about different things). we are. See the title of the thread: 'using composite types in insert/update'. that's what I'm talking about. I especially am not talking about the 'values' statement. For several reasons; mainly because SQL is an abortion of a language, it's got no regularity and attempts to justify requirements because of symmetry will end up causing more headaches. Another way of saying what you seem to be saying above is: I want things to work correctly, unless I happen to have a column name that happens to be the same as the table at which point I want everything to break. Upthread, I noted the usefulness in writing triggers. There are many other uses. btw, symmetry (making insert work more similarly to select) is tangential but surely a good thing. Record *types* are most definitely not first class objects; record/composite *values* on the other hand have been gaining support well, I used the terms record types and composite types interchangeably in this discussion. Sorry for the confusion. I don't know if you are arguing for or against the idea of 'update foo set foo = foo' working. (if against, why?) merlin -- 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] using composite types in insert/update
On Fri, Jan 30, 2009 at 02:47:49PM -0500, Merlin Moncure wrote: On 1/30/09, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: You are missing the point, using the composite type allows you to build the insert without knowing the specific layout of the table... Surely at *some* level you have to know that. You don't (if I understand your meaning) ...you just have to make sure the destination of the insert is the same as the source. Sounds as though there are at least two levels that know the specific layout of the tables involved then. 1) PG has to know the structure of the tables, and 2) you application relies on the fact that tables of the same name have the same structure. Sounds like a very simple ah-hoc nominal type system to me. -- Sam http://samason.me.uk/ -- 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] parallel restore
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Latest patch is attached. Starting to look at this now. Excellent! One thing that is bothering me is that if the connection parameters are such as to cause prompts for passwords, it's going to be broken beyond usability (multiple threads all trying to read the terminal at once). Is there anything we can do about that? If not, we've at least got to warn people to avoid it in the manual. I thought I had put in changes to cache the password, so you shouldn't get multiple prompts. That's one reason that we make sure we connect in the main thread before we ever fork/spawn children. Also, how does this interact with single_txn mode? I suspect that's just not very sane at all and we should forbid the combination. Yes. I thought I had done that too, will check. 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
Re: [HACKERS] using composite types in insert/update
Sam Mason wrote: On Fri, Jan 30, 2009 at 02:47:49PM -0500, Merlin Moncure wrote: On 1/30/09, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: You are missing the point, using the composite type allows you to build the insert without knowing the specific layout of the table... Surely at *some* level you have to know that. You don't (if I understand your meaning) ...you just have to make sure the destination of the insert is the same as the source. Sounds as though there are at least two levels that know the specific layout of the tables involved then. 1) PG has to know the structure of the tables, and 2) you application relies on the fact that tables of the What merlin is trying to solve is home-grown replication. By definition, the master and slave must have the same table(s). So I think he is looking for a more elegant method of performing slave updates; rather than mirror.field_a=master.field_a, mirror.field_b=master.field_b, etc... until you are blue in the face. What makes single field updating even worse is the maintained overhead involved if the table structure changes; can't just alter the two tables, you also have to modify the UPDATE statement. same name have the same structure. Sounds like a very simple ah-hoc nominal type system to me. No. Its an ad-hoc replication system. A change to UPDATE is needed for it to work, not a type system. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] using composite types in insert/update
On Fri, Jan 30, 2009 at 03:12:27PM -0500, Merlin Moncure wrote: On 1/30/09, Sam Mason s...@samason.me.uk wrote: quite often (i.e. a VALUES command with many singletons). This seems a bit annoying and appears to be what you were suggesting you wanted before (although you killed the relevant bit of context, making me think we may be talking about different things). we are. See the title of the thread: 'using composite types in insert/update'. that's what I'm talking about. I especially am not talking about the 'values' statement. Humm, I was talking about your example code: INSERT INTO foo VALUES '(something)'::foo; This isn't currently valid, but it sounds as though it needs to be. For several reasons; mainly because SQL is an abortion of a language, it's got no regularity and attempts to justify requirements because of symmetry will end up causing more headaches. Another way of saying what you seem to be saying above is: I want things to work correctly, unless I happen to have a column name that happens to be the same as the table at which point I want everything to break. Upthread, I noted the usefulness in writing triggers. There are many other uses. btw, symmetry (making insert work more similarly to select) is tangential but surely a good thing. I don't know if you are arguing for or against the idea of 'update foo set foo = foo' working. (if against, why?) I agree that the mechanism is good, it's just that the syntax you proposed comes with it's own built in footgun. Symmetry is also muddied by the fact that SELECT and INSERT/UPDATE are built on fundamentally different premises. It's only ever possible to modify a set of rows from one table at a time, whereas a SELECT is designed to work with multiple tables. -- Sam http://samason.me.uk/ -- 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] using composite types in insert/update
On 1/30/09, Sam Mason s...@samason.me.uk wrote: On Fri, Jan 30, 2009 at 03:12:27PM -0500, Merlin Moncure wrote: On 1/30/09, Sam Mason s...@samason.me.uk wrote: quite often (i.e. a VALUES command with many singletons). This seems a bit annoying and appears to be what you were suggesting you wanted before (although you killed the relevant bit of context, making me think we may be talking about different things). we are. See the title of the thread: 'using composite types in insert/update'. that's what I'm talking about. I especially am not talking about the 'values' statement. Humm, I was talking about your example code: INSERT INTO foo VALUES '(something)'::foo; This isn't currently valid, but it sounds as though it needs to be. hm. i don't think so...at least not quite (my thinko in orig example). I think per spec that would attempt to insert the constructed record into the first column. instead, we would want: INSERT INTO foo(foo) VALUES ... or INSERT INTO foo(foo) SELECT ... Assuming we didn't have a foo column, that would tell pg we are pushing in composite type: 'UPDATE' works simillar: SET foo = is the key that we are pushing composite type, not specific fields. I agree that the mechanism is good, it's just that the syntax you proposed comes with it's own built in footgun. Symmetry is also muddied by the fact that SELECT and INSERT/UPDATE are built on fundamentally different premises. It's only ever possible to modify a set of rows from one table at a time, whereas a SELECT is designed to work with multiple tables. double-check that statement vs. example above. I just don't see the problem. Only small gripe I can think of is that since you can't alias the table in the insert statement, if you have a column named 'foo', you're stuck...oh well. I don't think INSERT INTO foo f(f) VALUES ... or INSERT INTO foo(f) f VALUES ... are worth exploring. merlin -- 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] parallel restore
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: One thing that is bothering me is that if the connection parameters are such as to cause prompts for passwords, it's going to be broken beyond usability (multiple threads all trying to read the terminal at once). Is there anything we can do about that? I thought I had put in changes to cache the password, so you shouldn't get multiple prompts. Ah, you can tell I hadn't gotten to the bottom of the patch yet ;-). Still, that's not a 100% solution because of the cases where we use reconnections to change user IDs --- the required password would (usually) vary. It might be sufficient to forbid that case with parallel restore, though; I think it's mostly a legacy thing anyway. Also, how does this interact with single_txn mode? Yes. I thought I had done that too, will check. Yeah, found that 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] using composite types in insert/update
On Fri, Jan 30, 2009 at 03:29:29PM -0500, Andrew Chernow wrote: Sam Mason wrote: On Fri, Jan 30, 2009 at 02:47:49PM -0500, Merlin Moncure wrote: On 1/30/09, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: You are missing the point, using the composite type allows you to build the insert without knowing the specific layout of the table... Surely at *some* level you have to know that. You don't (if I understand your meaning) ...you just have to make sure the destination of the insert is the same as the source. Sounds as though there are at least two levels that know the specific layout of the tables involved then. 1) PG has to know the structure of the tables, and 2) you application relies on the fact that tables of the What merlin is trying to solve is home-grown replication. By definition, the master and slave must have the same table(s). Yes, we know that, but the code doesn't. I was just being pedantic and pointing out where the assumptions of this replication rest. same name have the same structure. Sounds like a very simple ah-hoc nominal type system to me. No. Its an ad-hoc replication system. A change to UPDATE is needed for it to work, not a type system. It seems convenient to think about the resulting assumptions as a type system. It did to me anyway, but apparently this is causing much confusion and it was a bad analogy to have drawn. -- Sam http://samason.me.uk/ -- 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] parallel restore
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: One thing that is bothering me is that if the connection parameters are such as to cause prompts for passwords, it's going to be broken beyond usability (multiple threads all trying to read the terminal at once). Is there anything we can do about that? I thought I had put in changes to cache the password, so you shouldn't get multiple prompts. Ah, you can tell I hadn't gotten to the bottom of the patch yet ;-). Still, that's not a 100% solution because of the cases where we use reconnections to change user IDs --- the required password would (usually) vary. It might be sufficient to forbid that case with parallel restore, though; I think it's mostly a legacy thing anyway. I didn't know such a thing even existed. What causes it to happen? I agree it should be forbidden. 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
Re: [HACKERS] array_map not SQL accessible?
Alvaro Herrera alvhe...@alvh.no-ip.org writes: It seems there's something wrong here. Ah, it's a bug in array_unnest: if its argument is toasted, it detoasts it into function-local memory, and then tries to keep a pointer to that across calls. Boo. Will fix. 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] using composite types in insert/update
On Fri, Jan 30, 2009 at 03:45:54PM -0500, Merlin Moncure wrote: On 1/30/09, Sam Mason s...@samason.me.uk wrote: I was talking about your example code: INSERT INTO foo VALUES '(something)'::foo; This isn't currently valid, but it sounds as though it needs to be. hm. i don't think so...at least not quite (my thinko in orig example). I think per spec that would attempt to insert the constructed record into the first column. bah, it would wouldn't it! why the hell was it designed like that, it's just inviting bugs! I'll use the following table definition below: create temp table foo ( a int, b text ); I was expecting: insert into foo values (1); to fail in the same way as: insert into foo (a,b) values (1); I've never realized before that if you leave off the column list it guesses what you want to do. instead, we would want: INSERT INTO foo(foo) VALUES ... I'm still not sure why anyone would want to do this! or INSERT INTO foo(foo) SELECT ... But why not just leave INSERT as it is, it works and is unambiguous! Assuming we didn't have a foo column, that would tell pg we are pushing in composite type: I still don't see why you want to encourage people to think have I got a similarly named column the whole time. It's fine when you're after some specific column because then you know what it's called and you're asking for it, so when it's the same as the table it's obvious. When it's the table you're asking for you don't want to worry about it breaking when a column gets added. 'UPDATE' works simillar: SET foo = is the key that we are pushing composite type, not specific fields. But why is this better than using a *? I agree that the mechanism is good, it's just that the syntax you proposed comes with it's own built in footgun. Symmetry is also muddied by the fact that SELECT and INSERT/UPDATE are built on fundamentally different premises. It's only ever possible to modify a set of rows from one table at a time, whereas a SELECT is designed to work with multiple tables. double-check that statement vs. example above. I'm not sure what I'm supposed to be checking, more verbosity please! I just don't see the problem. Only small gripe I can think of is that since you can't alias the table in the insert statement, if you have a column named 'foo', you're stuck...oh well. I'm not sure what you are saying; is this a nail in the coffin for using the table name as the key or have you just deliberately introduced the (fuzzy, un-checked) rule that you're not allowed to have a column the same name as the table. -- Sam http://samason.me.uk/ -- 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 get SE-PostgreSQL acceptable
Joshua Brindle wrote: Bruce Momjian wrote: Tom Lane wrote: Gregory Stark st...@enterprisedb.com writes: I don't think partitioning is really the same thing as row-level security. Of course not, but it seems to me that it can be used to accomplish most of the same practical use-cases. The main gripe about doing it via partitioning is that the user's nose gets rubbed in the fact that there can't be an enormous number of different security classifications in the same table (since he has to explicitly make a partition for each one). But the proposed implementation of row-level security would poop out pretty darn quick for such a case, too, and frankly I'm not seeing an application that would demand it. OK, putting on my crazy idea hat, if we split the primary and foreign keys by partition, it would give us polyinstantiation: http://en.wikipedia.org/wiki/Polyinstantiation because our unique indexes do not apply across partitions. Polyinstantiation is a desirable security feature and one that would be tough to implement without partitions. Polyinstantiation in this manner won't do it I don't think (if I'm understanding you correctly). As KaiGai already said, SELinux policy is flexible so we'll have more than just BLP policy to worry about. Also a top secret user will need to see all rows when he selects, and they should still have unique keys. He won't be able to write to secret or unclass rows but he'll be able to see them. Yea, it would take some work but it is an idea. -- 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] How to get SE-PostgreSQL acceptable
Yea, it would take some work but it is an idea. It's *an* idea,yes. But it introduces as many (or more) problems than it solves. --Josh -- 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 get SE-PostgreSQL acceptable
Josh Berkus wrote: Yea, it would take some work but it is an idea. It's *an* idea,yes. But it introduces as many (or more) problems than it solves. Ah, but my problems might be easier solved than the row-level permission problems. ;-) -- 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] using composite types in insert/update
On 1/30/09, Sam Mason s...@samason.me.uk wrote: But why not just leave INSERT as it is, it works and is unambiguous! Because *there is no way to insert a composite type!!!* (you can expand the type via INSERT ... SELECT, but not for UPDATE). SELECT foo FROM foo; pulls the foo composite from the table, not the fields. I still can't understand why you want to not be able to do this via insert. You are looking for more flexible way to imput fields, I am looking for a way to input type directly. But why is this better than using a *? because we are not updating specific fields...'*' denotes 'all columns'. we are setting the type to something else. I want to update the type directly, not it's fields, because I don't want to construct the update statement. (*) is better than *, because at least we are suggesting a composite. However, let's try and keep the syntax a little regular? select foo from foo; -- this is how it works now update foo set foo=somefoo; --why would you want update to work any way but this way? likewise, with aliases select foo f from foo; -- this is how it works now update foo f set f=somefoo; -- again, this is how it should work my only point was that there is no aliases in inserts, so there is a minute probability of case where you can't insert the composite type directly. your idea (i think): update foo f set (*) = somefoo; is a huge departure in syntax and semantics from the way things work in other places. merlin -- 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] using composite types in insert/update
On 1/30/09, Merlin Moncure mmonc...@gmail.com wrote: likewise, with aliases select foo f from foo; -- this is how it works now update foo f set f=somefoo; -- again, this is how it should work thinko: select f from foo f; -- this is how it really works now merlin -- 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] PQinitSSL broken in some use casesf
Andrew Chernow wrote: I am using a library that links with and initializes libcrypto (ie. CRYPTO_set_locking_callback) but not SSL. This causes problems even when using PQinitSSL(FALSE) because things like SSL_library_init(); are not called (unless I manually call them, copy and paste code from fe-secure.c which may change). If libpq does init ssl, it overwrites (and breaks) the other library's crypto. Shouldn't crypto and ssl init be treated as two different things? If not, how does one determine a version portable way of initializing SSL in a manner required by libpq? Lots of apps using encryption but don't necessarily use ssl, so they need to know how to init ssl for libpq. I didn't realize they were could be initialized separately, so we really don't have an answer for you. This is the first time I have heard of this requirement. -- 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] How to get SE-PostgreSQL acceptable
Bruce Momjian wrote: Josh Berkus wrote: Yea, it would take some work but it is an idea. It's *an* idea,yes. But it introduces as many (or more) problems than it solves. Ah, but my problems might be easier solved than the row-level permission problems. ;-) Or might not. Multi-partition indexes? Multi-partition uniqueness? Automated moving of rows between partitions? --Josh -- 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 get SE-PostgreSQL acceptable
Josh Berkus wrote: Bruce Momjian wrote: Josh Berkus wrote: Yea, it would take some work but it is an idea. It's *an* idea,yes. But it introduces as many (or more) problems than it solves. Ah, but my problems might be easier solved than the row-level permission problems. ;-) Or might not. Multi-partition indexes? Multi-partition uniqueness? Automated moving of rows between partitions? Are you trying to make some kind of point? -- 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] How to get SE-PostgreSQL acceptable
Bruce Momjian wrote: Josh Berkus wrote: Bruce Momjian wrote: Josh Berkus wrote: Yea, it would take some work but it is an idea. It's *an* idea,yes. But it introduces as many (or more) problems than it solves. Ah, but my problems might be easier solved than the row-level permission problems. ;-) Or might not. Multi-partition indexes? Multi-partition uniqueness? Automated moving of rows between partitions? Are you trying to make some kind of point? IMVHO Josh was describing a nice-to-have TODO list for a partitions feature in general. :-) Maybe he was saying that when they partitioning feature is designed that they try to think of polyinstantiation as they design it :-) -- 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 get SE-PostgreSQL acceptable
Bruce, Are you trying to make some kind of point? Yeah, that we're certainly not doing any of this for 8.4. If we're going for radical new approaches for row-level, why not also look at the VIEWS approach? If we worked out the same problems we need to fix for Bernd's patch, using automated manatory views to enforce row-level access is also plausible. --Josh -- 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 get SE-PostgreSQL acceptable
Josh Berkus wrote: Bruce, Are you trying to make some kind of point? Yeah, that we're certainly not doing any of this for 8.4. If we're going for radical new approaches for row-level, why not also look at the VIEWS approach? If we worked out the same problems we need to fix for Bernd's patch, using automated manatory views to enforce row-level access is also plausible. Sure, we can explore that too. -- 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] How to get SE-PostgreSQL acceptable
Joshua, Kohei-san, So, for 8.4: *if* we included in 8.4 a version of SEPostgres with all features *except* row-level security, would it still be useful to the SELinux community? I think we're just not going to work out the headache-inducing issues around row-level security in time for 8.4, and it seems to me that integrated system-level security labels at the table-and-column level are still very useful, even without row-level security. --Josh -- 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 get SE-PostgreSQL acceptable
On Fri, Jan 30, 2009 at 5:37 PM, Josh Berkus j...@agliodbs.com wrote: Bruce, Are you trying to make some kind of point? Yeah, that we're certainly not doing any of this for 8.4. If we're going for radical new approaches for row-level, why not also look at the VIEWS approach? If we worked out the same problems we need to fix for Bernd's patch, using automated manatory views to enforce row-level access is also plausible. I'm rather enchanted with the idea of using table partitioning to implement row-level security, but the obstacles seem rather formidable. Right now, a partitioned relation behaves nothing like a regular relation, and to use it for this purpose you'd need to make it transparent. IOW, you'd need to be able to define indices that spanned multiple partitions (including enforcement of unique constraints), you'd need to be able to make foreign keys that could point to a row in arbitrary subset of the partitions, you'd need automatic creation and deletion of partitions, you'd need better planner support for partitions, and you'd need to somehow deal with the issue of pg_class bloat. Plus, to make it truly transparent, you'd need multiple layers of partitioning, in case someone wanted to do row-level security and range partitioning simultaneously. Now, the plus side is that if we could do all of that, we'd have the infrastructure to support some truly awesome partitioning stuff, and not just row-level security. But it seems awfully hard. ...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] How to get SE-PostgreSQL acceptable
Josh Berkus wrote: Joshua, Kohei-san, So, for 8.4: *if* we included in 8.4 a version of SEPostgres with all features *except* row-level security, would it still be useful to the SELinux community? I think we're just not going to work out the headache-inducing issues around row-level security in time for 8.4, and it seems to me that integrated system-level security labels at the table-and-column level are still very useful, even without row-level security. Hasn't a plan for this already been posted? See http://archives.postgresql.org/pgsql-hackers/2009-01/msg02407.php 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
Re: [HACKERS] How to get SE-PostgreSQL acceptable
* Andrew Dunstan (and...@dunslane.net) wrote: Josh Berkus wrote: So, for 8.4: *if* we included in 8.4 a version of SEPostgres with all features *except* row-level security, would it still be useful to the SELinux community? I think we're just not going to work out the headache-inducing issues around row-level security in time for 8.4, and it seems to me that integrated system-level security labels at the table-and-column level are still very useful, even without row-level security. I tend to agree that they will be very useful. I'm not sure there will be much adoption without row-level in the security community though, to be honest. I'd like to see it as part of an overall plan to eventually do row-level support. Given the size of this overall work and feature set, I think it's appropriate to do it in a staged manner regardless. Hasn't a plan for this already been posted? See http://archives.postgresql.org/pgsql-hackers/2009-01/msg02407.php Sure, that's a plan, but Josh's question is certainly appropriate. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] mingw check hung
Andrew Dunstan wrote: Andrew Dunstan wrote: Magnus Hagander wrote: Andrew Dunstan wrote: Magnus Hagander wrote: Are we *sure*, btw, that this is actually a mingw issue, and not something else in the environment? Could you try a MSVC compiled binary on the same machine? My MSVC buildfarm animal runs on the same machine, and does not suffer the same problem. Meh. Stupid mingw :-) So how about we #ifdef out that NULL setting based on WIN32_ONLY_COMPILER, does that seem reasonable? The odd thing is that it doesn't seem to affect Vista, only XP. Anyway, yes, I think that would be OK. How do we then test to see if the original problem is still fixed? Further proof that this is a Windows version issue: I took the problem build from my XP and put it on my Vista box: the same build that causes a problem on XP runs perfectly on Vista. Go figure. Maybe we need a version check at runtime? That would be icky. Eventually does the crash come from the call SetEnvironemntVariable (.., NULL) on mingw-XP(or older?)? I'm also interested in this issue and want to know the cause. However is it necessary to call SetEnvironmentVariable() in the first place? My original patch doesn't contain SetEnvironmentVariable call in pg_unsetenv() because _putenv() seems to call SetEnvironmentVariable internally. regards, Hiroshi Inoue -- 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] mingw check hung
Hiroshi Inoue wrote: Eventually does the crash come from the call SetEnvironemntVariable (.., NULL) on mingw-XP(or older?)? I'm also interested in this issue and want to know the cause. The debugger shows that we actually fail on a popen() call in intdb. However, if we replace the calls to SetEnvironmentVariable(foo,NULL) with calls to SetEnvironmentVariable(foo,) then there is no failure. My theory is that on XP somehow the former is corrupting the environment such that when popen() tries to copy the environment for the new child process, it barfs. 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
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Josh Berkus wrote: Joshua, Kohei-san, So, for 8.4: *if* we included in 8.4 a version of SEPostgres with all features *except* row-level security, would it still be useful to the SELinux community? Yes, obviously. I think the granularity of access controls is an aspect of security. I think we're just not going to work out the headache-inducing issues around row-level security in time for 8.4, and it seems to me that integrated system-level security labels at the table-and-column level are still very useful, even without row-level security. For example, table-and-column level access control can provide such a worth which enables to store customer's credit-card-number within unaccessable column from all the web application (children of Apache) but accessable from settlement system (child of crond). It enables to prevent SQL injection to steal very sensitive info. Thanks, -- KaiGai Kohei kai...@kaigai.gr.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] How to get SE-PostgreSQL acceptable
Andrew Dunstan wrote: Josh Berkus wrote: Joshua, Kohei-san, So, for 8.4: *if* we included in 8.4 a version of SEPostgres with all features *except* row-level security, would it still be useful to the SELinux community? I think we're just not going to work out the headache-inducing issues around row-level security in time for 8.4, and it seems to me that integrated system-level security labels at the table-and-column level are still very useful, even without row-level security. Hasn't a plan for this already been posted? See http://archives.postgresql.org/pgsql-hackers/2009-01/msg02407.php FYI: * previous full-functional SE-PostgreSQL/Row-ACLs [kai...@fedora10 security]$ wc -l *.c */*.c 729 pgaceCommon.c 1547 pgaceHooks.c 721 rowacl/rowacl.c 1200 sepgsql/avc.c 623 sepgsql/core.c 1019 sepgsql/hooks.c 785 sepgsql/permissions.c 1097 sepgsql/proxy.c 7721 total * A lite SE-PostgreSQL without row-level security, large object support, writable system column [kai...@fedora10 sepgsql]$ wc -l *.c 904 checker.c 1181 avc.c 360 core.c 55 dummy.c 683 hooks.c 478 label.c 553 perms.c 4214 total Today, I'll debug the modified code... -- KaiGai Kohei kai...@kaigai.gr.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] How to get SE-PostgreSQL acceptable
KaiGai Kohei wrote: Hasn't a plan for this already been posted? See http://archives.postgresql.org/pgsql-hackers/2009-01/msg02407.php FYI: * previous full-functional SE-PostgreSQL/Row-ACLs [kai...@fedora10 security]$ wc -l *.c */*.c 729 pgaceCommon.c 1547 pgaceHooks.c 721 rowacl/rowacl.c 1200 sepgsql/avc.c 623 sepgsql/core.c 1019 sepgsql/hooks.c 785 sepgsql/permissions.c 1097 sepgsql/proxy.c 7721 total * A lite SE-PostgreSQL without row-level security, large object support, writable system column [kai...@fedora10 sepgsql]$ wc -l *.c 904 checker.c 1181 avc.c 360 core.c 55 dummy.c 683 hooks.c 478 label.c 553 perms.c 4214 total Today, I'll debug the modified code... Wow, that was fast. Where are you storing the security information for tables and columns? Did you add a special column to pg_class, etc? -- 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] How to get SE-PostgreSQL acceptable
Bruce Momjian wrote: KaiGai Kohei wrote: Hasn't a plan for this already been posted? See http://archives.postgresql.org/pgsql-hackers/2009-01/msg02407.php FYI: * previous full-functional SE-PostgreSQL/Row-ACLs [kai...@fedora10 security]$ wc -l *.c */*.c 729 pgaceCommon.c 1547 pgaceHooks.c 721 rowacl/rowacl.c 1200 sepgsql/avc.c 623 sepgsql/core.c 1019 sepgsql/hooks.c 785 sepgsql/permissions.c 1097 sepgsql/proxy.c 7721 total * A lite SE-PostgreSQL without row-level security, large object support, writable system column [kai...@fedora10 sepgsql]$ wc -l *.c 904 checker.c 1181 avc.c 360 core.c 55 dummy.c 683 hooks.c 478 label.c 553 perms.c 4214 total Today, I'll debug the modified code... Wow, that was fast. Where are you storing the security information for tables and columns? Did you add a special column to pg_class, etc? Security information is stored within padding field of HeapTupleHeader as we did. It can be fetched via sepgsql_(table|column|...)_getcon() functions, and can be set via SECURITY_LABEL = 'xxx'. -- KaiGai Kohei kai...@kaigai.gr.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] How to get SE-PostgreSQL acceptable
KaiGai Kohei wrote: Today, I'll debug the modified code... Wow, that was fast. Where are you storing the security information for tables and columns? Did you add a special column to pg_class, etc? Security information is stored within padding field of HeapTupleHeader as we did. It can be fetched via sepgsql_(table|column|...)_getcon() functions, and can be set via SECURITY_LABEL = 'xxx'. Well, we are not using row-level security values so why not store it in its own column regular or as part of the existing ACL structure. I think it will be very odd for system tables to have this special column but not user rows. -- 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] 8.4 release planning
On Thursday 29 January 2009 12:03:45 Robert Haas wrote: I don't believe that you can speed a project up much by adjusting the length of the release cycle, but it is *sometimes* possible to speed up a project by dividing up the work over more people. This is interesting. We had a problem in 8.3 (and most of the releases before that) of too many patches in the queue at the end of the development cycle. Most everyone agreed that more reviewers/committers would help, but given no way to conjure them up, they realized that wasn't a solution. Instead, we went to a tighter development cycle, with one month of dev and then a commifest. This allowed us to better parralelize both reviews and commits, allowed a number of patches to get bumped through multiple fests with relatively few compliants (after all, the next fest was just a month down the line), keep the patch queue pretty manageable (right up untill the end, when we stopped the cycle), and also delivered us some really big features along the way. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] reloptions with a namespace
Alvaro Herrera escreveu:
New patch attached, with pg_dump support (thanks to Tom for the SQL
heads-up).
Great! We're close. Just two minor gripes:
+ char *validnsps[] = { toast };
Surely, you forgot to add a NULL at the end. Patch is attached.
IIRC, my last patch includes a partial validation code for RESET cases. For
example, the last SQL will not be atomic (invalid reloption silently ignored).
So, why not apply the namespace validation code to RESET case too? Patch is
attached too. It does not handle the reloptions validation because the relOpts
initialization code is at parseRelOptions(); i leave it for a future refactor.
euler=# create table foo (a text) with (fillfactor=10);
CREATE TABLE
euler=# \d+ foo
Tabela public.foo
Coluna | Tipo | Modificadores | Storage | Descrição
+--+---+--+---
a | text | | extended |
Têm OIDs: não
Options: fillfactor=10
euler=# alter table foo reset (fillfactor,foo.fillfactor);
ALTER TABLE
euler=# \d+ foo
Tabela public.foo
Coluna | Tipo | Modificadores | Storage | Descrição
+--+---+--+---
a | text | | extended |
Têm OIDs: não
--
Euler Taveira de Oliveira
http://www.timbira.com/
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
--
-- Name: plperl; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: euler
--
CREATE PROCEDURAL LANGUAGE plperl;
ALTER PROCEDURAL LANGUAGE plperl OWNER TO euler;
--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: euler
--
CREATE PROCEDURAL LANGUAGE plpgsql;
ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO euler;
--
-- Name: plpythonu; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: euler
--
CREATE PROCEDURAL LANGUAGE plpythonu;
ALTER PROCEDURAL LANGUAGE plpythonu OWNER TO euler;
--
-- Name: pltcl; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: euler
--
CREATE PROCEDURAL LANGUAGE pltcl;
ALTER PROCEDURAL LANGUAGE pltcl OWNER TO euler;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: tst1; Type: TABLE; Schema: public; Owner: euler; Tablespace:
--
CREATE TABLE tst1 (
a text
)
WITH (fillfactor=10);
ALTER TABLE public.tst1 OWNER TO euler;
--
-- Name: tst2; Type: TABLE; Schema: public; Owner: euler; Tablespace:
--
CREATE TABLE tst2 (
a text
)
WITH (toast.fillfactor=20);
ALTER TABLE public.tst2 OWNER TO euler;
--
-- Name: tst3; Type: TABLE; Schema: public; Owner: euler; Tablespace:
--
CREATE TABLE tst3 (
a text
)
WITH (fillfactor=10, toast.fillfactor=20);
ALTER TABLE public.tst3 OWNER TO euler;
--
-- Data for Name: tst1; Type: TABLE DATA; Schema: public; Owner: euler
--
COPY tst1 (a) FROM stdin;
\.
--
-- Data for Name: tst2; Type: TABLE DATA; Schema: public; Owner: euler
--
COPY tst2 (a) FROM stdin;
\.
--
-- Data for Name: tst3; Type: TABLE DATA; Schema: public; Owner: euler
--
COPY tst3 (a) FROM stdin;
\.
--
-- Name: public; Type: ACL; Schema: -; Owner: euler
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM euler;
GRANT ALL ON SCHEMA public TO euler;
GRANT ALL ON SCHEMA public TO PUBLIC;
--
-- PostgreSQL database dump complete
--
*** pgsql.alvaro/src/backend/access/common/reloptions.c 2009-01-31
02:01:21.0 -0200
--- pgsql.euler/src/backend/access/common/reloptions.c 2009-01-31
02:16:29.0 -0200
***
*** 487,492
--- 487,519
{
ReloptElem*def = lfirst(cell);
+ /*
+* Error out if the namespace is not valid. A NULL namespace
+* is always valid.
+*/
+ if (def-nmspc != NULL)
+ {
+ boolvalid = false;
+ int i;
+
+ if (validnsps)
+ {
+ for (i = 0; validnsps[i]; i++)
+ {
+ if (pg_strcasecmp(def-nmspc,
validnsps[i]) == 0)
+ {
+ valid = true;
+ break;
+ }
+ }
+ }
+
+ if (!valid)
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+errmsg(unrecognized parameter
namespace \%s\,
+ def-nmspc)));
+ }
if (isReset)
{
Re: [HACKERS] reloptions with a namespace
Euler Taveira de Oliveira escreveu:
[Forgot the first patch...]
Alvaro Herrera escreveu:
New patch attached, with pg_dump support (thanks to Tom for the SQL
heads-up).
Great! We're close. Just two minor gripes:
+ char *validnsps[] = { toast };
Surely, you forgot to add a NULL at the end. Patch is attached.
IIRC, my last patch includes a partial validation code for RESET cases. For
example, the last SQL will not be atomic (invalid reloption silently ignored).
So, why not apply the namespace validation code to RESET case too? Patch is
attached too. It does not handle the reloptions validation because the relOpts
initialization code is at parseRelOptions(); i leave it for a future refactor.
euler=# create table foo (a text) with (fillfactor=10);
CREATE TABLE
euler=# \d+ foo
Tabela public.foo
Coluna | Tipo | Modificadores | Storage | Descrição
+--+---+--+---
a | text | | extended |
Têm OIDs: não
Options: fillfactor=10
euler=# alter table foo reset (fillfactor,foo.fillfactor);
ALTER TABLE
euler=# \d+ foo
Tabela public.foo
Coluna | Tipo | Modificadores | Storage | Descrição
+--+---+--+---
a | text | | extended |
Têm OIDs: não
--
Euler Taveira de Oliveira
http://www.timbira.com/
diff -cr pgsql.alvaro/src/backend/commands/tablecmds.c
pgsql.euler/src/backend/commands/tablecmds.c
*** pgsql.alvaro/src/backend/commands/tablecmds.c 2009-01-31
02:01:22.0 -0200
--- pgsql.euler/src/backend/commands/tablecmds.c2009-01-31
01:47:32.0 -0200
***
*** 351,357
Datum reloptions;
ListCell *listptr;
AttrNumber attnum;
! char *validnsps[] = { toast };
/*
* Truncate relname to appropriate length (probably a waste of time, as
--- 351,357
Datum reloptions;
ListCell *listptr;
AttrNumber attnum;
! static char*validnsps[] = { toast, NULL };
/*
* Truncate relname to appropriate length (probably a waste of time, as
***
*** 6459,6465
Datum repl_val[Natts_pg_class];
boolrepl_null[Natts_pg_class];
boolrepl_repl[Natts_pg_class];
! char *validnsps[] = { toast };
if (defList == NIL)
return; /* nothing to do */
--- 6459,6465
Datum repl_val[Natts_pg_class];
boolrepl_null[Natts_pg_class];
boolrepl_repl[Natts_pg_class];
! static char*validnsps[] = { toast, NULL };
if (defList == NIL)
return; /* nothing to do */
diff -cr pgsql.alvaro/src/backend/executor/execMain.c
pgsql.euler/src/backend/executor/execMain.c
*** pgsql.alvaro/src/backend/executor/execMain.c2009-01-31
02:01:22.0 -0200
--- pgsql.euler/src/backend/executor/execMain.c 2009-01-31 01:48:19.0
-0200
***
*** 2832,2838
Oid intoRelationId;
TupleDesc tupdesc;
DR_intorel *myState;
! char *validnsps[] = { toast };
Assert(into);
--- 2832,2838
Oid intoRelationId;
TupleDesc tupdesc;
DR_intorel *myState;
! static char*validnsps[] = { toast, NULL };
Assert(into);
Somente em pgsql.euler/src/backend/parser: gram.c
Somente em pgsql.euler/src/backend/parser: gram.h
Somente em pgsql.euler/src/backend/parser: scan.c
diff -cr pgsql.alvaro/src/backend/tcop/utility.c
pgsql.euler/src/backend/tcop/utility.c
*** pgsql.alvaro/src/backend/tcop/utility.c 2009-01-31 02:01:22.0
-0200
--- pgsql.euler/src/backend/tcop/utility.c 2009-01-31 01:47:51.0
-0200
***
*** 424,430
if (IsA(stmt, CreateStmt))
{
Datum toast_options;
! char *validnsps[] = { toast
};
/* Create the table itself */
relOid =
DefineRelation((CreateStmt *) stmt,
--- 424,430
if (IsA(stmt, CreateStmt))
{
Datum toast_options;
! static char *validnsps[] = {
toast, NULL };
/* Create the table itself */
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Bruce Momjian wrote: KaiGai Kohei wrote: Today, I'll debug the modified code... Wow, that was fast. Where are you storing the security information for tables and columns? Did you add a special column to pg_class, etc? Security information is stored within padding field of HeapTupleHeader as we did. It can be fetched via sepgsql_(table|column|...)_getcon() functions, and can be set via SECURITY_LABEL = 'xxx'. Well, we are not using row-level security values so why not store it in its own column regular or as part of the existing ACL structure. I think it will be very odd for system tables to have this special column but not user rows. Sorry, my description might easily make confusion. I read it again myself, indeed, it makes confusion. :( SECURITY_LABEL = 'xxx' means following sytle: CREATE TABLE t ( a int, b text SECURITY_LABEL = '...' ) SECURITY_LABEL = '...'; I don't provide both of security_label and security_acl system columns for system/user tables. I didn't write it explicitly, it might make you confusing. User cannot see what security label is assigned to them due to lack of system column, so new sepgsql_xxx_getcon() functions are provided an interface to see security label. In this patch, I don't touch new system columns. Thanks, -- KaiGai Kohei kai...@kaigai.gr.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] parallel restore
Okay, another question --- there are two places in pg_backup_custom.c where the patch #ifdef's out hasSeek tests on WIN32. Why is that? If checkSeek() is wrong on Windows, wouldn't it be better to fix 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
