Re: [PATCHES] INSERT ... RETURNING
Bruce, Are you still working on completing this? Attached is my latest rewrite of this patch. Basically, it adds RETURNING to INSERT/UPDATE/DELETE, and allows arbitrary expressions. I'm still not sure how to handle DELETE ... USING and UPDATE ... FROM as I don't see a way to project the expressions against multiple relations with just an EState to work from. That said, Tom has suggested I redo this using resjunk -- so I'll do just that for my next revision. Regards, Omar Index: doc/src/sgml/keywords.sgml === RCS file: /home/omar/cvs/cvs/pgsql/doc/src/sgml/keywords.sgml,v retrieving revision 2.15 diff -C6 -r2.15 keywords.sgml *** doc/src/sgml/keywords.sgml 27 Nov 2004 21:27:06 - 2.15 --- doc/src/sgml/keywords.sgml 30 Jul 2005 06:37:02 - *** *** 3234,3245 --- 3234,3252 entry/entry entrynon-reserved/entry entrynon-reserved/entry entrynon-reserved/entry /row row + entrytokenRETURNING/token/entry + entryreserved/entry + entry/entry + entry/entry + entry/entry +/row +row entrytokenRETURNS/token/entry entrynon-reserved/entry entryreserved/entry entryreserved/entry entry/entry /row Index: doc/src/sgml/ref/delete.sgml === RCS file: /home/omar/cvs/cvs/pgsql/doc/src/sgml/ref/delete.sgml,v retrieving revision 1.24 diff -C6 -r1.24 delete.sgml *** doc/src/sgml/ref/delete.sgml8 Apr 2005 00:59:58 - 1.24 --- doc/src/sgml/ref/delete.sgml30 Jul 2005 06:37:02 - *** *** 20,31 --- 20,32 refsynopsisdiv synopsis DELETE FROM [ ONLY ] replaceable class=PARAMETERtable/replaceable [ USING replaceable class=PARAMETERusinglist/replaceable ] [ WHERE replaceable class=PARAMETERcondition/replaceable ] + [ RETURNING replaceable class=PARAMETERexpression/replaceable [, ...] ] /synopsis /refsynopsisdiv refsect1 titleDescription/title *** *** 102,131 A value expression that returns a value of type typeboolean/type that determines the rows which are to be deleted. /para /listitem /varlistentry /variablelist /refsect1 refsect1 titleOutputs/title para !On successful completion, a commandDELETE/ command returns a command !tag of the form screen DELETE replaceable class=parametercount/replaceable /screen The replaceable class=parametercount/replaceable is the number of rows deleted. If replaceable class=parametercount/replaceable is 0, no rows matched the replaceable class=parametercondition/replaceable (this is not considered an error). /para /refsect1 refsect1 titleNotes/title para --- 103,155 A value expression that returns a value of type typeboolean/type that determines the rows which are to be deleted. /para /listitem /varlistentry + +varlistentry + term + literalRETURNING/literal + replaceable class=PARAMETERexpression/replaceable [, ...] + /term + listitem + para +An optional list of expressions to return. + /para + /listitem +/varlistentry /variablelist /refsect1 refsect1 titleOutputs/title para !On successful completion, a commandDELETE/ command without a !literalRETURNING/literal clause returns a command tag of the form screen DELETE replaceable class=parametercount/replaceable /screen The replaceable class=parametercount/replaceable is the number of rows deleted. If replaceable class=parametercount/replaceable is 0, no rows matched the replaceable class=parametercondition/replaceable (this is not considered an error). /para + + indexterm zone=sql-delete +primaryRETURNING/primary +secondaryDELETE/secondary + /indexterm + + para +If a literalRETURNING/literal clause is present, the expression +specified is evaluated for each deleted row and the result is +returned. + /para /refsect1 refsect1 titleNotes/title para *** *** 158,183 programlisting DELETE FROM films WHERE kind lt;gt; 'Musical'; /programlisting /para para Clear the table literalfilms/literal: programlisting DELETE FROM films; /programlisting /para /refsect1 refsect1 titleCompatibility/title para This command conforms to the SQL standard, except that the !literalUSING/ clause and the ability to reference other tables !in the literalWHERE/ clause are productnamePostgreSQL/ extensions. /para /refsect1 /refentry !-- Keep this comment at the end of the file --- 182,220 programlisting DELETE FROM films WHERE kind lt;gt; 'Musical';
Re: [PATCHES] [HACKERS] Autovacuum loose ends
Alvaro Herrera wrote: I still haven't added custom cost-based delays, but I don't see that as a showstopper for removing it. I just went through the CVS log and I don't see anything else that applies. I think you should at least add an autovacuum specific value for vacuum_cost_delay because it turns cost-based vacuum delay on or off. I believe not many will have vacuum_cost_delay enabled in postgresql.conf, but will want to enable it for autovacuum. At least I do. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] PL/Perl list value return causes segfault
David Fetter wrote: *** 716,724 listitem para ! In the current implementation, if you are fetching or returning ! very large data sets, you should be aware that these will all go ! into memory. /para /listitem /itemizedlist --- 766,776 listitem para ! If you are fetching or returning very large data sets using ! literalspi_exec_query/literal, you should be aware that ! these will all go into memory. You can avoid this by using ! literalspi_query/literal/literalspi_fetchrow/literal as ! illustrated earlier. /para /listitem /itemizedlist You have rolled 2 problems into one - spi_query+spi_fetchrow does not address the issue of returning large data sets. Suggest instead: para If you are fetching very large data sets using literalspi_exec_query/literal, you should be aware that these will all go into memory. You can avoid this by using literalspi_query/literal and literalspi_fetchrow/literal as illustrated earlier. /para para A similar problem occurs if a set-returning function passes a large set of rows back to postgres via literalreturn/literal. You can avoid this problem too by instead using literalreturn_next/literal for each row returned, as shown previously. /para cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Win32 build broken by recent changes to xlog.c
Magnus Hagander wrote: Seems it's dead on the buildfarm box as well: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=snakedt=2005-07-30%20 01:00:01 From what I can tell, the recent patch for O_DIRECT broke it. OK, fix attached, and some macro cleanup too. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: src/backend/access/transam/xlog.c === RCS file: /cvsroot/pgsql/src/backend/access/transam/xlog.c,v retrieving revision 1.213 diff -c -c -r1.213 xlog.c *** src/backend/access/transam/xlog.c 29 Jul 2005 19:29:59 - 1.213 --- src/backend/access/transam/xlog.c 30 Jul 2005 14:14:43 - *** *** 69,85 * default method.We assume that fsync() is always available, and that * configure determined whether fdatasync() is. */ ! #if defined(O_SYNC) #define CMP_OPEN_SYNC_FLAGO_SYNC ! #else ! #if defined(O_FSYNC) #define CMP_OPEN_SYNC_FLAGO_FSYNC #endif ! #endif #define OPEN_SYNC_FLAG(CMP_OPEN_SYNC_FLAG | PG_O_DIRECT) ! #if defined(O_DSYNC) ! #if defined(OPEN_SYNC_FLAG) #if O_DSYNC != CMP_OPEN_SYNC_FLAG #define OPEN_DATASYNC_FLAG(O_DSYNC | PG_O_DIRECT) #endif --- 69,86 * default method.We assume that fsync() is always available, and that * configure determined whether fdatasync() is. */ ! #ifdef O_SYNC #define CMP_OPEN_SYNC_FLAGO_SYNC ! #elif defined(O_FSYNC) #define CMP_OPEN_SYNC_FLAGO_FSYNC #endif ! #ifdef CMP_OPEN_SYNC_FLAG #define OPEN_SYNC_FLAG(CMP_OPEN_SYNC_FLAG | PG_O_DIRECT) + #endif ! #ifdef O_DSYNC ! #ifdef OPEN_SYNC_FLAG ! /* O_DSYNC is distinct? */ #if O_DSYNC != CMP_OPEN_SYNC_FLAG #define OPEN_DATASYNC_FLAG(O_DSYNC | PG_O_DIRECT) #endif *** *** 114,120 #define XLOG_BUFFER_POINTERALIGN(PTR) \ POINTERALIGN((ALIGNOF_XLOG_BUFFER), (PTR)) ! #if defined(OPEN_DATASYNC_FLAG) #define DEFAULT_SYNC_METHOD_STR open_datasync #define DEFAULT_SYNC_METHOD SYNC_METHOD_OPEN #define DEFAULT_SYNC_FLAGBIT OPEN_DATASYNC_FLAG --- 115,121 #define XLOG_BUFFER_POINTERALIGN(PTR) \ POINTERALIGN((ALIGNOF_XLOG_BUFFER), (PTR)) ! #ifdef OPEN_DATASYNC_FLAG #define DEFAULT_SYNC_METHOD_STR open_datasync #define DEFAULT_SYNC_METHOD SYNC_METHOD_OPEN #define DEFAULT_SYNC_FLAGBIT OPEN_DATASYNC_FLAG ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PATCHES] Updated instrumentation patch
Per recent discussion, here is yet another updated version of the instrumentation patch. Changes: * Added guc option disable_remote_admin, that disables any write operations (write, unlink, rename) even for the superuser. Set as PGC_POSTMASTER so it cannot be changed remotely. I put this under file locations, because that's where all the other config file information is. Though that doesn't feel completely right, I couldn't find a better place without creating a whole new category (it's not *connection* security, after all), and if that's to be done I think it's better if one of the committers pick name etc for it :-) * Make sure pg_file_stat() can only be used by superuser. It lacked this check previously. * Updated so it applies to current cvs. This means all oids have changed, since they were all used for other things now. Also added a required header that had moved with the datetime stuff. Actual code changes against the previous patch are very small. //Magnus instrumentation.new.patch Description: instrumentation.new.patch genfile.c Description: genfile.c ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] Autovacuum loose ends
Michael Paesold [EMAIL PROTECTED] writes: Alvaro Herrera wrote: I still haven't added custom cost-based delays, but I don't see that as a showstopper for removing it. I just went through the CVS log and I don't see anything else that applies. I think you should at least add an autovacuum specific value for vacuum_cost_delay because it turns cost-based vacuum delay on or off. It occurs to me that you could have that today, using the knowledge that the autovac daemon runs as the bootstrap user: use ALTER USER SET to attach user-specific vacuum delay settings to that role. This is a pretty bletcherous solution, because (a) it requires knowledge of an undocumented implementation detail and (b) it would interfere with using that role for normal manual maintenance. So I agree that a few extra GUC settings would be better. But we could get away without 'em. Along the same lines, it was suggested that we need a way to disable stats gathering on a per-database basis. We already have it: you can use ALTER DATABASE SET to control stats_row_level and stats_block_level that way. Neither of the above two objections apply to this usage, so I think we can mark off that wishlist item as done. (Of course, the soon-to-appear autovac documentation had better mention this trick.) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] Autovacuum loose ends
Tom Lane wrote: Michael Paesold [EMAIL PROTECTED] writes: Alvaro Herrera wrote: I still haven't added custom cost-based delays, but I don't see that as a showstopper for removing it. I just went through the CVS log and I don't see anything else that applies. I think you should at least add an autovacuum specific value for vacuum_cost_delay because it turns cost-based vacuum delay on or off. It occurs to me that you could have that today, using the knowledge that the autovac daemon runs as the bootstrap user: use ALTER USER SET to attach user-specific vacuum delay settings to that role. This is a pretty bletcherous solution, because (a) it requires knowledge of an undocumented implementation detail and (b) it would interfere with using that role for normal manual maintenance. So I agree that a few extra GUC settings would be better. But we could get away without 'em. Along the same lines, it was suggested that we need a way to disable stats gathering on a per-database basis. We already have it: you can use ALTER DATABASE SET to control stats_row_level and stats_block_level that way. Neither of the above two objections apply to this usage, so I think we can mark off that wishlist item as done. (Of course, the soon-to-appear autovac documentation had better mention this trick.) I am thinking we should move ahead with what we have now, suggest the work-arounds, and thensee what use-cases we have for it for later releases. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Updated instrumentation patch
I just realised the entry for pg_file_rename is duplicated in pg_proc.h. Unless someone can say it's a good thing (it was in the original patch..), please remove one of those entries before applying. It breaks the opr_sanity test. //Magnus -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Magnus Hagander Sent: Saturday, July 30, 2005 4:39 PM To: PostgreSQL-patches Subject: [PATCHES] Updated instrumentation patch Per recent discussion, here is yet another updated version of the instrumentation patch. Changes: * Added guc option disable_remote_admin, that disables any write operations (write, unlink, rename) even for the superuser. Set as PGC_POSTMASTER so it cannot be changed remotely. I put this under file locations, because that's where all the other config file information is. Though that doesn't feel completely right, I couldn't find a better place without creating a whole new category (it's not *connection* security, after all), and if that's to be done I think it's better if one of the committers pick name etc for it :-) * Make sure pg_file_stat() can only be used by superuser. It lacked this check previously. * Updated so it applies to current cvs. This means all oids have changed, since they were all used for other things now. Also added a required header that had moved with the datetime stuff. Actual code changes against the previous patch are very small. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Interval-day docs and regression tests
Patch applied. Thanks. --- Michael Glaesemann wrote: Please find attached diffs for documentation and simple regression tests for the new interval-day changes. I added tests for justify_hours() and justify_days() to interval.sql, as they take interval input and produce interval output. If there's a more appropriate place for them, please let me know. I've included the diff in the email as well (below) for ease of review. Michael Glaesemann grzm myrealbox com Index: doc/src/sgml/func.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.269 diff -c -r1.269 func.sgml *** doc/src/sgml/func.sgml22 Jul 2005 21:16:14 -1.269 --- doc/src/sgml/func.sgml26 Jul 2005 00:43:49 - *** *** 4903,4908 --- 4903,4926 such pair. /para + para +When adding an typeinterval/type value to (or subtracting an +typeinterval/type value from) a typetimestamp with time zone/type +value, the days component advances (or decrements) the date of the +typetimestamp with time zonetype by the indicated number of days. +Across daylight saving time changes (with the session tiem zone set to a +time zone that recognizes DST), this means literalinterval '1 day'/literal +does not necessarily equal literalinterval '24 hours'/literal. +For example, with the session time zone set to literalCST7CDT/ literal +literaltimestamp with time zone '2005-04-02 12:00-07' + interval '1 day' /literal +will produce literaltimestamp with time zone '2005-04-03 12:00-06'/literal, +while adding literalinterval '24 hours'/literal to the same initial +typetimestamp with time zone/type produces +literaltimestamp with time zone '2005-04-03 13:00-06'/ literal, as there is +a change in daylight saving time at literal2005-04-03 02:00/ literal in time zone +literalCST7CDT/literal. + /para + table id=operators-datetime-table titleDate/Time Operators/title Index: src/test/regress/expected/horology.out === RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/ horology.out,v retrieving revision 1.56 diff -c -r1.56 horology.out *** src/test/regress/expected/horology.out27 May 2005 21:31:23 -1.56 --- src/test/regress/expected/horology.out26 Jul 2005 00:43:49 - *** *** 598,603 --- 598,630 t (1 row) + -- timestamp with time zone, interval arithmetic around DST change + SET TIME ZONE 'CST7CDT'; + SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' as Apr 3, 12:00; + Apr 3, 12:00 + -- + Sun Apr 03 12:00:00 2005 CDT + (1 row) + + SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '24 hours' as Apr 3, 13:00; + Apr 3, 13:00 + -- + Sun Apr 03 13:00:00 2005 CDT + (1 row) + + SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '1 day' as Apr 2, 12:00; + Apr 2, 12:00 + -- + Sat Apr 02 12:00:00 2005 CST + (1 row) + + SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '24 hours' as Apr 2, 11:00; + Apr 2, 11:00 + -- + Sat Apr 02 11:00:00 2005 CST + (1 row) + + RESET TIME ZONE; SELECT timestamptz(date '1994-01-01', time '11:00') AS Jan_01_1994_10am; Jan_01_1994_10am -- Index: src/test/regress/expected/interval.out === RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/ interval.out,v retrieving revision 1.12 diff -c -r1.12 interval.out *** src/test/regress/expected/interval.out20 Jul 2005 16:42:32 -1.12 --- src/test/regress/expected/interval.out26 Jul 2005 00:43:49 - *** *** 228,230 --- 228,243 @ 4541 years 4 mons 4 days 17 mins 31 secs (1 row) + -- test justify_hours() and justify_days() + SELECT justify_hours(interval '6 months 3 days 52 hours 3 minutes 2 seconds') as 6 mons 5 days 4 hours 3 mins 2 seconds; + 6 mons 5 days 4 hours 3 mins 2 seconds + + @ 6 mons 5 days 4 hours 3 mins 2 secs + (1 row) + + SELECT justify_days(interval '6 months 36 days 5 hours 4 minutes 3 seconds') as 7 mons 6 days 5 hours 4 mins 3 seconds; + 7 mons 6 days 5 hours 4 mins 3 seconds + + @ 7 mons 6 days 5 hours 4 mins 3 secs + (1 row) + Index: src/test/regress/sql/horology.sql === RCS file:
Re: [PATCHES] [HACKERS] Autovacuum loose ends
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I think you should at least add an autovacuum specific value for vacuum_cost_delay because it turns cost-based vacuum delay on or off. I am thinking we should move ahead with what we have now, suggest the work-arounds, and thensee what use-cases we have for it for later releases. I think it's absolutely unquestionable that there is a use-case for running autovac with different vacuum-delay settings than you would want to apply to manually issued vacuums. We don't need to wait for field experience on that one; we already have it with the contrib version. So do we need to add new GUC variables? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Autovacuum loose ends
Bruce Momjian pgman@candle.pha.pa.us writes: I think you should at least add an autovacuum specific value for vacuum_cost_delay because it turns cost-based vacuum delay on or off. I am thinking we should move ahead with what we have now, suggest the work-arounds, and thensee what use-cases we have for it for later releases. I think it's absolutely unquestionable that there is a use-case for running autovac with different vacuum-delay settings than you would want to apply to manually issued vacuums. We don't need to wait for field experience on that one; we already have it with the contrib version. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Updated instrumentation patch
Magnus Hagander [EMAIL PROTECTED] writes: Per recent discussion, here is yet another updated version of the instrumentation patch. Changes: * Added guc option disable_remote_admin, that disables any write operations (write, unlink, rename) even for the superuser. Set as PGC_POSTMASTER so it cannot be changed remotely. I was envisioning it as disabling all filesystem access --- read as well as write. Essentially the abstract concept I want is that with this on, even a superuser cannot use Postgres to get at the underlying operating system. A name like enable_filesystem_access would probably be more appropriate. Also, as I already said, marking it as PGC_POSTMASTER is simply not adequate security. Once we have some sort of remote admin feature, I would expect it to support adjustment of even postmaster-level options (this would mean forcing a database restart of course) --- you can hardly say that you have a complete remote admin solution if you can't change shared_buffers or max_connections. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Updated instrumentation patch
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Per recent discussion, here is yet another updated version of the instrumentation patch. Changes: * Added guc option disable_remote_admin, that disables any write operations (write, unlink, rename) even for the superuser. Set as PGC_POSTMASTER so it cannot be changed remotely. I was envisioning it as disabling all filesystem access --- read as well as write. Essentially the abstract concept I want is that with this on, even a superuser cannot use Postgres to get at the underlying operating system. A name like enable_filesystem_access would probably be more appropriate. Also, as I already said, marking it as PGC_POSTMASTER is simply not adequate security. Once we have some sort of remote admin feature, I would expect it to support adjustment of even postmaster-level options (this would mean forcing a database restart of course) --- you can hardly say that you have a complete remote admin solution if you can't change shared_buffers or max_connections. How does this affect COPY? Is it not important because COPY can not write a null byte? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Updated instrumentation patch
Magnus Hagander wrote: Per recent discussion, here is yet another updated version of the instrumentation patch. Changes: * Added guc option disable_remote_admin, that disables any write operations (write, unlink, rename) even for the superuser. Set as PGC_POSTMASTER so it cannot be changed remotely. I was envisioning it as disabling all filesystem access --- read as well as write. Essentially the abstract concept I want is that with this on, even a superuser cannot use Postgres to get at the underlying operating system. A name like enable_filesystem_access would probably be more appropriate. Um. I thought the entire argument was about *writing* files. But it should be easy enough to stick requireRemoteAdmin() to all the functions. For the long term I was thinking something like restrict_superuser, which would disable both read and write, and COPY, and untrusted PL creation, etc, etc. But that's not for 8.1. Also, as I already said, marking it as PGC_POSTMASTER is simply not adequate security. Once we have some sort of remote admin feature, I would expect it to support adjustment of even postmaster-level options (this would mean forcing a database restart of course) --- you can hardly say that you have a complete remote admin solution if you can't change shared_buffers or max_connections. The point is you cannot *enable* it once it is *disabled*. Thus you cannot *elevate* your privileges. Thus not a security issue. I think any secure solution is going to have to block all write access to postgresql.conf, and that includes all the COPY TO and all the untrusted languages. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Updated instrumentation patch
Also, as I already said, marking it as PGC_POSTMASTER is simply not adequate security. Once we have some sort of remote admin feature, I would expect it to support adjustment of even postmaster-level options (this would mean forcing a database restart of course) --- you can hardly say that you have a complete remote admin solution if you can't change shared_buffers or max_connections. The point is you cannot *enable* it once it is *disabled*. Thus you cannot *elevate* your privileges. Thus not a security issue. I think any secure solution is going to have to block all write access to postgresql.conf, and that includes all the COPY TO and all the untrusted languages. Exactly. But we won't get that for 8.1. So for now, we block all write access through *new* functions, per the let's at least not add more security holes rule. //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Patch to mention cost-based delay in vacuum reference
Updated patch applied. Thanks. --- Alvaro Herrera wrote: Hackers, Subject says it all. Please review the grammar ... -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) Et put se mouve (Galileo Galilei) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: ref/vacuum.sgml === RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/ref/vacuum.sgml,v retrieving revision 1.36 diff -c -r1.36 vacuum.sgml *** ref/vacuum.sgml 2 Dec 2004 19:28:48 - 1.36 --- ref/vacuum.sgml 29 Jul 2005 17:03:36 - *** *** 174,179 --- 174,188 to occupy less disk space. commandVACUUM FULL/command will usually shrink the table more than a plain commandVACUUM/command would. /para + +para + During commandVACUUM/command execution, there can be a substantial + increase in I/O traffic, which cause poor performance for other active + sessions. Therefore, it is sometimes advisable to use + the cost-based vacuum delay feature. See xref + linkend=runtime endterm=runtime-config-resource-vacuum-cost for more + details. +/para /refsect1 refsect1 *** *** 229,234 --- 238,244 simplelist type=inline memberxref linkend=app-vacuumdb endterm=app-vacuumdb-title/member +memberxref linkend=runtime endterm=runtime-config-resource-vacuum-cost/member /simplelist /refsect1 /refentry ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Updated instrumentation patch
I think any secure solution is going to have to block all write access to postgresql.conf, and that includes all the COPY TO and all the untrusted languages. Exactly. But we won't get that for 8.1. So for now, we block all write access through *new* functions, per the let's at least not add more security holes rule. As far as I know, the only new functionality the patch adds _over_ copy is the ability to write nulls, and rename/unlink. Should we just throw an error when writing null bytes? Um. Yes. This patch goes one step further and allows you to block the writing of *any* file using these functions. The question is wether that one step further is far enough.. //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Updated instrumentation patch
Magnus Hagander wrote: I think any secure solution is going to have to block all write access to postgresql.conf, and that includes all the COPY TO and all the untrusted languages. Exactly. But we won't get that for 8.1. So for now, we block all write access through *new* functions, per the let's at least not add more security holes rule. As far as I know, the only new functionality the patch adds _over_ copy is the ability to write nulls, and rename/unlink. Should we just throw an error when writing null bytes? Um. Yes. This patch goes one step further and allows you to block the writing of *any* file using these functions. The question is wether that one step further is far enough.. I am thinking we can just block null byte writes and say it is the same as COPY, which we have always used. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Updated instrumentation patch
Magnus Hagander [EMAIL PROTECTED] writes: For the long term I was thinking something like restrict_superuser, which would disable both read and write, and COPY, and untrusted PL creation, etc, etc. But that's not for 8.1. That's exactly what I'm talking about. Also, as I already said, marking it as PGC_POSTMASTER is simply not adequate security. Once we have some sort of remote admin feature, I would expect it to support adjustment of even postmaster-level options (this would mean forcing a database restart of course) --- you can hardly say that you have a complete remote admin solution if you can't change shared_buffers or max_connections. The point is you cannot *enable* it once it is *disabled*. Thus you cannot *elevate* your privileges. Thus not a security issue. It will be as soon as we have remote admin. Once we have a real remote admin API, it becomes an argument, and it will have to be adjusted. But we don't have that today, and I see no need to create a new guc category just for this. After all, some of these functions will probably go away completely once we have such an API. None of these functions are getting into 8.1 anyway; we should be designing the long-term solution not making up short-lived hacks. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] P.tch to mention cost-based delay in vacuum reference
On Sat, Jul 30, 2005 at 11:46:37AM -0400, Bruce Momjian wrote: Updated patch applied. Thanks. Huh, something is very wrong with the markup: http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-vacuum.html -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) Having your biases confirmed independently is how scientific progress is made, and hence made our great society what it is today (Mary Gardiner) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Updated instrumentation patch
Once we have a real remote admin API, it becomes an argument, and it will have to be adjusted. But we don't have that today, and I see no need to create a new guc category just for this. After all, some of these functions will probably go away completely once we have such an API. None of these functions are getting into 8.1 anyway; we should be designing the long-term solution not making up short-lived hacks. I'm sorry, but then why the did my question: And finally, with something like that in place, would you be fine with the file editing functions as they stand (limiting them to the pg directories, as I believe it does)? get the answer: I'm OK with them even without the directory limitation as long as there's a way to disable them. If you had just said from the start that these functions would not be accepted even if the specific concerns raised were fixed, a lot of time invested by a lot of people would not have been necessary. I guess I just join the rank of people giving up on this. Too bad for the people who want to be able to remotely admin their stuff, because I now think everybody who actually cared have given up. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Autovacuum loose ends
On Sat, Jul 30, 2005 at 10:57:15AM -0400, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I think you should at least add an autovacuum specific value for vacuum_cost_delay because it turns cost-based vacuum delay on or off. I am thinking we should move ahead with what we have now, suggest the work-arounds, and thensee what use-cases we have for it for later releases. I think it's absolutely unquestionable that there is a use-case for running autovac with different vacuum-delay settings than you would want to apply to manually issued vacuums. We don't need to wait for field experience on that one; we already have it with the contrib version. So do we need to add new GUC variables? I was thinking in a GUC var for global setting, and a column in pg_autovacuum for individual, per table setting. Just one, for the vacuum_cost_limit parameter; I don't think we really need settable cost parameters. A case could be made for setting the vacuum_cost_delay parameter as well. Thoughts? -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) Es filósofo el que disfruta con los enigmas (G. Coli) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Updated instrumentation patch
-Original Message- From: [EMAIL PROTECTED] on behalf of Tom Lane Sent: Sat 7/30/2005 4:58 PM To: Magnus Hagander Cc: PostgreSQL-patches Subject: Re: [PATCHES] Updated instrumentation patch None of these functions are getting into 8.1 anyway; we should be designing the long-term solution not making up short-lived hacks. So, going back to pre 8.0, we fixed them so they don't work outside of the data directory as requested, yet they were not included for unknown reasons. We revisited some weeks before prior to feature freeze, and I researched all issues raised and ask for clarification on what you weren't happy with as all I'd found in the archives was a sentence along the lines of I really don't see any value in these. I found no outstanding issues in the archives, nor did I receive any in response to my questions. Having received no further objections, the patch was added to the queue. As soon as Bruce starts to look at it, presumably to apply it, you decide it's an unnacceptable security problem, and say you'd be perfectly happy if there was a GUC to disable the potentially dangerous functions. This info would have been nice before feature freeze, but, OK, I appreciate you're busy. Magnus updates the patch because he's yet another one of us that thinks this is useful functionality and adds the GUC you said would make you happy with these functions. You then state, with no discussion at all, that they're not going into 8.1 anyway, despite us doing everything you have asked. I have two questions if I may: 1) Is there any point us working on any kind of enhanced API for remote admin in the future, or will the same treatment be given to that? 2) Do you now have sole say over what does and doesn't go into the project? I don't mean to be disrespectful - your hard work and skills are hugely appreciated by the whole community, but I know for a fact that a number of them, who between them have contributed thousands of hours and lines of code to the project (and I'm talking about the core project, never mind pgAdmin et al) cannot understand your apparent insistence on us not providing remote admin capabilities. I think we simply need clarification on how the project works these days. Regards, Dave ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] P.tch to mention cost-based delay in vacuum reference
Alvaro Herrera wrote: On Sat, Jul 30, 2005 at 11:46:37AM -0400, Bruce Momjian wrote: Updated patch applied. Thanks. Huh, something is very wrong with the markup: http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-vacuum.html OK, fixed. link and linkend should not both be used. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] P.tch to mention cost-based delay in vacuum reference
On Sat, Jul 30, 2005 at 12:06:11PM -0400, Alvaro Herrera wrote: On Sat, Jul 30, 2005 at 11:46:37AM -0400, Bruce Momjian wrote: Updated patch applied. Thanks. Huh, something is very wrong with the markup: http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-vacuum.html This should fix it. Sorry. -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) Crear es tan difícil como ser libre (Elsa Triolet) Index: runtime.sgml === RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/runtime.sgml,v retrieving revision 1.340 diff -c -r1.340 runtime.sgml *** runtime.sgml30 Jul 2005 15:17:18 - 1.340 --- runtime.sgml30 Jul 2005 17:04:24 - *** *** 1380,1386 /sect3 sect3 id=runtime-config-resource-vacuum-cost ! titleCost-Based Vacuum Delay/title para During the execution of xref linkend=sql-vacuum --- 1380,1388 /sect3 sect3 id=runtime-config-resource-vacuum-cost ! title id=runtime-config-resource-vacuum-cost-title !Cost-Based Vacuum Delay ! /title para During the execution of xref linkend=sql-vacuum Index: ref/vacuum.sgml === RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/ref/vacuum.sgml,v retrieving revision 1.37 diff -c -r1.37 vacuum.sgml *** ref/vacuum.sgml 30 Jul 2005 15:45:49 - 1.37 --- ref/vacuum.sgml 30 Jul 2005 17:04:40 - *** *** 180,186 increase in I/O traffic, which cause poor performance for other active sessions. Therefore, it is sometimes advisable to use the cost-based vacuum delay feature. See xref ! linkend=runtime endterm=runtime-config-resource-vacuum-cost for more details. /para /refsect1 --- 180,186 increase in I/O traffic, which cause poor performance for other active sessions. Therefore, it is sometimes advisable to use the cost-based vacuum delay feature. See xref ! linkend=runtime endterm=runtime-config-resource-vacuum-cost-title for more details. /para /refsect1 *** *** 238,244 simplelist type=inline memberxref linkend=app-vacuumdb endterm=app-vacuumdb-title/member !memberxref linkend=runtime endterm=runtime-config-resource-vacuum-cost/member /simplelist /refsect1 /refentry --- 238,244 simplelist type=inline memberxref linkend=app-vacuumdb endterm=app-vacuumdb-title/member !memberxref linkend=runtime endterm=runtime-config-resource-vacuum-cost-title/member /simplelist /refsect1 /refentry ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] P.tch to mention cost-based delay in vacuum reference
On Sat, Jul 30, 2005 at 01:04:28PM -0400, Bruce Momjian wrote: Alvaro Herrera wrote: On Sat, Jul 30, 2005 at 11:46:37AM -0400, Bruce Momjian wrote: Updated patch applied. Thanks. Huh, something is very wrong with the markup: http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-vacuum.html OK, fixed. link and linkend should not both be used. Hmm. I like my solution best because the section title will appear in the link. -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) Porque Kim no hacia nada, pero, eso sí, con extraordinario éxito (Kim, Kipling) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] P.tch to mention cost-based delay in vacuum reference
OK, I backed out my fix and applied your, and tested the output. --- Alvaro Herrera wrote: On Sat, Jul 30, 2005 at 12:06:11PM -0400, Alvaro Herrera wrote: On Sat, Jul 30, 2005 at 11:46:37AM -0400, Bruce Momjian wrote: Updated patch applied. Thanks. Huh, something is very wrong with the markup: http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-vacuum.html This should fix it. Sorry. -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) Crear es tan dif?cil como ser libre (Elsa Triolet) [ Attachment, skipping... ] -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Interval-day docs and regression tests
Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: Please find attached diffs for documentation and simple regression tests for the new interval-day changes. The buildfarm results suggest that justify_days is broken in the integer-datetimes case, eg from panda: *** ./expected/interval.out Sat Jul 30 16:20:48 2005 --- ./results/interval.out Sat Jul 30 16:24:31 2005 *** *** 238,243 SELECT justify_days(interval '6 months 36 days 5 hours 4 minutes 3 seconds') as 7 mons 6 days 5 hours 4 mins 3 seconds; 7 mons 6 days 5 hours 4 mins 3 seconds ! @ 7 mons 6 days 5 hours 4 mins 3 secs (1 row) --- 238,243 SELECT justify_days(interval '6 months 36 days 5 hours 4 minutes 3 seconds') as 7 mons 6 days 5 hours 4 mins 3 seconds; 7 mons 6 days 5 hours 4 mins 3 seconds ! @ 1 mon 186 days 5 hours 4 mins 3 secs (1 row) Thanks, fixed and code cleaned up. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: src/backend/utils/adt/timestamp.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v retrieving revision 1.146 diff -c -c -r1.146 timestamp.c *** src/backend/utils/adt/timestamp.c 24 Jul 2005 04:37:07 - 1.146 --- src/backend/utils/adt/timestamp.c 30 Jul 2005 18:19:14 - *** *** 1915,1922 #ifdef HAVE_INT64_TIMESTAMP result-time += span-day * USECS_PER_DAY; ! result-day = result-time / USECS_PER_DAY; ! result-time -= result-day * USECS_PER_DAY; #else result-time += span-day * (double)SECS_PER_DAY; TMODULO(result-time, result-day, (double)SECS_PER_DAY); --- 1915,1921 #ifdef HAVE_INT64_TIMESTAMP result-time += span-day * USECS_PER_DAY; ! TMODULO(result-time, result-day, USECS_PER_DAY); #else result-time += span-day * (double)SECS_PER_DAY; TMODULO(result-time, result-day, (double)SECS_PER_DAY); *** *** 1939,1952 result-day = span-day; result-time = span-time; ! #ifdef HAVE_INT64_TIMESTAMP ! result-day += span-month * (double)DAYS_PER_MONTH; ! result-month = span-day / DAYS_PER_MONTH; ! result-day -= result-month * DAYS_PER_MONTH; ! #else ! result-day += span-month * (double)DAYS_PER_MONTH; ! TMODULO(result-day, result-month, (double)DAYS_PER_MONTH); ! #endif PG_RETURN_INTERVAL_P(result); } --- 1938,1945 result-day = span-day; result-time = span-time; ! result-day += span-month * DAYS_PER_MONTH; ! TMODULO(result-day, result-month, DAYS_PER_MONTH); PG_RETURN_INTERVAL_P(result); } ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] per user/database connections limit again
Here is promised documentation.
Be warned that both my writing skills and my english are far from good :)
--
Regards
Petr Jelinek (PJMODOS)
Index: doc/src/sgml/catalogs.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.109
diff -c -r2.109 catalogs.sgml
*** doc/src/sgml/catalogs.sgml 26 Jul 2005 16:38:25 - 2.109
--- doc/src/sgml/catalogs.sgml 30 Jul 2005 18:48:10 -
***
*** 1019,1024
--- 1019,1035
/row
row
+ entrystructfieldrolconnlimit/structfield/entry
+ entrytypeint4/type/entry
+ entry/entry
+ entry
+For roles that can login this sets maximum amount of concurrent
+connections this role can make. Default value (-1) means
+unlimited connections, zero (0) means role can't login.
+ /entry
+ /row
+
+ row
entrystructfieldrolpassword/structfield/entry
entrytypetext/type/entry
entry/entry
***
*** 1922,1927
--- 1933,1949
/row
row
+ entrystructfielddatconnlimit/structfield/entry
+ entrytypeint4/type/entry
+ entry/entry
+ entry
+Sets maximum amount of concurrent connections that can be made
+to this database. Default value (-1) means unlimited connections,
+zero (0) means that database isn't accepting connections.
+ /entry
+ /row
+
+ row
entrystructfielddatlastsysoid/structfield/entry
entrytypeoid/type/entry
entry/entry
***
*** 4812,4817
--- 4834,4850
/row
row
+ entrystructfieldrolconnlimit/structfield/entry
+ entrytypeint4/type/entry
+ entry/entry
+ entry
+For roles that can login this sets maximum amount of concurrent
+connections this role can make. Default value (-1) means
+unlimited connections, zero (0) means role can't login.
+ /entry
+ /row
+
+ row
entrystructfieldrolpassword/structfield/entry
entrytypetext/type/entry
entry/entry
***
*** 5094,5099
--- 5127,5143
/row
row
+ entrystructfielduseconnlimit/structfield/entry
+ entrytypeint4/type/entry
+ entry/entry
+ entry
+This sets maximum amount of concurrent connections this user can make.
+Default value (-1) means unlimited connections,
+zero (0) means user can't login.
+ /entry
+ /row
+
+ row
entrystructfieldpasswd/structfield/entry
entrytypetext/type/entry
entry/entry
Index: doc/src/sgml/ref/alter_database.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/alter_database.sgml,v
retrieving revision 1.15
diff -c -r1.15 alter_database.sgml
*** doc/src/sgml/ref/alter_database.sgml5 Jan 2005 14:22:39 -
1.15
--- doc/src/sgml/ref/alter_database.sgml30 Jul 2005 18:48:11 -
***
*** 23,28
--- 23,34
ALTER DATABASE replaceable class=PARAMETERname/replaceable SET
replaceableparameter/replaceable { TO | = } {
replaceablevalue/replaceable | DEFAULT }
ALTER DATABASE replaceable class=PARAMETERname/replaceable RESET
replaceableparameter/replaceable
+ ALTER DATABASE replaceable class=PARAMETERname/replaceable [ [ WITH ]
replaceable class=PARAMETERoption/replaceable [ ... ] ]
+
+ where replaceable class=PARAMETERoption/replaceable can be:
+
+ CONNECTION LIMIT replaceable class=PARAMETERconnlimit/replaceable
+
ALTER DATABASE replaceable class=PARAMETERname/replaceable RENAME TO
replaceablenewname/replaceable
ALTER DATABASE replaceable class=PARAMETERname/replaceable OWNER TO
replaceablenew_owner/replaceable
***
*** 51,57
/para
para
!The third form changes the name of the database. Only the database
owner or a superuser can rename a database; non-superuser owners must
also have the
literalCREATEDB/literal privilege. The current database cannot
--- 57,68
/para
para
!The third form changes certain per-database settings. (See below for
!details.) Only database owner or superuser can change these settings.
! /para
!
! para
!The fourth form changes the name of the database. Only the database
owner or a superuser can rename a database; non-superuser owners must
also have the
literalCREATEDB/literal privilege. The current database cannot
***
*** 60,66
/para
para
!The fourth form changes the owner of the database. Only a superuser
can change the database's owner.
/para
/refsect1
--- 71,77
/para
para
!The fifth form changes the owner of the database. Only a superuser
can change the database's owner.
/para
/refsect1
Re: [PATCHES] PL/PGSQL: Dynamic Record Introspection
Bruce Momjian wrote: Your patch has been added to the PostgreSQL unapplied patches list It was agreed earlier that this should be saved for 8.2, I believe. -Neil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] PL/Perl list value return causes segfault
On Sat, Jul 30, 2005 at 09:47:58AM -0400, Andrew Dunstan wrote:
David Fetter wrote:
You have rolled 2 problems into one - spi_query+spi_fetchrow does not
address the issue of returning large data sets.
Suggest instead:
[suggestion]
Revised patch attached. Thanks for catching this :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Index: doc/src/sgml/plperl.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plperl.sgml,v
retrieving revision 2.42
diff -c -r2.42 plperl.sgml
*** doc/src/sgml/plperl.sgml13 Jul 2005 02:10:42 - 2.42
--- doc/src/sgml/plperl.sgml31 Jul 2005 00:33:00 -
***
*** 46,52
para
To create a function in the PL/Perl language, use the standard
xref linkend=sql-createfunction endterm=sql-createfunction-title
!syntax:
programlisting
CREATE FUNCTION replaceablefuncname/replaceable
(replaceableargument-types/replaceable) RETURNS
replaceablereturn-type/replaceable AS $$
# PL/Perl function body
--- 46,57
para
To create a function in the PL/Perl language, use the standard
xref linkend=sql-createfunction endterm=sql-createfunction-title
!syntax. A PL/Perl function must always return a scalar value. You
!can return more complex structures (arrays, records, and sets)
!in the appropriate context by returning a reference.
!Never return a list. Here follows an example of a PL/Perl
!function.
!
programlisting
CREATE FUNCTION replaceablefuncname/replaceable
(replaceableargument-types/replaceable) RETURNS
replaceablereturn-type/replaceable AS $$
# PL/Perl function body
***
*** 282,288
/para
para
!PL/Perl provides two additional Perl commands:
variablelist
varlistentry
--- 287,293
/para
para
!PL/Perl provides three additional Perl commands:
variablelist
varlistentry
***
*** 293,303
termliteralfunctionspi_exec_query/(replaceablequery/replaceable [,
replaceablemax-rows/replaceable])/literal/term
termliteralfunctionspi_exec_query/(replaceablecommand/replaceable)/literal/term
listitem
para
!Executes an SQL command. Here is an example of a query
!(commandSELECT/command command) with the optional maximum
!number of rows:
programlisting
$rv = spi_exec_query('SELECT * FROM my_table', 5);
/programlisting
--- 298,315
termliteralfunctionspi_exec_query/(replaceablequery/replaceable [,
replaceablemax-rows/replaceable])/literal/term
termliteralfunctionspi_exec_query/(replaceablecommand/replaceable)/literal/term
+
termliteralfunctionspi_query/(replaceablecommand/replaceable)/literal/term
+
termliteralfunctionspi_fetchrow/(replaceablecommand/replaceable)/literal/term
+
listitem
para
!literalspi_exec_query/literal executes an SQL command and
! returns the entire rowset as a reference to an array of hash
! references. emphasisYou should only use this command when you know
! that the result set will be relatively small./emphasis Here is an
! example of a query (commandSELECT/command command) with the
! optional maximum number of rows:
!
programlisting
$rv = spi_exec_query('SELECT * FROM my_table', 5);
/programlisting
***
*** 345,351
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');
! CREATE FUNCTION test_munge() RETURNS SETOF test AS $$
my $rv = spi_exec_query('select i, v from test;');
my $status = $rv-gt;{status};
my $nrows = $rv-gt;{processed};
--- 357,363
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');
! CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
my $rv = spi_exec_query('select i, v from test;');
my $status = $rv-gt;{status};
my $nrows = $rv-gt;{processed};
***
*** 360,366
SELECT * FROM test_munge();
/programlisting
! /para
/listitem
/varlistentry
--- 372,416
SELECT * FROM test_munge();
/programlisting
! /para
! para
! literalspi_query/literal and literalspi_fetchrow/literal
! work together as a pair for rowsets which may be large, or for cases
! where you wish to return rows as they arrive.
! literalspi_fetchrow/literal works emphasisonly/emphasis with
! literalspi_query/literal. The following example illustrates how
! you use them together:
!
! programlisting
! CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);
!
! CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
! use Digest::MD5 qw(md5_hex);
! my $file = '/usr/share/dict/words';
! my $t = localtime;
! elog(NOTICE, opening
Re: [PATCHES] per user/database connections limit again
Bruce Momjian pgman@candle.pha.pa.us writes: I have worked over your patch and I think it is ready for application. I've made another pass over this and should be able to commit tomorrow (I'm about to knock off for today, and ran out of time to test pg_dumpall). One thing I changed was that it didn't make sense to me for CREATE DATABASE to copy the template database's datconnlimit. We don't copy its datallowconn or datconfig, so why datconnlimit? BTW I disagree with removing datallowconn; that is different from datconnlimit = 0 because it is enforced even against superusers. (Similar remarks apply for rolcanlogin vs rolconnlimit.) regards, tom lane bin7rNzkZLnar.bin Description: connlimit.patch.gz ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] PL/PGSQL: Dynamic Record Introspection
This has been saved for the 8.2 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Titus von Boxberg wrote: Tom Lane schrieb: Titus von Boxberg [EMAIL PROTECTED] writes: It works for me if we want to have an NFIELDS construct. Personally I'm still not convinced that we need one --- what's the use-case? I have removed the NFIELDS construct I'd prefer arbitrary expression, but I suppose there's no harm in doing the simple case first and generalizing if there's demand. I took the no harm way. Attached please find the updated patch. The patch is against HEAD of 050721. I switched the syntax to your proposal, renamed the functions in pl_comp.c and updated the sgml doc source and regression test files accordingly. Regards Titus *** ./doc/src/sgml/plpgsql.sgml.orig Sat Jul 2 08:59:47 2005 --- ./doc/src/sgml/plpgsql.sgml Sat Jul 23 17:24:54 2005 *** *** 867,872 --- 867,921 /para para + To obtain the values of the fields the record is made up of, + the record variable can be qualified with the column or field + name. This can be done either by literally using the column name + or the column name for indexing the record can be taken out of a scalar + variable. The syntax for this notation is Record_variable.(IndexVariable). + To get information about the column field names of the record, + a special expression exists that returns all column names as an array: + RecordVariable.(*) . + Thus, the RECORD can be viewed + as an associative array that allows for introspection of it's contents. + This feature is especially useful for writing generic triggers that + operate on records with unknown structure. + Here is an example procedure that shows column names and values + of the predefined record NEW in a trigger procedure: + programlisting + + CREATE OR REPLACE FUNCTION show_associative_records() RETURNS TRIGGER AS $$ + DECLARE + colname TEXT; + colcontent TEXT; + colnamesTEXT[]; + colnINT4; + coliINT4; + BEGIN + -- obtain an array with all field names of the record + colnames := NEW.(*); + RAISE NOTICE 'All column names of test record: %', colnames; + -- show field names and contents of record + coli := 1; + coln := array_upper(colnames,1); + RAISE NOTICE 'Number of columns in NEW: %', coln; + FOR coli IN 1 .. coln LOOP + colname := colnames[coli]; + colcontent := NEW.(colname); + RAISE NOTICE 'column % of NEW: %', quote_ident(colname), quote_literal(colcontent); + END LOOP; + -- Do it with a fixed field name: + -- will have to know the column name + RAISE NOTICE 'column someint of NEW: %', quote_literal(NEW.someint); + RETURN NULL; + END; + $$ LANGUAGE plpgsql; + --CREATE TABLE test_records (someint INT8, somestring TEXT); + --CREATE TRIGGER tr_test_record BEFORE INSERT ON test_records FOR EACH ROW EXECUTE PROCEDURE show_associative_records(); + + /programlisting +/para + +para Note that literalRECORD/ is not a true data type, only a placeholder. One should also realize that when a applicationPL/pgSQL/application function is declared to return type typerecord/, this is not quite the *** ./src/pl/plpgsql/src/pl_comp.c.orig Wed Jul 6 16:42:10 2005 --- ./src/pl/plpgsql/src/pl_comp.cThu Jul 21 21:28:15 2005 *** *** 995,1001 new = palloc(sizeof(PLpgSQL_recfield)); new-dtype = PLPGSQL_DTYPE_RECFIELD; ! new-fieldname = pstrdup(cp[1]); new-recparentno = ns-itemno; plpgsql_adddatum((PLpgSQL_datum *) new); --- 995,1002 new = palloc(sizeof(PLpgSQL_recfield)); new-dtype = PLPGSQL_DTYPE_RECFIELD; ! new-fieldindex.fieldname = pstrdup(cp[1]); ! new-fieldindex_flag = RECFIELD_USE_FIELDNAME; new-recparentno = ns-itemno; plpgsql_adddatum((PLpgSQL_datum *) new); *** *** 1101,1107 new = palloc(sizeof(PLpgSQL_recfield)); new-dtype = PLPGSQL_DTYPE_RECFIELD; ! new-fieldname = pstrdup(cp[2]); new-recparentno = ns-itemno; plpgsql_adddatum((PLpgSQL_datum *) new); ---
