Re: [HACKERS] [PATCHES] ALTER SEQUENCE
On Mon, 2003-03-03 at 20:47, Christopher Kings-Lynne wrote:
Hey, with this new ALTER SEQUENCE patch, how about this for an idea:
I submitted a patch to always generate non-colliding index and sequence
names. Seemed like an excellent idea. However, 7.3 dumps tables like this:
CREATE TABLE blah
a SERIAL
);
SELECT SETVAL('blah_a_seq', 10);
Sort of thing...
How about we add a new form to ALTER SEQUENCE sequence ...?
ALTER SEQUENCE ON blah(a) CURRVAL 10 (or whatever the syntax is)
The spec proposes:
ALTER SEQUENCE sequence RESTART WITH value;
I suppose (since SERIAL is nonstandard anyway) we could do:
ALTER SEQUENCE ON table(column) RESTART WITH value;
The problem is that we really don't have an easy way of determining if
there is a sequence on table(column) to start with and ONLY that table.
I don't think I'd want to allow that on user sequences at all because
they're often used in stranger ways, and the user doing the alteration
may not know that.
As far as getting dependencies on the sequence, the currently proposed
method of retrieving the next value of a sequence generator is 'NEXT
VALUE FOR sequence' -- but Tom isn't going to like that :)
Might get somewhere by making a special domain thats marked as being
serial, and using that in the column. Create the sequence and tie it to
the domain. Now you know the sequence tied to the column (because it's
on the domain). Just disallow 'special' serial sequences domains to
be used in other ways.
Prevention of the domain from being altered would also help, as you can
then prevent the default from changing.
--
Rod Taylor [EMAIL PROTECTED]
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
[HACKERS] Best setup for RAM drive
Hello, I need some insight on the best way to use a RAM drive in a Postgresql installation. Here is our situation and current setup: Postgresql 7.2.1 Dual PIII 800 RAID 5 SCSI disks Platypus 8GB PCI QikDrive (the RAM drive). http://www.platypus.net The Platypus RAM drive is a PCI card with 8GB of ram onboard with an external power supply so if the main power to the server goes off, the RAM is still powered, so it's persistent between reboots. Currently the disk size of our database is 3.2GB, so we put the whole pgsql directory on the RAM drive. Current preformance is very snappy with the bottleneck being the CPUs. The concern of course is if something happends to the RAM drive we are S.O.L. and have to go to the last backup (pg_dump happens each night). The other concern is if the disk size of the database grows past or near 8gb, we would either have to get a bigger RAM drive or somehow split things betten SCSI and RAM drive. I don't quite grasp the full inner workings of Postgresql, but for those of you who obviously do, is there a better way of setting things up where you could still use the RAM drive for portions of the pgsql directory structure while putting the rest on disk where it's safer? Should we just put pgsql/data/pg_xlog on the RAM drive? Also, in the very near future we will be upgrading to another server, pg7.3.2 with dual P4 2.4 xenon's. The RAM drive will go into this new server. Any suggestions? Thanks Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Problem with foreign keys (performance and deadlocks)
I posted this message to psql-bugs in December but I have not heard if this has been fixed or not. This is holding up our development and testing of our product using Linux and PostgreSQL. Thanks ** ORIGINAL MESSAGE *** Sorry for this being so long but I want to describe this as thoroughly as possible. I am having two problems with foreign keys. One is a performance problem and the other is a deadlock problem but they are related to the same root cause. I am running PostgreSQL 7.3 (the released version). I have isolated it down to a simple test: Given the following database: create table names ( id integer not null, name text not null, primary key (id) ); create table orders ( id integer not null, nameid integer, description text, primary key (id) ); alter table orders add constraint oname foreign key(nameid) references names(id); insert into names values (1,'bob'); insert into names values (2,'fred'); insert into names values (3,'sam'); insert into orders values (1,1,'bob1'); insert into orders values (2,1,'bob2'); insert into orders values (3,1,'bob3'); insert into orders values (4,2,'fred1'); insert into orders values (5,3,'sam1'); To reproduce the bug, start psql on the database in two different shells. In shell A: begin; update orders set description='bob1-1' where id=1; In shell B: begin; update orders set description='bob2-1' where id=2; The update in shell B will blocuntilll you do a commit; or rollback; in shell A. This blocking should not occur. The problem is that the update in shell A causes a SELECT 1 FROM ONLY public.names x WHERE id = $1 FOR UPDATE OF x statement to be generated internally as part of the foreign key checking. For shell A this works fine but when shell B executes this line it blocks until the transaction in shell A does a commit or rollback. The purpose of this SELECT seems to be two-fold: 1. To make sure that row in the target table exists. 2. To make sure that the row does not get deleted or that column in that row does not get changed until the commit happens because other transactions cannot see the changes until the commit happens. As a test I went into backend/utils/adt/ri_triggers.c and removed the FOR UPDATE OF X from the foreign key checks and the concurrency issues disappeared. This still make check 1 happen but removed the safety net of check 2. The FOR UPDATE OF X seems to grab a lock that cannot be shared so the second foreign key select must wait until the first one releases. Is there a weaker lock that can applied to the foreign key check in ri_triggers.c? Is a new type of lock FOR FKEY OF X required? This really drags down our system when we get alot of traffic. It also also causes deadlocks. DEADLOCK The example is a very simple case but in my application where I have more tables and multiple foreign keys I run into deadlocks. In the simplest case I have multiple information tables that are the targets of foreign keys. I have 2 data tables that have foreign keys into the information tables. If I am inserting/updating rows in tables data1 and data2. Here is an example I made up to (hopefully) make this clear: create table names ( id integer not null, name text not null, primary key (id) ); create table billaddr ( id integer not null, address text not null, primary key (id) ); create table shipaddr ( id integer not null, address text not null, primary key (id) ); create table phone_orders ( id integer not null, nameid integer, billid integer, shipid integer, description text, primary key (id) ); alter table phone_orders add constraint poname foreign key(nameid) references names(id); alter table phone_orders add constraint pobaddr foreign key(billid) references billaddr(id); alter table phone_orders add constraint posaddr foreign key(shipid) references shipaddr(id); create table web_orders ( id integer not null, nameid integer, billid integer, shipid integer, description text, primary key (id) ); alter table web_orders add constraint woname foreign key(nameid) references names(id); alter table web_orders add constraint wobaddr foreign key(billid) references billaddr(id); alter table web_orders add constraint wosaddr foreign key(shipid) references shipaddr(id); insert into names values (1,'bob'); insert into names values (2,'fred'); insert into names values (3,'sam'); insert into billaddr values (1,'123 main st'); insert into billaddr values (2,'456 minor ave'); insert into shipaddr values (1,'789 major ct'); insert into shipaddr values (2,'912 losers lane'); insert into phone_orders values (1,1,1,1,'phone order 1'); insert into phone_orders values (2,2,2,2,'phone order 2'); insert into web_orders values (1,1,1,1,'web order 1'); insert into web_orders values (2,2,2,2,'web order 2'); Once again start psql on the database in two different
Re: [HACKERS] XML ouput for psql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The XML standard does not call for any table format. But a number of table formats have been established within the XML framework. Some of them are formatting-oriented (e.g., the HTML model, or CALS which is used in DocBook) and some of them are processing-oriented (e.g., SQL/XML). Which do we need? And which do we need from psql in particular (keeping in mind that psql is primarily for interactive use and shell-scripting)? In any case, it should most likely be a standard table model and not a hand-crafted one. I think all psql needs is a simple output, similar to the ones used by Oracle, Sybase, and MySQL; the calling application should then process it in some way as needed (obviously this is not for interactive use). Where can one find a standard table model? All of the DBs I mentioned (and the perl module DBIx:XML_RDB) all share a similar theme, with subtle differences (i.e. some use row, some row num=x, some have rowset). I'd be happy to write whatever format we can find or develop. My personal vote is the DBIx::XML_RDB format, perhaps with the row number that Oracle uses, producing this: ?xml version=1.0? RESULTSET statement=select * from xmltest ROW num=1 scoops3/scoops flavorchocolate/flavor /ROW ROW num=2 scoops2/scoops flavorvanilla/flavor /ROW /RESULTSET (If, for whatever reason, we go the processing-oriented route, then I claim that there should not be a different output with and without \x mode.) I agree with this. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200303041444 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE+ZQJNvJuQZxSWSsgRArGEAKD4xs+4Ns3syG175T3k80B6MvNJvgCbBkvF hCkf5SMjLzMJ84uMl1w4tMY= =a2Uq -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Updateable views...
Let me preface this by expressing my appreciation for all the hard work for the people who develop, maintain, and support PostGreSQL. I've been using it for a little over two years for a variety of projects and have been extremely happy with both the software and the support on these lists. Recently I began trying to fill in my gaps in understanding the theories underlying database work -- mainly by reading some textbooks and research papers -- and I've had my eyes opened to lot of interesting things I hadn't considered before. Then I began digging around PostGreSQL to see if it offered the tools to play around with these ideas; in many cases it did and I've been happily playing around with them. The one place I haven't been able to use PostGreSQL to experiment is with regards to updateable views. I've found a few threads in -general and -hackers (including one linked from the ToDo list), but they all seem to die out without really reaching any sort of conclusion. I've also seen that in many cases it appears possible to use triggers/rules to simulate updateable views, but that feels like an inelegant solution to me. Are there people working on this topic? I'ld be interested in helping out. Eric ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] XML ouput for psql
* [EMAIL PROTECTED] [EMAIL PROTECTED] [2003-03-04 14:21]: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The XML standard does not call for any table format. But a number of table formats have been established within the XML framework. Some of them are formatting-oriented (e.g., the HTML model, or CALS which is used in DocBook) and some of them are processing-oriented (e.g., SQL/XML). Which do we need? And which do we need from psql in particular (keeping in mind that psql is primarily for interactive use and shell-scripting)? In any case, it should most likely be a standard table model and not a hand-crafted one. I think all psql needs is a simple output, similar to the ones used by Oracle, Sybase, and MySQL; the calling application should then process it in some way as needed (obviously this is not for interactive use). Where can one find a standard table model? All of the DBs I mentioned (and the perl module DBIx:XML_RDB) all share a similar theme, with subtle differences (i.e. some use row, some row num=x, some have rowset). I'd be happy to write whatever format we can find or develop. My personal vote is the DBIx::XML_RDB format, perhaps with the row number that Oracle uses, producing this: ?xml version=1.0? RESULTSET statement=select * from xmltest ROW num=1 scoops3/scoops flavorchocolate/flavor /ROW ROW num=2 scoops2/scoops flavorvanilla/flavor /ROW /RESULTSET (If, for whatever reason, we go the processing-oriented route, then I claim that there should not be a different output with and without \x mode.) I agree with this. I'm interested in creating XML documents that have heirarcy. I can produce the above with Perl. Acually, the difficult part has been getting the information back into the database. Getting it out is a very simple query. I imagine that every language/environment has an SQL-XML library somewhere, but I wasn't able to find something that would go from XML to SQL. I wrote a utility that takes an xml document, and xml configuration file, and writes the document to a PostgerSQL data base using the configuration file to figure out what goes where. The configuration file makes some use of XPath to pluck the correct values out of the xml doucment. I suppose the same code could generate a document, but it is so easy to do using Perl and cgi, I've not bothered. It has some constraints, but it is a very useful utility. I've been able to abosorb XML documents into my PostgreSQL db just by tweeking the configuration file. Currently, I am porting it to C++ from Perl. -- Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Error codes revisited
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 As promised, I've been looking over the error handling (especially the archived discussions) and it's a real rat's nest. :) I'm not sure where we should start, but just getting some error codes enabled and out there would be a great start. The protocol changes can come later. And the codes should not be part of the string. What about a variable that allowed the codes to be switched on so a number is returned instead of a string? This would be off by default so as not to break existing applications. Similarly, we can return other information (FILE, LINE, etc.) with different variables. This should all be doable without a protocol change, as long as everything is returned as a string in a standard format. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200303041516 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE+ZQo2vJuQZxSWSsgRAiKiAKDImuVDD5v4mvY1ClrTo9YrYFlDogCgwz1C Q/DS7rHZ2XWCPuZd8oQoVeA= =ixmb -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Best setup for RAM drive
Chris Sutton kirjutas T, 04.03.2003 kell 17:03: Hello, I need some insight on the best way to use a RAM drive in a Postgresql installation. Here is our situation and current setup: Postgresql 7.2.1 Dual PIII 800 RAID 5 SCSI disks Platypus 8GB PCI QikDrive (the RAM drive). http://www.platypus.net The Platypus RAM drive is a PCI card with 8GB of ram onboard with an external power supply so if the main power to the server goes off, the RAM is still powered, so it's persistent between reboots. Currently the disk size of our database is 3.2GB, so we put the whole pgsql directory on the RAM drive. Current preformance is very snappy with the bottleneck being the CPUs. The concern of course is if something happends to the RAM drive we are S.O.L. and have to go to the last backup (pg_dump happens each night). The other concern is if the disk size of the database grows past or near 8gb, we would either have to get a bigger RAM drive or somehow split things betten SCSI and RAM drive. I don't quite grasp the full inner workings of Postgresql, but for those of you who obviously do, is there a better way of setting things up where you could still use the RAM drive for portions of the pgsql directory structure while putting the rest on disk where it's safer? Should we just put pgsql/data/pg_xlog on the RAM drive? Also, in the very near future we will be upgrading to another server, pg7.3.2 with dual P4 2.4 xenon's. The RAM drive will go into this new server. Any suggestions? The most obvious suggestion is to put the WAL files on RAM disk - these are the things that would most directly affect _write_ performance as these are the only ones the *must* hit the disk befor the transaction can be committed. If you are after read performance, then you just ;) have to determine which are the most frequently used files that are not reasonably cached . --- Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Best setup for RAM drive
On Tue, 4 Mar 2003, Chris Sutton wrote: Hello, I need some insight on the best way to use a RAM drive in a Postgresql installation. Here is our situation and current setup: Postgresql 7.2.1 First suggestion: upgrade to 7.2.4 to address several bugs. The concern of course is if something happends to the RAM drive we are S.O.L. and have to go to the last backup (pg_dump happens each night). If you are concerned, I would definitely backup more often. Increased performance of the disk system will speed up dumps. The other concern is if the disk size of the database grows past or near 8gb, we would either have to get a bigger RAM drive or somehow split things betten SCSI and RAM drive. There has been a lot of talk over the last few years about introducing user-defined storage locations for objects under Postgres. I'm not sure that this will get into 7.4. If it did, I would recommend storing hot tables/indexes (frequently accessed) and all temporary backing files (used for large sorts, joins, etc). The problem is, however, making sure the planner knows that the cost of retrieving a page is different on a solid state disk when compared to a RAID 5 on a PC. You *could* use symlinks, but postgres wont know anything about them: operations on relations/objects such as add, drop, rename, etc will simply unlink the symlink and create a new file on the disk system. Should we just put pgsql/data/pg_xlog on the RAM drive? You need to look at the nature of your database. If it is static, pg_xlog isn't seeing much action. If there is a small amount of modification to data but you can get it all with pg_dump on a frequent basis -- sure, putting pg_xlog on a RAM disk will speed it up. If your database is getting updated often and you cannot afford to lose data during a powerfailure (RAM disk goes down too), then don't put pg_xlog on it. In fact, put nothing important on it. You can get a similar performance increase by turning off fsync() in postgresql.conf -- but, you lose the guarantee of the persistence of your data. Gavin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] test to be ignored ...
---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
