Re: [PATCHES] Proposed patch: synchronized_scanning GUC variable
Hi Russell, On Jan 28, 2008 7:27 AM, Russell Smith <[EMAIL PROTECTED]> wrote: > Can somebody explain why it's important to load with > synchronized_scanning off? > > do_sql_command(g_conn, "SET synchronized_scanning TO off"); It's the start point of this patch. See this thread [ http://archives.postgresql.org/pgsql-hackers/2008-01/msg00987.php ] for more information. -- Guillaume ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Proposed patch: synchronized_scanning GUC variable
On Mon, 2008-01-28 at 17:27 +1100, Russell Smith wrote: > Can somebody explain why it's important to load with > synchronized_scanning off? *Loading* with synchronized scanning off is not important (and is not implemented by the patch). *Dumping* with synchronized scanning off is necessary to ensure that the order of the rows in the pg_dump matches the on-disk order of the rows in the table, which is important if you want to preserve the clustering of the table data on restore. See the -hackers thread: http://markmail.org/message/qbytsco6oj2qkxsa -Neil ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Proposed patch: synchronized_scanning GUC variable
Tom Lane wrote: Per today's -hackers discussion, add a GUC variable to allow clients to disable the new synchronized-scanning behavior, and make pg_dump disable sync scans so that it will reliably preserve row ordering. This is a pretty trivial patch, but seeing how late we are in the 8.3 release cycle, I thought I'd better post it for comment anyway. regards, tom lane Index: src/bin/pg_dump/pg_dump.c === RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v retrieving revision 1.481 diff -c -r1.481 pg_dump.c *** src/bin/pg_dump/pg_dump.c 1 Jan 2008 19:45:55 - 1.481 --- src/bin/pg_dump/pg_dump.c 27 Jan 2008 20:00:18 - *** *** 553,558 --- 553,572 do_sql_command(g_conn, "SET DATESTYLE = ISO"); /* +* If supported, set extra_float_digits so that we can dump float data +* exactly (given correctly implemented float I/O code, anyway) +*/ + if (g_fout->remoteVersion >= 70400) + do_sql_command(g_conn, "SET extra_float_digits TO 2"); + + /* +* If synchronized scanning is supported, disable it, to prevent +* unpredictable changes in row ordering across a dump and reload. +*/ + if (g_fout->remoteVersion >= 80300) + do_sql_command(g_conn, "SET synchronized_scanning TO off"); + + /* * Start serializable transaction to dump consistent data. */ do_sql_command(g_conn, "BEGIN"); Hi, Can somebody explain why it's important to load with synchronized_scanning off? do_sql_command(g_conn, "SET synchronized_scanning TO off"); Thanks Russell Smith ---(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] Proposed patch: synchronized_scanning GUC variable
On Sun, 2008-01-27 at 21:54 +, Gregory Stark wrote: > I liked the "synchronized_sequential_scans" idea myself. I think that's a bit too long. How about "synchronized_scans", or "synchronized_seqscans"? -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Proposed patch: synchronized_scanning GUC variable
Gregory Stark <[EMAIL PROTECTED]> writes: > I liked the "synchronized_sequential_scans" idea myself. The name is still open for discussion --- it's an easy search-and-replace in the patch ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Proposed patch: synchronized_scanning GUC variable
> >> Per today's -hackers discussion, add a GUC variable to > allow clients > >> to disable the new synchronized-scanning behavior, and > make pg_dump > >> disable sync scans so that it will reliably preserve row > ordering. > >> This is a pretty trivial patch, but seeing how late we are > in the 8.3 > >> release cycle, I thought I'd better post it for comment anyway. > > > > +1 > > I liked the "synchronized_sequential_scans" idea myself. > > But otherwise, sure. > this will save some hackwork I have to do for a import/export project I am doing. +1, please Regards, Gevik Babakhani PostgreSQL NL http://www.postgresql.nl TrueSoftware BV http://www.truesoftware.nl ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Proposed patch: synchronized_scanning GUC variable
"Jonah H. Harris" <[EMAIL PROTECTED]> writes: > On Jan 27, 2008 3:07 PM, Tom Lane <[EMAIL PROTECTED]> wrote: >> Per today's -hackers discussion, add a GUC variable to allow clients to >> disable the new synchronized-scanning behavior, and make pg_dump disable >> sync scans so that it will reliably preserve row ordering. This is a >> pretty trivial patch, but seeing how late we are in the 8.3 release >> cycle, I thought I'd better post it for comment anyway. > > +1 I liked the "synchronized_sequential_scans" idea myself. But otherwise, sure. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Proposed patch: synchronized_scanning GUC variable
On Jan 27, 2008 3:07 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > Per today's -hackers discussion, add a GUC variable to allow clients to > disable the new synchronized-scanning behavior, and make pg_dump disable > sync scans so that it will reliably preserve row ordering. This is a > pretty trivial patch, but seeing how late we are in the 8.3 release > cycle, I thought I'd better post it for comment anyway. +1 -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] Proposed patch: synchronized_scanning GUC variable
Per today's -hackers discussion, add a GUC variable to allow clients to
disable the new synchronized-scanning behavior, and make pg_dump disable
sync scans so that it will reliably preserve row ordering. This is a
pretty trivial patch, but seeing how late we are in the 8.3 release
cycle, I thought I'd better post it for comment anyway.
regards, tom lane
Index: doc/src/sgml/config.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.162
diff -c -r1.162 config.sgml
*** doc/src/sgml/config.sgml27 Jan 2008 19:12:28 - 1.162
--- doc/src/sgml/config.sgml27 Jan 2008 20:00:16 -
***
*** 4611,4616
--- 4611,4638
+
+ synchronized_scanning
(boolean)
+
+synchronized_scanning configuration
parameter
+
+
+
+ This allows sequential scans of large tables to synchronize with each
+ other, so that concurrent scans read the same block at about the
+ same time and hence share the I/O workload. When this is enabled,
+ a scan might start in the middle of the table and then wrap
+ around the end to cover all rows, so as to synchronize with the
+ activity of scans already in progress. This can result in
+ unpredictable changes in the row ordering returned by queries that
+ have no ORDER BY clause. Setting this parameter to
+ off ensures the pre-8.3 behavior in which a sequential
+ scan always starts from the beginning of the table. The default
+ is on.
+
+
+
+
Index: src/backend/access/heap/heapam.c
===
RCS file: /cvsroot/pgsql/src/backend/access/heap/heapam.c,v
retrieving revision 1.248
diff -c -r1.248 heapam.c
*** src/backend/access/heap/heapam.c14 Jan 2008 01:39:09 - 1.248
--- src/backend/access/heap/heapam.c27 Jan 2008 20:00:18 -
***
*** 59,64
--- 59,68
#include "utils/syscache.h"
+ /* GUC variable */
+ bool synchronized_scanning = true;
+
+
static HeapScanDesc heap_beginscan_internal(Relation relation,
Snapshot snapshot,
int nkeys, ScanKey key,
***
*** 104,110
* the thresholds for these features could be different, we make them
the
* same so that there are only two behaviors to tune rather than four.
* (However, some callers need to be able to disable one or both of
!* these behaviors, independently of the size of the table.)
*
* During a rescan, don't make a new strategy object if we don't have
to.
*/
--- 108,115
* the thresholds for these features could be different, we make them
the
* same so that there are only two behaviors to tune rather than four.
* (However, some callers need to be able to disable one or both of
!* these behaviors, independently of the size of the table; also there
!* is a GUC variable that can disable synchronized scanning.)
*
* During a rescan, don't make a new strategy object if we don't have
to.
*/
***
*** 129,135
scan->rs_strategy = NULL;
}
! if (allow_sync)
{
scan->rs_syncscan = true;
scan->rs_startblock = ss_get_location(scan->rs_rd,
scan->rs_nblocks);
--- 134,140
scan->rs_strategy = NULL;
}
! if (allow_sync && synchronized_scanning)
{
scan->rs_syncscan = true;
scan->rs_startblock = ss_get_location(scan->rs_rd,
scan->rs_nblocks);
Index: src/backend/utils/misc/guc.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.431
diff -c -r1.431 guc.c
*** src/backend/utils/misc/guc.c27 Jan 2008 19:12:28 - 1.431
--- src/backend/utils/misc/guc.c27 Jan 2008 20:00:18 -
***
*** 110,115
--- 110,116
extern intCommitSiblings;
extern char *default_tablespace;
extern char *temp_tablespaces;
+ extern bool synchronized_scanning;
extern bool fullPageWrites;
#ifdef TRACE_SORT
***
*** 1053,1058
--- 1054,1068
},
{
+ {"synchronized_scanning", PGC_USERSET, COMPAT_OPTIONS_PREVIOUS,
+ gettext_noop("Enable synchronized scans."),
+ NULL
+ },
+ &synchronized_scanning,
+ true, NULL, NULL
+ },
+
+ {
{"archive_mode", PGC_POSTMASTER, WAL_SETTINGS,
gettext_noop("Allows archiving of WAL files
Re: [PATCHES] [8.4] Updated WITH clause patch (non-recursive)
On Jan 27, 2008 8:13 PM, Neil Conway <[EMAIL PROTECTED]> wrote: > (Compare that with the irritation we may well see from the removal of > implicit casts in 8.3, which will break *far* more applications, for a > benefit that many users will no doubt find rather hard to observe.) It's a bit off-topic but I was thinking the same *before* porting a real application to 8.3. There are cases where it's just annoying to not have the casts anymore but I find also a bunch of broken behaviours (interval > int for example) which I'm quite happy to detect and fix. But I'm pretty sure we'll have a lot of feedback on this, probably mostly negative at first. -- Guillaume ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [8.4] Updated WITH clause patch (non-recursive)
On Sun, 2008-01-27 at 12:36 -0500, Tom Lane wrote:
> Both of the above arguments hold water only if we implement compatible
> *semantics*, not merely syntax, so I find them unconvincing at this
> stage.
How are the semantics of the proposed patch incompatible with the SQL
spec or the implementations in other systems? The proposed patch is a
*subset* of the functionality in the SQL spec, but it isn't incompatible
with it as far as I know (recursive and non-recursive WITH are distinct
features).
> > (3) It avoids the need to repeat subqueries multiple times in the main
> > query, which can make queries more concise. Defining subqueries outside
> > the main SELECT body can also have readability advantages.
>
> Views fix that too.
Sure, if you're willing to resort to DDL, and lose most of the
conciseness / readability gain.
> The point is that when you break people's apps you'll be able to point
> to some real increment in functionality to justify it.
If your application uses an identifier that is a reserved word in SQL-92
and in pretty much all major databases, I don't think you have much
cause for grievance when it becomes a reserved word in Postgres -- the
writing has been on the wall for a while. Do you have any reason to
think that "WITH" is a particularly common table or column name, by the
way?
Note also the keywords.c hack in 8.3 for the WITH keyword means that
pg_dump already treats WITH as a reserved word, so most dumps should
load without changes.
> With the patch as it stands you'd essentially be saying "we're going
> to cause you pain now for benefit later", which is a hard selling
> proposition.
Again, the readability + compatibility arguments are non-zero benefits,
IMHO. But your argument is essentially a public-relations one ("it will
look bad if..."), which I don't find very convincing. If we explain that
WITH is a reserved word per SQL spec and is part of the planned support
for recursive queries (whether in 8.4 or later), I can't see very many
users being annoyed.
(Compare that with the irritation we may well see from the removal of
implicit casts in 8.3, which will break *far* more applications, for a
benefit that many users will no doubt find rather hard to observe.)
-Neil
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] [8.4] Updated WITH clause patch (non-recursive)
Neil Conway <[EMAIL PROTECTED]> writes: > (1) This is SQL-standard syntax (and not even wacko syntax, at that!), > and there is merit in implementing it on those grounds alone. > (2) It is supported by DB2, MS SQL and Oracle, so there is a further > compatibility argument to be made. Both of the above arguments hold water only if we implement compatible *semantics*, not merely syntax, so I find them unconvincing at this stage. > (3) It avoids the need to repeat subqueries multiple times in the main > query, which can make queries more concise. Defining subqueries outside > the main SELECT body can also have readability advantages. Views fix that too. >> If it doesn't provide any additional expressive capabilities then I >> think he didn't like taking "with" as a reserved word. > If we're going to implement recursive queries eventually (which we > almost surely will, whether in 8.4 or a future release), we'll need to > make WITH more reserved at some point anyway, so I don't see much to be > gained in the long run by delaying it. The point is that when you break people's apps you'll be able to point to some real increment in functionality to justify it. With the patch as it stands you'd essentially be saying "we're going to cause you pain now for benefit later", which is a hard selling proposition. I'm not opposed to applying this patch if it's an incremental step along a clearly defined path to full WITH support in 8.4. I'm less eager to put it in if there's not a plan and a commitment to make that happen. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [8.4] Updated WITH clause patch (non-recursive)
Gregory Stark <[EMAIL PROTECTED]> writes: > I still hope to do recursive queries for 8.4 so I don't have strong feelings > for this part either way. One question that hasn't been asked is whether this patch is likely to help, or to get in the way, for a more full-fledged implementation. I don't recall at the moment if Greg has a credible design sketch for the remaining work, but it might be a good idea to review that before deciding. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [8.4] Updated WITH clause patch (non-recursive)
Hello On 27/01/2008, Neil Conway <[EMAIL PROTECTED]> wrote: > On Sun, 2008-01-27 at 09:17 +, Gregory Stark wrote: > > Tom's feeling at the time was that even though it was providing something > > from > > the standard, it wasn't actually allowing the user to do anything he > > couldn't > > before. > > I think this feature has value: > +1 I thing so is better commit smaller pieces more often than one time one big patch. Nine months long feature freeze time is enough. Regards Pavel Stehule > (1) This is SQL-standard syntax (and not even wacko syntax, at that!), > and there is merit in implementing it on those grounds alone. > > (2) It is supported by DB2, MS SQL and Oracle, so there is a further > compatibility argument to be made. > > (3) It avoids the need to repeat subqueries multiple times in the main > query, which can make queries more concise. Defining subqueries outside > the main SELECT body can also have readability advantages. > > > If it doesn't provide any additional expressive capabilities then I > > think he didn't like taking "with" as a reserved word. > > Note that we can make WITH a type_func_name_keyword, rather than a > full-on reserved_keyword, which reduces the force of this argument > slightly. > > If we're going to implement recursive queries eventually (which we > almost surely will, whether in 8.4 or a future release), we'll need to > make WITH more reserved at some point anyway, so I don't see much to be > gained in the long run by delaying it. > > -Neil > > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [8.4] Updated WITH clause patch (non-recursive)
On Sun, 2008-01-27 at 09:17 +, Gregory Stark wrote: > Tom's feeling at the time was that even though it was providing something from > the standard, it wasn't actually allowing the user to do anything he couldn't > before. I think this feature has value: (1) This is SQL-standard syntax (and not even wacko syntax, at that!), and there is merit in implementing it on those grounds alone. (2) It is supported by DB2, MS SQL and Oracle, so there is a further compatibility argument to be made. (3) It avoids the need to repeat subqueries multiple times in the main query, which can make queries more concise. Defining subqueries outside the main SELECT body can also have readability advantages. > If it doesn't provide any additional expressive capabilities then I > think he didn't like taking "with" as a reserved word. Note that we can make WITH a type_func_name_keyword, rather than a full-on reserved_keyword, which reduces the force of this argument slightly. If we're going to implement recursive queries eventually (which we almost surely will, whether in 8.4 or a future release), we'll need to make WITH more reserved at some point anyway, so I don't see much to be gained in the long run by delaying it. -Neil ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [8.4] Updated WITH clause patch (non-recursive)
"Neil Conway" <[EMAIL PROTECTED]> writes: > Remaining work is to review the guts of the patch (which shouldn't take > long), and write documentation and regression tests. I'm personally > hoping to see this get into the tree fairly early in the 8.4 cycle, > pending discussion of course. Note that as it stands it directly inlines the subquery into the query everywhere you use it. So if the user was hoping to save database work by avoiding duplicate subqueries he or she may be disappointed. On the other hand inlining it can allow the planner to produce better plans. Tom's feeling at the time was that even though it was providing something from the standard, it wasn't actually allowing the user to do anything he couldn't before. If it doesn't provide any additional expressive capabilities then I think he didn't like taking "with" as a reserved word. I still hope to do recursive queries for 8.4 so I don't have strong feelings for this part either way. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
