Re: [HACKERS] Synch Replication
Hi Niranjan, Thanks very much! On Mon, Feb 9, 2009 at 3:08 PM, K, Niranjan (NSN - IN/Bangalore) niranja...@nsn.com wrote: Now, the active and standby database are up running even after the execution of the SQL (create table). What was the problem? The problem is that 1-byte variable was assigned the value casted to 4-bytes, which overwrote another variable (which lives next to the 1-byte val) wrongly. This behavior varies based on environment (ex. memory alignment). So, the trouble wasn't reproduced on my machine though it occurred on yours. It's my disgraceful bug.. :( But when I logged in the standby instance by executing 'psql -d replication', I did not see the table that was created on the primary. I have few questions: - I'am not sure whether the replication is done but I'am not able to view? Will I be able to view the replication by logging inside to standby instance? Hotstandby patch will allow to read from standby. Is this patch integrated in sync replication patch? No, hot standby and synch rep are independent patch now. So, you cannot issue any queries to the standby server during replication. The progress of replication can be checked via 'ps' command as follows. This reports the LSN already the standby server has received and written (or fsynced). [primary] $ pgrep -fl wal 1803 postgres: wal writer process 1830 postgres: wal sender process postgres 127.0.0.1(34604) replicated to: write 0/1F74DD0, flush 0/1F68878 [standby] $ pgrep -fl wal 1828 postgres: wal receiver process replicated to: write 0/1F74DD0, flush 0/1F68878 - I brought down the active instance by executing 'pg_ctl -D /home/postgres/postgresHSB/actdata stop' hoping that trigger file will enable failover. But I was not able to login to standby instance. Not sure why? Please let me know the failover procedure which you carried out. As follows? 1) pg_ctl -D /home/postgres/postgresHSB/actdata stop 2) touch /home/postgres/postgresHSB/finish.trigger Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 64 bit PostgreSQL 8.3.6 build on AIX 5300-09-02-0849 with IBM XL C/C++ 10.1.0.1 - initdb fails (could not dump unrecognized node type: 650)
OK, I've managed to compile a 32 bit PostgreSQL successfully and it runs. I'm not yet sure if the issues are due to using the C++ compiler to compile C code, optimization flags or 64 bit builts, but I'll rebuilt it a few times to pinpoint the real issue. Either way, I think it was my mistake to use xlC_r instead of xlc_r. Thanks a lot for the support. If I get these issues sorted out, I'll see if I can add the machine to the buildfarm. I've recompiled it with clean environment variables (no CC defined, etc), using xlc (/usr/vac/bin/xlc_r), the C compiler instead of using xlC_r (the C++ compiler). No -q64 (64 bit build) and using: CFLAGS='-qnooptimize' ./configure --enable-cassert --enable-debug --with-includes=/opt/freeware/include --with-libraries=/opt/freeware/lib --enable-thread-safety I see gmake using: xlc -qnooptimize -qnoansialias -g -I../../../src/include -I/opt/freeware/include -c -o pg_enum.o pg_enum.c Now initdb runs fine. phobos$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale en_US. The default database encoding has accordingly been set to LATIN1. The default text search configuration will be set to english. fixing permissions on existing directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 32MB/204800 creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok WARNING: enabling trust authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using: /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data or /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start On Mon, Feb 9, 2009 at 9:40 AM, Tom Lane t...@sss.pgh.pa.us wrote: Mihai Criveti cmi...@boreas.ro writes: So if this is a compiler bug, it certainly isn't an obvious one. I'll dig deeper to see how I can convince configure to use -qnooptimize. Set CFLAGS in its environment. The default is set in src/template/aix CFLAGS=-O2 -qmaxmem=16384 -qsrcmsg -qlonglong When I've used the build farm scripts, configure gives xlC_r these flags: configure:7117: xlC_r -q64 -o conftest -O2 -qmaxmem=16384 -qsrcmsg -qlonglong -g -I/opt/freeware/include/libxml2 -L/opt/freeware/lib conftest.c -lm 5 Dunno where the -q64 came from ... 1506-396 (W) Option -qlonglong is incompatible with option -qlanglvl=extc99 and is ignored. Seems like these switches might need a revisit for latest AIX. regards, tom lane -- Criveti Mihai http://unixsadm.blogspot.com
Re: [HACKERS] 64 bit PostgreSQL 8.3.6 build on AIX 5300-09-02-0849 with IBM XL C/C++ 10.1.0.1 - initdb fails (could not dump unrecognized node type: 650)
So if this is a compiler bug, it certainly isn't an obvious one. I'll dig deeper to see how I can convince configure to use -qnooptimize. Set CFLAGS in its environment. The default is set in src/template/aix CFLAGS=-O2 -qmaxmem=16384 -qsrcmsg -qlonglong When I've used the build farm scripts, configure gives xlC_r these flags: configure:7117: xlC_r -q64 -o conftest -O2 -qmaxmem=16384 -qsrcmsg -qlonglong -g -I/opt/freeware/include/libxml2 -L/opt/freeware/lib conftest.c -lm 5 Um, why are you using the C++ frontend ? Have you tried xlc_r or cc_r instead ? Using xlC_r or xlc_r, implicitly sets -qansialias. IIRC we had some issues with aliasing on other platforms too ? Would that switch be wrong ? Doc sais: Use type-based aliasing during optimization. Dunno where the -q64 came from ... Probably together with the choice of compiler command ? Andreas -- 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] 64 bit PostgreSQL 8.3.6 build on AIX 5300-09-02-0849 with IBM XL C/C++ 10.1.0.1 - initdb fails (could not dump unrecognized node type: 650)
Yes, I've had CC exported as xlC_r -q64 to do 64 bit builds, and use vacpp C++ instead of C. Guess it didn't like that, and ended up with some horrible compiler optimization or something that killed it. Are there any other tests I can run now that PostgreSQL is installed? Seems to work fine for basic stuff anyway. phobos% /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start server starting phobos% /usr/local/pgsql/bin/psql -U postgres Welcome to psql 8.3.6, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# CREATE ROLE testuser LOGIN PASSWORD 'test123'; CREATE ROLE postgres=# CREATE DATABASE testdb; CREATE DATABASE postgres=# CREATE TABLE test (ID serial PRIMARY KEY, name varchar(25) NOT NULL UNIQUE); NOTICE: CREATE TABLE will create implicit sequence test_id_seq for serial column test.id NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index test_pkey for table test NOTICE: CREATE TABLE / UNIQUE will create implicit index test_name_key for table test CREATE TABLE postgres=# INSERT INTO test VALUES (default, 'quux'); INSERT 0 1 postgres=# SELECT * FROM test; id | name +-- 1 | quux (1 row) \q On Mon, Feb 9, 2009 at 11:40 AM, Zeugswetter Andreas OSB sIT andreas.zeugswet...@s-itsolutions.at wrote: So if this is a compiler bug, it certainly isn't an obvious one. I'll dig deeper to see how I can convince configure to use -qnooptimize. Set CFLAGS in its environment. The default is set in src/template/aix CFLAGS=-O2 -qmaxmem=16384 -qsrcmsg -qlonglong When I've used the build farm scripts, configure gives xlC_r these flags: configure:7117: xlC_r -q64 -o conftest -O2 -qmaxmem=16384 -qsrcmsg -qlonglong -g -I/opt/freeware/include/libxml2 -L/opt/freeware/lib conftest.c -lm 5 Um, why are you using the C++ frontend ? Have you tried xlc_r or cc_r instead ? Using xlC_r or xlc_r, implicitly sets -qansialias. IIRC we had some issues with aliasing on other platforms too ? Would that switch be wrong ? Doc sais: Use type-based aliasing during optimization. Dunno where the -q64 came from ... Probably together with the choice of compiler command ? Andreas -- Criveti Mihai http://unixsadm.blogspot.com
Re: [HACKERS] Synch Replication
Hi Niranjan, On Mon, Feb 9, 2009 at 6:58 PM, K, Niranjan (NSN - IN/Bangalore) niranja...@nsn.com wrote: 1) pg_ctl -D /home/postgres/postgresHSB/actdata stop 2) touch /home/postgres/postgresHSB/finish.trigger Yes. This the procedure that I followed. I have attached the relevant logs. change_standby_mode.log - Commands used to change from continous recovery mode of the standby instance ps.log - ps command before and after executing the SQL. Thanks for the informations! --- [postg...@node1 ~]$ psql -d replication psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? --- I think that your standby postmaster is running under port = 5433, so please specify -p 5433. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] RE: [HACKERS] 64 bit PostgreSQL 8.3.6 build on AIX 5300-09-02-0849 with IBM XL C/C++ 10.1.0.1 - initdb fails (could not dump unrecognized node type: 650)
Yes, I've had CC exported as xlC_r -q64 to do 64 bit builds, and use vacpp C++ instead of C. Guess it didn't like that, and ended up with some horrible compiler optimization or something that killed it. Have you determined whether the problem is optimization or 64bit ? Are there any other tests I can run now that PostgreSQL is installed? Well, the next thing would be running the regression tests. Since the -qnooptimize build is not optimal, an interesting build would probably be with: CC=xlc_r -q64 -qnoansialias Andreas -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On 2/9/09, Andrew Dunstan and...@dunslane.net wrote: David Fetter wrote: On Sun, Feb 08, 2009 at 11:51:22AM -0500, Tom Lane wrote: Now, if you want to argue that we should get rid of SET WITHOUT OIDS altogether, +1 for removing it altogether. Row OIDs are and ugly wart :P That might be true but I know of apps that use them. Having the ability to migrate those slowly by using SET WITHOUT OIDS is a Good Thing (tm). +1 for removal. Also, whether the removal happens or not, I would suggest a setting that makes Postgres accept, but ignore default_with_oids / WITH OIDS settings. The problem is how to migrate apps that definitely do not use oids, in a situation where you have hundred of databases. Scanning all dbs and doing ALTER table would be option, if it would work 100% and would not touch data. Otherwise is cannot be used. Trying to manually manipulate dump files which are filled with SET default_with_oids each time database is dumped/reloaded is also not an option. Currently the only sane path seems to patch Postgres to ignore the settings, but that does not seem very user-friendly approach... -- marko -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On Mon, Feb 09, 2009 at 02:47:21PM +0200, Marko Kreen wrote: That might be true but I know of apps that use them. Having the ability to migrate those slowly by using SET WITHOUT OIDS is a Good Thing (tm). +1 for removal. Also, whether the removal happens or not, I would suggest a setting that makes Postgres accept, but ignore default_with_oids / WITH OIDS settings. Err, you mean a setting that makes Postgres throw an error on the use of WITH OIDS. Just silently ignoring the option is a fantastic way to break applications silently. Making pg_dump not output the WITH OIDS option on tables may be an easier option. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Martijn van Oosterhout wrote: Making pg_dump not output the WITH OIDS option on tables may be an easier option. Or just run ALTER TABLE WITHOUT OIDS for all the tables before dumping. -- 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] UUIDs using e2fs library on Linux in 8.4
On 7 feb, 23:44, da...@fetter.org (David Fetter) wrote: On Sat, Feb 07, 2009 at 10:44:48PM -0500, David Lee Lambert wrote: In the same spirit as the FreeBSD-native UUID generator that was discussed here a couple months ago, I was able to link Postgres 8.4 against the UUID generator embedded in the Linux ext2fs toolchain. There's a license conflict issue with that patch, namely that you've used the GPL, which means we can't ship it. Is that license from something else, or would you consider re-licensing it under BSDL-compatible terms? I originally based the code on something else (the pguuid project on GBorg; the original author's Hotmail address seems dead). However, I just checked, and no line of code what I posted matches his code, except some #include lines and function declarations. I am willing to apply the BSD license to it if I actually can claim the copyright. -- DLL -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On 2/9/09, Martijn van Oosterhout klep...@svana.org wrote: On Mon, Feb 09, 2009 at 02:47:21PM +0200, Marko Kreen wrote: That might be true but I know of apps that use them. Having the ability to migrate those slowly by using SET WITHOUT OIDS is a Good Thing (tm). +1 for removal. Also, whether the removal happens or not, I would suggest a setting that makes Postgres accept, but ignore default_with_oids / WITH OIDS settings. Err, you mean a setting that makes Postgres throw an error on the use of WITH OIDS. Just silently ignoring the option is a fantastic way to break applications silently. For me, ignoring is easier... But yeah, error would be better, if it does not affect reloading the dump. Making pg_dump not output the WITH OIDS option on tables may be an easier option. I don't like it - it would require more work from users, but does not seem to be any way safer. You usually do the check if db works on restore time, not dump time... From clarity standpoint, options that turns both default_with_oids and WITH OIDS to errors seems the best. -- marko -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On 2/9/09, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Martijn van Oosterhout wrote: Making pg_dump not output the WITH OIDS option on tables may be an easier option. Or just run ALTER TABLE WITHOUT OIDS for all the tables before dumping. This does not work on dbs that are actually in use... -- marko -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On 2/9/09, Martijn van Oosterhout klep...@svana.org wrote: Making pg_dump not output the WITH OIDS option on tables may be an easier option. OTOH, the pg_dump already has option --oids. If the option is not given, is there any point putting WITH OIDS / default_with_oids into dump? -- marko -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On Mon, Feb 09, 2009 at 03:19:55PM +0200, Marko Kreen wrote: Making pg_dump not output the WITH OIDS option on tables may be an easier option. I don't like it - it would require more work from users, but does not seem to be any way safer. You usually do the check if db works on restore time, not dump time... Another idea, have WITH OIDS just append a column to the table called OID with SERIAL type. People see them, go whoops and drop them. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] 64 bit PostgreSQL 8.3.6 build on AIX 5300-09-02-0849 with IBM XL C/C++ 10.1.0.1 - initdb fails (could not dump unrecognized node type: 650)
OK, I've compiled a 64 bit optimized version, and it works great! No issues what so ever in configure, make or install. Thanks a lot for all the support :-). PostgreSQL rocks! What I've used to build it: CC=xlc_r -q64 -qnoansialias AR=ar -X64 OBJECT_MODE=64 ./configure --enable-cassert --enable-debug --with-includes=/opt/freeware/include --with-libraries=/opt/freeware/lib --enable-thread-safety gmake sudo gmake install /usr/local/pgsql/bin/postgres: 64-bit XCOFF executable or object module not stripped /usr/local/pgsql/bin/initdb: 64-bit XCOFF executable or object module not stripped (and so on) /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale en_US. The default database encoding has accordingly been set to LATIN1. The default text search configuration will be set to english. fixing permissions on existing directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 32MB/204800 creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok WARNING: enabling trust authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using: /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data or /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start % /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start server starting On Mon, Feb 9, 2009 at 12:32 PM, Zeugswetter Andreas OSB sIT andreas.zeugswet...@s-itsolutions.at wrote: Yes, I've had CC exported as xlC_r -q64 to do 64 bit builds, and use vacpp C++ instead of C. Guess it didn't like that, and ended up with some horrible compiler optimization or something that killed it. Have you determined whether the problem is optimization or 64bit ? Are there any other tests I can run now that PostgreSQL is installed? Well, the next thing would be running the regression tests. Since the -qnooptimize build is not optimal, an interesting build would probably be with: CC=xlc_r -q64 -qnoansialias Andreas -- Criveti Mihai http://unixsadm.blogspot.com
Re: [HACKERS] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On Feb 9, 2009, at 7:47 AM, Marko Kreen mark...@gmail.com wrote: On 2/9/09, Andrew Dunstan and...@dunslane.net wrote: David Fetter wrote: On Sun, Feb 08, 2009 at 11:51:22AM -0500, Tom Lane wrote: Now, if you want to argue that we should get rid of SET WITHOUT OIDS altogether, +1 for removing it altogether. Row OIDs are and ugly wart :P That might be true but I know of apps that use them. Having the ability to migrate those slowly by using SET WITHOUT OIDS is a Good Thing (tm). +1 for removal. Why? What benefit do we get out of denying users this option? Also, whether the removal happens or not, I would suggest a setting that makes Postgres accept, but ignore default_with_oids / WITH OIDS settings. The problem is how to migrate apps that definitely do not use oids, in a situation where you have hundred of databases. Scanning all dbs and doing ALTER table would be option, if it would work 100% and would not touch data. Otherwise is cannot be used. That might be true in your environment, but is certainly not true in general. We have many DDL commands that require full-table rewrites, and they are FAR from useless. Trying to manually manipulate dump files which are filled with SET default_with_oids each time database is dumped/reloaded is also not an option. Currently the only sane path seems to patch Postgres to ignore the settings, but that does not seem very ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synch Replication
Hi Niranjan, On Mon, Feb 9, 2009 at 10:39 PM, K, Niranjan (NSN - IN/Bangalore) niranja...@nsn.com wrote: But after I login to replication database (note the active I had brought it down earlier created a finish.trigger), I still cannot see the table that was created on the primary. Also please note that the LSN had changed after replication in the ps command. Did you create the table in 'replication' database? If not, please connect to the correct database which includes the table. In log-shipping, the database objects are basically identical between the primary and the standby server. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Table Partitioning Feature
Hi Emmanuel,
We are considering to following approach:
1. metadata table pg_partitions is defined as follows:
CATALOG(pg_partitions,2336) BKI_WITHOUT_OIDS
{
Oid partrelid; // partition table Oid
Oid parentrelid; // Parent table Oid
int4parttype; // Type of partition, list, hash, range
Oidpartkey;// partition key Oid
Oidkeytype; /// type of partition key.
int4keyorder /// order of the key in multi-key partitions.
textmin;
textmax; // min and max for range parti
text[] list;
inthash; // hash value
} FormData_pg_partitions;
2. C triggers will fire a query on this table to get the relevant
partition of the inserted/updated data using SPI interface. The query
will look something like (for range partitioning)
select min(partrelid)
from pg_partitions
where parentrelid = 2934 // we know this value
and (
( $1 between to_int(min ) and to_int(max) and
keyorder = 1) OR
($2 between to_date (min) and to_date (max) and
keyorder =2 )
)
group by
parentrelid
having
count(*) = number of partition keys
$1, $2, ... are the placeholders of the actual partition key values of
trigger tuple.
Since we know the type of partition keys, and the parentrelid, this
kind of query string can be saved in another table say, pg_part_map.
And its plan can be parsed once and saved in cache to be reused.
Do you see any issue with using SPI interface within triggers?
The advantage of this kind of approah is that trigger code can be made
genric for any kind of partition table.
Thanks,
Amit
Persistent Systems,
www.persistentsys.com
On 1/23/09, Emmanuel Cecchet m...@frogthinker.org wrote:
Amit,
You might want to put this on the
http://wiki.postgresql.org/wiki/Table_partitioning wiki
page.
How does your timeline look like for this implementation?
I would be happy to contribute C triggers to your implementation. From what
I understood in
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00269.php,
you already have an implementation that parses the grammar and generates
rules as if someone had written them. Is this code available?
Regarding the use of triggers to push/move data to partitions, what if
someone declares triggers on partitions? Especially if you have
subpartitions, let's consider the case where there is a trigger on the
parent, child and grandchild. If I do an insert in the parent, the user
trigger on the parent will be executed, then the partition trigger that
decides to move to the grandchild. Are we going to bypass the child trigger?
If we also want fast COPY operations on partitioned table, we could have an
optimized implementation that could bypass triggers and move the tuple
directly to the appropriate child table.
Thanks for this big contribution,
Emmanuel
Hi,
We are implementing table partitioning feature to support
- the attached commands. The syntax conforms to most of the suggestion
mentioned in
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php,
barring the following:
-- Specification of partition names is optional. System will be able to
generate partition names in such cases.
-- sub partitioning
We are using pgsql triggers to push/move data to appropriate partitions,
but we will definitely consider moving to C language triggers as suggested
by manu.
- Global non-partitioned indexes (that will extend all the partitions).
- Foreign key support for tables referring to partitioned tables.
Please feel free to post your comments and suggestions.
Thanks,
Amit
Persistent Systems
--
Emmanuel Cecchet
FTO @ Frog Thinker Open Source Development Consulting
--
Web: http://www.frogthinker.org
email: m...@frogthinker.org
Skype: emmanuel_cecchet
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] I can see beta now
A month ago I warned that we had an unusually high number of open items: http://archives.postgresql.org/pgsql-hackers/2009-01/msg00321.php Fortunately, thanks to a lot of hard work, my list of open items has shrunk from +200 to 44: http://momjian.us/cgi-bin/pgsql/open Now, that list is _imperfect_ and reflects items that are also on the commit fest page. I have emailed individually asking about most of the items but if you have feedback on any of them, please let me know. I also have personal email collections for Magnus, Marc, and Joe Conway. There are now seven unapplied patches on the commit-fest page: http://wiki.postgresql.org/wiki/CommitFest_2008-11 not counting the SE-PostgreSQL and Recovery, Replication, Hot Standby patches. I can now see us closing the existing items in the next several weeks and preparing for 8.4 beta. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On 2/9/09, Robert Haas robertmh...@gmail.com wrote: On Feb 9, 2009, at 7:47 AM, Marko Kreen mark...@gmail.com wrote: On 2/9/09, Andrew Dunstan and...@dunslane.net wrote: David Fetter wrote: On Sun, Feb 08, 2009 at 11:51:22AM -0500, Tom Lane wrote: Now, if you want to argue that we should get rid of SET WITHOUT OIDS altogether, +1 for removing it altogether. Row OIDs are and ugly wart :P That might be true but I know of apps that use them. Having the ability to migrate those slowly by using SET WITHOUT OIDS is a Good Thing (tm). +1 for removal. Why? What benefit do we get out of denying users this option? Why should we continue to support historical special case? It is not a feature that adds anything to user experience with Postgres. Anyway, that was my vote only. If there are developers interested in supporting oids feel free to do so. But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs or requires table rewrite, it turned from minor annoyance to big annoyance. So I'd like have a reasonable path for getting rid of them, which we don't have currently. Removing them completely is simplest path, but adding extra features to support it is another. If we are talking about adding a feature, then I like retargeting pg_dump --oids from data-only flag to apply to both data and schema. Yes, this is incompatible change, but the change affects feature we are discouraging anyway. If this does not work, then we need another postgresql.conf option. Also, whether the removal happens or not, I would suggest a setting that makes Postgres accept, but ignore default_with_oids / WITH OIDS settings. The problem is how to migrate apps that definitely do not use oids, in a situation where you have hundred of databases. Scanning all dbs and doing ALTER table would be option, if it would work 100% and would not touch data. Otherwise is cannot be used. That might be true in your environment, but is certainly not true in general. We have many DDL commands that require full-table rewrites, and they are FAR from useless. Compared to not having the DDL commands or having DDL commands that do not rewrite the tables? ;) -- marko -- 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] 64 bit PostgreSQL 8.3.6 build on AIX 5300-09-02-0849 with IBM XL C/C++ 10.1.0.1 - initdb fails (could not dump unrecognized node type: 650)
All regression tests work. Everything seems to be in order :-) Followup with regression tests (rand the installchecks as postgres user): $ gmake check [..] === All 114 tests passed. === [after setting up the database and all:] $ gmake installcheck ... test xml ... ok test stats... ok test tablespace ... ok === All 114 tests passed. === $ gmake installcheck-parallel === All 114 tests passed. === gmake[2]: Leaving directory `/home/cmihai/build/postgresql-8.3.6/src/test/regress' gmake[1]: Leaving directory `/home/cmihai/build/postgresql-8.3.6/src/test' On Mon, Feb 9, 2009 at 4:06 PM, Mihai Criveti cmi...@boreas.ro wrote: OK, I've compiled a 64 bit optimized version, and it works great! No issues what so ever in configure, make or install. Thanks a lot for all the support :-). PostgreSQL rocks! What I've used to build it: CC=xlc_r -q64 -qnoansialias AR=ar -X64 OBJECT_MODE=64 ./configure --enable-cassert --enable-debug --with-includes=/opt/freeware/include --with-libraries=/opt/freeware/lib --enable-thread-safety gmake sudo gmake install /usr/local/pgsql/bin/postgres: 64-bit XCOFF executable or object module not stripped /usr/local/pgsql/bin/initdb: 64-bit XCOFF executable or object module not stripped (and so on) /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale en_US. The default database encoding has accordingly been set to LATIN1. The default text search configuration will be set to english. fixing permissions on existing directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 32MB/204800 creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok WARNING: enabling trust authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using: /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data or /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start % /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start server starting On Mon, Feb 9, 2009 at 12:32 PM, Zeugswetter Andreas OSB sIT andreas.zeugswet...@s-itsolutions.at wrote: Yes, I've had CC exported as xlC_r -q64 to do 64 bit builds, and use vacpp C++ instead of C. Guess it didn't like that, and ended up with some horrible compiler optimization or something that killed it. Have you determined whether the problem is optimization or 64bit ? Are there any other tests I can run now that PostgreSQL is installed? Well, the next thing would be running the regression tests. Since the -qnooptimize build is not optimal, an interesting build would probably be with: CC=xlc_r -q64 -qnoansialias Andreas -- Criveti Mihai http://unixsadm.blogspot.com -- Criveti Mihai http://unixsadm.blogspot.com
Re: [HACKERS] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Marko Kreen wrote: But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs or requires table rewrite, it turned from minor annoyance to big annoyance. So I'd like have a reasonable path for getting rid of them, which we don't have currently. Removing them completely is simplest path, but adding extra features to support it is another. If we are talking about adding a feature, then I like retargeting pg_dump --oids from data-only flag to apply to both data and schema. Yes, this is incompatible change, but the change affects feature we are discouraging anyway. How about a pg_dump flag that simply suppresses OIDs from the data and schema? 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On 2/9/09, Andrew Dunstan and...@dunslane.net wrote: Marko Kreen wrote: But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs or requires table rewrite, it turned from minor annoyance to big annoyance. So I'd like have a reasonable path for getting rid of them, which we don't have currently. Removing them completely is simplest path, but adding extra features to support it is another. If we are talking about adding a feature, then I like retargeting pg_dump --oids from data-only flag to apply to both data and schema. Yes, this is incompatible change, but the change affects feature we are discouraging anyway. How about a pg_dump flag that simply suppresses OIDs from the data and schema? But we already have flag that is correlated to use of oids? I don't see why we should bother users who are not using oids with it. -- marko -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On Mon, Feb 09, 2009 at 10:44:17AM -0500, Andrew Dunstan wrote: Marko Kreen wrote: But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs or requires table rewrite, it turned from minor annoyance to big annoyance. So I'd like have a reasonable path for getting rid of them, which we don't have currently. Removing them completely is simplest path, but adding extra features to support it is another. If we are talking about adding a feature, then I like retargeting pg_dump --oids from data-only flag to apply to both data and schema. Yes, this is incompatible change, but the change affects feature we are discouraging anyway. How about a pg_dump flag that simply suppresses OIDs from the data and schema? Defaults matter. How about one that *preserves* the aforementioned OIDs and have the default, if it finds OIDs, error out with a message like this: You have explicit OIDs in this database, which have been deprecated since 8.1. If despite this, you would like to preserve them, use the --oids option for pg_dump. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Andrew Dunstan wrote: How about a pg_dump flag that simply suppresses OIDs from the data and schema? pg_dump -s postgres | sed -e 's/SET default_with_oids = true;/-- /' -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On 2/9/09, David Fetter da...@fetter.org wrote: On Mon, Feb 09, 2009 at 10:44:17AM -0500, Andrew Dunstan wrote: Marko Kreen wrote: But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs or requires table rewrite, it turned from minor annoyance to big annoyance. So I'd like have a reasonable path for getting rid of them, which we don't have currently. Removing them completely is simplest path, but adding extra features to support it is another. If we are talking about adding a feature, then I like retargeting pg_dump --oids from data-only flag to apply to both data and schema. Yes, this is incompatible change, but the change affects feature we are discouraging anyway. How about a pg_dump flag that simply suppresses OIDs from the data and schema? Defaults matter. How about one that *preserves* the aforementioned OIDs and have the default, if it finds OIDs, error out with a message like this: You have explicit OIDs in this database, which have been deprecated since 8.1. If despite this, you would like to preserve them, use the --oids option for pg_dump. +1 for the warning. If --oids is not given, do the check. I would argue that the check should also see if there is index on the oid field, if not it's unusable anyway. So mosts users who have oid columns because of migration from older version, won't be bothered. Or can the oid column be usable without index? -- marko -- 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] 64 bit PostgreSQL 8.3.6 build on AIX 5300-09-02-0849 with IBM XL C/C++ 10.1.0.1 - initdb fails (could not dump unrecognized node type: 650)
Mihai Criveti cmi...@boreas.ro writes:
OK, I've compiled a 64 bit optimized version, and it works great! No issues
what so ever in configure, make or install. Thanks a lot for all the support
:-).
PostgreSQL rocks!
What I've used to build it:
CC=xlc_r -q64 -qnoansialias
Hmm. I think -qnoansialias corresponds to gcc's -fno-strict-aliasing,
which we *know* is necessary to build a working Postgres on recent gcc
versions. I have not checked the exact symptoms of -fstrict-aliasing
recently, but what you're reporting is definitely consistent with the
idea that the compiler is improperly reordering some assignments, which
is basically what the aliasing business is about. So that switch seems
like the critical issue here.
I see that configure knows about that switch, but it only tries to use
it if CC = xlc:
elif test x${CC} = xxlc; then
# AIX xlc has to have strict aliasing turned off too
PGAC_PROG_CC_CFLAGS_OPT([-qnoansialias])
fi
So it seems we have a couple of problems here. Using xlc_r or xlC_r
or adding -q64 to CC (rather than CFLAGS which is where it really
belongs) will confuse this check. It also seems a bit schizophrenic
to have this bit of knowledge wired into configure itself while there
are some other AIX-specific switches in src/template/aix.
Would it be reasonable to change the test quoted above to
elif test $PORTNAME = aix; then ...
that is try -qnoansialias anytime the compiler isn't gcc and the
platform is aix? Is xlc used on any platform other than aix?
Also, has anyone got a clue what the switches selected in
src/template/aix actually do, and whether they still make sense
for modern AIX versions?
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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Heikki Linnakangas wrote: Andrew Dunstan wrote: How about a pg_dump flag that simply suppresses OIDs from the data and schema? pg_dump -s postgres | sed -e 's/SET default_with_oids = true;/-- /' No good for non-text dumps. 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] 64 bit PostgreSQL 8.3.6 build on AIX 5300-09-02-0849 with IBM XL C/C++ 10.1.0.1 - initdb fails (could not dump unrecognized node type: 650)
On 9 Feb 2009, at 16:04, Tom Lane wrote: Hmm. I think -qnoansialias corresponds to gcc's -fno-strict-aliasing, which we *know* is necessary to build a working Postgres on recent gcc versions. I have not checked the exact symptoms of -fstrict-aliasing recently, but what you're reporting is definitely consistent with the idea that the compiler is improperly reordering some assignments, which is basically what the aliasing business is about. So that switch seems like the critical issue here. Just for the record Tom, I am building postgresql on my test box with vectoring (-ftree-vectorize) and -O3, which pretty much turns the strict-aliasing flag off. it compiles, passes default tests, passes my tests, and works beautifully. Vectoring is pretty much only used in numeric code. That's on 32bit machine, mac os x. -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Marko Kreen mark...@gmail.com writes: But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs or requires table rewrite, it turned from minor annoyance to big annoyance. So I'd like have a reasonable path for getting rid of them, which we don't have currently. We've had SET WITHOUT OIDS since 7.3 or thereabouts. Anybody who hasn't applied it in all that time either does not care, or actually needs the OIDs and will be unhappy if we arbitrarily remove the feature. 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Andrew Dunstan wrote: Heikki Linnakangas wrote: Andrew Dunstan wrote: How about a pg_dump flag that simply suppresses OIDs from the data and schema? pg_dump -s postgres | sed -e 's/SET default_with_oids = true;/-- /' No good for non-text dumps. *shrug*, create a text dump then. -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Andrew Dunstan and...@dunslane.net writes: Heikki Linnakangas wrote: Andrew Dunstan wrote: How about a pg_dump flag that simply suppresses OIDs from the data and schema? pg_dump -s postgres | sed -e 's/SET default_with_oids = true;/-- /' No good for non-text dumps. Also it would fail badly if the dump had in fact been made with -o. Currently there are two behaviors in pg_dump: 1. With -o: preserve both the existence of oid columns and their exact contents 2. Without -o: preserve the existence of oid columns, but don't worry about duplicating their contents (default). It might be worth extending the switch to provide a third option to get rid of oid columns altogether, but I'm really not convinced that this is better than suggesting that people run ALTER SET WITHOUT OIDS on all their tables. 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Why? What benefit do we get out of denying users this option? Why should we continue to support historical special case? It is not a feature that adds anything to user experience with Postgres. Anyway, that was my vote only. If there are developers interested in supporting oids feel free to do so. But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs or requires table rewrite, it turned from minor annoyance to big annoyance. So I'd like have a reasonable path for getting rid of them, which we don't have currently. Removing them completely is simplest path, but adding extra features to support it is another. Well, see Tom's point upthread: OIDs are extensively used for system tables, and are not going away. So this is a pipe dream. In the meantime, ALTER TABLE WITHOUT OIDS is (at least for some people) an easier migration path than dump+reload. That might be true in your environment, but is certainly not true in general. We have many DDL commands that require full-table rewrites, and they are FAR from useless. Compared to not having the DDL commands or having DDL commands that do not rewrite the tables? ;) Not having them, of course. If we remove ALTER TABLE WITHOUT OIDS, it's going to encourage people to do stuff like this: CREATE TABLE blah_without_oids AS SELECT * FROM blah; ALTER TABLE blah ... ALTER TABLE blah ... -- move foreign keys, constraints, etc. DROP TABLE blah; ALTER TABLE blah_without_oids RENAME TO blah; ...or else dump+reload. ISTM that if anything that's going to encourage people to keep the OIDs in there because it's too much work to get rid of them. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On 2/9/09, Tom Lane t...@sss.pgh.pa.us wrote: Marko Kreen mark...@gmail.com writes: But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs or requires table rewrite, it turned from minor annoyance to big annoyance. So I'd like have a reasonable path for getting rid of them, which we don't have currently. We've had SET WITHOUT OIDS since 7.3 or thereabouts. Anybody who hasn't applied it in all that time either does not care, or actually needs the OIDs and will be unhappy if we arbitrarily remove the feature. Sure I did not care. Because I thought I can get rid of them anytime I wanted. But it seems it's not the case... We've set default_with_oids = false, for quite a long time. But there are still tables remaining with oids. And this discussion showed it now easy to get rid of them. I can patch Postgres myself, but I was thinking maybe others want also some solution. -- marko -- 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] add_path optimization
Bruce Momjian br...@momjian.us wrote: Where are we on this: the original patch, and Kevin's slow queries? Robert's patch is not the cause of the 8.4 problems with my queries, and (as Robert pointed out) a separate thread has been started to discuss those issues. From my perspective, Robert's patch has improved plan time in every test of a complex query that I've run. I have compared plans for some queries with and without the patch, and in when I have done so the EXPLAIN output has been byte-for-byte identical, it just got to that plan faster. In this post: http://archives.postgresql.org/pgsql-hackers/2009-02/msg00118.php Tom points out that the additional optimizations included in 8.4 can increase plan time a bit, So there might be an argument for installing Robert's optimization or something like it in 8.4 to buy some of that back, rather than waiting for 8.5. -Kevin -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Heikki Linnakangas wrote: Andrew Dunstan wrote: How about a pg_dump flag that simply suppresses OIDs from the data and schema? pg_dump -s postgres | sed -e 's/SET default_with_oids = true;/-- /' No good for non-text dumps. Also it would fail badly if the dump had in fact been made with -o. Don't do that then. We're not talking about filtering any old dump you have lying around. We're talking about adding a new flag to pg_dump. If you can run pg_dump with a new flag, surely you can run it without -o in text mode and use sed just as well. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On 2/9/09, Robert Haas robertmh...@gmail.com wrote: Why? What benefit do we get out of denying users this option? Why should we continue to support historical special case? It is not a feature that adds anything to user experience with Postgres. Anyway, that was my vote only. If there are developers interested in supporting oids feel free to do so. But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs or requires table rewrite, it turned from minor annoyance to big annoyance. So I'd like have a reasonable path for getting rid of them, which we don't have currently. Removing them completely is simplest path, but adding extra features to support it is another. Well, see Tom's point upthread: OIDs are extensively used for system tables, and are not going away. So this is a pipe dream. In the meantime, ALTER TABLE WITHOUT OIDS is (at least for some people) an easier migration path than dump+reload. Sorry, I was talking only about oids in user tables. That might be true in your environment, but is certainly not true in general. We have many DDL commands that require full-table rewrites, and they are FAR from useless. Compared to not having the DDL commands or having DDL commands that do not rewrite the tables? ;) Not having them, of course. If we remove ALTER TABLE WITHOUT OIDS, it's going to encourage people to do stuff like this: CREATE TABLE blah_without_oids AS SELECT * FROM blah; ALTER TABLE blah ... ALTER TABLE blah ... -- move foreign keys, constraints, etc. DROP TABLE blah; ALTER TABLE blah_without_oids RENAME TO blah; ...or else dump+reload. ISTM that if anything that's going to encourage people to keep the OIDs in there because it's too much work to get rid of them. By removing I mean that in version 8.6 you simply cannot create user table with oids. Thus no need to get rid of them. If we keep the possibility to create tables with oids, obviously the ALTER, etc command must also be kept. -- marko -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Marko Kreen wrote: We've set default_with_oids = false, for quite a long time. But there are still tables remaining with oids. And this discussion showed it now easy to get rid of them. Do you still need the oids? If not, run ALTER TABLE WITHOUT OIDS before upgrading to 8.4, while it's still fast. If yes, you couldn't use the option to remove them at pg_dump anyway because you still need them after the upgrade. -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Marko Kreen mark...@gmail.com writes: On 2/9/09, Tom Lane t...@sss.pgh.pa.us wrote: We've had SET WITHOUT OIDS since 7.3 or thereabouts. Anybody who hasn't applied it in all that time either does not care, or actually needs the OIDs and will be unhappy if we arbitrarily remove the feature. Sure I did not care. Because I thought I can get rid of them anytime I wanted. But it seems it's not the case... Sure, you can still get rid of them, because SET WITHOUT OIDS isn't going away. It will be a bit more expensive than it used to be, but if you've not applied it before migrating to 8.4, that very strongly suggests that you don't care about getting rid of oids anyhow. The other half of this thread seems to be pointed in the direction of *forcing* users to get rid of oids, which is not happening as far as I'm concerned. It'd be breaking stuff to no purpose. I've been known to vote for breaking apps when there was a purpose to it (eg tightening implicit coercions) but removing the ability to have oids in user tables wouldn't buy us anything meaningful. 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] I can see beta now
On Mon, Feb 9, 2009 at 9:39 AM, Bruce Momjian br...@momjian.us wrote: There are now seven unapplied patches on the commit-fest page: http://wiki.postgresql.org/wiki/CommitFest_2008-11 not counting the SE-PostgreSQL and Recovery, Replication, Hot Standby patches. I can now see us closing the existing items in the next several weeks and preparing for 8.4 beta. Is there any agreement about which committers will be handling which of the remaining CommitFest patches? Is any help needed from the rest of the community? I think Alvaro is about ready to commit the last piece of autovacuum and reloptions, and as for Reducing some DDL Locks to ShareLock there is currently nothing for a committer to do as there is no updated patch. Heikki is working on infrastructure changes for recovery and Hot Standby. That still leaves the following patches in need of some action (commit, reject, bump to 8.5, or request changes) by a committer: - SE-PostgreSQL Lite - GIN fast insert - B-Tree Emulation for GIN - Improve Performance of Multi-Batch Hash Join for Skewed Data Sets - Proposal of PITR performance improvement - updated hash functions ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On Mon, Feb 9, 2009 at 11:36 AM, Marko Kreen mark...@gmail.com wrote: On 2/9/09, Tom Lane t...@sss.pgh.pa.us wrote: Marko Kreen mark...@gmail.com writes: But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs or requires table rewrite, it turned from minor annoyance to big annoyance. So I'd like have a reasonable path for getting rid of them, which we don't have currently. We've had SET WITHOUT OIDS since 7.3 or thereabouts. Anybody who hasn't applied it in all that time either does not care, or actually needs the OIDs and will be unhappy if we arbitrarily remove the feature. Sure I did not care. Because I thought I can get rid of them anytime I wanted. But it seems it's not the case... We've set default_with_oids = false, for quite a long time. But there are still tables remaining with oids. And this discussion showed it now easy to get rid of them. I can patch Postgres myself, but I was thinking maybe others want also some solution. I must be missing something. Why would you need to patch PostgreSQL and how would it help you if you did? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On 2/9/09, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Marko Kreen wrote: We've set default_with_oids = false, for quite a long time. But there are still tables remaining with oids. And this discussion showed it now easy to get rid of them. Do you still need the oids? If not, run ALTER TABLE WITHOUT OIDS before upgrading to 8.4, while it's still fast. If yes, you couldn't use the option to remove them at pg_dump anyway because you still need them after the upgrade. Indeed. I must apologize. I seems I read too fast and got the impression the bug applies also to older versions of Postgres. If this is not the case and ALTER still works fine on older versions, most of my comments do not apply, because indeed, we can clean it up on 8.3. There is still minor problem that it will be made expensive on 8.4, but as it is not released yet, it can be solved by advising users to clean up their tables on 8.3. -- marko -- 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 GUC var: autovacuum_process_all_tables
Simon Riggs wrote: On Thu, 2009-02-05 at 18:54 -0300, Alvaro Herrera wrote: I don't see them as conflicting; I see yours as a missing feature, namely the ability to add tables to an autovacuum group, which could have settings attached. Being able to do that is the whole point of moving settings to reloptions. So your changes will allow these? ALTER DATABASE foo SET (autovacuum_enabled = false); ALTER SCHEMA foo SET (autovacuum_enabled = false); Hmm. Perhaps being able to turn autovacuum on/off per-database is desirable and possible, but I can't see doing it per schema. CREATE TABLE GROUP foo_group; ALTER TABLE foo SET TABLE GROUP foo_group; ALTER TABLE foo2 SET TABLE GROUP foo_group; ALTER TABLE GROUP SET (autovacuum_enabled = false); Yes, something like that. Hopefully the grouping of tables is not purely related to AV? Hmm, good question. I was envisioning it only for autovacuum, but it hasn't been vetted on pgsql-hackers. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 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] How to get SE-PostgreSQL acceptable
KaiGai Kohei escribió: One melancholic thing is adding a member into pg_proc. It defines more than 2000 of entries which I have to modify correctly. :( Is there any script to help it? Try a search for coccinelle, sdiff, or was it spatch? It got featured on http://LWN.net/ not many weeks ago. -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On 2/9/09, Robert Haas robertmh...@gmail.com wrote: On Mon, Feb 9, 2009 at 11:36 AM, Marko Kreen mark...@gmail.com wrote: On 2/9/09, Tom Lane t...@sss.pgh.pa.us wrote: Marko Kreen mark...@gmail.com writes: But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs or requires table rewrite, it turned from minor annoyance to big annoyance. So I'd like have a reasonable path for getting rid of them, which we don't have currently. We've had SET WITHOUT OIDS since 7.3 or thereabouts. Anybody who hasn't applied it in all that time either does not care, or actually needs the OIDs and will be unhappy if we arbitrarily remove the feature. Sure I did not care. Because I thought I can get rid of them anytime I wanted. But it seems it's not the case... We've set default_with_oids = false, for quite a long time. But there are still tables remaining with oids. And this discussion showed it now easy to get rid of them. I can patch Postgres myself, but I was thinking maybe others want also some solution. I must be missing something. Why would you need to patch PostgreSQL and how would it help you if you did? We use dumps to move db's around and they contain lot of SET default_with_oids that the pg_dump happily puts there. Remembering to filter them out each time a database is created does not work. So it would be good if we can use such dump, but receiving Postgres would ignore any requests to create tables with oids. -- marko -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Marko Kreen mark...@gmail.com writes: On 2/9/09, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Do you still need the oids? If not, run ALTER TABLE WITHOUT OIDS before upgrading to 8.4, while it's still fast. If yes, you couldn't use the option to remove them at pg_dump anyway because you still need them after the upgrade. Indeed. I must apologize. I seems I read too fast and got the impression the bug applies also to older versions of Postgres. If this is not the case and ALTER still works fine on older versions, most of my comments do not apply, because indeed, we can clean it up on 8.3. I think actually we are in violent agreement ;-). The argument for getting rid of userland OIDs, as far as I can see, is to eliminate future development effort and risk of bugs associated with them. Now if OIDs are staying in system tables ... which they are, for the foreseeable future ... then the only real cost or risk associated with userland OIDs is driven precisely by ALTER SET WITHOUT OIDS. Because that creates a situation with a table that used to have OIDs and no longer does, except there are still vestiges of its having OIDs, ie rows in the table that contain an OID. So the patch I'm proposing attacks that problem directly by making sure there is no intermediate status. Either a table has OIDS (and so do all its rows) or not (and none of its rows do either). I think this pretty much eliminates the risk of induced bugs, and it does it without taking away functionality that applications might depend on. Unless you want to argue that SET WITHOUT OIDS is fast is a property that apps are depending on, but that seems like a bit of a stretch. 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] I can see beta now
Robert Haas wrote: On Mon, Feb 9, 2009 at 9:39 AM, Bruce Momjian br...@momjian.us wrote: There are now seven unapplied patches on the commit-fest page: http://wiki.postgresql.org/wiki/CommitFest_2008-11 not counting the SE-PostgreSQL and Recovery, Replication, Hot Standby patches. I can now see us closing the existing items in the next several weeks and preparing for 8.4 beta. Is there any agreement about which committers will be handling which of the remaining CommitFest patches? Is any help needed from the rest of the community? No. I talked to Oleg on IM and he is waiting for an official review. Is someone going to review his work or should he commit it (with Teodor) because they know this area of the code better than anyone else? I think Alvaro is about ready to commit the last piece of autovacuum and reloptions, and as for Reducing some DDL Locks to ShareLock Good. there is currently nothing for a committer to do as there is no updated patch. Heikki is working on infrastructure changes for recovery and Hot Standby. That still leaves the following patches in Heikki is responsible for this group of patches. need of some action (commit, reject, bump to 8.5, or request changes) by a committer: - SE-PostgreSQL Lite - GIN fast insert - B-Tree Emulation for GIN - Improve Performance of Multi-Batch Hash Join for Skewed Data Sets - Proposal of PITR performance improvement - updated hash functions Yep. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new GUC var: autovacuum_process_all_tables
Josh Berkus wrote: On the other hand, I'd been keen on a runtime suset autovaccum=on/off which we could call from a cron job or the pgadmin scheduler in order to have maintenance windows. Unless that's already becoming possible? autovacuum=on/off is already SIGHUP as of 8.3 (not SUSET, since it makes no sense to change it in a single connection). Right. What I'm saying is that if it *didn't* require a sighup, then users could cronjob starting and stopping Autovac themselves. Hmm, I'm not sure I understand what you're suggesting. Maybe what you want is that we have a SQL-accesible function that sends SIGHUP to the postmaster? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Alvaro Herrera alvhe...@commandprompt.com writes: KaiGai Kohei escribió: One melancholic thing is adding a member into pg_proc. It defines more than 2000 of entries which I have to modify correctly. :( Is there any script to help it? Try a search for coccinelle, sdiff, or was it spatch? It got featured on http://LWN.net/ not many weeks ago. FWIW, every single time I've had to add a column to pg_proc (and I've done it several times now), an Emacs macro got the job done with a few minutes' thought. I'm sure sed would work as well. The contents of those DATA lines are really pretty stylized. 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] renaming storage parameters
Hi, Euler Taveira is arguing in an autovacuum thread that we should give storage parameters a different name; his argument is that autovacuum_enabled is not really a parameter that relates to storage. He is proposing relation parameters. I am against the idea of renaming them, for two reasons: 1. it's a user-visible change that doesn't seem to buy a lot; 2. it's a tedious patch to write. Can I get some votes? If you think they should be renamed but to a different name than relation parameters, please state what that is too. -- 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] I can see beta now
Bruce Momjian br...@momjian.us writes: I talked to Oleg on IM and he is waiting for an official review. Is someone going to review his work or should he commit it (with Teodor) because they know this area of the code better than anyone else? Obviously they know the innards of GIN better than the rest of us, but I still had some concerns about the API to the rest of the system. 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] new GUC var: autovacuum_process_all_tables
Hopefully the grouping of tables is not purely related to AV? Hmm, good question. I was envisioning it only for autovacuum, but it hasn't been vetted on pgsql-hackers. I think we're in danger of inventing a solution in search of a problem here. AIUI, the main reason for table groups would be to define different autovacuum policies for different groups of tables. Right now, that would be pretty stupid, because there are only two possible policies: yes and no. But if the policy is something very complex, then you're not going to want to redefine it for each individual table. Instead, you're going to want to define it once and then point individual tables at it. But you could do that just as well by assigning each policy a name or number and then setting a reloption on the table to refer to that name or number, which would completely avoid the need to invent all-new, non-standard syntax. But if we do decide to invent such a syntax, it's not good enough to say that we should make it general because it might be useful for a purpose other than autovacuum. We should have a pretty specific idea of what sort of purpose that might be. Otherwise, we'll likely find (when the purpose finally arises) that the supposedly-general model we introduced doesn't fit it as well as we thought. But right now, we don't even have ONE use case for the general syntax, let alone two, because the future autovacuum enhancements that would make use of that syntax haven't been designed yet (or at least haven't been discussed here yet). ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new GUC var: autovacuum_process_all_tables
Alvaro Herrera alvhe...@commandprompt.com writes: Josh Berkus wrote: Right. What I'm saying is that if it *didn't* require a sighup, then users could cronjob starting and stopping Autovac themselves. Hmm, I'm not sure I understand what you're suggesting. Maybe what you want is that we have a SQL-accesible function that sends SIGHUP to the postmaster? Like, say, pg_reload_conf()? But actually a cron job would almost certainly find it more pleasant to use pg_ctl reload and never bother with a database connection at all. 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
On 2/9/09, Tom Lane t...@sss.pgh.pa.us wrote: Marko Kreen mark...@gmail.com writes: On 2/9/09, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Do you still need the oids? If not, run ALTER TABLE WITHOUT OIDS before upgrading to 8.4, while it's still fast. If yes, you couldn't use the option to remove them at pg_dump anyway because you still need them after the upgrade. Indeed. I must apologize. I seems I read too fast and got the impression the bug applies also to older versions of Postgres. If this is not the case and ALTER still works fine on older versions, most of my comments do not apply, because indeed, we can clean it up on 8.3. I think actually we are in violent agreement ;-). The argument for getting rid of userland OIDs, as far as I can see, is to eliminate future development effort and risk of bugs associated with them. Now if OIDs are staying in system tables ... which they are, for the foreseeable future ... then the only real cost or risk associated with userland OIDs is driven precisely by ALTER SET WITHOUT OIDS. Because that creates a situation with a table that used to have OIDs and no longer does, except there are still vestiges of its having OIDs, ie rows in the table that contain an OID. So the patch I'm proposing attacks that problem directly by making sure there is no intermediate status. Either a table has OIDS (and so do all its rows) or not (and none of its rows do either). I think this pretty much eliminates the risk of induced bugs, and it does it without taking away functionality that applications might depend on. Yes. I agree with the patch. And I'm all for robustness. Unless you want to argue that SET WITHOUT OIDS is fast is a property that apps are depending on, but that seems like a bit of a stretch. No. I'm not concerned with ALTER command, I'm concerned about reloading dumps from older versions. So my, uh, new argument is - starting with 8.4, it is very hard to get rid of oids on user tables because all the tools work against user. So either: the 8.4 will be a flag day and all users need to clean up their database on 8.3, or we give some option for them to lessen the pain. Considering that default_with_oids went false in 8.1 (?), affected are users who are reusing their dumps or postgresql.conf from 8.0 and below. Maybe there are not many of such users (?) so flag day approach it ok. -- marko -- 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] renaming storage parameters
On Mon, Feb 9, 2009 at 12:19 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Euler Taveira is arguing in an autovacuum thread that we should give storage parameters a different name; his argument is that autovacuum_enabled is not really a parameter that relates to storage. He is proposing relation parameters. I am against the idea of renaming them, for two reasons: 1. it's a user-visible change that doesn't seem to buy a lot; 2. it's a tedious patch to write. Can I get some votes? If you think they should be renamed but to a different name than relation parameters, please state what that is too. -1. Even if this is a good idea in general, it's a bad idea right now, because we're trying to get 8.4 beta out the door. I also don't see that the name storage parameters is all that terrible. Surely the purpose of autovacuum is allow reuse of storage space, no? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Tom Lane escribió: Alvaro Herrera alvhe...@commandprompt.com writes: KaiGai Kohei escribi�: One melancholic thing is adding a member into pg_proc. It defines more than 2000 of entries which I have to modify correctly. :( Is there any script to help it? Try a search for coccinelle, sdiff, or was it spatch? It got featured on http://LWN.net/ not many weeks ago. Here it is: http://lwn.net/Articles/315686/ FWIW, every single time I've had to add a column to pg_proc (and I've done it several times now), an Emacs macro got the job done with a few minutes' thought. I'm sure sed would work as well. The contents of those DATA lines are really pretty stylized. I've had to do it only once, but yes, I did it with a simple s// command in Vim. -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Marko Kreen mark...@gmail.com writes: No. I'm not concerned with ALTER command, I'm concerned about reloading dumps from older versions. So my, uh, new argument is - starting with 8.4, it is very hard to get rid of oids on user tables because all the tools work against user. That's a pretty overstated claim. It's exactly the same tool as before, it's just slower. So either: the 8.4 will be a flag day and all users need to clean up their database on 8.3, or we give some option for them to lessen the pain. Considering that default_with_oids went false in 8.1 (?), affected are users who are reusing their dumps or postgresql.conf from 8.0 and below. Indeed. If they have not bothered to remove oids from their tables up to now, what are the odds that they're going to bother in the future? IMHO, the only way they'd care is if we try to force them to care (ie by removing oids as a user option), which I'm against. So I see no flag day here. They'll still have oids and they still won't care. 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] renaming storage parameters
Alvaro Herrera alvhe...@commandprompt.com writes: Euler Taveira is arguing in an autovacuum thread that we should give storage parameters a different name; his argument is that autovacuum_enabled is not really a parameter that relates to storage. He is proposing relation parameters. I am against the idea of renaming them, for two reasons: 1. it's a user-visible change that doesn't seem to buy a lot; 2. it's a tedious patch to write. Can I get some votes? I agree with leaving them alone. Storage might not be exactly le mot juste anymore but it still gives you a good idea what they're meant for; in particular that they are targeted at implementation concerns rather than SQL-level semantics of the table. Moving to a content-free name like relation parameter in order to cover all possible uses doesn't seem like it helps anyone understand anything better. 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Tom Lane wrote: Considering that default_with_oids went false in 8.1 (?), affected are users who are reusing their dumps or postgresql.conf from 8.0 and below. No, they have upgraded along the way. pg_dump carefully preserves the with/without oids property of the tables it is dumping. And rightly so. This has nothing to do with default_without_oids. Indeed. If they have not bothered to remove oids from their tables up to now, what are the odds that they're going to bother in the future? IMHO, the only way they'd care is if we try to force them to care (ie by removing oids as a user option), which I'm against. So I see no flag day here. They'll still have oids and they still won't care. I have clients I have not yet managed to ween off oids, because they have legacy apps, sometimes third party apps, that rely on them. I don't want to make it any harder to get them over the hurdle. 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] new GUC var: autovacuum_process_all_tables
On Mon, Feb 9, 2009 at 12:31 PM, Robert Haas robertmh...@gmail.com wrote: Hopefully the grouping of tables is not purely related to AV? Hmm, good question. I was envisioning it only for autovacuum, but it hasn't been vetted on pgsql-hackers. I think we're in danger of inventing a solution in search of a problem here. AIUI, the main reason for table groups would be to define different autovacuum policies for different groups of tables. Right now, that would be pretty stupid, because there are only two possible policies: yes and no. not really... the idea is to let one group to have autovacuum on in certain periods of time and let them of the rest of the time... or maybe a group of tables should be autovacuumed every 50 updates (vac_base_thresh) and some tables every 100, in some hours maybe we need to have different vac_cost_delay and vac_cost_limit... actually there are different parameters that could be set... Instead, you're going to want to define it once and then point individual tables at it. But you could do that just as well by assigning each policy a name or number and then setting a reloption on the table to refer to that name or number, which would completely avoid the need to invent all-new, non-standard syntax. well the reloptions *is* invented and non-standard syntax But if we do decide to invent such a syntax, it's not good enough to say that we should make it general because it might be useful for a purpose other than autovacuum. We should have a pretty specific idea of what sort of purpose that might be. Otherwise, we'll likely find (when the purpose finally arises) that the supposedly-general model we introduced doesn't fit it as well as we thought. But right now, we don't even have ONE use case for the general syntax, let alone two, because the future autovacuum enhancements that would make use of that syntax haven't been designed yet (or at least haven't been discussed here yet). --- devil's advocate mode on --- a general purpose scheduler could be used for: - REINDEX - moving data around for OLAP - periodically execute SP that has to change the status of a process in a time driven way... - autovacuum, and programming manual vacuums --- devil's advocate mode off --- now, we actually can do that work with external schedulers (cron in linux, the windows task scheduler, etc)... the only two reasons i can think to prefer our own sintax for this is: pg_dump support to keep pilicies alive even in a fresh installed machine and marketing (two good reasons if you ask me) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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 GUC var: autovacuum_process_all_tables
AIUI, the main reason for table groups would be to define different autovacuum policies for different groups of tables. Right now, that would be pretty stupid, because there are only two possible policies: yes and no. not really... the idea is to let one group to have autovacuum on in certain periods of time and let them of the rest of the time... Yes, but that's a future enhancement, we don't have that now. or maybe a group of tables should be autovacuumed every 50 updates (vac_base_thresh) and some tables every 100, in some hours maybe we need to have different vac_cost_delay and vac_cost_limit... actually there are different parameters that could be set... Instead, you're going to want to define it once and then point individual tables at it. But you could do that just as well by assigning each policy a name or number and then setting a reloption on the table to refer to that name or number, which would completely avoid the need to invent all-new, non-standard syntax. well the reloptions *is* invented and non-standard syntax Yes, but we already have that one. IMO we should try to reuse it and only invent new stuff if there is a compelling reason - which is so far absent from this discussion. But if we do decide to invent such a syntax, it's not good enough to say that we should make it general because it might be useful for a purpose other than autovacuum. We should have a pretty specific idea of what sort of purpose that might be. Otherwise, we'll likely find (when the purpose finally arises) that the supposedly-general model we introduced doesn't fit it as well as we thought. But right now, we don't even have ONE use case for the general syntax, let alone two, because the future autovacuum enhancements that would make use of that syntax haven't been designed yet (or at least haven't been discussed here yet). --- devil's advocate mode on --- a general purpose scheduler could be used for: - REINDEX - moving data around for OLAP - periodically execute SP that has to change the status of a process in a time driven way... - autovacuum, and programming manual vacuums --- devil's advocate mode off --- AFAICS, table groups wouldn't help with any of that stuff. I think you're proving my point that we have no idea what we're implementing, so it's a little premature to talk about what else the same infrastructure can be used for. now, we actually can do that work with external schedulers (cron in linux, the windows task scheduler, etc)... the only two reasons i can think to prefer our own sintax for this is: pg_dump support to keep pilicies alive even in a fresh installed machine and marketing (two good reasons if you ask me) Which are all great points, but not what I was talking about. I am talking about the table group stuff. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Andrew Dunstan and...@dunslane.net writes: I have clients I have not yet managed to ween off oids, because they have legacy apps, sometimes third party apps, that rely on them. I don't want to make it any harder to get them over the hurdle. Surely the major cost there is going to be fixing those apps; I think focusing on whether SET WITHOUT OIDS is zero-cost is worrying about entirely the wrong thing. Also, if they are using the oids (and presumably relying on them to be unique), the tables can't be as huge as all that --- they'd have to be under a billion or so rows, else the 32-bit width of oids would have forced a change a long time ago. So even a rewriting form of SET WITHOUT OIDS doesn't seem all that painful. Compared to an app migration that's still not happened after N years, I can't believe it's a problem. 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] renaming storage parameters
* Alvaro Herrera: Euler Taveira is arguing in an autovacuum thread that we should give storage parameters a different name; his argument is that autovacuum_enabled is not really a parameter that relates to storage. He is proposing relation parameters. They also apply to indices, right? I think it's a bit odd to call those relations (but there's precedent inside PostgreSQL), so it's just replacing one strange terminology with another. -- 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] GIN improvements
Jeff Davis pg...@j-davis.com writes: On Wed, 2009-02-04 at 14:40 -0500, Robert Haas wrote: Well, there's nothing to force that plan to be invalidated when the state of the pending list changes, is there? Would it be unreasonable to invalidate cached plans during the pending list cleanup? If the pending list cleanup is done by VACUUM then such an invalidation already happens (VACUUM forces it after updating pg_class.reltuples/ relpages). What's bothering me is the lack of any reasonable mechanism for invalidating plans in the other direction, ie when the list grows past the threshold where this code wants to turn off indexscans. Since the threshold depends on parameters that can vary across sessions, you'd more or less have to send a global invalidation after every addition to the list, in case that addition put it over the threshold in some other session's view. That's unreasonably often, in my book. Also, as mentioned earlier, I'm pretty down on the idea of a threshold where indexscans suddenly turn off entirely; that's not my idea of how the planner ought to work. But the real bottom line is: if autovacuum is working properly, it should clean up the index before the list ever gets to the point where it'd be sane to turn off indexscans. So I don't see why we need to hack the planner for this at all. If any hacking is needed, it should be in the direction of making sure autovacuum puts sufficient priority on this task. 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] New pg_dump patch -- document statistics collector exception
Apologies. Turns out the name of the relevant setting was changed for
8.3! So a revised patch is attached.
For backing, see:
http://www.postgresql.org/docs/8.3/static/release-8-3.html
" Numerous changes in administrative server parameters...
stats_block_level and stats_row_level are merged into track_counts."
Bruce Momjian wrote:
Bryce Nesbitt wrote:
This is a proposed patch to document disabling the statistics collector
pg_dump activity, and give a bit more visibility to the PGOPTIONS
environment variable supported by libpq.
It is an alternative to the prior patch, which supplied a --no-stats flag.
This is a documentation only patch, not tied to a recent code change.
Patch applied, with spelling correction already noted.
Index: pg_dump.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.514
diff -c -2 -r1.514 pg_dump.c
*** pg_dump.c 18 Jan 2009 20:44:45 - 1.514
--- pg_dump.c 20 Jan 2009 20:47:25 -
***
*** 236,239
--- 236,240
static int outputNoTablespaces = 0;
static int use_setsessauth = 0;
+ static int noStatsCollection = 0;
static struct option long_options[] = {
***
*** 278,281
--- 279,283
{role, required_argument, NULL, 3},
{use-set-session-authorization, no_argument, use_setsessauth, 1},
+ {no-stats, no_argument, noStatsCollection, 1},
{NULL, 0, NULL, 0}
***
*** 430,433
--- 432,437
else if (strcmp(optarg, no-tablespaces) == 0)
outputNoTablespaces = 1;
+ else if (strcmp(optarg, no-stats) == 0)
+ noStatsCollection = 1;
else if (strcmp(optarg, use-set-session-authorization) == 0)
use_setsessauth = 1;
***
*** 613,616
--- 617,629
do_sql_command(g_conn, SET statement_timeout = 0);
+ /*
+ * Disable collection of statistics. pg_dump's activity may be very different
+ * from what you are trying to analyze in the stats tables.
+ */
+ if( noStatsCollection ) {
+ do_sql_command(g_conn, SET stats_block_level = false);
+ do_sql_command(g_conn, SET stats_row_level = false);
+ }
+
/*
* Start serializable transaction to dump consistent data.
***
*** 833,836
--- 846,850
printf(_( -U, --username=NAME connect as specified database user\n));
printf(_( -W, --password force password prompt (should happen automatically)\n));
+ printf(_( --no-stats disable statistics collection (superuser only)\n));
printf(_(\nIf no database name is supplied, then the PGDATABASE environment\n
--
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 pg_dump patch -- document statistics collector exception
Apologies. Turns out the name of the relevant setting was changed for 8.3! So a revised patch is attached. For backing, see: http://www.postgresql.org/docs/8.3/static/release-8-3.html Numerous changes in administrative server parameters... stats_block_level and stats_row_level are merged into track_counts. Bryce Nesbitt wrote: We don't want a pg_dump flag; the doc mention is good enough. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] updated hash functions for postgresql v1
Kenneth Marshall k...@rice.edu writes: I have updated the patch posted by Jeff Davis on January 9th to include the micro-patch above as well as updated the polymorphism regressions tests. This applies cleanly to the latest CVS pull. Applied --- thanks for being persistent about resolving the doubts on this. One thing that apparently neither of you realized was that the polymorphism results were varying between bigendian and littleendian machines; I suppose you are using different hardware and that's why you didn't agree on what the results should be. Since we already agreed we were going to tolerate endianness dependence in the hash functions, I fixed that by adding some ORDER BYs. 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Sorry, I was indeed thinking of newly added columns rather than dropped columns. We define the row representation such that one may have fewer rows than the tupledesc and how to interpret that in such a way as to make adding nullable columns a convenient operation. How is doing the same here and fixing a case where we weren't following the definition any more of a kludge than how we handle newly added columns? Which incidentally I don't think is at all kludgy. I think what you propose would be a mistake. We want to encourage people to move *away* from OIDS. - making drop kids prohibitively expensive and adding an operation to add kids which we hope nobody needs seems like heading in the wrong direction. Sorry for top posting - in this case i'm using google mail's mobile interface but it's no better about this and makes properly threading responses nigh impossible. On 2009-02-09, Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark greg.st...@enterprisedb.com writes: I don't understand what's wrong with the existing setup where DROP OIDS is a free operation. It breaks things, in particular http://archives.postgresql.org/pgsql-hackers/2008-11/msg00332.php We could kluge around that particular problem, but the objection I have to doing so is I'm 100% certain it won't be the last such bug. It seems exactly equivalent to how we handle DROP COLUMN It is just about exactly *unlike* DROP COLUMN, because in DROP COLUMN we retain a memory that there used to be a column there. A close emulation of DROP COLUMN would involve inventing some representation of oidisdropped, and going through every one of the multitudinous places that special-case dropped columns in order to see if each one needs a similar special case for dropped OIDs. The bug mentioned above stems directly from not expecting a table to still contain OIDs after SET WITHOUT OIDS, so I don't think this parallel is mistaken. Note that I'm willing to lay a significant side bet that we still have bugs of omission with dropped columns, too. But we'll fix those as we come to them. I don't think it is worth making a similar open-ended commitment of resources just to keep SET WITHOUT OIDS fast. ... where the natt field of the tuple disagrees with the tuple descriptor and any additional columns are implicitly null. No, that's the mechanism that makes ADD COLUMN feasible (and indeed pretty easy). DROP COLUMN is the far newer and uglier mess around attisdropped. regards, tom lane -- greg -- 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] WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
Greg Stark st...@enterprisedb.com writes: I think what you propose would be a mistake. We want to encourage people to move *away* from OIDS. Why? I don't agree with that premise, and therefore not with any of the rest of your argument. 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] new GUC var: autovacuum_process_all_tables
On Mon, Feb 9, 2009 at 1:44 PM, Robert Haas robertmh...@gmail.com wrote: AIUI, the main reason for table groups would be to define different autovacuum policies for different groups of tables. Right now, that would be pretty stupid, because there are only two possible policies: yes and no. not really... the idea is to let one group to have autovacuum on in certain periods of time and let them of the rest of the time... Yes, but that's a future enhancement, we don't have that now. that was what simon was talking about, IIRC... he was speculating about a possible future syntax for grouping tables for use with a possible future postgres scheduler... well the reloptions *is* invented and non-standard syntax Yes, but we already have that one. IMO we should try to reuse it and only invent new stuff if there is a compelling reason - which is so far absent from this discussion. reloptions is what we will use for autovacumm (actually Alvaro already applied that patch)... no one is touching that... the group syntax is for a future feature... But if we do decide to invent such a syntax, it's not good enough to say that we should make it general because it might be useful for a purpose other than autovacuum. We should have a pretty specific idea of what sort of purpose that might be. Otherwise, we'll likely find (when the purpose finally arises) that the supposedly-general model we introduced doesn't fit it as well as we thought. But right now, we don't even have ONE use case for the general syntax, let alone two, because the future autovacuum enhancements that would make use of that syntax haven't been designed yet (or at least haven't been discussed here yet). --- devil's advocate mode on --- a general purpose scheduler could be used for: - REINDEX - moving data around for OLAP - periodically execute SP that has to change the status of a process in a time driven way... - autovacuum, and programming manual vacuums --- devil's advocate mode off --- AFAICS, table groups wouldn't help with any of that stuff. I think table groups are not being implemented now... it was a mere speculation about a way to apply a policy in a set of tables... actually, Alvaro's response was: something like that so we have to actually wait for his proposal before start a war on that and before we think it could be general enough to include other policies (like the ones for an scheduler) you're proving my point that we have no idea what we're implementing, so it's a little premature to talk about what else the same infrastructure can be used for. that's because we are not implementing that now... it's for the future... now, we actually can do that work with external schedulers (cron in linux, the windows task scheduler, etc)... the only two reasons i can think to prefer our own sintax for this is: pg_dump support to keep pilicies alive even in a fresh installed machine and marketing (two good reasons if you ask me) Which are all great points, but not what I was talking about. I am talking about the table group stuff. me too -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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 pg_dump patch -- document statistics collector exception
Bruce Momjian wrote: Apologies. Turns out the name of the relevant setting was changed for 8.3! So a revised patch is attached. For backing, see: http://www.postgresql.org/docs/8.3/static/release-8-3.html " Numerous changes in administrative server parameters... stats_block_level and stats_row_level are merged into track_counts." Bryce Nesbitt wrote: We don't want a pg_dump flag; the doc mention is good enough. Doh! Try this one instead. Postgres 8.3 changed the name of the flag mentioned in the doc. Index: ref/pg_dump.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v retrieving revision 1.108 diff -c -2 -r1.108 pg_dump.sgml *** ref/pg_dump.sgml 7 Feb 2009 15:25:51 - 1.108 --- ref/pg_dump.sgml 9 Feb 2009 20:19:48 - *** *** 760,773 library will apply. /para para !The database activity of applicationpg_dump/application is !normally collected by the statistics collector. If this is !undesirable, you can set parameters literalstats_block_level/literal !and literalstats_row_level/literal to false via the !applicationlibpq/ envarPGOPTIONS/envar environment variable, !or via literalALTER USER/literal. /para - /refsect1 --- 760,772 library will apply. /para + para !The database activity of applicationpg_dump/application is normally collected by the !statistics collector. If this is undesirable, you can set parameter !literaltrack_counts/literal to false via envarPGOPTIONS/envar !or the literalALTER USER/literal command. For versions prior to 8.3, set !literalstats_block_level/literal and literalstats_row_level/literal instead. /para /refsect1 -- 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 pg_dump patch -- document statistics collector exception
We don't want a pg_dump flag; the doc mention is good enough. Doh! Try this one instead. Postgres 8.3 changed the name of the flag mentioned in the doc. [ Please avoid html-only email.] OK, patch applied, but I removed the mention of pre-8.3 because this is going only in the 8.4 docs. Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] temporarily stop autovacuum
Hi, Is there any way to stop autovacuum temporarily?(other than edit postgresql.conf and reload it) Pgpool-II does not want autovacuum running while doing onlie recovery. -- Tatsuo Ishii SRA OSS, Inc. Japan -- 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] temporarily stop autovacuum
On Tue, 2009-02-10 at 10:15 +0900, Tatsuo Ishii wrote: Hi, Is there any way to stop autovacuum temporarily?(other than edit postgresql.conf and reload it) Pgpool-II does not want autovacuum running while doing onlie recovery. It would be a significant hack but you could update pg_autovacuum to set all relations to false. Joshua D. Drake -- Tatsuo Ishii SRA OSS, Inc. Japan -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] temporarily stop autovacuum
Is there any way to stop autovacuum temporarily?(other than edit postgresql.conf and reload it) Pgpool-II does not want autovacuum running while doing onlie recovery. It would be a significant hack but you could update pg_autovacuum to set all relations to false. Thanks. Maybe it will be easier to talk to the autovacuum launcher daemon process directly? I will look in to the code. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1530)
The series of SE-PostgreSQL patches are updated: [1/5] http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r1530.patch [2/5] http://sepgsql.googlecode.com/files/sepostgresql-utils-8.4devel-3-r1530.patch [3/5] http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r1530.patch [4/5] http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1530.patch [5/5] http://sepgsql.googlecode.com/files/sepostgresql-tests-8.4devel-3-r1530.patch - List of updates: * These are rebased to the latest CVS HEAD because of conflictions. - The src/include/catalog/pg_proc.h got a confliction due to the newly added SQL functions. - The src/bin/pg_dump/pg_dump.c got a confliction due to the stuff to dump toast_reloptions. * bugfix: An incorrect procedure entry for sepgsql_server_getcon(). * cleanup: A strange error message in testcases. Rest of parts are unchanged. Please comment anything. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] Table Partitioning Feature
Hi Amit,
I will be traveling until next Tuesday and will have no access to email
so don't be surprised if I don't follow up this week.
The overall approach seems sound. The metadata table should help also
for DDL to find out overlapping ranges or duplicate list entries.
So far, I have not tried to use the SPI interface from a C trigger so I
don't see any disadvantage yet. We would have to assess the performance
to make sure it's not going to be a show stopper.
I think that the main issue of the trigger approach is that other
triggers might interfere. The 'partition trigger' must be the last of
the 'before insert' triggers and if the destination (child) table has a
trigger, we must ensure that this trigger is not going to require a new
routing.
Another issue is the result that will be returned by insert/copy
commands if all tuples are moved to other tables, the result will be 0.
We might want to have stats that would collect where tuples where moved
for a particular command (I don't know yet what would be the best place
to collect these stats but they could probably be updated by the trigger).
Also would the trigger be attached to all tables in the hierarchy or
only to the top parent?
What kind of query would you use with more than 1 level of inheritance
(e.g. parent=year, child=month, grand-child=day)? It looks like we have
to parse the leaves of the graph but intermediate nodes would help
accelerating the search.
An alternative approach (I haven't assessed the feasibility yet) would
be to try to call the query planner. If we ask to select the partition
value of the tuple, the query planner should return the table it is
going to scan (as in EXPLAIN SELECT * FROM t WHERE key=$1).
Let me know what you think,
Emmanuel
We are considering to following approach:
1. metadata table pg_partitions is defined as follows:
CATALOG(pg_partitions,2336) BKI_WITHOUT_OIDS
{
Oid partrelid; // partition table Oid
Oid parentrelid; // Parent table Oid
int4parttype; // Type of partition, list, hash, range
Oidpartkey;// partition key Oid
Oidkeytype; /// type of partition key.
int4keyorder /// order of the key in multi-key partitions.
textmin;
textmax; // min and max for range parti
text[] list;
inthash; // hash value
} FormData_pg_partitions;
2. C triggers will fire a query on this table to get the relevant
partition of the inserted/updated data using SPI interface. The query
will look something like (for range partitioning)
select min(partrelid)
from pg_partitions
where parentrelid = 2934 // we know this value
and (
( $1 between to_int(min ) and to_int(max) and
keyorder = 1) OR
($2 between to_date (min) and to_date (max) and
keyorder =2 )
)
group by
parentrelid
having
count(*) = number of partition keys
$1, $2, ... are the placeholders of the actual partition key values of
trigger tuple.
Since we know the type of partition keys, and the parentrelid, this
kind of query string can be saved in another table say, pg_part_map.
And its plan can be parsed once and saved in cache to be reused.
Do you see any issue with using SPI interface within triggers?
The advantage of this kind of approah is that trigger code can be made
genric for any kind of partition table.
Thanks,
Amit
Persistent Systems,
www.persistentsys.com
On 1/23/09, Emmanuel Cecchet m...@frogthinker.org wrote:
Amit,
You might want to put this on the
http://wiki.postgresql.org/wiki/Table_partitioning wiki
page.
How does your timeline look like for this implementation?
I would be happy to contribute C triggers to your implementation. From what
I understood in
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00269.php,
you already have an implementation that parses the grammar and generates
rules as if someone had written them. Is this code available?
Regarding the use of triggers to push/move data to partitions, what if
someone declares triggers on partitions? Especially if you have
subpartitions, let's consider the case where there is a trigger on the
parent, child and grandchild. If I do an insert in the parent, the user
trigger on the parent will be executed, then the partition trigger that
decides to move to the grandchild. Are we going to bypass the child trigger?
If we also want fast COPY operations on partitioned table, we could have an
optimized implementation that could bypass triggers and move the tuple
directly to the appropriate child table.
Thanks for this big contribution,
Emmanuel
Hi,
We are implementing table partitioning feature to support
- the attached commands. The syntax conforms to most of the suggestion
mentioned in
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php,
barring the following:
-- Specification of partition names is optional. System will be able to
generate partition names in such cases.
--
Re: [HACKERS] [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Hi,
alvhe...@postgresql.org (Alvaro Herrera) wrote:
Log Message:
---
Update autovacuum to use reloptions instead of a system catalog, for
per-table overrides of parameters.
This removes a whole class of problems related to misusing the catalog,
and perhaps more importantly, gives us pg_dump support for the parameters.
Based on a patch by Euler Taveira de Oliveira, heavily reworked by me.
I tested this changes and found two issues:
1. fillfactor.* options are silently ignored when the table doesn't have
toast relation. Should we notice the behabior to users?
ex. NOTICE: toast storage parameters are ignored
because the table doesn't have toast relations.
2. psql's \d+ doesn't show toast storage parameters.
Neither \d+ for base tables nor toast relations show toast.* parameters
though there are some values in pg_class.reloptions.
I think we should show toast.* parameters in \d+ for base tables
because it has consistency; we set them at ALTER TABLE for base tables.
=# CREATE TABLE tbl (t text) WITH (fillfactor=90, toast.fillfactor=70);
=# SELECT 'tbl'::regclass::oid;
oid
---
16388
=# \d+ tbl
Table public.tbl
Column | Type | Modifiers | Storage | Description
+--+---+--+-
t | text | | extended |
Has OIDs: no
Options: fillfactor=90
*** Should we show toast.fillfactor=70 here? ***
=# \d+ pg_toast.pg_toast_16388
TOAST table pg_toast.pg_toast_16388
Column | Type | Storage | Description
+-+-+-
chunk_id | oid | plain |
chunk_seq | integer | plain |
chunk_data | bytea | plain |
*** No descriptions about options here. ***
=# SELECT oid, relname, reloptions FROM pg_class
WHERE oid = 'pg_toast.pg_toast_16388'::regclass;
oid |relname | reloptions
---++-
16391 | pg_toast_16388 | {fillfactor=70}
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Table Partitioning Feature
On Mon, Feb 9, 2009 at 9:16 AM, Amit Gupta amit.pc.gu...@gmail.com wrote:
Hi Emmanuel,
We are considering to following approach:
1. metadata table pg_partitions is defined as follows:
CATALOG(pg_partitions,2336) BKI_WITHOUT_OIDS
{
Oid partrelid; // partition table Oid
Oid parentrelid; // Parent table Oid
int4parttype; // Type of partition, list, hash, range
Oidpartkey;// partition key Oid
Oidkeytype; /// type of partition key.
int4keyorder /// order of the key in multi-key partitions.
textmin;
textmax; // min and max for range parti
text[] list;
inthash; // hash value
} FormData_pg_partitions;
2. C triggers will fire a query on this table to get the relevant
partition of the inserted/updated data using SPI interface. The query
will look something like (for range partitioning)
select min(partrelid)
from pg_partitions
where parentrelid = 2934 // we know this value
and (
( $1 between to_int(min ) and to_int(max) and
keyorder = 1) OR
($2 between to_date (min) and to_date (max) and
keyorder =2 )
)
group by
parentrelid
having
count(*) = number of partition keys
$1, $2, ... are the placeholders of the actual partition key values of
trigger tuple.
Since we know the type of partition keys, and the parentrelid, this
kind of query string can be saved in another table say, pg_part_map.
And its plan can be parsed once and saved in cache to be reused.
Do you see any issue with using SPI interface within triggers?
The advantage of this kind of approah is that trigger code can be made
genric for any kind of partition table.
I am a little fuzzy on what you're proposing here, but I think you're
saying that you're only going to support range partitioning on
integers or dates and that you plan to use the text type to store the
integer or date values. FWIW, those don't seem like very good
decisions to me. I think you should aim to support range partitioning
on any combination of a datatype and a less-than operator, similar to
what pg_statistic does for statistics. pg_statistic uses anyarray to
store the datums.
I am also somewhat skeptical about the idea of using triggers for
this. I haven't scrutinized the issue in detail, so I may be all
wet... but ISTM that the concerns raised elsewhere about the order in
which triggers can be expected to fire may bite you fairly hard. ISTM
the right semantics are something like this:
- fire all of the row-level BEFORE triggers on the parent table
(giving up if any return NULL)
- determine the correct child table based on the resulting tuple
- fire all of the row-level BEFORE triggers on the child table (giving
up if any return NULL)
- insert the tuple into the child table
- fire all of the row-level AFTER triggers on the child table... and
possibly also the parent table... not sure about the order
You will also need to fire statement-level triggers on the appropriate
tables, which is a little tricky. Presumably you want the tables on
which the AFTER triggers fire to be the same ones as those on which
the BEFORE triggers fire, but you don't know which child tables you're
actually going to hit until you actually perform the action. Maybe
the right thing to do is fire both sets of triggers on the parent
table and those child tables not excluded by constraint exclusion...?
But I'm not sure about that.
Anyway, getting these types of behavior via triggers may be tricky.
But then again maybe not: I haven't read the code.
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
