Re: [GENERAL] Could DBmirror be done with the mechine using RPM installation instaed of souce code [tar.gz] ?
I use the two mechines with FC3 and postgresql 8.1 RPM installation and would like to do DBmirror or other postgresql database replication from master to slave server . Could I do this replication using RPM or I must use the tar.gz version ? Could anybody suggest me about replication? There should be a 'postgresql-contrib' rpm for FC3 (use your favourite search engine). If you can't find one - create an rpm (it's pretty easy). That's getting way outside of this mailing list's scope though. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Could DBmirror be done with the mechine using RPM installation instaed of souce code [tar.gz] ?
I try to do it by rpm installation buttheseemed to miss some file which was described by the doc. for ex. pending.so and its' path and perl /./DBmirror.pl could not run Could you suggest me the URL how to install DBmirror by the rpm mechine? Thanks Amrit 2006/2/25, chris smith [EMAIL PROTECTED]: I use the twomechines with FC3 and postgresql 8.1 RPM installation and would like to do DBmirror or other postgresql database replication from master to slave server . Could I do this replication using RPM or I must use the tar.gz version ? Could anybody suggest me about replication?There should be a 'postgresql-contrib' rpm for FC3 (use your favourite search engine).If you can't find one - create an rpm (it's pretty easy). That'sgetting way outside of this mailing list's scope though.
[GENERAL] rotate records
Hello. Im using Postgresql 7.3 on Linux. I created sequence CREATE SEQUENCE event_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 5 START 1 CACHE 1 CYCLE; And I created table which uses this sequence as primary key. CREATE TABLE hw_messages ( event_id INT PRIMARY KEY DEFAULT nextval(event_id_seq ') NOT NULL, device_name varchar(50) NOT NULL CONSTRAINT hw_messages_pkey PRIMARY KEY (event_id) ) WITH OIDS; My question is how I can rotate the records in the table. I have maximum number of records in the table defined by sequence. Every time when I try to insert a new record, I get an error about duplicate key. Even if I manually delete a record somewhere from the middle I still might get this error. If I state CYCLE attribute in the sequence, doesnt it mean that while inserting new records into database if the maximum is met the old records should be deleted? If it is not correct, how can I rotate the records in the table? Thanks you in advance, Natasha.
Re: [GENERAL] How to specify infinity for intervals ?
OK, added to TODO. --- Michael Glaesemann wrote: On Feb 25, 2006, at 12:09 , Bruce Momjian wrote: We have this TODO: o Allow infinite dates just like infinite timestamps Do we need to add intervals to this? I think to be consistent with the other datetime types, might as well. Then one would be able to get an answer to test=# select 'infinity'::timestamp - current_timestamp; ERROR: cannot subtract infinite timestamps Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] rotate records
On Fri, Feb 24, 2006 at 04:31:48PM -0800, Natasha Galkina wrote:
I created sequence
CREATE SEQUENCE event_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 5
START 1
CACHE 1
CYCLE;
[...]
My question is how I can rotate the records in the table. I have maximum
number of records in the table defined by sequence.
Sequences are just number generators; they don't impose constraints
on tables that use them. The sequence's maximum value has no bearing
on the number of records that a table can hold.
Every time when I try to insert a new record, I get an error about
duplicate key. Even if I manually delete a record somewhere from
the middle I still might get this error. If I state CYCLE attribute
in the sequence, doesn't it mean that while inserting new records
into database if the maximum is met the old records should be deleted?
No, CYCLE affects only the values returned by the sequence.
If it is not correct, how can I rotate the records in the table?
One way would be to use a trigger to delete records having the same
event_id as the record being inserted. Here's an example:
CREATE SEQUENCE foo_seq MINVALUE 1 MAXVALUE 3 CYCLE;
CREATE TABLE foo (
id integer PRIMARY KEY DEFAULT nextval('foo_seq')
CHECK (id BETWEEN 1 AND 3),
val text NOT NULL
);
CREATE FUNCTION foo_rotate() RETURNS trigger AS '
BEGIN
DELETE FROM foo WHERE id = NEW.id;
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER foo_insert BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE foo_rotate();
INSERT INTO foo (val) VALUES ('one');
INSERT INTO foo (val) VALUES ('two');
INSERT INTO foo (val) VALUES ('three');
SELECT * FROM foo;
id | val
+---
1 | one
2 | two
3 | three
(3 rows)
INSERT INTO foo (val) VALUES ('four');
INSERT INTO foo (val) VALUES ('five');
SELECT * FROM foo;
id | val
+---
3 | three
1 | four
2 | five
(3 rows)
The CHECK constraint on the primary key ensures that you can't
exceed the record limit by explicitly setting the primary key value.
If you do something like this then be sure to vacuum the table
frequently, as each insert that rotates creates a dead tuple due
to the delete.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Requesting LinuxWorld East staffers
I'm confused about Linux World east, though. I googled it and it looks like it was in Boston last month. It is next month. Ah, found it. LinuxWorld at the Boston Convention and Exhibit Center on April 3-6 2006 Sorry, I'm in NYC area and was hoping it was at the Javits Center. brew == Strange Brew ([EMAIL PROTECTED]) Check out my Stock Option Covered Call website http://www.callpix.com and my Musician's Online Database Exchange http://www.TheMode.com == ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] rotate records
On Sat, Feb 25, 2006 at 10:12:38AM -0700, Michael Fuhr wrote: If it is not correct, how can I rotate the records in the table? One way would be to use a trigger to delete records having the same event_id as the record being inserted. I should mention that with the example I posted you can still get duplicate key violations if enough concurrent transactions insert into the table at the same time. I'll have to think a little more about the best way to avoid that. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] User groups
On Thursday 23 February 2006 20:37, Russell Denney wrote:
Any user groups in the Tampa, St. Petersburg, Florida area? Anyone
interested in starting a group? I am new to PostgreSQL and many aspects
of programming, but I would very much like to meet with those that have
the same interests, regardless of programming skills.
There isn't currently a user group in the Tampa area, but if you're interested
in starting one I know there are a couple companies using postgresql in that
area so I think you could find some other attendees. I have family in that
area, so I'd certainly make an attempt to swing down from Gainesville; you
might also send an invite out to any members of the Tampa PHP users group
(http://php.meetup.com/315/events/?eventId=4836735action=detail), there are
a couple of folks who belong to that who are pg fans. You could make a site
on meetup like the php folks did, or maybe make a site at the pg user group
site. http://pugs.postgresql.org/
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] non-btree primary key
Hello, looks like PostgreSQL (8.0/8.1) has no support for using other-than-btree indexes for primary keys. Is there a (perhaps un(der)documented) way to specify the index type? Rationale: I'm trying to have PKs on a type that defines only the = and operators, and would work with a hash-based PK. I know I could work around it with a UNIQUE INDEX, but would like to avoid that for exactly the reason described in the manual: : Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT : NULL, but identifying a set of columns as primary key also provides : metadata about the design of the schema, as a primary key implies : that other tables may rely on this set of columns as a unique : identifier for rows. Please cc me on replies, I have problems with the postgresql.org majordomo. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to specify infinity for intervals ?
On Fri, Feb 24, 2006 at 10:09:25PM -0500, Bruce Momjian wrote: Karsten Hilbert wrote: I will also stay with the hope that one day before long we will have 'infinite'::interval. We have this TODO: o Allow infinite dates just like infinite timestamps Do we need to add intervals to this? Yes. Thanks. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] non-btree primary key
Roman Neuhauser [EMAIL PROTECTED] writes: looks like PostgreSQL (8.0/8.1) has no support for using other-than-btree indexes for primary keys. Is there a (perhaps un(der)documented) way to specify the index type? No --- at present this is not possible because btree is the only index type that implements uniqueness checking. If that got fixed, we might consider adding syntax to allow PK specification of index type. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] createuser permssion for group
Is it possible to give a group the the createuser permission ? This way if a user in the group they can create users. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] upgrade PostgreSQL 8.x on production FreeBSD
Thank you all for the valuable advices. I'll try them on a test server and will come back. Thanks again! Iv ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Could DBmirror be done with the mechine using RPM installation instaed of souce code [tar.gz] ?
On 2/26/06, Amrit Angsusing [EMAIL PROTECTED] wrote: I try to do it by rpm installation but the seemed to miss some file which was described by the doc. for ex. pending.so and its' path and perl /./DBmirror.pl could not run If you have a problem copy the exact error message, nobody can help you with that message. Could you suggest me the URL how to install DBmirror by the rpm mechine? Google? rpm -ivh /path/to/rpm.rpm That will install the rpm, then you need to set it up. Read the appropriate documentation (ie the docs included with dbmirror) on how to do it. 2006/2/25, chris smith [EMAIL PROTECTED]: I use the two mechines with FC3 and postgresql 8.1 RPM installation and would like to do DBmirror or other postgresql database replication from master to slave server . Could I do this replication using RPM or I must use the tar.gz version ? Could anybody suggest me about replication? There should be a 'postgresql-contrib' rpm for FC3 (use your favourite search engine). If you can't find one - create an rpm (it's pretty easy). That's getting way outside of this mailing list's scope though. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ltree + gist index performance degrades significantly over a night
On Fri, Feb 24, 2006 at 09:02:04AM -0800, CG wrote: I have a search table which I use for partial-match text searches: snip For a string Hello World the ltree is created like 'h.e.l.l.o.w.o.r.l.d' ... If I wanted to find all rows with orl in them i would construct an lquery like '*.o.r.l.*' and use the ~ operator in the where clause. I would link to the table items by the item_id ... Is there some reason you can't use tsearch2? I suspect it would probably work better; if nothing else you'd probably get better support since a lot more people use it. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Temporal Databases
On Fri, Feb 24, 2006 at 11:56:58AM -0500, Brad Nicholson wrote: Simon Riggs wrote: A much easier way is to start a serialized transaction every 10 minutes and leave the transaction idle-in-transaction. If you decide you really need to you can start requesting data through that transaction, since it can see back in time and you already know what the snapshot time is (if you record it). As time moves on you abort and start new transactions... but be careful that this can effect performance in other ways. We're currently prototyping a system (still very much in it's infancy) that uses the Slony-I shipping mechanism to build an off line temporal system for point in time reporting purposes. The idea being that the log shipping files will contain only the committed inserts, updates and deletes. Those log files are then applied to an off line system which has a trigger defined on each table that re-write the statements, based on the type of statement, into a temporally sensitive format. If you want to get an exact point in time snapshot with this approach, you are going to have to have timestamps on all table in your source database that contain the exact time of the statement table. Otherwise, a best guess (based on the time the slony sync was generated) is the closest that you will be able to come. Have you looked at using timelines in PITR for stuff like this? Depending on your needs, it might be less work to do it this way. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Postgres Win32 libpq Samples
Hello, I am very new to postgreSQL I am using the win32 platform is there a website URL that has Sample Client Apps written for win32? I need Samples of very basic things like: Connect to a db on a remote server create new db create table add records drop records ect... there are some Samples here http://www.postgresql.org/docs/8.1/static/libpq-example.html but they will not compile on win32 using VC++ 7 Thank you VERY much in advance for any links to Samples Sam ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] createuser permssion for group
On 2/26/06, Jebus [EMAIL PROTECTED] wrote: Is it possible to give a group the the createuser permission ? This way if a user in the group they can create users. Version 8.1.x does.. postgres=# \h create group ... | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] From ASCII to UTF-8
As part of a migration from 8.0 to 8.1 i want to convert the data from ASCII to UTF-8. I dumped the database with pg_dump (8.0) and tried to convert it with iconv, but it shows an error: $ iconv -t ASCII -t UTF-8 fahstats_data.dump -o fahstats_data_utf-8.dump iconv: illegal input sequence at position 71407864 That position contains the decimal value 233: $ od -A d -j 71407864 -N 1 -t u1 fahstats_data.dump 71407864 233 71407865 I could use pg_dump -E in 8.1 but it is in another machine with ADSL connection and the dump size is 1.8GB. It would take more than 12 hours. How to install pg_dump 8.1 only? I tried to copy the executable and the libs but it did not work. Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] From ASCII to UTF-8
Clodoaldo Pinto wrote: As part of a migration from 8.0 to 8.1 i want to convert the data from ASCII to UTF-8. ASCII is a subset of UTF-8, so if you really wanted to do that you wouldn't need to do anything. I dumped the database with pg_dump (8.0) and tried to convert it with iconv, but it shows an error: $ iconv -t ASCII -t UTF-8 fahstats_data.dump -o ^^ ^^ Mistake? fahstats_data_utf-8.dump iconv: illegal input sequence at position 71407864 That position contains the decimal value 233: Well, that is not an ASCII character, so you need to use a different source encoding for iconv. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] ECPG and COPY and PQputCopyData - don't get errors
Since I can't use ECPG COPY, I'm trying to use libpq PQputCopyData. Not realizing I had to use CSV format to include column header names, I used: PQexec(conn, COPY detail FROM stdin) I sent the column headers as the first line of the input. While this I now know this is wrong, the problem here is that I got no error response back. In the postmaster log, I see: ERROR: invalid input syntax for type numeric: messageid CONTEXT: COPY detail, line 1, column messagesize: messageid However, every call to PQputCopyData, as well as PQputCopyEnd returned a result of 1. Ok, so I fixed the above error, but any other data load errors are only showing up in the postgres log file. I tried defining PQsetNoticeReceiver(conn, pgsqlNoticeReceiver, (void *) dummy); For testing, pgsqlNoticeReceiver just writes a message and aborts. However, pgsqlNoticeReceiver is never called. What am I missing? I need to abort the transaction on any errors in the COPY. Wes ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] postgresql documentation
Hi, I've noticed that the PDF version of the manuals for 8.0 and 8.1 are lacking bookmarks and/or TOC and document reference links. If this is generated via LaTeX, such links oculd easily be incorporated via the hyperref package. It would make the document much easier to navigate. -- % Randy Yates % Midnight, on the water... %% Fuquay-Varina, NC% I saw... the ocean's daughter. %%% 919-577-9882% 'Can't Get It Out Of My Head' [EMAIL PROTECTED] % *El Dorado*, Electric Light Orchestra http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql documentation
Randy Yates wrote: Hi, I've noticed that the PDF version of the manuals for 8.0 and 8.1 are lacking bookmarks and/or TOC and document reference links. If this is generated via LaTeX, such links oculd easily be incorporated via the hyperref package. It would make the document much easier to navigate. The PDF version is docbook-tex-pdf .. if you can make bookmarks have at it :) Warning... docbook-tex with our documentation can take days. Joshua D. Drake -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql documentation
Joshua D. Drake [EMAIL PROTECTED] writes: Randy Yates wrote: Hi, I've noticed that the PDF version of the manuals for 8.0 and 8.1 are lacking bookmarks and/or TOC and document reference links. If this is generated via LaTeX, such links oculd easily be incorporated via the hyperref package. It would make the document much easier to navigate. The PDF version is docbook-tex-pdf .. if you can make bookmarks have at it :) Warning... docbook-tex with our documentation can take days. Joshua D. Drake Hi Joshua, Well, it's tempting! Can you tell me how, using FC4, I can grab the documentation build environment from the repository and download it to my machine? I would love to be able to make a small contribution to such a fantastic project. -- % Randy Yates % Watching all the days go by... %% Fuquay-Varina, NC% Who are you and who am I? %%% 919-577-9882% 'Mission (A World Record)', [EMAIL PROTECTED] % *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql documentation
On Feb 26, 2006, at 12:30 , Randy Yates wrote: Can you tell me how, using FC4, I can grab the documentation build environment from the repository and download it to my machine? You can find instructions on how to build the documentation in the documentation in Appendix G: http://www.postgresql.org/docs/current/interactive/docguide.html I'm not familiar with Fedora, but hopefully you'll be able to use rpms or even build the tools you need from source. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Temporal Databases
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Jim C. Nasby) wrote: On Fri, Feb 24, 2006 at 11:56:58AM -0500, Brad Nicholson wrote: Simon Riggs wrote: A much easier way is to start a serialized transaction every 10 minutes and leave the transaction idle-in-transaction. If you decide you really need to you can start requesting data through that transaction, since it can see back in time and you already know what the snapshot time is (if you record it). As time moves on you abort and start new transactions... but be careful that this can effect performance in other ways. We're currently prototyping a system (still very much in it's infancy) that uses the Slony-I shipping mechanism to build an off line temporal system for point in time reporting purposes. The idea being that the log shipping files will contain only the committed inserts, updates and deletes. Those log files are then applied to an off line system which has a trigger defined on each table that re-write the statements, based on the type of statement, into a temporally sensitive format. If you want to get an exact point in time snapshot with this approach, you are going to have to have timestamps on all table in your source database that contain the exact time of the statement table. Otherwise, a best guess (based on the time the slony sync was generated) is the closest that you will be able to come. Have you looked at using timelines in PITR for stuff like this? Depending on your needs, it might be less work to do it this way. No. That would require taking a whole lot of PITR snapshots in order to get answers for previous days and such. We've got applications that are pretty stateful, where the point of temporalizing is that this allows achieving detailed history that transforms the data into a fundamentally richer form. The point of the exercise isn't to occasionally be able to look at how things were ten minutes ago. For online systems running 24x7, it's implausible that we'd get questions within ten minutes such that such a slightly-old transaction could be of any use. It is, instead, to be able to much more comprehensively look at all [available] historical states over a fairly substantial period of time. -- output = (cbbrowne @ gmail.com) http://cbbrowne.com/info/postgresql.html ASSEMBLER is a language. Any language that can take a half-dozen keystrokes and compile it down to one byte of code is all right in my books. Though for the REAL programmer, assembler is a waste of time. Why use a compiler when you can code directly into memory through a front panel. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql documentation
Michael Glaesemann wrote: On Feb 26, 2006, at 12:30 , Randy Yates wrote: Can you tell me how, using FC4, I can grab the documentation build environment from the repository and download it to my machine? You can find instructions on how to build the documentation in the documentation in Appendix G: http://www.postgresql.org/docs/current/interactive/docguide.html I'm not familiar with Fedora, but hopefully you'll be able to use rpms or even build the tools you need from source. Fedora has rpms for all the docbook stuff... Look for docbook-tools I think it is. Joshua D. Drake Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
