[HACKERS] distibuted transactions, SQL+XPath+XTree
Hi list, I see the following business opportunity for Postgres: I) Simple man can't program middleware to connect XML-client and Postgres. II) Request into several databases does not exist. III) Notebooks need several switching-on and switching-off during transaction. IV) Distance between strings are not supported, that makes aproximate searching impossible. V) There is no possibility to hide some (not all) records of table, granted to other users, from these users Proposed solutions to these opportunities: I) DBMS inserts into tables and selects from tables along correlation Primary Key - Foreign Key. II) Databases get nicknames, groups of databases get name of group. These names are used in requests. III) Operator to freeze transaction. IV) Operator to order records on base of distance between strings. V) Subdivide records of all tables into classes, specify class number in record. I ask you to implement these solutions, that Postgres get advantage before other DBMS-es. I have prepered several drawing http://sql50.euro.ru/sql5.11.3.ppt to explain ideas. More details are described below. === 1) [slides 2-12] Problem: Browser is very widespread client in epoch of internet. Non-programmers can master 'insert', 'select', 'update', 'delete', but are not capable to use sophisticated syntax of proprietary web-server for input of XML. It's necessary to exclude this syntax, and give possibility to install DBMS and immediately use it, like user install and use Teleport, FlashGet, browser and so on. Solution: DBMS itself must communicate via HTTP, accept XML, and place data from it into tables under some agreement. My proposal about agreement: *) xml-element is written into table with identical name (i.e. tag name coincides with table name), xml-attribute is written into field with identical name (i.e. attribute name coincides with field name); *) tables are bound into tree by values of Primary Keys and Foreign Keys; *) value of primary keys of new record is assigned by trigger. If user uses simple scheme, than this is enough! An ambiguity can exist in complex scheme because of several refering fields, than user must append symbol '#' and name of necessary refering field to end of name of sending XML-tag (it looks like new tag name with symbol '#' inside name). Let's name this by term 'determination' [symbol '$' is used for list to have possibility to solve ambiguity for list simultaneously with ambiguity for enclosed XML-element, i.e. to append two refering field to name of sending XML-tag]. P.S. [slides 13-21] Of course, we spead decision to manual 'insert'. 2) [slides 22-31] Problem: Usage of both SQL/XML-functions, and syntax of proprietary web-server give very bulky code to extract tree as XML. This makes more difficulties for contact of DBMS on CML, GML, HumanML, OPML, RCML, SBML, ebXML, MDDL, RIXML, XBRL, xCBL and other (turing all relational fields into XML-elements is suitable for browser, but not suitable for other cases). Solution: To avoid sophisticated programing, 'select' itself must return data to client (if only 'select' is not used inside 'insert ... select ...'). I propose laconic 'select a.b.c' to select data from tables 'a', 'b', 'c'. Let's name this by term 'XTree' - in analogy with 'XPath'. If user uses simple scheme, than this is enough! An ambiguity can exist in complex scheme because of several refering fields, than user must append symbol '#' and name of necessary refering field to end of table name (it looks like new table name with symbol '#' inside name). Let's name this by term 'refinement'. P.S. [slides 32-39] All possible compositions of determinations in XML-tree and all possible compositions of refinement in 'select' are considered, appropriate XML- and SQL-syntaxes are proposed. P.S. [slides 40-49] Examples of usage of refinement are demonstrated. 3) [slides 50-58] Problem: Non-predictable/non-repeated input data (XML-elements) is written into XML-field of relational table. XQuery is offered to process data in these xml-fields. But user is not capable to manipulate records by SQL and XML-elements by XQuery in one request (even in case of refusal from relational storage in favour of XML-database, that means in favour of non-relational 'engine', enclosed cycles of XQuery create very bulky code, in which user is not orientated). Solution: I propose to append XPath into SQL, that SQL can process XML-elements and attributes (i.e. to avoid XQuery). Thus SQL can process records and XML-elements simultaneously. P.S. [slides 59-72] Of course, we generalize XPath and XTree upto XLang, and consider all possible use cases. --- 4) [slides 73-83-116] Problem: SQL would more flexible and convenient for distributed request (gethering data from several databases and scattering them into several databases), than branded programs; including SQL is more convenient for replication, than branded programs. But there is no necessary syntax.
Re: [HACKERS] Permanent settings
On Tuesday 19 February 2008 20:08, Tom Lane wrote:
> Josh Berkus <[EMAIL PROTECTED]> writes:
> > On Tuesday 19 February 2008 15:05, Bruce Momjian wrote:
> >> One idea would be to remove duplicate postgresql.conf appended entries
> >> on server start.
> >
> > I think anything which has us appending extra settings to the end of the
> > file is a non-starter. We'd get "I changed the setting, but nothing's
> > happening" error reports 8x hour on #postgresql.
>
> Yeah, I agree. Any proposal that makes it materially harder for people
> to maintain the config files with an editor is going to suffer so much
> push-back that it will ultimately fail. And adding extra copies of
> settings to an existing file does make it harder.
>
+1
> What I would suggest is to write a function in contrib/adminpack that
> updates the config file by replacing the variable assignment in-place.
> (Yes, it will have to be smart enough to parse the config file, but
> that hardly requires a great deal of smarts.) If that implementation
> sees sufficient usage then we can migrate the functionality into core.
>
phppgadmin would certainly use said function if it existed in core, so I'd
suggest if we go that route put it in 8.4 straight away. My guess is it
would also be easier to maintain if it was built-in.
> It was complained up-thread that some installations make the config
> files read-only to the postgres user, but I see no conflict there.
> Anyone who does that is saying that they don't *want* automatic changes
> to the configuration settings. Such folk will not consider it a
> feature for the database to make an end-run around that policy.
>
Hmm I don't think I've ever seen one like this, but thinking about it I
suppose I could see the argument and way to do it... but yes, I think you'd
get an error that the file was read-only, so the behavior would be similar to
trying to edit it on the box as postgres user.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Permanent settings
On Tuesday 19 February 2008 14:32, Andrew Dunstan wrote:
> Andrew Dunstan wrote:
> > Alvaro Herrera wrote:
> >> Joshua D. Drake wrote:
> >>> IMO this should all be in the database and that's it. The idea that our
> >>> global settings are in a file seems unusual consider we have a
> >>> perfectly good storage engine available.
> >>
> >> That doesn't work, because many settings must be loaded before the
> >> database is fully operational.
> >
this is a valid objection, though I think it could be worked around.
> > Not to mention what would happen if the database had a problem so we
> > couldn't read the config.
>
people bring this objection for moving pg_hba settings into the db, but I
think the same answer applies; having a command line flag for the postmaster
to read options from a file probably gets you around this.
> Although, on further reflection, we could probably meet both of these
> objections by having the database maintain a text version of the config
> which it would load on startup.
>
yes, that would probably work.
> One other possible objection is that it would allow any superuser to
> set things that currently require direct access to the config files, so
> that would be a major change in security arrangements.
>
If you are superuser, you can write a C function (or just install adminpacks
functions) and do this anyway. (there might be a way to prevent this, but I'm
not quite sure how you would do it)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] ANALYZE to be ignored by VACUUM
Gregory Stark <[EMAIL PROTECTED]> wrote: > > 4. ANALYZE finishes in a short time. > There was a paper with a nice algorithm posted a while back which required > only constant memory but it depended on scanning the entire table. I think to > do n_distinct estimates we'll need some statistics which are either gathered > opportunistically whenever a seqscan happens or maintained by an index. VACUUM would be another good timing for the alogrithm, because it does a seqscan. If we do so, we need to separate an analyzing transaction into sampling and updating-stats transactions to keep vacuums as ignorable transactions. However, VACUUM will not do a seqscan when we have Dead Space Map or Segment Visibility Map. We will need incremental statistics updating if reliable n_distinct estimation requires many samples. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.3 / 8.2.6 restore comparison
Bah. It's the stuff in the format translation path and conversion to/from datums that is the bottleneck. We sped up COPY TO recently by a factor of 10 using similar approaches to what we did for COPY FROM in the past. There's a format conversion that is the culprit. We routinely get about 12 MB/s of heap insertion rate per CPU core and it's CPU bound. You can peek in on what's happening using gstack on Linux, or the gdb "attach and print stacktrace" approach for a crude profile. - Luke On 2/19/08 1:36 PM, "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On Tue, 19 Feb 2008 13:21:46 -0800 > "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > >> Were their any thoughts on this? I am also finding that backing up >> this database is rudely slow with the same type of odd metrics >> (almost zero (or zero) iowait). We can saturate a CPU but the CPU is >> certainly not pushing the data to disk as fast as it could. > > Further on this. We have tested on RHEL4 and RHEL5. Their are two > machines, each with 32GB of ram. I have four of these in the RHEL 5 > machine: > > processor : 0 > vendor_id : AuthenticAMD > cpu family : 15 > model : 65 > model name : Dual-Core AMD Opteron(tm) Processor 8216 > stepping: 2 > cpu MHz : 2411.132 > cache size : 1024 KB > physical id : 0 > siblings: 2 > core id : 0 > cpu cores : 2 > fpu : yes > fpu_exception : yes > cpuid level : 1 > wp : yes > flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge > mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext > fxsr_opt rdtscp lm 3dnowext 3dnow pni cx16 lahf_lm cmp_legacy svm > extapic cr8_legacy bogomips: 4823.59 TLB size: 1024 4K > pages clflush size: 64 cache_alignment : 64 > address sizes : 40 bits physical, 48 bits virtual > power management: ts fid vid ttp tm stc > > Here is the mini specs: > > # 5U Q524 - Quad Opteron 24 SCSI > # Tyan S4885G3NR 800 Series CPU Opteron 800 > # AMD Opteron 880 - 2.4GHz 2Core x 4 > # 32GB - DDR ECC REG 400MHz (16x2GB) x 1 (16 x 2GB 3200 ECC REG Smart > Modular (32GB) # HD 1: 73GB SCSI 15K RPM x 24 > # PCI 1: MegaRaid SCSI 320-2E - 2ch, U320, PCI Express, 128MB > # MegaRaid LSIiTBBU01 Battery - Order #: LSI9 > # PCI 2: MegaRaid SCSI 320-2E - 2ch, U320, PCI Express, 128MB > # MegaRaid LSIiTBBU01 Battery - Order #: LSI9 > # DVD-ROM/Sony 3.5 Inch Floppy Drive > > The configuration is: > > / RAID 1 > / xlogs RAID 1 > /data1 10 drives RAID 10 > /data2 10 drives RAID 10 > > The thing that is frustrating here, is it appears that PostgreSQL just > can't utilize the hardware. I *know* it can because we have larger > machines in production that use PostgreSQL happily. However when I have > 220G backups taking 8 hours and restores taking 11 hours, I begin to > wonder where the bottleneck is. > > Assuming 25 megs a second per drive (random write) on data1 and data2 > we should be pushing 250M a second. Just to be insanely conservative > let's cut that in half to 125M per second. That means every 10 seconds > we should do ~ 1G. That means every minute we should to ~ 6G, which > means 360G an hour. > > Granted we have index creation and everything else going on but 11 > hours and no IO chewing? > > As a note these are reproducible on both machines regardless of RHEL5 > or RHEL4. > > I know there are much stats here but I have provided them in previous > posts on this thread. Perhaps someone sees a red flag in the hardware? > > > Sincerely, > > Joshua D. Drake > - -- > The PostgreSQL Company since 1997: http://www.commandprompt.com/ > PostgreSQL Community Conference: http://www.postgresqlconference.org/ > Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate > PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD8DBQFHu0vyATb/zqfZUUQRAiuYAJ9ut6i/cPv2MYc8RO2+wNw09M5/WwCfUaGY > sAkFt+S14i0kFMn6mz9juBw= > =TNys > -END PGP SIGNATURE- > ---(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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] minimal update trigger
As discussed a little while back, I would like to add a generic trigger
function which will force an update to skip if the new and old tuples
are identical.
The guts of this is the following snippet of code:
|rettuple = newtuple = trigdata->tg_newtuple;
oldtuple = trigdata->tg_trigtuple;
if (newtuple->t_len == oldtuple->t_len &&
newtuple->t_data->t_hoff == oldtuple->t_data->t_hoff &&
HeapTupleHeaderGetNatts(newtuple->t_data) ==
HeapTupleHeaderGetNatts(oldtuple->t_data) &&
(newtuple->t_data->t_infomask & ~HEAP_XACT_MASK) ==
(oldtuple->t_data->t_infomask & ~HEAP_XACT_MASK) &&
memcmp(((char *)newtuple->t_data) + offsetof(HeapTupleHeaderData,
t_bits),
((char *)oldtuple->t_data) + offsetof(HeapTupleHeaderData,
t_bits),
newtuple->t_len - offsetof(HeapTupleHeaderData, t_bits)) ==
0)
{
rettuple = NULL;
}
return rettuple;
I propose to call the function pg_minimal_update.
Unless there is an objection I will put together a patch + docs for this
shortly. Not quite sure what section of the docs to put it in - maybe a new
subsection of the Functions chapter?
cheers
andrew
|
---(end of broadcast)---
TIP 6: explain analyze is your friend
[HACKERS] gateway test II
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ignore this one too - Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.4 (FreeBSD) iD8DBQFHu4f44QvfyHIvDvMRAi8MAJ49R+neMaH0LVoucke1nhNhNCG4wACgrY+L smEllqSZzFcGc6rl/z7+EC0= =bK5v -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] testing a post directly on server
ignore, just a test -- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including PL/PgSQL by default
Tom Lane wrote:
Still and all, I will hold still for having it be installed by default
as long as there is a simple way for the DBA to change that default
--- let's say, roughly as simple as it is now for the DBA to make it the
default if he wishes (ie "create language plpgsql" in template1) and
revoke that again if he changes his mind ("drop language plpgsql" in
template1). initdb-time switches are not an adequate answer, not least
because most packagers don't make it easy to control them.
The way I intended to do it would indeed allow it to be undone simply by
executing 'drop language plpgsql' in template1.
I'm not clear about what else you want.
cheers
andrew
---(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: [HACKERS] 8.3 / 8.2.6 restore comparison
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 19 Feb 2008 13:36:48 -0800 "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: Hello, Some more testing on this. This time (using 8.3) I modified my restore process to use multiple processes by manipulating TOC files. I used three processes for the data copies, two processes for the pk creation, two process for normal indexes and two processes for constraint creation. The machine averaged 40-60MB/s write versus the pathetic ~ 2/3 MB/s on a single thread. It had an average I/O wait of < 10%. Lastly it restored 57G of data 1.25 hours. Under my single thread testing 57G would have taken ~ 3 hours. I am pretty sure I can make it faster too as I wasn't balancing with tablespaces nor did I move the xlogs off. IMO this pretty much proves that we need to seriously look at a multi connection restores. I can't imagine a situation where we look at people that have dual and quad cores on their desktops and say... sorry we can't use that to help you get your data quicker. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHu4QvATb/zqfZUUQRAhyZAJ9U468fVDm8ww/2TrjDt6gM2wtlhwCffYYq KJEsKpvRm6efiMQ+uAn/cs4= =ZEGc -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] gateway test ...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ignore this - Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.4 (FreeBSD) iD8DBQFHu4K74QvfyHIvDvMRAjWLAKDLM6871H63wIYypeGRse+np7lfRwCgwQCb fR8FPeU3RhUE1SdOGCP9zfs= =8BC6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Permanent settings
Josh Berkus <[EMAIL PROTECTED]> writes: > On Tuesday 19 February 2008 15:05, Bruce Momjian wrote: >> One idea would be to remove duplicate postgresql.conf appended entries >> on server start. > I think anything which has us appending extra settings to the end of the > file is a non-starter. We'd get "I changed the setting, but nothing's > happening" error reports 8x hour on #postgresql. Yeah, I agree. Any proposal that makes it materially harder for people to maintain the config files with an editor is going to suffer so much push-back that it will ultimately fail. And adding extra copies of settings to an existing file does make it harder. What I would suggest is to write a function in contrib/adminpack that updates the config file by replacing the variable assignment in-place. (Yes, it will have to be smart enough to parse the config file, but that hardly requires a great deal of smarts.) If that implementation sees sufficient usage then we can migrate the functionality into core. It was complained up-thread that some installations make the config files read-only to the postgres user, but I see no conflict there. Anyone who does that is saying that they don't *want* automatic changes to the configuration settings. Such folk will not consider it a feature for the database to make an end-run around that policy. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Including PL/PgSQL by default
Andrew Dunstan <[EMAIL PROTECTED]> writes:
> Thanks. The only significant problem I saw mentioned other than the
> rather ephemeral security issues was the one regarding statically linked
> postgres.
Nothing like establishing one's point by carefully ignoring all the
nontrivial problems.
I think the real $64 issue is that plpgsql provides a usable procedural
programming language on the server side, and is therefore a springboard
to enable users doing things the DBA might not like --- the example of
using server-side resources to do password cracking is one. Another
example is that it'd enable use of covert communication channels such as
CPU usage, which'd be a heck of a lot harder to do with only SQL access.
Thus it is entirely reasonable for a DBA to see plpgsql as exacerbating
any security issues that might exist, *whether or not plpgsql itself has
any holes*. Indeed, I'd say a DBA who does not realize that that's a
risk is a fool.
What was that again about "let's be secure by default"? This proposal
is certainly not moving in that direction.
Still and all, I will hold still for having it be installed by default
as long as there is a simple way for the DBA to change that default
--- let's say, roughly as simple as it is now for the DBA to make it the
default if he wishes (ie "create language plpgsql" in template1) and
revoke that again if he changes his mind ("drop language plpgsql" in
template1). initdb-time switches are not an adequate answer, not least
because most packagers don't make it easy to control them.
BTW, why all the pressure for this when we've already made it possible
for database owners to create the language by default?
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: [HACKERS] Including PL/PgSQL by default
Neil Conway wrote: On Tue, 2008-02-19 at 18:13 -0500, Andrew Dunstan wrote: I am having trouble locating the previous thread - can someone please point me at it? http://markmail.org/message/kyjbj5qovadfoe3w Thanks. The only significant problem I saw mentioned other than the rather ephemeral security issues was the one regarding statically linked postgres. I therefore propose that a) loading plpgsql in template1 can be disabled by an initdb switch, and b) initdb will not try to load it if postgres is statically linked, assuming we can develop a reasonable test for that. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 19 Feb 2008 15:25:44 -0800 Neil Conway <[EMAIL PROTECTED]> wrote: > On Tue, 2008-02-19 at 18:13 -0500, Andrew Dunstan wrote: > > I am having trouble locating the previous thread - can someone > > please point me at it? > > http://markmail.org/message/kyjbj5qovadfoe3w > Excellent that thread is better than the two I found. Sincerely, Joshua D. Drake > -Neil > > > > ---(end of > broadcast)--- TIP 7: You can help support the > PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHu2dCATb/zqfZUUQRAqT9AJ0WaUpPj/5mvw+VfRKgY86gTyjURgCeJxUL Cx2L5WvrXMDg1j/NW7QlD54= =/yV6 -END PGP SIGNATURE- ---(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: [HACKERS] Permanent settings
On Tuesday 19 February 2008 15:05, Bruce Momjian wrote: > One idea would be to remove duplicate postgresql.conf appended entries > on server start. I think anything which has us appending extra settings to the end of the file is a non-starter. We'd get "I changed the setting, but nothing's happening" error reports 8x hour on #postgresql. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(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: [HACKERS] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 19 Feb 2008 18:13:53 -0500 Andrew Dunstan <[EMAIL PROTECTED]> wrote: > I am having trouble locating the previous thread - can someone please > point me at it? I am having trouble finding one that makes a cohesive argument against but here we go: http://archives.postgresql.org/pgsql-sql/2000-05/msg00215.php http://archives.postgresql.org/pgsql-hackers/2004-04/msg00952.php Of course there are tons of results of users wondering why we don't offer such as simple and useful feature. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHu2ayATb/zqfZUUQRAmL7AJoCQyNmbLIbZNXG9JjMQu2ax/vRJQCfcevF TF6TzTSr/1ep8PuSNMcGK2g= =bFqN -END PGP SIGNATURE- ---(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: [HACKERS] Including PL/PgSQL by default
On Tue, 2008-02-19 at 18:13 -0500, Andrew Dunstan wrote: > I am having trouble locating the previous thread - can someone please > point me at it? http://markmail.org/message/kyjbj5qovadfoe3w -Neil ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Including PL/PgSQL by default
Tom Lane wrote: David Fetter <[EMAIL PROTECTED]> writes: On Tue, Feb 19, 2008 at 12:11:05PM -0500, Tom Lane wrote: This has been proposed before, and rejected before. Have you got any new arguments? The longer it's been since the last vuln in PL/PgSQL, the harder it is to argue for having it not be there by default. You are attacking a straw man, which is that the only argument against having PL/PgSQL installed is the risk of security holes in it. I am having trouble locating the previous thread - can someone please point me at it? cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
One idea would be to remove duplicate postgresql.conf appended entries on server start. --- Josh Berkus wrote: > Magnus, > > > That's basically "include" but with a different name, no? > > Yes. FWIW, I seem to be lagged about 3 hours on -hackers. > > > Why do you need to split it in two columns, and what would go in what > > column? > > Current data: > > postgres=# select name, category from pg_settings; > name | category > -+--- > allow_system_table_mods | Developer Options > archive_command | Write-Ahead Log / Settings > archive_mode| Write-Ahead Log / Settings > archive_timeout | Write-Ahead Log / Settings > > How it should be: > > postgres=# select name, category, subcategory from pg_settings; > name | category | subcategory > -+ > allow_system_table_mods | Developer Options | > archive_command | Write-Ahead Log| Settings > archive_mode| Write-Ahead Log| Settings > archive_timeout | Write-Ahead Log| Settings > > this would then allow us to do this: > > select * from pg_settings_categories > name order > Developer Options 37 > Write-Ahead Log 11 > > select * from pg_settings_subcategories > name category > allow_system_table_mods Developer Options > archive_command Write-Ahead Log > archive_mode Write-Ahead Log > > and then generate a file which looks like this: > > # == Write-Ahead Log == > # Settings > > archive_command = '/bin/rsync' > archive_mode = 'on' > > # Fsync > > fsync = on > wal_buffers = 8mb > > ... etc. > > This would allow the automatically generated version to be readable and > searchable, if not quite as narrative as the present postgresql.conf. > > > > > > > 3) have command line config write to postgresql.auto.conf, dumping the > > > whole of pg_settings organized with headings in categories order. > > > > Don't get what you mean here. You mean you want a commandline tool to > > generate a config file from pg_settings? > > I meant from the SQL command line. > > > Another question completely, but related, is if it's actually the right > > thing to use postgresql.conf to write documentation. The way it is now > > we basically add all new config options to postgresql.conf.sample along > > with a comment that is the documentation. A different approach would be > > to only include the very most common settings, or possibly even only > > those that initdb sets to something non-default, in > > postgresql.conf.sample, and have the rest only added when they're > > actually used. Documentation really belongs in the documentation, after > > all... > > Yeah, we've taken an Apache-like approach of including heavy comments on > the settings in the settings file itself. Unfortunately, I think changing > that practice at this point would alienate a bunch of users. > > -- > --Josh > > Josh Berkus > PostgreSQL @ Sun > San Francisco > > ---(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 -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Permanent settings
Magnus, > That's basically "include" but with a different name, no? Yes. FWIW, I seem to be lagged about 3 hours on -hackers. > Why do you need to split it in two columns, and what would go in what > column? Current data: postgres=# select name, category from pg_settings; name | category -+--- allow_system_table_mods | Developer Options archive_command | Write-Ahead Log / Settings archive_mode| Write-Ahead Log / Settings archive_timeout | Write-Ahead Log / Settings How it should be: postgres=# select name, category, subcategory from pg_settings; name | category | subcategory -+ allow_system_table_mods | Developer Options | archive_command | Write-Ahead Log| Settings archive_mode| Write-Ahead Log| Settings archive_timeout | Write-Ahead Log| Settings this would then allow us to do this: select * from pg_settings_categories nameorder Developer Options 37 Write-Ahead Log 11 select * from pg_settings_subcategories namecategory allow_system_table_mods Developer Options archive_command Write-Ahead Log archive_modeWrite-Ahead Log and then generate a file which looks like this: # == Write-Ahead Log == # Settings archive_command = '/bin/rsync' archive_mode = 'on' # Fsync fsync = on wal_buffers = 8mb ... etc. This would allow the automatically generated version to be readable and searchable, if not quite as narrative as the present postgresql.conf. > > > 3) have command line config write to postgresql.auto.conf, dumping the > > whole of pg_settings organized with headings in categories order. > > Don't get what you mean here. You mean you want a commandline tool to > generate a config file from pg_settings? I meant from the SQL command line. > Another question completely, but related, is if it's actually the right > thing to use postgresql.conf to write documentation. The way it is now > we basically add all new config options to postgresql.conf.sample along > with a comment that is the documentation. A different approach would be > to only include the very most common settings, or possibly even only > those that initdb sets to something non-default, in > postgresql.conf.sample, and have the rest only added when they're > actually used. Documentation really belongs in the documentation, after > all... Yeah, we've taken an Apache-like approach of including heavy comments on the settings in the settings file itself. Unfortunately, I think changing that practice at this point would alienate a bunch of users. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(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: [HACKERS] pg_dump additional options for performance
On Wed, 2008-02-06 at 15:13 +, Simon Riggs wrote: > The -s option creates the table, as well as creating constraints and > indexes. These objects need to be dropped prior to loading, if we are to > follow the performance recommendations in the docs. But the only way to > do that is to manually edit the script to produce a cut down script. > > So it would be good if we could dump objects in 3 groups > 1. all commands required to re-create table > 2. data > 3. all commands required to complete table after data load > > My proposal is to provide two additional modes: > --schema-pre-load corresponding to (1) above > --schema-post-load corresponding to (3) above Another thought: We could also break step #3 into two steps: those objects required for correctness (e.g. unique indexes, other constraints, etc); and those objects that are merely for performance (i.e. non-constraining indexes). Let's call these steps #3A and #3B. After reading the thread here: http://archives.postgresql.org/pgsql-performance/2008-02/msg00211.php it's clear that building indexes can take a long time, and they aren't strictly required for correct database operation. Often, it's perfectly reasonable to operate the database without a few of the indexes, so long as they don't imply a constraint. Step #3B could be done with "CONCURRENTLY" to allow uninterrupted operation. We could even allow pg_restore to run step #3B in multiple sessions building different indexes to use multiple processor cores. This is just a thought. I can see that it's getting fairly complex, and it may be better to just leave these things up to the DBA. Regards, Jeff Davis ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
On Tue, 2008-02-19 at 13:31 -0800, Josh Berkus wrote: > Magnus, All, > > This is something I've been thinking about too, just because my efforts to > write auto-config scripts have gotten bogged down in the need to parse and > write .conf files in a paltform-agnostic way and preserve comments. I > agree with Magnus that it's something we need to address. Having the > ability to update .conf through an api other than reading & writing a file > one line will make developing future autotuning tools significanly easier. > > I think that the idea of just appending extra lines to the bottom of the > file > in chronoligical (or random) order is so messy and hackish that it's simply > not worthy of consideration for the PostgreSQL project. > > Instead, here's my proposal: > > 1) add to the top of postgresql.conf another file switch, like this: > > # auto_config_file = 'ConfigDir/postgresql.auto.conf' > # if set, the auto config file will be read by the system and override > the > settings in the rest of this postgresql.conf file, which will be ignored. > # to disable automated and SQL command-line-based configuration > # comment the above or set it to an empty string That's basically "include" but with a different name, no? > 2) split the "category" column in pg_settings into two columns, and add a > categories lookup table, so it can be sorted properly Why do you need to split it in two columns, and what would go in what column? > 3) have command line config write to postgresql.auto.conf, dumping the > whole of pg_settings organized with headings in categories order. Don't get what you mean here. You mean you want a commandline tool to generate a config file from pg_settings? Another question completely, but related, is if it's actually the right thing to use postgresql.conf to write documentation. The way it is now we basically add all new config options to postgresql.conf.sample along with a comment that is the documentation. A different approach would be to only include the very most common settings, or possibly even only those that initdb sets to something non-default, in postgresql.conf.sample, and have the rest only added when they're actually used. Documentation really belongs in the documentation, after all... But again, that's a different question - it's equally valid with or without an API way for modifying the configuration. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Permanent settings
Magnus, All, This is something I've been thinking about too, just because my efforts to write auto-config scripts have gotten bogged down in the need to parse and write .conf files in a paltform-agnostic way and preserve comments. I agree with Magnus that it's something we need to address. Having the ability to update .conf through an api other than reading & writing a file one line will make developing future autotuning tools significanly easier. I think that the idea of just appending extra lines to the bottom of the file in chronoligical (or random) order is so messy and hackish that it's simply not worthy of consideration for the PostgreSQL project. Instead, here's my proposal: 1) add to the top of postgresql.conf another file switch, like this: # auto_config_file = 'ConfigDir/postgresql.auto.conf' # if set, the auto config file will be read by the system and override the settings in the rest of this postgresql.conf file, which will be ignored. # to disable automated and SQL command-line-based configuration # comment the above or set it to an empty string 2) split the "category" column in pg_settings into two columns, and add a categories lookup table, so it can be sorted properly 3) have command line config write to postgresql.auto.conf, dumping the whole of pg_settings organized with headings in categories order. I think an arrangement like that will work well with pg_settings based config, autotuning, while still allowing backwards-compatible manual control via postgresql.conf. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Question regarding TODO item:Allow to_date() and to_timestamp() accept localized month names
I have started to develop this item. I would like to see whether I am on the right track. 1. For this item we need an array of localized month/day names. I was thinking of (re)initializing the array item using palloc, repalloc pfree etc.. when SET LC_MESSAGES is called. Would this be correct? 2. In order to "know" when to return the localized values a DCH_S_TM (=0x10, suffix "TM") is provided at dch_date. This is where I want to check the localized array created above. Any thoughts? Regards, Gevik. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.3 / 8.2.6 restore comparison
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 19 Feb 2008 13:21:46 -0800 "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > Were their any thoughts on this? I am also finding that backing up > this database is rudely slow with the same type of odd metrics > (almost zero (or zero) iowait). We can saturate a CPU but the CPU is > certainly not pushing the data to disk as fast as it could. Further on this. We have tested on RHEL4 and RHEL5. Their are two machines, each with 32GB of ram. I have four of these in the RHEL 5 machine: processor : 0 vendor_id : AuthenticAMD cpu family : 15 model : 65 model name : Dual-Core AMD Opteron(tm) Processor 8216 stepping: 2 cpu MHz : 2411.132 cache size : 1024 KB physical id : 0 siblings: 2 core id : 0 cpu cores : 2 fpu : yes fpu_exception : yes cpuid level : 1 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt rdtscp lm 3dnowext 3dnow pni cx16 lahf_lm cmp_legacy svm extapic cr8_legacy bogomips: 4823.59 TLB size: 1024 4K pages clflush size: 64 cache_alignment : 64 address sizes : 40 bits physical, 48 bits virtual power management: ts fid vid ttp tm stc Here is the mini specs: # 5U Q524 - Quad Opteron 24 SCSI # Tyan S4885G3NR 800 Series CPU Opteron 800 # AMD Opteron 880 - 2.4GHz 2Core x 4 # 32GB - DDR ECC REG 400MHz (16x2GB) x 1 (16 x 2GB 3200 ECC REG Smart Modular (32GB) # HD 1: 73GB SCSI 15K RPM x 24 # PCI 1: MegaRaid SCSI 320-2E - 2ch, U320, PCI Express, 128MB # MegaRaid LSIiTBBU01 Battery - Order #: LSI9 # PCI 2: MegaRaid SCSI 320-2E - 2ch, U320, PCI Express, 128MB # MegaRaid LSIiTBBU01 Battery - Order #: LSI9 # DVD-ROM/Sony 3.5 Inch Floppy Drive The configuration is: / RAID 1 / xlogs RAID 1 /data1 10 drives RAID 10 /data2 10 drives RAID 10 The thing that is frustrating here, is it appears that PostgreSQL just can't utilize the hardware. I *know* it can because we have larger machines in production that use PostgreSQL happily. However when I have 220G backups taking 8 hours and restores taking 11 hours, I begin to wonder where the bottleneck is. Assuming 25 megs a second per drive (random write) on data1 and data2 we should be pushing 250M a second. Just to be insanely conservative let's cut that in half to 125M per second. That means every 10 seconds we should do ~ 1G. That means every minute we should to ~ 6G, which means 360G an hour. Granted we have index creation and everything else going on but 11 hours and no IO chewing? As a note these are reproducible on both machines regardless of RHEL5 or RHEL4. I know there are much stats here but I have provided them in previous posts on this thread. Perhaps someone sees a red flag in the hardware? Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHu0vyATb/zqfZUUQRAiuYAJ9ut6i/cPv2MYc8RO2+wNw09M5/WwCfUaGY sAkFt+S14i0kFMn6mz9juBw= =TNys -END PGP SIGNATURE- ---(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: [HACKERS] 8.3 / 8.2.6 restore comparison
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 7 Feb 2008 18:40:37 -0800 "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > On Thu, 7 Feb 2008 18:32:41 -0800 > "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > > > > > Do you want counts or actual output? > > % time seconds usecs/call callserrors syscall > -- --- --- - - > 58.221.597638 33 47795 write > 31.860.874104 23 38024 recvfrom > 4.430.121559 92 1319 munmap > 4.170.114445 3 37906 lseek > 0.700.019341 13 1440 brk > 0.560.015402 12 1316 mmap > 0.040.000991 2638 open > 0.010.000298 838 close > 0.010.000156 438 time > 0.000.08 8 1 semop > -- --- --- - - > 100.002.743942127915 total > Were their any thoughts on this? I am also finding that backing up this database is rudely slow with the same type of odd metrics (almost zero (or zero) iowait). We can saturate a CPU but the CPU is certainly not pushing the data to disk as fast as it could. Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHu0hqATb/zqfZUUQRAm77AJ41yWT4f6UNiMwyHtcq9GASK51uDgCgpKzf lu14eCbSDl2v2Rsq40zww8E= =DshT -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Permanent settings
On Tue, 2008-02-19 at 19:38 +, Gregory Stark wrote: > "Magnus Hagander" <[EMAIL PROTECTED]> writes: > > > On Tue, Feb 19, 2008 at 04:58:21PM +, Gregory Stark wrote: > > > >> The include file method is workable but isn't perfect. What happens if a > >> user > >> connects with pgadmin and changes a parameter but that parameter is > >> overridden > >> by a variable in the config file? > > > > Um, if you put the include statement at the bottom, isn't that the one that > > will override? > > I was picturing putting it on top on the general principle that manual changes > should override automatic ones. I see I'm in the minority though. Oh. > It doesn't really matter though, this is all in the manually edited file -- > the admin can always move it around or add other configuration settings below > it. Yeah - or take it away completely if needed. > >> The alternative is to have two files and read them both. Then if you > >> change a > >> variable which is overridden by the other source you can warn that the > >> change > >> is ineffective. > > > > Ok, now I don't follow. If we use an include, we do have two files, and we > > read them both, no? > > Not from the point of view of the guc processing. It's all one source. Even if > it remembered which file various settings came from it's not going to remember > what order they arrived or what might hypothetically override a new setting. > > I was describing have two independent files read separately and kept track of > separately. That would hard code one having preference over the other and mean > that pgadmin could look at the guc source to see if there's a command-line > variable, environment variable, or popstgresql.conf setting which overrides > the postgresql.auto (or whatever) settings. Oh, ok. That does seem a bit more complicated than needed, though. > >> I think on balance the include file method is so much simpler that I > >> prefer it. > > > > Yeah, that is one very clear argument for that method. > > Still my feeling. We can put comments in the default config warning about the > consequences to pgadmin of overriding variables after the include. Yeah. Another thing I thought about - would people prefer a function or a parameter to the SET statement. Since I would imagine they're more or less only to be used from frontend programs like pgadmin, it could just as well be a function... //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Permanent settings
"Magnus Hagander" <[EMAIL PROTECTED]> writes: > On Tue, Feb 19, 2008 at 04:58:21PM +, Gregory Stark wrote: > >> The include file method is workable but isn't perfect. What happens if a user >> connects with pgadmin and changes a parameter but that parameter is >> overridden >> by a variable in the config file? > > Um, if you put the include statement at the bottom, isn't that the one that > will override? I was picturing putting it on top on the general principle that manual changes should override automatic ones. I see I'm in the minority though. It doesn't really matter though, this is all in the manually edited file -- the admin can always move it around or add other configuration settings below it. >> The alternative is to have two files and read them both. Then if you change a >> variable which is overridden by the other source you can warn that the change >> is ineffective. > > Ok, now I don't follow. If we use an include, we do have two files, and we > read them both, no? Not from the point of view of the guc processing. It's all one source. Even if it remembered which file various settings came from it's not going to remember what order they arrived or what might hypothetically override a new setting. I was describing have two independent files read separately and kept track of separately. That would hard code one having preference over the other and mean that pgadmin could look at the guc source to see if there's a command-line variable, environment variable, or popstgresql.conf setting which overrides the postgresql.auto (or whatever) settings. >> I think on balance the include file method is so much simpler that I prefer >> it. > > Yeah, that is one very clear argument for that method. Still my feeling. We can put comments in the default config warning about the consequences to pgadmin of overriding variables after the include. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Permanent settings
Andrew Dunstan wrote: Alvaro Herrera wrote: Joshua D. Drake wrote: IMO this should all be in the database and that's it. The idea that our global settings are in a file seems unusual consider we have a perfectly good storage engine available. That doesn't work, because many settings must be loaded before the database is fully operational. Not to mention what would happen if the database had a problem so we couldn't read the config. Although, on further reflection, we could probably meet both of these objections by having the database maintain a text version of the config which it would load on startup. One other possible objection is that it would allow any superuser to set things that currently require direct access to the config files, so that would be a major change in security arrangements. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Permanent settings
On Tue, Feb 19, 2008 at 04:58:21PM +, Gregory Stark wrote: > "Magnus Hagander" <[EMAIL PROTECTED]> writes: > > > Yeah, that may actually be a very good way to implement it. I don't like > > the idea of continously appending to an existing file, but if we did have a > > separate file with a tightly controlled format that would be doable. > > +1 > > Separating the automatically written configuration and the explicit user > configuration is definitely the right approach. My experience comes from > Debian where packages editing their own configuration files is verboten. > Otherwise you run into problems reconciling user-made changes and automatic > changes. > > The include file method is workable but isn't perfect. What happens if a user > connects with pgadmin and changes a parameter but that parameter is overridden > by a variable in the config file? Um, if you put the include statement at the bottom, isn't that the one that will override? > The alternative is to have two files and read them both. Then if you change a > variable which is overridden by the other source you can warn that the change > is ineffective. Ok, now I don't follow. If we use an include, we do have two files, and we read them both, no? > I think on balance the include file method is so much simpler that I prefer > it. Yeah, that is one very clear argument for that method. Since there have been no major protests, I assume that if I can come up with reasonably pretty code without opening up any horrible holes, going by the include method is the way to go? //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: [HACKERS] Permanent settings
On Tue, Feb 19, 2008 at 02:19:16PM -0300, Alvaro Herrera wrote: > Gregory Stark wrote: > > > The alternative is to have two files and read them both. Then if you change > > a > > variable which is overridden by the other source you can warn that the > > change > > is ineffective. > > > > I think on balance the include file method is so much simpler that I prefer > > it. > > I think this is a good idea. I would suggest being able to query > exactly which config file a setting came from -- so you can see whether > it's the stock postgresql.conf, or the locally-modified > postgresql.local.conf. Yeah, that's something I've been thinking about quite apart from this one. If you have a bunch of include files, it can be interesting to know exactly where the current setting was picked up. It's not like you can't find it elsewhere, but it's quicker if we could add that to pg_settings. I'll add that to my TODO list for things to look at when I work on this. //Magnus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
Alvaro Herrera wrote: Joshua D. Drake wrote: IMO this should all be in the database and that's it. The idea that our global settings are in a file seems unusual consider we have a perfectly good storage engine available. That doesn't work, because many settings must be loaded before the database is fully operational. Not to mention what would happen if the database had a problem so we couldn't read the config. Knowing when not to use a database is as important as knowing when to use one. cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 19 Feb 2008 15:22:42 -0300 Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Joshua D. Drake wrote: > > > IMO this should all be in the database and that's it. The idea that > > our global settings are in a file seems unusual consider we have a > > perfectly good storage engine available. > > That doesn't work, because many settings must be loaded before the > database is fully operational. Right but couldn't that be changed or if not, why not only have the settings that "must" be loaded before the database is fully operation in the postgresql.conf file. I can hear the wails of we don't want multiple configuration sources but we already have multiple configuration sources and having 90% of the configuration in the database should would make it easier. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHux+nATb/zqfZUUQRAjJtAKCCaH8Ubud/OZ8Gv7oOAJf8jjO9nACfQFf8 WMzBcyZcNvWGgoPv6b1fR/w= =IEBA -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > IMO this should all be in the database and that's it. The idea that our > global settings are in a file seems unusual consider we have a > perfectly good storage engine available. The sufficient reason why not is that many of these settings must be available before we can read the database. 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: [HACKERS] Permanent settings
Joshua D. Drake wrote: > IMO this should all be in the database and that's it. The idea that our > global settings are in a file seems unusual consider we have a > perfectly good storage engine available. That doesn't work, because many settings must be loaded before the database is fully operational. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 19 Feb 2008 15:36:26 +0100 Magnus Hagander <[EMAIL PROTECTED]> wrote: > Currently, pgAdmin supports editing postgresql.conf remotely using the > adminpack to open the file, change it locally in memory, and using the > adminpack again to write it back. This means that in theory pgAdmin > needs a full postgresql.conf parser. Right now it doesn't have this - > it just exposes the config file itself. Which sucks for usability, > and it's something I've heard a lot of people complain about. Other > databases (in my personal experience MSSQL, but IIRC I've had people > say the same about other ones as well) support configuring the > database remotely (and using a GUI for the most common options), and > this is a feature that a lot of users are lacking in PostgreSQL. I'd > like to do something about that. > > > What I'd really like to see is something like a new keyword on the SET > command, so you could to SET PERMANENT foo=bar, which would write the > configuration back into postgresql.conf. IMO this should all be in the database and that's it. The idea that our global settings are in a file seems unusual consider we have a perfectly good storage engine available. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHuxvEATb/zqfZUUQRAhoFAJ4jDcM0WyUuu0VgN9EZL7qRmMPPSACfb7Gh dyY7w3KDaCO1xQMdhtF50x0= =6/GJ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] CVS repository invalid revision
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 18 Feb 2008 15:59:29 -0500 (EST) Greg Smith <[EMAIL PROTECTED]> wrote: > On Sun, 17 Feb 2008, Christian Robottom Reis wrote: > > > Ah, interesting. Do you have a URL for the Subversion conversion? > > Is it a mirror of CVS, and if so, how often is it updated? > > The Subversion one is at > https://projects.commandprompt.com/public/pgsql/repo/ and I haven't > noticed comments on how often that particular one updates yet. I think it is every 4 hours but I am in the process of changing it (after testing) so that it is every 10 minutes. Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHuxivATb/zqfZUUQRAmwiAJ9esKdMzhqiJeCR712A2HirymZocQCcDqez KbrSmqmstQc52NDqJZNziBA= =2TVA -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Including PL/PgSQL by default
David Fetter <[EMAIL PROTECTED]> writes: > On Tue, Feb 19, 2008 at 12:11:05PM -0500, Tom Lane wrote: >> This has been proposed before, and rejected before. Have you got >> any new arguments? > The longer it's been since the last vuln in PL/PgSQL, the harder it is > to argue for having it not be there by default. You are attacking a straw man, which is that the only argument against having PL/PgSQL installed is the risk of security holes in it. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Permanent settings
> > Gregory Stark wrote: > > > The alternative is to have two files and read them both. Then if you > change a > > variable which is overridden by the other source you can warn that the > change > > is ineffective. > > > > I think on balance the include file method is so much simpler that I > prefer it. > > I think this is a good idea. I would suggest being able to query > exactly which config file a setting came from -- so you can see whether > it's the stock postgresql.conf, or the locally-modified > postgresql.local.conf. > So a junior DBA goes to manage the db. Makes a change the postgresql.conf file and bounces the db. The change doesn't stick. That doesn't sound like fun and it also sounds like Oracle's spfile and pfile. Jon ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Including PL/PgSQL by default
On Tue, Feb 19, 2008 at 12:11:05PM -0500, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > Let's put PL/PgSQL in template1 by default, as some downstream > > packagers are already doing. If someone really must remove it, > > they can still do that. > > This has been proposed before, and rejected before. Have you got > any new arguments? The longer it's been since the last vuln in PL/PgSQL, the harder it is to argue for having it not be there by default. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Severe regression in autoconf 2.61
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Am Dienstag, 19. Februar 2008 schrieb Tom Lane: >> Unfortunately, that gives the compiler enough of a syntactic clue >> to guess that fseeko is probably an undeclared function, and therefore >> *it will not error out*, only generate a warning, if it's not seen >> a declaration for fseeko. > Please try the attached patch. Seems to fix the problem, please apply. 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: [HACKERS] Permanent settings
Gregory Stark wrote: > The alternative is to have two files and read them both. Then if you change a > variable which is overridden by the other source you can warn that the change > is ineffective. > > I think on balance the include file method is so much simpler that I prefer > it. I think this is a good idea. I would suggest being able to query exactly which config file a setting came from -- so you can see whether it's the stock postgresql.conf, or the locally-modified postgresql.local.conf. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Including PL/PgSQL by default
David Fetter <[EMAIL PROTECTED]> writes: > Let's put PL/PgSQL in template1 by default, as some downstream > packagers are already doing. If someone really must remove it, they > can still do that. This has been proposed before, and rejected before. Have you got any new arguments? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Permanent settings
"Magnus Hagander" <[EMAIL PROTECTED]> writes: > Yeah, that may actually be a very good way to implement it. I don't like > the idea of continously appending to an existing file, but if we did have a > separate file with a tightly controlled format that would be doable. +1 Separating the automatically written configuration and the explicit user configuration is definitely the right approach. My experience comes from Debian where packages editing their own configuration files is verboten. Otherwise you run into problems reconciling user-made changes and automatic changes. The include file method is workable but isn't perfect. What happens if a user connects with pgadmin and changes a parameter but that parameter is overridden by a variable in the config file? The alternative is to have two files and read them both. Then if you change a variable which is overridden by the other source you can warn that the change is ineffective. I think on balance the include file method is so much simpler that I prefer it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(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
[HACKERS] Including PL/PgSQL by default
Folks, Let's put PL/PgSQL in template1 by default, as some downstream packagers are already doing. If someone really must remove it, they can still do that. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Permanent settings
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > What I'd really like to see is something like a new keyword on the SET > command, so you could to SET PERMANENT foo=bar, which would write the > configuration back into postgresql.conf. FWIW, I made a Pl/PerlU function that did this at one point. At first, I parsed the postgresql.conf file and rewrote it, but after a while I switched to the include a writeable file idea. The downside to that was trying to follow the path of files to figure out what a particular setting was (e.g. "grep 'effective' postgresql.conf" no longer provided a canonical answer), so at the end of the day I simply appended a big comment to the bottom of the postgresql.conf file and added the settings there. Rather than adding 100 lines for 100 changes to the same variable, the function checked the postgresql.conf into version control[1] after every change. That might be ambitious for the SET command to handle, but it would sure be a slick feature :) If not that, it might be nice to provide a switch to allow 100 lines, with timestamp, if desired. An optional comment from the command line would be another nice touch: SET PERMANENT effective_cache_size='4GB' COMMENT='Added more RAM to box' [1] As long as your version control was cvs, subversion, git, or rcs. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200802191128 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAke7BGAACgkQvJuQZxSWSsg4GQCg3nnXaRBvZqJRnFIkq+Y8sXRr hZ4AoPVQnJEnk3lJFpNJmikuDwaqz88c =5BwE -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Permanent settings
On Tue, Feb 19, 2008 at 11:14:59AM -0500, Andrew Dunstan wrote: > > > Richard Huxton wrote: > >Magnus Hagander wrote: > >> > >>Right. I don't think we have any settings that depend on order, do we? > > > >That's what I was trying to think of - nothing came to mind. > > > > custom_variable_classes and dependents? I think we could easily get away with saying that you can't change custom_variable_classes remotely through this interface. That's not something the user generally changes, that's something that's set when you install a new module. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Permanent settings
On Tue, Feb 19, 2008 at 11:09:43AM -0500, Aidan Van Dyk wrote: > * Magnus Hagander <[EMAIL PROTECTED]> [080219 10:39]: > > On Tue, Feb 19, 2008 at 10:34:26AM -0500, Aidan Van Dyk wrote: > > > > Are you suggesting we keep appending? So if I set the same parameter 100 > > > > times, it would show up on 100 rows? > > > > > > In my opinion, absolutely. It's easy, safe, and the "overhead" > > > associated with it is minimal, and not in any critical path "work" path. > > > Add to that the fact that the admin can easily clean up the file any > > > time he wants too. > > > > I think that's entirely unworkable. While I absolutelyi don't want to break > > things for people who use the config file as the primary interface (heck, > > *I* am one of those people), it has to be usable for the case it's trying > > to fix. And this really wouldn't be. > > Can you explain why this wouldn't be usable? Because you will end up with an ever-growing file, that will be a PITA to deal with. Consider it after 10k+ changes. (yes, I can see that happening. You know how some people use GUIs) Or 100k. The problem does not happen at 100 lines... I can see the solution with a single file with them all in, but it needs to be able to overwrite them IMHO. //Magnus ---(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: [HACKERS] Permanent settings
Richard Huxton wrote: Magnus Hagander wrote: Right. I don't think we have any settings that depend on order, do we? That's what I was trying to think of - nothing came to mind. custom_variable_classes and dependents? cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Permanent settings
On Tue, Feb 19, 2008 at 11:11:05AM -0500, Aidan Van Dyk wrote: > * Csaba Nagy <[EMAIL PROTECTED]> [080219 10:59]: > > > Are you suggesting we keep appending? So if I set the same parameter 100 > > > times, it would show up on 100 rows? > > > > What about not touching the config file at all, but write to a separate > > file which is completely under the control of postgres and include that > > at the end of the config file ? You just said includes are a new feature > > which could complicate things, so why not use it actually in your > > advantage ;-) > > > > That way disabling the overrides would be as simple as commenting out > > the inclusion of the postgres controlled config file. And it would > > separate the user writable and machine writable configuration... > > Yes, I think that would be necessary (like I said), because in most > installations, I don't even thing the postgres user even has write > access to the main config file. The postgres user gets it by default whenever you run a standard initdb, AFAIK. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Permanent settings
Aidan Van Dyk wrote: Any "set permanent" settings should be *appended* to the main config file, preferably with a comment line, like: # Set by user from client on some_guc option = some_value Are you suggesting we keep appending? So if I set the same parameter 100 times, it would show up on 100 rows? In my opinion, absolutely. It's easy, safe, and the "overhead" associated with it is minimal, and not in any critical path "work" path. Add to that the fact that the admin can easily clean up the file any time he wants too. I think this is quite unacceptable and ugly. Creating an ever-growing file that the admin would have to clean up by hand is horrid. ISTM that this whole area is likely to be difficult unless we move to a more structured config file (JSON, anyone?) cheers andrew ---(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: [HACKERS] Permanent settings
* Csaba Nagy <[EMAIL PROTECTED]> [080219 10:59]: > > Are you suggesting we keep appending? So if I set the same parameter 100 > > times, it would show up on 100 rows? > > What about not touching the config file at all, but write to a separate > file which is completely under the control of postgres and include that > at the end of the config file ? You just said includes are a new feature > which could complicate things, so why not use it actually in your > advantage ;-) > > That way disabling the overrides would be as simple as commenting out > the inclusion of the postgres controlled config file. And it would > separate the user writable and machine writable configuration... Yes, I think that would be necessary (like I said), because in most installations, I don't even thing the postgres user even has write access to the main config file. a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Permanent settings
* Magnus Hagander <[EMAIL PROTECTED]> [080219 10:39]: > On Tue, Feb 19, 2008 at 10:34:26AM -0500, Aidan Van Dyk wrote: > > > Are you suggesting we keep appending? So if I set the same parameter 100 > > > times, it would show up on 100 rows? > > > > In my opinion, absolutely. It's easy, safe, and the "overhead" > > associated with it is minimal, and not in any critical path "work" path. > > Add to that the fact that the admin can easily clean up the file any > > time he wants too. > > I think that's entirely unworkable. While I absolutelyi don't want to break > things for people who use the config file as the primary interface (heck, > *I* am one of those people), it has to be usable for the case it's trying > to fix. And this really wouldn't be. Can you explain why this wouldn't be usable? I see the following propeties: *) KISS *) Easily "function-able" *) 0 cost on the server writing "new/changed" GUC settings (open/seek/write/close) *) 0 cost on setting "permanent" settings via commands *) 0 cost on PostgreSQL config code infrastructure *) 0 cost on "running" database *) minimal cost on "reading" config file (a few more lines) This seems to be usable for everything the case it's trying to fix wants: *) simple, and guarenteed to work, not loosing any existing config file syntax *) not hard to maintain/backport *) not expensive to a running database cluster *) "permanent" settings are saved/reloaded correctly I don't see anything that would make this unusable for the purpose of having the server be able to "permanently" save GUC settings. A user using this interface isn't going to care if a file is 1 line, or 100 lines, and whether the config file parsing (on startup or reload) takes 13.34ms or 13.69ms. a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Permanent settings
Magnus Hagander wrote: On Tue, Feb 19, 2008 at 03:53:11PM +, Richard Huxton wrote: Magnus Hagander wrote: What I'd really like to see is something like a new keyword on the SET command, so you could to SET PERMANENT foo=bar, which would write the configuration back into postgresql.conf. I don't have a complete solution for how to actually implement it, so I'm just throwing out some ideas for comment. Not sure if it's of interest, but you might want to look at the postfix mailserver configuration setup and see if that translates to an API. postconf lists the configuration settings (in alphabetical order) SELECT * FROM pg_settings postconf -n list non-default settings SELECT * FROM pg_settings WHERE NOT source='default' postconf display "setting = value" SHOW log_destination postconf -e = edit the configuration file, changing that setting That's the one remaining :-) The editing option replaces any existing version of that setting and adds the new value at the end of the file. Eh, it cannot both replace it, and add it at the end of the file, can it? Does it replace it in-line, or does it remove the in-line entry and put the new one at the end? Or are you saying it edits in-line entries and appends new ones at the end? Sorry, - Edits existing lines. - Adds new ones to end of file. - Leaves blank lines, comments etc. alone Having all the values at the end of the file works well, because for a simple setup you don't need to change many settings and they don't depend on order. Right. I don't think we have any settings that depend on order, do we? That's what I was trying to think of - nothing came to mind. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Permanent settings
On Tue, Feb 19, 2008 at 03:53:11PM +, Richard Huxton wrote: > Magnus Hagander wrote: > >What I'd really like to see is something like a new keyword on the SET > >command, so you could to SET PERMANENT foo=bar, which would write the > >configuration back into postgresql.conf. > > > >I don't have a complete solution for how to actually implement it, so I'm > >just throwing out some ideas for comment. > > Not sure if it's of interest, but you might want to look at the postfix > mailserver configuration setup and see if that translates to an API. > > postconf > lists the configuration settings (in alphabetical order) SELECT * FROM pg_settings > postconf -n > list non-default settings SELECT * FROM pg_settings WHERE NOT source='default' > postconf > display "setting = value" SHOW log_destination > postconf -e = > edit the configuration file, changing that setting That's the one remaining :-) > The editing option replaces any existing version of that setting and > adds the new value at the end of the file. Eh, it cannot both replace it, and add it at the end of the file, can it? Does it replace it in-line, or does it remove the in-line entry and put the new one at the end? Or are you saying it edits in-line entries and appends new ones at the end? > Having all the values at the end of the file works well, because for a > simple setup you don't need to change many settings and they don't > depend on order. Right. I don't think we have any settings that depend on order, do we? //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Permanent settings
On Tue, 2008-02-19 at 16:41 +0100, Magnus Hagander wrote: > The end result wouldn't be "as clean" as some would expect, but it would > certainly be easier code-wise. For example, I'm sure someone would get the > suggestion to go edit postgresql.conf to change a config value, and be > surprised when it didn't show up as a changed setting because it was > overridden from another file.. Yes, but at least the override part would be nicely separated in a file, and could suggestively be named as something like postgresql.conf.override, and hopefully will stick out sufficiently for those who edit the config file directly to wonder about it's purpose... and of course always editable directly too, so you can easily manually fix foot-shooting mistakes made from the admin interface. It would be just simply rewritten each time you change something without regard to the manual changes, and possibly ignored altogether if your manual changes violate it's expected layout. Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Permanent settings
Magnus Hagander wrote: What I'd really like to see is something like a new keyword on the SET command, so you could to SET PERMANENT foo=bar, which would write the configuration back into postgresql.conf. I don't have a complete solution for how to actually implement it, so I'm just throwing out some ideas for comment. Not sure if it's of interest, but you might want to look at the postfix mailserver configuration setup and see if that translates to an API. postconf lists the configuration settings (in alphabetical order) postconf -n list non-default settings postconf display "setting = value" postconf -e = edit the configuration file, changing that setting The editing option replaces any existing version of that setting and adds the new value at the end of the file. Having all the values at the end of the file works well, because for a simple setup you don't need to change many settings and they don't depend on order. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Permanent settings
On Tue, Feb 19, 2008 at 04:38:16PM +0100, Csaba Nagy wrote: > > Are you suggesting we keep appending? So if I set the same parameter 100 > > times, it would show up on 100 rows? > > What about not touching the config file at all, but write to a separate > file which is completely under the control of postgres and include that > at the end of the config file ? You just said includes are a new feature > which could complicate things, so why not use it actually in your > advantage ;-) > > That way disabling the overrides would be as simple as commenting out > the inclusion of the postgres controlled config file. And it would > separate the user writable and machine writable configuration... Yeah, that may actually be a very good way to implement it. I don't like the idea of continously appending to an existing file, but if we did have a separate file with a tightly controlled format that would be doable. The end result wouldn't be "as clean" as some would expect, but it would certainly be easier code-wise. For example, I'm sure someone would get the suggestion to go edit postgresql.conf to change a config value, and be surprised when it didn't show up as a changed setting because it was overridden from another file.. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Permanent settings
On Tue, Feb 19, 2008 at 10:34:26AM -0500, Aidan Van Dyk wrote: > * Magnus Hagander <[EMAIL PROTECTED]> [080219 10:28]: > > > > But if you *need* a way to "set permanent", couldn't you do with with > > > the following KISS idea? > > > > > > Any "set permanent" settings should be *appended* to the main config > > > file, preferably with a comment line, like: > > > # Set by user from client on > > > some_guc option = some_value > > > > Are you suggesting we keep appending? So if I set the same parameter 100 > > times, it would show up on 100 rows? > > In my opinion, absolutely. It's easy, safe, and the "overhead" > associated with it is minimal, and not in any critical path "work" path. > Add to that the fact that the admin can easily clean up the file any > time he wants too. I think that's entirely unworkable. While I absolutelyi don't want to break things for people who use the config file as the primary interface (heck, *I* am one of those people), it has to be usable for the case it's trying to fix. And this really wouldn't be. > > > This does 2 things: > > > > > > 1) Eliminates a need for a fancy config parser/editor/rewriter > > > 2) Makes it very easy to implement > > > 3) Even allows adminpack to have a "set_permanent" function that could do > > > it > > >all internally, and not actually need support in the backend core. > > > > I should warn you that it's on my radar to try to find the compromises > > necessary to merge the required functionality away from adminpack and into > > core for 8.4. Not sure if I'll manage, but I'm certainly going to try. > > Having to install contrib modules to be able to read your logfiles (just > > one of several examples) is another thing that most users I've come in > > contact with hate. > > Sure, but the "append via an adminpack function" has the added benifit > that it can easily be "backported" as a contrib module to the previous > versions that pgadmin supports as well. I should clearify that I'm not at all against implementing it as a function - that may well be a better way. And it would be backportable. I'm only against the "stick it in adminpack because some people don't want it" part. It should be made good enough that it's not disruptive for those that don't want it, and good enough to be in core. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Permanent settings
> Are you suggesting we keep appending? So if I set the same parameter 100 > times, it would show up on 100 rows? What about not touching the config file at all, but write to a separate file which is completely under the control of postgres and include that at the end of the config file ? You just said includes are a new feature which could complicate things, so why not use it actually in your advantage ;-) That way disabling the overrides would be as simple as commenting out the inclusion of the postgres controlled config file. And it would separate the user writable and machine writable configuration... Cheers, Csaba. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Severe regression in autoconf 2.61
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Please try the attached patch. Shortly. > What is currently the consequence of the problem? Does it fail to build, > fail > to run, or does it fail with large files? The consequence of the problem is that pg_dump/pg_restore are compiled without any visible prototypes for fseeko/ftello, which has implications that'd depend on the architecture's rules for passing/returning off_t as opposed to int. I'd say "not work at all" is possible and "not work for large files" is certain. The backend doesn't seem to use these functions so it shouldn't be affected. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Permanent settings
* Magnus Hagander <[EMAIL PROTECTED]> [080219 10:28]: > > But if you *need* a way to "set permanent", couldn't you do with with > > the following KISS idea? > > > > Any "set permanent" settings should be *appended* to the main config > > file, preferably with a comment line, like: > > # Set by user from client on > > some_guc option = some_value > > Are you suggesting we keep appending? So if I set the same parameter 100 > times, it would show up on 100 rows? In my opinion, absolutely. It's easy, safe, and the "overhead" associated with it is minimal, and not in any critical path "work" path. Add to that the fact that the admin can easily clean up the file any time he wants too. > > This does 2 things: > > > > 1) Eliminates a need for a fancy config parser/editor/rewriter > > 2) Makes it very easy to implement > > 3) Even allows adminpack to have a "set_permanent" function that could do it > >all internally, and not actually need support in the backend core. > > I should warn you that it's on my radar to try to find the compromises > necessary to merge the required functionality away from adminpack and into > core for 8.4. Not sure if I'll manage, but I'm certainly going to try. > Having to install contrib modules to be able to read your logfiles (just > one of several examples) is another thing that most users I've come in > contact with hate. Sure, but the "append via an adminpack function" has the added benifit that it can easily be "backported" as a contrib module to the previous versions that pgadmin supports as well. a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Permanent settings
On Tue, Feb 19, 2008 at 09:53:30AM -0500, Aidan Van Dyk wrote: > * Magnus Hagander <[EMAIL PROTECTED]> [080219 09:37]: > > > One way might be to simply have the config file reader store the location > > for each setting where it was found, and when you do a SET PERMANENT (if > > that's what we'd call it) it'll go back to that place and make the > > modification there. If a setting hasn't previously been set, we could just > > append it to the end of the main configuration file. > > > > One thing that can be hard to deal with is comments. It would be good if > > there was some way to support reading/writing simple comments (say a # at > > the end of the line) through this API, but I think it's OK not to deal with > > complex multi-line comments. I think it's fairly safe to say that the vast > > majority of users will *either* change their configuration through the > > config file *or* through the API. Or those that use both aren't likely to > > use really complex combinations of config files and comments and such. > > Speaking as one who favours the unix admin style (i.e. editing the > config file), take the following with a grain of salt. > > But if you *need* a way to "set permanent", couldn't you do with with > the following KISS idea? > > Any "set permanent" settings should be *appended* to the main config > file, preferably with a comment line, like: > # Set by user from client on > some_guc option = some_value Are you suggesting we keep appending? So if I set the same parameter 100 times, it would show up on 100 rows? > This does 2 things: > > 1) Eliminates a need for a fancy config parser/editor/rewriter > 2) Makes it very easy to implement > 3) Even allows adminpack to have a "set_permanent" function that could do it >all internally, and not actually need support in the backend core. I should warn you that it's on my radar to try to find the compromises necessary to merge the required functionality away from adminpack and into core for 8.4. Not sure if I'll manage, but I'm certainly going to try. Having to install contrib modules to be able to read your logfiles (just one of several examples) is another thing that most users I've come in contact with hate. //Magnus ---(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: [HACKERS] Timezone view
Naz Gassiep wrote: > It may also be beneficial to add the ISO 3166 column into that view, the > data is in zone.tab and I can't see a reason to not include it. We also have the country name in iso3166.tab and the geo coordinates. And there is also a comment field. Which ones make sense to add? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Common Sub-expression removal
Hi, One thing that occurred to me when reading the "Ad Hoc Indexes" thread was that PG doesn't seem to do much with tidying up common sub-expressions (I'm not sure why I remembered about it as it's not particularly related, strange). Anyway, as an example imagine I have a large table that I want to do a self join on: SELECT m1.source_ls_id, m1.movement_date, m2.movement_date FROM bcms.source_movements m1, bcms.source_movements m2 WHERE m1.source_ls_id = m2.source_ls_id AND m1.movement_date < m2.movement_date; I get a plan that sorts the movements table twice, giving the correct answer but taking a while to actually get it. Merge Join (cost=58981120.56..138431232.17 rows=1498156785 width=12) Merge Cond: (m1.source_ls_id = m2.source_ls_id) Join Filter: (m1.movement_date < m2.movement_date) -> Sort (cost=29490560.28..29889000.48 rows=159376080 width=8) Sort Key: m1.source_ls_id -> Seq Scan on source_movements m1 (cost=0.00..2874586.80 rows=159376080 width=8) -> Sort (cost=29490560.28..29889000.48 rows=159376080 width=8) Sort Key: m2.source_ls_id -> Seq Scan on source_movements m2 (cost=0.00..2874586.80 rows=159376080 width=8) The time I actually tend to notice it more is when the join is between two expensive views; this test case is nice and easy to reason about though. Sam ---(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: [HACKERS] Permanent settings
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED] On Behalf Of Magnus Hagander > Sent: Tuesday, February 19, 2008 8:36 AM > To: pgsql-hackers > Subject: [HACKERS] Permanent settings > > What I'd really like to see is something like a new keyword on the SET > command, so you could to SET PERMANENT foo=bar, which would write the > configuration back into postgresql.conf. How about putting an indicator in the postgresql.conf file dynamic=1 and then the db could manage the file else the dynamic change wouldn't stick on a restart? You wouldn't need to add a new keyword this way and less likely for a DBA to mess up the syntax. Jon ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
* Magnus Hagander <[EMAIL PROTECTED]> [080219 09:37]: > One way might be to simply have the config file reader store the location > for each setting where it was found, and when you do a SET PERMANENT (if > that's what we'd call it) it'll go back to that place and make the > modification there. If a setting hasn't previously been set, we could just > append it to the end of the main configuration file. > > One thing that can be hard to deal with is comments. It would be good if > there was some way to support reading/writing simple comments (say a # at > the end of the line) through this API, but I think it's OK not to deal with > complex multi-line comments. I think it's fairly safe to say that the vast > majority of users will *either* change their configuration through the > config file *or* through the API. Or those that use both aren't likely to > use really complex combinations of config files and comments and such. Speaking as one who favours the unix admin style (i.e. editing the config file), take the following with a grain of salt. But if you *need* a way to "set permanent", couldn't you do with with the following KISS idea? Any "set permanent" settings should be *appended* to the main config file, preferably with a comment line, like: # Set by user from client on some_guc option = some_value This does 2 things: 1) Eliminates a need for a fancy config parser/editor/rewriter 2) Makes it very easy to implement 3) Even allows adminpack to have a "set_permanent" function that could do it all internally, and not actually need support in the backend core. This relies on the fact that a "later" setting over-rides an earlier one. This also means that your postgres user actually has write access to the config files (is this something people normally allow?) I guess this "write" problem could be overcome with yet-another-guc to specify the "permanent write file" path.. a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
[HACKERS] Permanent settings
Currently, pgAdmin supports editing postgresql.conf remotely using the adminpack to open the file, change it locally in memory, and using the adminpack again to write it back. This means that in theory pgAdmin needs a full postgresql.conf parser. Right now it doesn't have this - it just exposes the config file itself. Which sucks for usability, and it's something I've heard a lot of people complain about. Other databases (in my personal experience MSSQL, but IIRC I've had people say the same about other ones as well) support configuring the database remotely (and using a GUI for the most common options), and this is a feature that a lot of users are lacking in PostgreSQL. I'd like to do something about that. What I'd really like to see is something like a new keyword on the SET command, so you could to SET PERMANENT foo=bar, which would write the configuration back into postgresql.conf. I don't have a complete solution for how to actually implement it, so I'm just throwing out some ideas for comment. I don't think we need to be able to parse and deal with "very complex configuration files", as long as we're not likely to corrupt them badly. The task got a bit harder with the support of include files, but I'm sure it's doable. One way might be to simply have the config file reader store the location for each setting where it was found, and when you do a SET PERMANENT (if that's what we'd call it) it'll go back to that place and make the modification there. If a setting hasn't previously been set, we could just append it to the end of the main configuration file. One thing that can be hard to deal with is comments. It would be good if there was some way to support reading/writing simple comments (say a # at the end of the line) through this API, but I think it's OK not to deal with complex multi-line comments. I think it's fairly safe to say that the vast majority of users will *either* change their configuration through the config file *or* through the API. Or those that use both aren't likely to use really complex combinations of config files and comments and such. (before someone complains about the "argh, editing config files remote is insecure" - we can always have a config option to turn it off. And it can still be protected by not giving the server write permissions on the file, or selinux, or whatever) Thoughts? More ranting? //Magnus ---(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: [HACKERS] Getting available options
On Tue, Feb 19, 2008 at 11:17:51AM -0300, Alvaro Herrera wrote: > Magnus Hagander wrote: > > > That would work for all the settings here I think, except for > > log_destinatino > > which actually supports multiple choices from the enumeration. But that > > would be fairly easy to implement on top of it. > > I think DateStyle should also be considered carefully, given how it has > two different settings in one variable (USE_XXX_STYLE and DATEORDER_XXX) Yeah, but you can turn those into enumerable combinations, no? (A,A; A,B; B,A; B,B etc) > TimeZone would also be a little more difficult than an enum. Right, I didn't even have timezone under the enum list so far. Since the list is just too long... For that one there is already a view available to get all options, and you're likely to want to group them (for example, by continent) anyway.. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Getting available options
Magnus Hagander wrote: > That would work for all the settings here I think, except for log_destinatino > which actually supports multiple choices from the enumeration. But that > would be fairly easy to implement on top of it. I think DateStyle should also be considered carefully, given how it has two different settings in one variable (USE_XXX_STYLE and DATEORDER_XXX) TimeZone would also be a little more difficult than an enum. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Getting available options
> I like option 1 the best. Minimally invasive, and probably > easier to handle on the client than 2. +1 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Getting available options
On Tue, Feb 19, 2008 at 02:56:42PM +0100, Peter Eisentraut wrote: > Am Dienstag, 19. Februar 2008 schrieb Magnus Hagander: > > 1) Extend pg_settings with a column that contains an array of the available > > options. Extend the config_string GUC struct with a hook that can return > > this data. > > Before we get to the interface for reporting this to the client, I wonder how > we should deal with this in the backend. Right now, we have string options > with assign hooks. You'd need to add a reporting hook or something for each > parameter. I'm thinking, perhaps we should invent a new GUC data type enum, > now that enum is an accepted way to categorize with data around here. :) > That could simplify the code a bit. That would probably be a good idea - that one is in my scattered notes about how to do it :-) I figured I'd start with the interface and then proceed there later... That would work for all the settings here I think, except for log_destinatino which actually supports multiple choices from the enumeration. But that would be fairly easy to implement on top of it. So yes, I agree that creating an enum type for GUC would probably be a good way to implement it. But I think trying to expose it as a SQL level enum is not a good idea - just keeping it as a string there seems a lot better. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Getting available options
Am Dienstag, 19. Februar 2008 schrieb Magnus Hagander: > 1) Extend pg_settings with a column that contains an array of the available > options. Extend the config_string GUC struct with a hook that can return > this data. Before we get to the interface for reporting this to the client, I wonder how we should deal with this in the backend. Right now, we have string options with assign hooks. You'd need to add a reporting hook or something for each parameter. I'm thinking, perhaps we should invent a new GUC data type enum, now that enum is an accepted way to categorize with data around here. :) That could simplify the code a bit. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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: [HACKERS] Getting available options
On Feb 19, 2008 1:22 PM, Magnus Hagander <[EMAIL PROTECTED]> wrote: > I'd like to add a way for a client (in this case, typicallyi pgadmin, > phppgadmin or similar) to get the available options on the server for the > GUC settings we have that take fixed options. This is to make it possible > to generate pretty dropdown lists of the options. > > One way would be to simply pre-populate the client with the available > options. This sucks in at least two cases - the client has to be updated > whenever the server is. And, more importantly, in some cases the available > options depend on server-side build-time configuration (for example, > log_destination depends on win32/non win32 and syslog yes/no, wal_sync_method > depends on what's available on the OS). > > In order to somehow get this data down from the client, I see a couple of > ways: > > 1) Extend pg_settings with a column that contains an array of the available > options. Extend the config_string GUC struct with a hook that can return > this data. > > 2) Create a separate function, for the sake of argument > pg_get_available_options(). Extend config_string GUC struct the > same way here. > > 3) Create one function for each of these, so a > pg_get_available_log_destinations(), pg_get_available_wal_sync_methods() > etc etc. > > 4) The best method available that I obviously didn't think of yet > > > Option 3 seems like an excessively ugly solution :) But what about the others? > What would people prefer? I like option 1 the best. Minimally invasive, and probably easier to handle on the client than 2. 3 is just ugly as you say. You should be ashamed of yourself :-p -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Oracle-compatible database company ---(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
[HACKERS] Getting available options
I'd like to add a way for a client (in this case, typicallyi pgadmin, phppgadmin or similar) to get the available options on the server for the GUC settings we have that take fixed options. This is to make it possible to generate pretty dropdown lists of the options. One way would be to simply pre-populate the client with the available options. This sucks in at least two cases - the client has to be updated whenever the server is. And, more importantly, in some cases the available options depend on server-side build-time configuration (for example, log_destination depends on win32/non win32 and syslog yes/no, wal_sync_method depends on what's available on the OS). In order to somehow get this data down from the client, I see a couple of ways: 1) Extend pg_settings with a column that contains an array of the available options. Extend the config_string GUC struct with a hook that can return this data. 2) Create a separate function, for the sake of argument pg_get_available_options(). Extend config_string GUC struct the same way here. 3) Create one function for each of these, so a pg_get_available_log_destinations(), pg_get_available_wal_sync_methods() etc etc. 4) The best method available that I obviously didn't think of yet Option 3 seems like an excessively ugly solution :) But what about the others? What would people prefer? //Magnus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Severe regression in autoconf 2.61
Am Dienstag, 19. Februar 2008 schrieb Tom Lane:
> Previously, AC_FUNC_FSEEKO did this to test if fseeko was available:
> return !fseeko;
> Now it does this:
> return fseeko (stdin, 0, 0) && (fseeko) (stdin, 0, 0);
>
> Unfortunately, that gives the compiler enough of a syntactic clue
> to guess that fseeko is probably an undeclared function, and therefore
> *it will not error out*, only generate a warning, if it's not seen
> a declaration for fseeko.
Please try the attached patch.
What is currently the consequence of the problem? Does it fail to build, fail
to run, or does it fail with large files?
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
diff -ur ../cvs-pgsql/config/c-library.m4 ./config/c-library.m4
--- ../cvs-pgsql/config/c-library.m4 2005-04-04 09:21:37.0 +0200
+++ ./config/c-library.m4 2008-02-19 10:28:11.0 +0100
@@ -297,3 +297,29 @@
])dnl AC_CACHE_VAL
AC_MSG_RESULT([$pgac_cv_printf_arg_control])
])# PGAC_FUNC_PRINTF_ARG_CONTROL
+
+
+# backport from Autoconf 2.61a
+# http://git.savannah.gnu.org/gitweb/?p=autoconf.git;a=commitdiff;h=f0c325537a22105536ac8c4e88656e50f9946486
+
+# AC_FUNC_FSEEKO
+# --
+AN_FUNCTION([ftello], [AC_FUNC_FSEEKO])
+AN_FUNCTION([fseeko], [AC_FUNC_FSEEKO])
+AC_DEFUN([AC_FUNC_FSEEKO],
+[_AC_SYS_LARGEFILE_MACRO_VALUE(_LARGEFILE_SOURCE, 1,
+ [ac_cv_sys_largefile_source],
+ [Define to 1 to make fseeko visible on some hosts (e.g. glibc 2.2).],
+ [[#include /* for off_t */
+ #include ]],
+ [[int (*fp) (FILE *, off_t, int) = fseeko;
+ return fseeko (stdin, 0, 0) && fp (stdin, 0, 0);]])
+
+# We used to try defining _XOPEN_SOURCE=500 too, to work around a bug
+# in glibc 2.1.3, but that breaks too many other things.
+# If you want fseeko and ftello with glibc, upgrade to a fixed glibc.
+if test $ac_cv_sys_largefile_source != unknown; then
+ AC_DEFINE(HAVE_FSEEKO, 1,
+[Define to 1 if fseeko (and presumably ftello) exists and is declared.])
+fi
+])# AC_FUNC_FSEEKO
diff -ur ../cvs-pgsql/configure ./configure
--- ../cvs-pgsql/configure 2008-02-19 10:26:38.0 +0100
+++ ./configure 2008-02-19 10:28:27.0 +0100
@@ -18051,11 +18051,13 @@
cat confdefs.h >>conftest.$ac_ext
cat >>conftest.$ac_ext <<_ACEOF
/* end confdefs.h. */
-#include
+#include /* for off_t */
+ #include
int
main ()
{
-return fseeko (stdin, 0, 0) && (fseeko) (stdin, 0, 0);
+int (*fp) (FILE *, off_t, int) = fseeko;
+ return fseeko (stdin, 0, 0) && fp (stdin, 0, 0);
;
return 0;
}
@@ -18095,11 +18097,13 @@
cat >>conftest.$ac_ext <<_ACEOF
/* end confdefs.h. */
#define _LARGEFILE_SOURCE 1
-#include
+#include /* for off_t */
+ #include
int
main ()
{
-return fseeko (stdin, 0, 0) && (fseeko) (stdin, 0, 0);
+int (*fp) (FILE *, off_t, int) = fseeko;
+ return fseeko (stdin, 0, 0) && fp (stdin, 0, 0);
;
return 0;
}
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ANALYZE to be ignored by VACUUM
"ITAGAKI Takahiro" <[EMAIL PROTECTED]> writes: > 4. ANALYZE finishes in a short time. >It is ok that VACUUM takes a long time because it is not a transaction, >but ANALYZE should not. It requres cleverer statistics algorithm. >Sampling factor 10 is not enough for pg_stats.n_distinct. We seems to >estimate n_distinct too low for clustered (ordered) tables. Unfortunately no constant size sample is going to be enough for reliable n_distinct estimates. To estimate n_distinct you really have to see a percentage of the table, and to get good estimates that percentage has to be fairly large. There was a paper with a nice algorithm posted a while back which required only constant memory but it depended on scanning the entire table. I think to do n_distinct estimates we'll need some statistics which are either gathered opportunistically whenever a seqscan happens or maintained by an index. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] RFP: Recursive query in 8.4
Hi,
As I promised before we would like to propose implementing the
recursive query as defined in the standard for PostgreSQL 8.4.
The work is supported by Sumitomo Electric Information Systems Co.,
Ltd. (http://www.sei-info.co.jp/) and SRA OSS, Inc. Japan
(http://www.sraoss.co.jp).
1. Overview
We propose to implement the recursive query (WITH RECURSIVE clause)
defined in SQL:1999 and later. With the recursive query, one can
easily inquire the data expressed as tree and graph structures. The
actual syntax we prefer is the one defined in SQL:2008 (it's not
published yet, but I have a closest draft).
We do not propose the WITH clause without RECURSIVE key word here
since someone else has already made a proposal for this.
(http://archives.postgresql.org/pgsql-patches/2008-01/msg00105.php)
2. Example
For those who are not familiar with the recursive query, I include an
example:
CREATE TABLE department (
id INT PRIMARY KEY,
parent_department INT REFERENCES department,
name TEXT
);
INSERT INTO department VALUES (0, NULL, 'ROOT');
INSERT INTO department VALUES (1, 0, 'A');
INSERT INTO department VALUES (2, 1, 'B');
INSERT INTO department VALUES (3, 2, 'C');
INSERT INTO department VALUES (4, 2, 'D');
INSERT INTO department VALUES (5, 0, 'E');
INSERT INTO department VALUES (6, 4, 'F');
INSERT INTO department VALUES (7, 4, 'G');
This will represent a tree structure of an organization:
ROOT ---> A ---> B ---> C ---> F
| |
| +> D
|
+-> E ---> G
If you want to extract all departments "under" A, you could use a
recursive query:
WITH RECURSIVE subdepartment AS
(
--
SELECT * FROM department WHERE id = 'A'
UNION ALL
-- recursive term referring to "subdepartment"
SELECT d.* FROM department AS d, subdepartment AS sd
WHERE d.id = sd.parent_department
)
SELECT * FROM subdepartment;
This will return A, B, C, D and F.
2. The syntax
As described above, we refers to the SQL:2008's WITH RECURSIVE clause syntax.
WITH RECURSIVE clause ::=
WITH RECURSIVE AS ( )
[ SEARCH clause | CYCLE clause ]
In the example above, is "subdepartment" and is SELECTs in pareses.
must have one or more "anchor" expressions. This is
required by the standard.
The anchor expressions are consisted with "none recursive term"
(SELECT * FROM department WHERE id = 'A') + UNION ALL + "recursive
term" (SELECT d.* FROM department AS d, subdepartment AS sd WHERE d.id
= sd.parent_department). is "SELECT * FROM subdepartment".
Note that the standard allows to use an UNION without ALL. However
this proposal only allow UNION ALL due to an implementation reason.
Other limitations required by the standard include:
- aggregate functions are not allowed in the recursive term
- GROUP BY is not allowed in the recursive term
- outer joins are not allowed in the recursive term
3. Processing a recursive query
If a WITH clause includes a recursive referencing cycle, we call the
set of as "partition". In the example above,
there is a partition in which subdepartment referees to itself. We
limit number of list elements in a partition up to 1, which means it
should be a self reference.
While processing a recursive query, we start with a partition which
does not depend on any other partitions.
There is a working table WT and an intermediate table RT to evaluate a
partition. We implement WT and RT using tuplestore.
The algorithm is shown below.
[using the width first search]
1. evaluate non recursive term or partition depending on other
partitions and assign the result to RT
2. execute recursive terms
2.1 WT := RT
2.2 while WT is not empty repeat 2.3 to 2.6. if WT is empty returns RT
2.3 replace the name of recursive term with WT
2.4 evaluate the recursive term and store into WT
2.5 append WT to RT
2.6 go back to 2.2
Pseudo code shown below.
1. RT := none recursive query result
2. for i = 1..N (N = number of partitions)
2.1 WT := RT
2.2 while !empty(WT); do
2.3 subdepartment := WT
2.4 WT := result of recursive term
2.5 RT := WT UNION ALL RT
2.6 done
Execution trace shown below.
WITH RECURSIVE subdepartment AS
(
-- non recursive term
SELECT * FROM department WHERE id = 'A'
UNION ALL
-- recursive term referring to "subdepartment"
SELECT d.* FROM department AS d, subdepartment AS sd
WHERE d.id = sd.parent_department
)
SELECT * FROM subdepartment;
1)
RT = {'A'} WT = {'A'}
2)
SELECT d.* FROM department AS d, WT({'A'}) AS sd
WHERE d.id = sd.parent_id
WT = {'B'}
RT = RT({'A'}) UNION ALL(*) WT({'B'})
=> RT = {'A', 'B'}
3)
SELECT d.* FROM department AS d, WT({'B', 'C'}) AS sd
WHERE d.id = sd.parent_id
=> WT = {'C', 'D'}
RT = RT({'A', 'B'}) UNION ALL WT({'C', 'D'})
=> RT = {'A', 'B', 'C', 'D'}
4)
SELECT d.* FROM department AS d, WT({'B', 'C'}) AS sd
WHERE d.id = sd.parent_id
=> WT = {'F'}
RT = RT({'A', 'B', 'C', 'D'}) UNION ALL WT({'F'})
=> RT = {'A', 'B', 'C', 'D', 'F'}
5)
SELECT d.* FROM departm
