Re: [HACKERS] Database owner installable modules patch
On Mon, Apr 7, 2008 at 3:59 AM, Gregory Stark [EMAIL PROTECTED] wrote: I wonder if there's much of a use case for any statements aside from CREATE statements. If we restrict it to CREATE statements we could hack things to create pg_depend entries automatically. In which case we wouldn't need an uninstall script at all. Well, the example that got me interested in this stuff originally was trying to make pl/java easier to install. It does a bunch of CREATEs... and some GRANTs. Plus ISTM that a pretty common case might be to create a table for some reference data and then fill it with default values. Also, I just had a look at the postgis install script, which at the very least seems to update an opclass entry after creating it. None of that suggests that an uninstaller script would be needed if we understood the deps well enough, but only allowing creates for installs seems a bit restrictive. One thing that's nice about arbitrary sql for install / uninstall is that module authors can test it outside the context of doing an actual module installation - they just execute their scripts. The hacks to do this seem pretty dirty but on the other hand the idea of having modules consist of a bunch of objects rather than arbitrary SQL actually seems cleaner and more robust. It *does* seem cleaner for the examples that I've looked at. Are they totally representative though? Not sure. It also implies a bunch more work to create stuff, as we need to understand what's going on so as to create those pg_depend entries. I'm receptive to the idea of uninstall simply attempting to drop anything related to the module in pg_depend in the correct order. I can't think of anything created by a module that we couldn't represent there, and it's a nice way of ensuring that an uninstall script cleans up properly. Cheers Tom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database owner installable modules patch
On Mon, Apr 7, 2008 at 11:46 AM, Tom Dunstan [EMAIL PROTECTED] wrote: On Mon, Apr 7, 2008 at 3:59 AM, Gregory Stark [EMAIL PROTECTED] wrote: I wonder if there's much of a use case for any statements aside from CREATE statements. If we restrict it to CREATE statements we could hack things to create pg_depend entries automatically. In which case we wouldn't need an uninstall script at all. The hacks to do this seem pretty dirty but on the other hand the idea of having modules consist of a bunch of objects rather than arbitrary SQL actually seems cleaner and more robust. It *does* seem cleaner for the examples that I've looked at. Are they totally representative though? Not sure. It also implies a bunch more work to create stuff, as we need to understand what's going on so as to create those pg_depend entries. This has been bouncing around in my head a bit. I was picturing the module code itself having to understand all the CREATE statements in order to set up the dependencies... but perhaps an easier way would simply be to have the create statements themselves insert a pg_depend entry when they're done, if they detect that we're currently installing a module. There's already a flag for that that the superuser code looks at in the patch. Maybe you were ahead of me, and this was the hack that you were referring to. :) I tend to hate global flags like that because they leave weird non-obvious dependencies across the codebase, but perhaps it's the best way to do it in this case. It would mean hacking every create command in the system to understand it, though. :( Cheers Tom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
Pavan Deolasee wrote:
On Fri, Apr 4, 2008 at 11:10 AM, Tom Lane [EMAIL PROTECTED] wrote:
The
policy of this project is that we only put nontrivial bug fixes into
back branches, and I don't think this item qualifies ...
Got it. I will submit a patch for HEAD.
Thanks,
As I mentioned earlier, I patched 8.3.1 with Pavan's patch and have been
running tests. After a few days I have got postgres to lock up - not
sure if it is related. Below is a ps from my system (NetBSD 3).
TEST ps -ax | grep post
1952 ? IWs 13:52.24 postgres: writer process
2113 ? Ss0:03.04 postgres: logger process
2157 ? Ss0:03.12 postgres: autovacuum launcher process
2199 ? Is0:00.04 postgres: metauser metadb [local] SELECT
2472 ? DWs 814:23.50 postgres: metauser metadb localhost(65524)
COMMIT
2661 ? DWs 0:11.27 postgres: metauser metadb localhost(65525) idle
2680 ? Ss1:18.75 postgres: stats collector process
3156 ? Ss0:45.12 postgres: wal writer process
24362 ? IWs 0:00.00 postgres: autovacuum worker process
25024 ? IWs 0:00.00 postgres: autovacuum worker process
25134 ? IWs 0:00.00 postgres: autovacuum worker process
3289 ttyp5 I 0:01.96 /usr/local/pgsql/bin/postgres -D ../data/metadb
and I was disconnected in my client app with the following message:
[WARN] PSQL:exec - failed in command SELECT
relname,n_tup_ins,n_live_tup,n_dead_tup,pg_total_relation_size('s8_.'
|| relname)*10/(1024*1024),last_autovacuum FROM pg_stat_user_tables
WHERE schemaname='s8_' ORDER BY n_tup_ins DESC
[WARN] error = 'server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.'
[WARN] ConnectionNB: PQconsumeInput failed with error 'server closed the
connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.'
The server is still running but I can't access it. A top yields:
load averages: 0.23, 0.23, 0.2109:53:58
110 processes: 109 sleeping, 1 on processor
Memory: 513M Act, 256M Inact, 1336K Wired, 75M Exec, 557M File, 2776K Free
Swap: 600M Total, 600M Free
PID USERNAME PRI NICE SIZE RES STATE TIME WCPUCPU COMMAND
463 root 20 6132K 14M select 0:06 0.05% 0.05% kdeinit
2472 postgres -22 -2 4580K4K mclpl814:23 0.00% 0.00% postgres
2631 root -220 644K4K mclpl606:25 0.00% 0.00% test_writer
1622 root 20 8456K 14M select19:05 0.00% 0.00% kdeinit
1952 postgres 2 -2 3544K4K netlck13:52 0.00% 0.00% postgres
233 root 2024M 31M select 4:47 0.00% 0.00% XFree86
451 root 20 3544K 15M select 4:45 0.00% 0.00% kdeinit
16 root 180 0K 182M syncer 3:51 0.00% 0.00% [ioflush]
17 root -180 0K 182M aiodoned 1:46 0.00% 0.00% [aiodoned]
15 root -180 0K 182M pgdaemon 1:30 0.00% 0.00% [pagedaemon]
1301 root -220 4092K4K mclpl 1:23 0.00% 0.00% kdeinit
2680 postgres 2 -2 3560K 1588K poll 1:18 0.00% 0.00% postgres
1493 root 20 3488K 17M select 1:09 0.00% 0.00% korgac
461 root 20 3748K 16M select 0:57 0.00% 0.00% kdeinit
3156 postgres 2 -2 3448K 1792K select 0:45 0.00% 0.00% postgres
1174 root 20 2608K 2928K select 0:40 0.00% 0.00% profiler
1428 root 20 3376K 13M select 0:26 0.00% 0.00% kdeinit
2661 postgres -22 -2 4896K4K mclpl 0:11 0.00% 0.00% postgres
I'm not convinced this is a postgresql bug (state=mclpl concerns me),
but it's the first time I've seen it. I suppose it could be:
http://www.netbsd.org/cgi-bin/query-pr-single.pl?number=35224.
Anything I can do which might help isolating the problem?
Regards
Stuart
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Ordered Append Node
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: I've been hacking on the idea of an Append node which maintains the ordering of its subtables merging their records in order. I finally got round to looking at this ... A lot of things to chew on. Thanks very much. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Database owner installable modules patch
Sorry to keep replying to myself, but part of the point of doing a
patch was to force myself (and whoever else is interested to examine
stuff that comes up...
On Mon, Apr 7, 2008 at 11:46 AM, Tom Dunstan [EMAIL PROTECTED] wrote:
None of that suggests that an uninstaller script would be needed if we
understood the deps well enough, but only allowing creates for
installs seems a bit restrictive.
OK, I found an example that does NOT fit the just drop all
dependencies scenario, but that I would still like to support. I just
had a look at the postgis pl/java support, and its install does stuff
like SELECT sqlj.install_jar('file://${PWD}/postgis_pljava.jar',
'postgis_pljava_jar', false); and SELECT
sqlj.add_type_mapping('geometry', 'org.postgis.pljava.PLJGeometry');.
There's no way we can deal with that sort of thing automatically, so
we'll have to support uninstall scripts regardless.
The question then becomes: is it worth trying to do stuff
automatically if we provide a manual method anyway? I think the answer
is probably yes, because having pgsql clean up automatically for the
vast majority of cases is a good thing. If it's only exotic cases that
need a manual uninstall script, why force one on everyone else?
Cheers
Tom
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New style of hash join proposal
Bruce Momjian [EMAIL PROTECTED] writes: If the scanning of the inner side is a performance problem, why would we be choosing a nested loop in the first place, vs. another type of join? I clearly haven't done a good job explaining this as nobody seems to getting what I'm describing. I think I'm better off focusing on the patches I've already started rather than starting yet another project though so perhaps I should put this aside until I can construct a good demonstration. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] CLogControlLock
just started with 8.4 devel. Still focussing on LWlocks. With the same load (#users benchmarktool) I now see LockID 11 (CLogControlLock) to be in the top waiting list. This one was never noticable in 8.3. Did anything change with respect to this? Thanks Paul - Paul van den Bogaard [EMAIL PROTECTED] ISV-E -- ISV Engineering, Opensource Engineering group Sun Microsystems, Inc phone:+31 334 515 918 Saturnus 1 extentsion: x (70)15918 3824 ME Amersfoort mobile: +31 651 913 354 The Netherlands fax:+31 334 515 001 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCHES] [HACKERS] Database owner installable modules patch
Tom Dunstan [EMAIL PROTECTED] writes:
OK, I found an example that does NOT fit the just drop all
dependencies scenario, but that I would still like to support. I just
had a look at the postgis pl/java support, and its install does stuff
like SELECT sqlj.install_jar('file://${PWD}/postgis_pljava.jar',
'postgis_pljava_jar', false); and SELECT
sqlj.add_type_mapping('geometry', 'org.postgis.pljava.PLJGeometry');.
There's no way we can deal with that sort of thing automatically, so
we'll have to support uninstall scripts regardless.
Well, that just begs the question of what those commands actually *do*.
It seems not unlikely that they'd be inserting data into tables that
would belong to the module, in which case an uninstall that dropped
the table would be fine.
I still like the idea of uninstall being just a DROP MODULE with
subsequent cascading. If you want to argue that that isn't sufficient
you really need a pretty convincing example why not.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] problem with locks on head, backend crash
Hello when I tested ptop, I found some problems this pgbench is very slow and when after getting table of locks from ptop I got backend crash. [EMAIL PROTECTED] ~]$ /usr/local/pgsql/bin/pgbench -c80 -t1000 postgres starting vacuum...end. WARNING: you don't own a lock of type ShareLock WARNING: you don't own a lock of type ShareLock WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. from log: LOG: unexpected EOF on client connection LOG: could not send data to client: Přerušena roura (SIGPIPE) LOG: unexpected EOF on client connection LOG: could not send data to client: Přerušena roura (SIGPIPE) LOG: unexpected EOF on client connection WARNING: problem in alloc set MessageContext: req size alloc size for chunk 0x8b6d1e0 in block 0x8b6bb50 WARNING: problem in alloc set MessageContext: req size alloc size for chunk 0x8b6d1e0 in block 0x8b6bb50 LOG: server process (PID 29693) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another s HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCHES] [HACKERS] Database owner installable modules patch
On Mon, Apr 7, 2008 at 7:55 PM, Tom Lane [EMAIL PROTECTED] wrote:
Tom Dunstan [EMAIL PROTECTED] writes:
OK, I found an example that does NOT fit the just drop all
dependencies scenario, but that I would still like to support. I just
had a look at the postgis pl/java support, and its install does stuff
like SELECT sqlj.install_jar('file://${PWD}/postgis_pljava.jar',
'postgis_pljava_jar', false); and SELECT
sqlj.add_type_mapping('geometry', 'org.postgis.pljava.PLJGeometry');.
There's no way we can deal with that sort of thing automatically, so
we'll have to support uninstall scripts regardless.
Well, that just begs the question of what those commands actually *do*.
It seems not unlikely that they'd be inserting data into tables that
would belong to the module, in which case an uninstall that dropped
the table would be fine.
Those tables belong to a *different* module, though. I'm picturing
three modules here: pljava, postgis, and a postgis-pljava support
module that requires the first two, since it should be possible to
install postgis without requiring pljava. The above stuff was from the
install script of the postgis-pljava code, but inserted data into
tables owned by pljava.
Cheers
Tom
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] problem with locks on head, backend crash
Pavel Stehule wrote: when I tested ptop, I found some problems Which version are you running? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] problem with locks on head, backend crash
On 07/04/2008, Heikki Linnakangas [EMAIL PROTECTED] wrote: Pavel Stehule wrote: when I tested ptop, I found some problems Which version are you running? I am sorry, HEAD 8.4 today actualized Pavel -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] problem with locks on head, backend crash
Pavel Stehule escribió: WARNING: problem in alloc set MessageContext: req size alloc size for chunk 0x8b6d1e0 in block 0x8b6bb50 WARNING: problem in alloc set MessageContext: req size alloc size for chunk 0x8b6d1e0 in block 0x8b6bb50 I suggest you make distclean and rebuild the whole thing from scratch -- I have seen these kind of problems when a loadable module is not built the same as the backend, for example. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] problem with locks on head, backend crash
On 07/04/2008, Alvaro Herrera [EMAIL PROTECTED] wrote: Pavel Stehule escribió: WARNING: problem in alloc set MessageContext: req size alloc size for chunk 0x8b6d1e0 in block 0x8b6bb50 WARNING: problem in alloc set MessageContext: req size alloc size for chunk 0x8b6d1e0 in block 0x8b6bb50 I suggest you make distclean and rebuild the whole thing from scratch -- I have seen these kind of problems when a loadable module is not built the same as the backend, for example. I did it, but maybe there was some ??? I can't to repeat it after restart Pavel -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Feature freeze status
We are down to 12 feature freeze items (240 emails): http://momjian.us/cgi-bin/pgpatches Most are not ready to apply but require feedback to the author. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Status of GIT mirror (Was [HACKERS] having problem in rsync'ing cvs)
On Mar 27, 2008, at 10:47 AM, Aidan Van Dyk wrote: I was recently made aware of this: http://repo.or.cz/w/PostgreSQL.git? a=commit;h=69db64c737012a8d2d6fbcce3ace7136cb2bc85f I started digging around to figure this out on Tuesday. It appears as if the rsync mirror of CVS is not always good. It seems like long running CVS operations (like I'm guessing a full tree tag of REL8_3_STABLE) aren't mirrored atomically. Of course, CVS isn't atomic, so we can't really blame it. What appears to have happened is that my rsync caught the rsync mirror when the tree was not all tagged, so when the fromcvs went about making the new branch on the first appearance of REL8_3_STABLE, it had to remove a bunch of files from the branch because they were *not* tagged with that symbol in CVS (or at least, not presently tagged with that symbol in the rsync mirror of CVS)... I would guess that any incremental CVS mirror/conversion is going to hit this at some random time too. Of course, the risk of hitting it goes up as the frequency of your rsync updates go up. Hrm... is there a way to momentarily lock-out access to CVS? What I'm thinking is to have a script that periodically locks CVS access, takes a snapshot of the tree (preferably via a filesystem snapshot), and then unlocks. That snapshot would then be used to drive the mirrors. That would ensure that mirrors always had an atomic view of things. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Feature freeze status
Bruce, We are down to 12 feature freeze items (240 emails): http://momjian.us/cgi-bin/pgpatches Most are not ready to apply but require feedback to the author. Yaaay! Maybe we should make the next commit-fest June 1 to give people some time off? And some time to improve the tools? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] build multiple indexes in single table pass?
On Apr 1, 2008, at 10:03 PM, Greg Smith wrote: The idea we were bouncing around went a step past that and considered this: if you have good statistics on a table, and you have a sample set of queries you want to execute against it, how would you use that information to plan what indexes should be created? Needing to be able to create multiple indexes at once efficiently was an implementation detail to pull that off. Someone at EnterpriseDB (Pavan?) did work on that. I don't know what the status of that effort is. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] New boxes available for QA
On Apr 1, 2008, at 7:20 PM, Stephen Frost wrote: * Greg Smith ([EMAIL PROTECTED]) wrote: =4 cores, =8GB RAM, and =8 disks with a usable write-caching controller in it. hrmmm. So a DL385G2, dual-proc/dual-core with 16GB of ram and 8 SAS disks with a Smart Array P800 w/ 512MB of write cache would be helpful? I've got quite a few such machines, along with larger DL585s. I can't make one externally available immediately but I could set one up to do benchmark runs and to dump the results to a public site. What I don't have atm is alot of time though, of course. Are there scripts and whatnot to get such a set up going quickly? Ditto here; I could possibly find one for running benchmarks for the community. We're also working towards building our own performance lab and running our own benchmarks (that reflect our application workload); once that's up I could run benchmarks against other versions if that would be useful. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
On Apr 3, 2008, at 12:52 AM, Zoltan Boszormenyi wrote: Where is the info in the sequence to provide restarting with the _original_ start value? There isn't any. If you want the sequence to start at some magic value, adjust the minimum value. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Feature freeze status
Josh Berkus wrote: Bruce, We are down to 12 feature freeze items (240 emails): http://momjian.us/cgi-bin/pgpatches Most are not ready to apply but require feedback to the author. Yaaay! Maybe we should make the next commit-fest June 1 to give people some time off? And some time to improve the tools? Agreed. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature freeze status
On Mon, 7 Apr 2008 14:31:51 -0400 (EDT) Bruce Momjian [EMAIL PROTECTED] wrote: Maybe we should make the next commit-fest June 1 to give people some time off? And some time to improve the tools? Agreed. +1 Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature freeze status
Josh Berkus wrote: Maybe we should make the next commit-fest June 1 to give people some time off? And some time to improve the tools? I would rather do the commit fests often, to keep the patch queue and the commit fests short. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY Transform support
On Apr 3, 2008, at 4:51 PM, Andrew Dunstan wrote: Several years ago Bruce and I discussed the then theoretical use of a SELECT query as the source for COPY TO, and we agreed that the sane analog would be to have an INSERT query as the target of COPY FROM. This idea seems to take that rather further. If doable I think it would be cool, as long as people don't try using it as an alternative storage engine. I can just imagine people creating views over such SELECT statements ... Why not? There's certainly cases where doing just that could be very valuable. Storing older information that you're less likely to query comes to mind... in those cases you're going to be seqscanning anyway, so being able to read off a compact on-disk form is likely to be a win performance-wise. It could certainly be a win storage-wise. If someone wants to look at syntax options, I'm pretty certain that Oracle supports this. IIRC you actually create what appears to the database to be a real table, except for restrictions on what you can actually do with it (for example, IIRC it's read-only). -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
[HACKERS] File system snapshots for multiple file systems
Right now it isn't possible to use file system snapshots a reliable backup if you are using multiple file systems for tablespaces because most systems don't allow the simultaneous snapshoting of multiple file system. Our documentation mentions this: If your database is spread across multiple file systems, there might not be any way to obtain exactly-simultaneous frozen snapshots of all the volumes. For example, if your data files and WAL log are on different disks, or if tablespaces are on different file systems, it might not be possible to use snapshot backup because the snapshots must be simultaneous. Read your file system documentation very carefully before trusting to the consistent-snapshot technique in such situations. The safest approach is to shut down the database server for long enough to establish all the frozen snapshots. However, it occurred to me that if someone turned on continuous arciving during the file system snapshots, then you could use PITR to recover from file system snapshots that were not simultaneous. Should this be documented? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \G command -- send query and output using extended format
On Apr 3, 2008, at 9:35 AM, Tom Lane wrote: Dawid Kuroczko [EMAIL PROTECTED] writes: The idea of \G command is to perform the query, but with printing query results using extended table output format. Seems a bit useless --- if you prefer \x format, wouldn't you prefer it all the time? Or at least often enough that the toggling command is fine? I'm dubious that this is worth eating up a command letter for. I agree about not eating a letter, but... I often find myself wanting to do stuff like tuple-only output for a single query in scripts. It would be nice if there was a command (\G ?) that would accept additional options for controlling output. It should be possible to map the options to existing psql output stuff, so that you could force any single command to output in any format that you wanted it to. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] File system snapshots for multiple file systems
Bruce Momjian wrote:
Right now it isn't possible to use file system snapshots a reliable
backup if you are using multiple file systems for tablespaces because
most systems don't allow the simultaneous snapshoting of multiple file
system. Our documentation mentions this:
If your database is spread across multiple file systems, there might not
be any way to obtain exactly-simultaneous frozen snapshots of all the
volumes. For example, if your data files and WAL log are on different
disks, or if tablespaces are on different file systems, it might not be
possible to use snapshot backup because the snapshots must be
simultaneous. Read your file system documentation very carefully before
trusting to the consistent-snapshot technique in such situations. The
safest approach is to shut down the database server for long enough to
establish all the frozen snapshots.
However, it occurred to me that if someone turned on continuous arciving
during the file system snapshots, then you could use PITR to recover
from file system snapshots that were not simultaneous.
Should this be documented?
If you use continuous archiving, the snapshot indeed doesn't need to be
atomic. In fact, you can use tar instead of filesystem snapshots. And in
fact, that's exactly how you take the base backup with PITR, and that is
documented.
Incidentally, I looked at this stuff just a couple of days ago, and it
occurred to me that we really should make it easier to take a hot backup
with that mechanism. We shouldn't require setting up archive_command,
and WAL archiving, if all you want is to take a backup from a live
system. From user point of view, it should be a matter of:
1. call pg_start_backup('foo')
2. tar/etc. the whole data directory, except for pg_xlog
3. tar pg_xlog
4. call pg_stop_backup()
If we just made sure that we don't delete or recycle any WAL files while
the backup is being taken, that would work, right?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] File system snapshots for multiple file systems
On Mon, Apr 7, 2008 at 2:58 PM, Heikki Linnakangas
[EMAIL PROTECTED] wrote:
Incidentally, I looked at this stuff just a couple of days ago, and it
occurred to me that we really should make it easier to take a hot backup
with that mechanism. We shouldn't require setting up archive_command, and
WAL archiving, if all you want is to take a backup from a live system. From
user point of view, it should be a matter of:
1. call pg_start_backup('foo')
2. tar/etc. the whole data directory, except for pg_xlog
3. tar pg_xlog
4. call pg_stop_backup()
If we just made sure that we don't delete or recycle any WAL files while
the backup is being taken, that would work, right?
Or checkpoint, yes? I don't see tar backing up large (100+GB)
databases in 5 minutes.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | [EMAIL PROTECTED]
Edison, NJ 08837 | http://www.enterprisedb.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY Transform support
Decibel! wrote: On Apr 3, 2008, at 4:51 PM, Andrew Dunstan wrote: Several years ago Bruce and I discussed the then theoretical use of a SELECT query as the source for COPY TO, and we agreed that the sane analog would be to have an INSERT query as the target of COPY FROM. This idea seems to take that rather further. If doable I think it would be cool, as long as people don't try using it as an alternative storage engine. I can just imagine people creating views over such SELECT statements ... Why not? There's certainly cases where doing just that could be very valuable. Storing older information that you're less likely to query comes to mind... in those cases you're going to be seqscanning anyway, so being able to read off a compact on-disk form is likely to be a win performance-wise. It could certainly be a win storage-wise. If someone wants to look at syntax options, I'm pretty certain that Oracle supports this. IIRC you actually create what appears to the database to be a real table, except for restrictions on what you can actually do with it (for example, IIRC it's read-only). You're serious aren't you? Quite apart from any other reason why not, this would be a horrid hack and is just the sort of feature we rightly eschew, IMNSHO. COPY is designed as a bulk load/unload facility. It's fragile enough in that role. If we really want to support an alternative storage engine then we should tackle that front on and not via a back door like this. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] File system snapshots for multiple file systems
Heikki Linnakangas wrote:
However, it occurred to me that if someone turned on continuous arciving
during the file system snapshots, then you could use PITR to recover
from file system snapshots that were not simultaneous.
Should this be documented?
If you use continuous archiving, the snapshot indeed doesn't need to be
atomic. In fact, you can use tar instead of filesystem snapshots. And in
fact, that's exactly how you take the base backup with PITR, and that is
documented.
Right. My point is that for people who don't want continuous archiving,
doing it _only_ during the snapshots allows multi-filesystem snapshots
to be reliable.
Incidentally, I looked at this stuff just a couple of days ago, and it
occurred to me that we really should make it easier to take a hot backup
with that mechanism. We shouldn't require setting up archive_command,
and WAL archiving, if all you want is to take a backup from a live
system. From user point of view, it should be a matter of:
1. call pg_start_backup('foo')
2. tar/etc. the whole data directory, except for pg_xlog
3. tar pg_xlog
4. call pg_stop_backup()
If we just made sure that we don't delete or recycle any WAL files while
the backup is being taken, that would work, right?
Yes, agreed. This is exactly the issue I was raising. You are showing
it as tar, but I am showing it as multi-volume snapshots. To do what
you want to do above, you would have to stop doing checkpoints during
the start/stop, which I am a little afraid of with 'tar --- file system
snapshots are much faster. Also, you would have to file system snapshot
the /pg_xlog file system at the end but it is the same logic.
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY Transform support
Le Monday 07 April 2008 21:04:26 Andrew Dunstan, vous avez écrit : Quite apart from any other reason why not, this would be a horrid hack and is just the sort of feature we rightly eschew, IMNSHO. COPY is designed as a bulk load/unload facility. It's fragile enough in that role. And my main concern would still be left as-is, COPY wouldn't have any facility to cope with data representation not matching what datatype input functions want to read. More often than not, you get this kind of data from another system, so in a deterministic way, and you want a chance to transform their representation just before PostgreSQL parses it. May I try to insist on my second syntax proposal form: COPY destination_table(col1, col2, col3) USING (field1, field2 || field3, myfunc(field4, field5)) FROM 'file.txt' WITH ... This allows for the case I'm concerned with to be taken care of, AND the other case pointed out by several posters on this thread too. N input fields, M stored columns, any line to row transformation (after same column splitting as of now), any column pre-parsing through SQL callable functions --- myfunc is called before feeding col3%ROWTYPE input function, e.g. And no support (that I see) for optional second storage system back door. Comments? -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Decibel! írta: On Apr 3, 2008, at 12:52 AM, Zoltan Boszormenyi wrote: Where is the info in the sequence to provide restarting with the _original_ start value? There isn't any. If you want the sequence to start at some magic value, adjust the minimum value. There's the START WITH option for IDENTITY columns and this below is paragraph 8 under General rules of 14.10 truncate table statement in 6WD2_02_Foundation_2007-12.pdf (page 902): 8) If RESTART IDENTITY is specified and the table descriptor of T includes a column descriptor IDCD of an identity column, then: a) Let CN be the column name included in IDCD and let SV be the start value included in IDCD. b) The following alter table statement is effectively executed without further Access Rule checking: ALTER TABLE TN ALTER COLUMN CN RESTART WITH SV This says that the original start value is used, not the minimum value. IDENTITY has the same options as CREATE SEQUENCE. In fact the identity column specification links to 11.63 sequence generator definition when it comes to IDENTITY sequence options. And surprise, surprise, 11.64 alter sequence generator statement now defines ALTER SEQUENCE sn RESTART [WITH newvalue] where omitting the WITH newval part also uses the original start value. Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature freeze status
[EMAIL PROTECTED] (Heikki Linnakangas) writes: Josh Berkus wrote: Maybe we should make the next commit-fest June 1 to give people some time off? And some time to improve the tools? I would rather do the commit fests often, to keep the patch queue and the commit fests short. But if it means that the people working on commit fests never get out of the state of working on a commit fest, this mayn't look good to them :-(. - Giving those that worked hard some time off seems like a nice reward, and - If we saw the tools actually improve between now and June 1, that would seem like a pretty good deal. -- output = (cbbrowne @ linuxdatabases.info) http://cbbrowne.com/info/linuxdistributions.html We all live in a yellow subroutine, a yellow subroutine, a yellow subroutine... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature freeze status
Chris Browne wrote: [EMAIL PROTECTED] (Heikki Linnakangas) writes: Josh Berkus wrote: Maybe we should make the next commit-fest June 1 to give people some time off? And some time to improve the tools? I would rather do the commit fests often, to keep the patch queue and the commit fests short. But if it means that the people working on commit fests never get out of the state of working on a commit fest, this mayn't look good to them :-(. - Giving those that worked hard some time off seems like a nice reward, and - If we saw the tools actually improve between now and June 1, that would seem like a pretty good deal. And, as was rightly pointed out to me a couple of days ago, there is nothing that says you have to wait for a commitfest to start reviewing/committing. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] Database owner installable modules patch
On Sun, Apr 06, 2008 at 11:29:50PM +0100, Gregory Stark wrote: I wonder if there's much of a use case for any statements aside from CREATE statements. Yes. Some modules could have COPY or equivalent in them, as they could easily contain data. 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New boxes available for QA
FYI, we (Stefan and I) started a wiki page to organize this effort: http://wiki.postgresql.org/wiki/Performances_QA_testing . Ideas and participation are very welcome. I also described the platform we have here and the usage of each server: http://wiki.postgresql.org/wiki/QA_Platform_hosted_at_Open_Wide_%28France%29 . I started working on it this week-end. I'll update this page as servers are booked/used and when we add more boxes. -- Guillaume -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] Integer datetime by default
Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: Applied to HEAD. At this point it would probably be a good idea if a couple of buildfarm machines were to start testing builds with --disable-integer-datetimes ... any volunteers out there? I have changed the dashboard page to reflect the new default. I believe one or two people are going to change their configs. I'm not sure what we do about this in MSVC config - I will explore. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature freeze status
Heikki Linnakangas [EMAIL PROTECTED] writes: Josh Berkus wrote: Maybe we should make the next commit-fest June 1 to give people some time off? And some time to improve the tools? I would rather do the commit fests often, to keep the patch queue and the commit fests short. Just throwing out a crazy idea. What if we had a commitfest as scheduled at the start of May but made it a Tom-free commitfest. Specifically to try to organize a larger work-force rather than to leave it all on Tom's shoulders. Not that your efforts aren't appreciated but surely you wouldn't mind a break? I'm thinking we should have a column in the commitfest info reviewer and specifically assign all the patches to someone, preferably distributed over as wide a list as possible. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature freeze status
Bruce Momjian [EMAIL PROTECTED] writes: Josh Berkus wrote: Maybe we should make the next commit-fest June 1 to give people some time off? And some time to improve the tools? Agreed. I don't agree, not even a little bit. The reason this fest has been so long and painful is that the queue had accumulated so much stuff. If we slip the fest schedule we just start building up a large queue again. Better tools would be good, but unless someone commits to producing a tool that will be ready by June but not by May, that's not a good reason to slide either. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature freeze status
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Josh Berkus wrote: Maybe we should make the next commit-fest June 1 to give people some time off? And some time to improve the tools? Agreed. I don't agree, not even a little bit. The reason this fest has been so long and painful is that the queue had accumulated so much stuff. If we slip the fest schedule we just start building up a large queue again. Better tools would be good, but unless someone commits to producing a tool that will be ready by June but not by May, that's not a good reason to slide either. Fine with me --- I just wanted to give Tom a break. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature freeze status
Gregory Stark [EMAIL PROTECTED] writes: Just throwing out a crazy idea. What if we had a commitfest as scheduled at the start of May but made it a Tom-free commitfest. Specifically to try to organize a larger work-force rather than to leave it all on Tom's shoulders. Not that your efforts aren't appreciated but surely you wouldn't mind a break? It certainly did seem that Bruce and I were the only ones doing any very serious amount of work for this fest. That's not sustainable, folks. BTW, I hate to keep repeating myself, but *this fest still isn't over*, and what's left is achieving consensus about some future development directions --- in particular, most of the remaining items are about what we want to change in the indexam API and what is our roadmap for free space map/dead space map/visibility map/etc. There are plenty of people on this list who could be contributing useful comments about those issues. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY Transform support
Dimitri Fontaine [EMAIL PROTECTED] writes: And my main concern would still be left as-is, COPY wouldn't have any facility to cope with data representation not matching what datatype input functions want to read. That's sufficiently covered by the proposal to allow a COPY FROM as a table source within SELECT, no? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CLogControlLock
Paul van den Bogaard [EMAIL PROTECTED] writes: just started with 8.4 devel. Still focussing on LWlocks. With the same load (#users benchmarktool) I now see LockID 11 (CLogControlLock) to be in the top waiting list. This one was never noticable in 8.3. Did anything change with respect to this? Uh, no, 8.4 devel has hardly started and I can't think of any changes we've applied that would be likely to change the load on CLogControlLock. What is your test case? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Implement a few changes to how shared libraries and dynamically
[EMAIL PROTECTED] (Peter Eisentraut) writes: Implement a few changes to how shared libraries and dynamically loadable modules are built. Seems this patch has broken all the Windows buildfarm animals ... is anybody on that? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature freeze status
On Mon, 7 Apr 2008 22:05:09 -0400 (EDT) Bruce Momjian [EMAIL PROTECTED] wrote: Better tools would be good, but unless someone commits to producing a tool that will be ready by June but not by May, that's not a good reason to slide either. Fine with me --- I just wanted to give Tom a break. We could just ban him from reviewing for a fest :P. Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Implement a few changes to how shared libraries and dynamically
Tom Lane wrote: [EMAIL PROTECTED] (Peter Eisentraut) writes: Implement a few changes to how shared libraries and dynamically loadable modules are built. Seems this patch has broken all the Windows buildfarm animals ... is anybody on that? Not all, only those that use the Makefile build system, i.e. MSVC is not failing, but MinGW and Cygwin are. Specifically, we are failing thus during initdb: creating conversions ... FATAL: could not access file $libdir/ascii_and_mic: No such file or directory I can't immediately see the cause, although presumably it's the changes in Makefile.shlib that have done it. Maybe we need to set up a Windows machine or two somewhere so that committers can test out stuff like this ... cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
