Re: [GENERAL] table as log (multiple writers and readers)

Oh, one other thing On Thu, Apr 17, 2008 at 12:44:51PM +0800, Craig Ringer wrote: > > One way I can think of doing it is to write a seen_log that notes what the > > client has already seen with a timestamp of (say) 1 minute. Then you can > > say "go forward from this time excluding ids (ids her

Re: [GENERAL] table as log (multiple writers and readers)

On Thu, Apr 17, 2008 at 12:44:51PM +0800, Craig Ringer wrote: > It won't work with multiple concurrent writers. There is no guarantee > that an INSERT with a timestamp older than the one you just saw isn't > waiting to commit. This is pretty unlikely -- I won't say impossible, because I'm sure the

Re: [GENERAL] table as log (multiple writers and readers)

Andrew Sullivan wrote: > On Thu, Apr 17, 2008 at 12:35:33AM +0800, Craig Ringer wrote: >> That's subject to the same issues, because a transaction's >> current_timestamp() is determined at transaction start. > > But clock_timestamp() (and its ancestors in Postgres) don't have that > restriction.

Re: [GENERAL] Binary bytea to literal strings

Dragan Zubac wrote: > Hello > > Got one table: > > Column | Type | Modifiers > -+-+--- > message | bytea | > > insert data like for example: > > insert into zub (message) values (E'\004\065 Ciao

Re: [GENERAL] table as log (multiple writers and readers)

On Thu, Apr 17, 2008 at 12:35:33AM +0800, Craig Ringer wrote: > That's subject to the same issues, because a transaction's > current_timestamp() is determined at transaction start. But clock_timestamp() (and its ancestors in Postgres) don't have that restriction. I dunno that it's enough for yo

[GENERAL] Binary bytea to literal strings

Hello Got one table: Column | Type | Modifiers -+-+--- message | bytea | insert data like for example: insert into zub (message) values (E'\004\065 Ciao'); is it possible to somehow get the

Re: [GENERAL] table as log (multiple writers and readers)

brian wrote: I don't mean to rely on *only* the timestamp, but for the reader to remember both the last ID and the timestamp for that particular transaction. When the next read occurs it should check to see if there's an earlier timestamp with a higher ID than that remembered. [snip] Wait-

Re: [GENERAL] Master-master replication with PostgreSQL

On Wed, 16 Apr 2008, Rob Collins wrote: My client has a flawed MS SQL Server system There's another kind? From what I've read, Slony-I does only master-slave replication and Slony-II is not being actively developed. Is this right? Are there any viable master-master replication tools for Pos

Re: [GENERAL] ALTER TABLE DDL Triggers?

On Wed, Apr 16, 2008 at 2:49 PM, Chris Browne <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] ("Richard Broersma") writes: > > I don't believe that DDL Triggers exist, correct? > > That is correct.[1] > > The usual point is that you cannot attach triggers to pg_catalog > tables, which would be the "

Re: [GENERAL] ALTER TABLE DDL Triggers?

[EMAIL PROTECTED] ("Richard Broersma") writes: > I don't believe that DDL Triggers exist, correct? That is correct.[1] The usual point is that you cannot attach triggers to pg_catalog tables, which would be the "obvious" way of trying to notice DDL changes. (e.g. - by having triggers that woul

Re: [GENERAL] Curiosity with catalog table array attribute index dimensions

On Apr 16, 2008, at 4:26 PM, Tom Lane wrote: Erik Jones <[EMAIL PROTECTED]> writes: So, I noticed today that pg_index.indkey index values start at 0 instead of 1 as I'd expected. Are there other cases of this? Shouldn't that be documented somewhere? Well, the CREATE TYPE manual page has F

Re: [GENERAL] Curiosity with catalog table array attribute index dimensions

Erik Jones <[EMAIL PROTECTED]> writes: > So, I noticed today that pg_index.indkey index values start at 0 > instead of 1 as I'd expected. Are there other cases of this? > Shouldn't that be documented somewhere? Well, the CREATE TYPE manual page has For historical reasons (i.e., this is

Re: [GENERAL] How to recovery data from folder data installation?

Ron Mayer wrote: > Magnus Hagander wrote: > > Craig Ringer wrote: > >> with a version of PostgreSQL with the same minor version as the one > >> you were using on the server, eg if you were using 8.1.4 you should > >> get the latest PostgreSQL in the 8.1 series (NOT 8.2 or 8.3) to try > >> to read t

Re: [GENERAL] How to recovery data from folder data installation?

Magnus Hagander wrote: Craig Ringer wrote: with a version of PostgreSQL with the same minor version as the one you were using on the server, eg if you were using 8.1.4 you should get the latest PostgreSQL in the 8.1 series (NOT 8.2 or 8.3) to try to read the data. What you mean here is of cour

[GENERAL] Curiosity with catalog table array attribute index dimensions

So, I noticed today that pg_index.indkey index values start at 0 instead of 1 as I'd expected. Are there other cases of this? Shouldn't that be documented somewhere? Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywher

[GENERAL] ALTER TABLE DDL Triggers?

I don't believe that DDL Triggers exist, correct? Actually I am really curious about what options exist to automatically keep table definitions in sink (for two table) over time: for example a table and history table pair. I realise that History-Entity-Attribute-Value tables don't need this kind

Re: [GENERAL] Storage sizes for dates/times (documentation bug?)

On Wed, Apr 16, 2008 at 08:21:15PM +0100, Sam Mason wrote: > Hum, what's an "EMR"? Sorry, Electronic Medical Record. > Why not do: > > CREATE TYPE tstz AS ( ts TIMESTAMP WITH TIME ZONE, tz TEXT ); > > And use this instead? That should work. At the time (a couple of years ago) I wasn't aware

Re: [GENERAL] table as log (multiple writers and readers)

(I originally missed replying to all here; sorry about the duplicate, Vance, but figured others might be interested. On Wed, Apr 16, 2008 at 1:55 PM, Vance Maverick <[EMAIL PROTECTED]> wrote: > > Another approach would be to queue the log entries in a "staging" table, > so that a single process

Re: [GENERAL] "vacuum" and "cluster"

Jimmy Choi escribió: > Presumably, even if CLUSTER does reindexing internally, it only does > that for the index used for clustering. Since REINDEX includes all > indices, CLUSTER cannot truly replace REINDEX. Correct? No. Cluster rewrites all indexes (otherwise their entries would end up pointin

Re: [GENERAL] Storage sizes for dates/times (documentation bug?)

On Wed, Apr 16, 2008 at 05:09:56PM +0200, Karsten Hilbert wrote: > On Tue, Apr 15, 2008 at 03:31:46PM +0100, Sam Mason wrote: > > But I was under the impression that you didn't want any time zone > > information. > Wrong impression. Doh, yes. > > I must be missing something then, can you explain

Re: [GENERAL] "vacuum" and "cluster"

Presumably, even if CLUSTER does reindexing internally, it only does that for the index used for clustering. Since REINDEX includes all indices, CLUSTER cannot truly replace REINDEX. Correct? Jimmy On Wed, Apr 16, 2008 at 12:06 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Craig Ringer escribió

Re: [GENERAL] table as log (multiple writers and readers)

Craig Ringer wrote: brian wrote: Use a timestamp column also. That's subject to the same issues, because a transaction's current_timestamp() is determined at transaction start. So, in a situation like this: WRITER 1WRITER 2READER 1 BEG

Re: [GENERAL] table as log (multiple writers and readers)

Vance Maverick wrote: Another approach would be to queue the log entries in a "staging" table, so that a single process could move them into the log. This is fairly heavyweight, but it would guarantee the consistent sequencing of the log as seen by a reader (even if the order of entries in the

Re: [GENERAL] Suggestion for psql command interpretation

At 04:46 AM 4/16/2008, Colin Wetherbee wrote: Tom Lane wrote: Colin Wetherbee <[EMAIL PROTECTED]> writes: I just thought I'd report it here in case it wasn't supposed to happen, but from what you say, it seems like it's a "feature". Well, it's more of a historical hangover. Personally I'd not

Re: [GENERAL] generate_series woes

On Wed, Apr 16, 2008 at 09:01:10AM -0400, Merlin Moncure wrote: > On Wed, Apr 16, 2008 at 8:37 AM, Volkan YAZICI <[EMAIL PROTECTED]> wrote: > > hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > > > select i from generate_series(1,10) {hint:10} as i; > > > > > > i'm not proposiung syntax. i'm

Re: [GENERAL] I need to ecrypt one column to an output file

Ralph Smith wrote: I need to do a simple query and output to a file. No problem. But how do I encrypt one column's output? There are lots of developer related links here, and info to "use the /contrib pgcrypto", but I'm not a PG developer and I failed to find any info on HOW TO USE that libr

Re: [GENERAL] table as log (multiple writers and readers)

Craig Ringer wrote: > brian wrote: > > > Use a timestamp column also. > > That's subject to the same issues. [...] > I don't think it's even OK in the case of a single-statement INSERT (where the > transaction is implicit) and/or with the use of clock_timestamp() ... though > I'm less sure about

Re: [GENERAL] Master-master replication with PostgreSQL

Yes, basically there is something that is not theremakedelta is what allow me to do thatI took that information from bucardo mailing list But the important thing is that is possible and maybe one day will be a true grid multimaster replication systemright now I didnt find anyth

Re: [GENERAL] Master-master replication with PostgreSQL

Rodrigo Gonzalez escribió: > Yes and no > > bucardo cannot handle master-master-master > > What I am doing is > > masterA-masterB > masterA-masterC > masterA-masterD Oh, I see -- makes sense. Quite misleading docs, then. -- Alvaro Herrerahttp://www.CommandPro

Re: [GENERAL] Master-master replication with PostgreSQL

Yes and no bucardo cannot handle master-master-master What I am doing is masterA-masterB masterA-masterC masterA-masterD That is something that bucardo can handle and allow me to replicate masterB-masterC but everything goes thru masterA always Alvaro Herrera escribió: Rodrigo Gonzalez

Re: [GENERAL] Master-master replication with PostgreSQL

Rodrigo Gonzalez escribió: > Using star replication I have all the nodes as masterso n > master-master replication with the common DB as master to all the nodes > and all the nodes as master to the common DB Oh, so the Bucardo docs are incorrect. http://www.bucardo.org/bucardo.html#Bucard

Re: [GENERAL] Master-master replication with PostgreSQL

Using star replication I have all the nodes as masterso n master-master replication with the common DB as master to all the nodes and all the nodes as master to the common DB Alvaro Herrera escribió: Julio Cesar Sánchez González wrote: El mié, 16-04-2008 a las 12:13 +0100, Rob Collins

Re: [GENERAL] table as log (multiple writers and readers)

brian wrote: Use a timestamp column also. That's subject to the same issues, because a transaction's current_timestamp() is determined at transaction start. So, in a situation like this: WRITER 1WRITER 2READER 1 BEGIN BEGIN

Re: [GENERAL] table as log (multiple writers and readers)

Vance Maverick wrote: I want to create a table with the semantics of a log. There may be multiple concurrent writers, because log entries will be generated by triggers. And there will be multiple concurrent readers -- mostly remote processes watching the log over time. I'd like to guarantee th

Re: [GENERAL] "vacuum" and "cluster"

Craig Ringer escribió: > It's not stated explicitly, but I'm pretty sure discussion here has > mentioned that too. Given that, VACUUM FULL on a just-CLUSTERed table > should be redundant. It is, and a REINDEX is redundant too because CLUSTER does it internally. -- Alvaro Herrera

[GENERAL] table as log (multiple writers and readers)

I want to create a table with the semantics of a log. There may be multiple concurrent writers, because log entries will be generated by triggers. And there will be multiple concurrent readers -- mostly remote processes watching the log over time. I'd like to guarantee that each of those readers

Re: [GENERAL] "vacuum" and "cluster"

Jimmy Choi wrote: Hello, Does running "cluster" remove the need to run "vacuum"? My understanding is that `CLUSTER' creates a new table file, then swaps it out for the old one. http://www.postgresql.org/docs/8.3/static/sql-cluster.html " During the cluster operation, a temporary copy of th

Re: [GENERAL] Master-master replication with PostgreSQL

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 >> Try with bucardo ("http://bucardo.org/";) may be help you :). > Doesn't bucardo handle conflicts with "whichever one I apply last > wins"? That doesn't seem safe in all situations (or any, IMO). With Bucardo, conflict handling is chosen as

[GENERAL] "vacuum" and "cluster"

Hello, Does running "cluster" remove the need to run "vacuum"? I get a feeling that since cluster is already physically reordering the rows, it may as well remove the dead rows... no? My second question is, if vacuum is still needed, does it matter whether I run vacuum first or cluster first?

Re: [GENERAL] Storage sizes for dates/times (documentation bug?)

On Tue, Apr 15, 2008 at 05:29:09PM +0200, Martijn van Oosterhout wrote: > > Perhaps I confuse this with some limitation of a previous > > implementation of the enum type. Also perhaps I was > > misguided into thinking tags cannot be modified by the > > "don't delete from table of tags" part. > >

Re: [GENERAL] Storage sizes for dates/times (documentation bug?)

On Tue, Apr 15, 2008 at 03:31:46PM +0100, Sam Mason wrote: > But I was under the impression that you didn't want any time zone > information. Wrong impression. > You wanted to know that that an appointment was at 3PM at > the patients local time, ... plus "what does local time mean". > attemptin

[GENERAL] "vacuum" and "cluster"

Hello, Does running "cluster" remove the need to run "vacuum"? I get a feeling that since cluster is already physically reordering the rows, it may as well remove the dead rows... no? My second question is, if vacuum is still needed, does it matter whether I run vacuum first or cluster first? H

Re: [GENERAL] Master-master replication with PostgreSQL

Hello http://www.postgresql.at/english/pr_cybercluster_e.html didn't test it myself though Sincerely Dragan Rob Collins wrote: Hello Dimitri To clarify the requirement: much like you, we're not looking for synchronous replication, which would be too slow. The branches and central server c

Re: [GENERAL] Master-master replication with PostgreSQL

Hi, Le mercredi 16 avril 2008, Rob Collins a écrit : > To clarify the requirement: much like you, we're not looking for > synchronous replication, which would be too slow. The branches and central > server can be different for about 5 to 10 minutes. But the branches need to > be able to function i

Re: [GENERAL] Master-master replication with PostgreSQL

On Apr 16, 2008, at 8:44 AM, Rob Collins wrote: Hello Dimitri To clarify the requirement: much like you, we're not looking for synchronous replication, which would be too slow. The branches and central server can be different for about 5 to 10 minutes. But the branches need to be able to

Re: [GENERAL] Master-master replication with PostgreSQL

Julio Cesar Sánchez González wrote: > > El mié, 16-04-2008 a las 12:13 +0100, Rob Collins escribió: > > We're looking for an open-source database solution that has a Python > > interface and will do master-master replication. My client has a > > flawed MS SQL Server system, with the replication h

Re: [GENERAL] Master-master replication with PostgreSQL

On Apr 16, 2008, at 8:47 AM, Julio Cesar Sánchez González wrote: From what I've read, Slony-I does only master-slave replication and Slony-II is not being actively developed. Is this right? Are there any viable master-master replication tools for PostgreSQL. (They could be commercial/paid for

Re: [GENERAL] Master-master replication with PostgreSQL

Hello Dimitri To clarify the requirement: much like you, we're not looking for synchronous replication, which would be too slow. The branches and central server can be different for about 5 to 10 minutes. But the branches need to be able to function independently if the network or central goes dow

Re: [GENERAL] util/int8.h: missing int8_text() function

Michael Enke wrote: > Hi all, > I migrate from 8.1 to 8.3. > In 8.2.7 and previous there are functions int4_text() and int8_text(), > in 8.3.0 they are missing (not documented that they are removed in the > release notes). > What I'm supposed to use instead of int8_text() now? > (I use my own data

Re: [GENERAL] Master-master replication with PostgreSQL

Rob Collins escribió: Hello We're looking for an open-source database solution that has a Python interface and will do master-master replication. My client has a flawed MS SQL Server system, with the replication hand-coded in Python. They don't want to pay very high licence fees for the MS SQ

Re: [GENERAL] Master-master replication with PostgreSQL

Hi, Le mercredi 16 avril 2008, Rob Collins a écrit : > There is one central server with 19 branches. Some tables need to replicate > from the central server to the branches. Other tables are centralised from > the branches into one totalling table at the centre. A few tables need to > replicate in

Re: [GENERAL] generate_series woes

On Wed, Apr 16, 2008 at 8:37 AM, Volkan YAZICI <[EMAIL PROTECTED]> wrote: > hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > > i think it would be better off not to limit some functionality for > > builtin functions. it would be much nicer to have the ability to hint > > planer about rowc

Re: [GENERAL] Master-master replication with PostgreSQL

El mié, 16-04-2008 a las 12:13 +0100, Rob Collins escribió: > Hello > > We're looking for an open-source database solution that has a Python > interface and will do master-master replication. My client has a > flawed MS SQL Server system, with the replication hand-coded in > Python. They don't wa

Re: [GENERAL] generate_series woes

On Wed, Apr 16, 2008 at 03:37:22PM +0300, Volkan YAZICI wrote: > I'm strongly declined for such non-SQL compliant solutions. I'd be > appreciated if hackers can solve the problem internally, without bugging > SQL syntax. for generate_series - sure. but i have functions which change (in a known way

Re: [GENERAL] generate_series woes

hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > i think it would be better off not to limit some functionality for > builtin functions. it would be much nicer to have the ability to hint > planer about rowcount from function *in* the sql. > > something like: > > select i from generate_serie

Re: [GENERAL] generate_series woes

On Mon, Apr 14, 2008 at 11:21:58AM +0200, Harald Fuchs wrote: > I think there's something sub-optimal with generate_series. > In the following, "documents" is a table with more than 12 rows, > vacuumed and analyzed before the queries. > Since generate_series is a builtin function, can't it tell

Re: [GENERAL] generate_series woes

In article <[EMAIL PROTECTED]>, "Merlin Moncure" <[EMAIL PROTECTED]> writes: > On Mon, Apr 14, 2008 at 5:21 AM, Harald Fuchs <[EMAIL PROTECTED]> wrote: >> I think there's something sub-optimal with generate_series. >> In the following, "documents" is a table with more than 12 rows, >> vacuumed

[GENERAL] Master-master replication with PostgreSQL

Hello We're looking for an open-source database solution that has a Python interface and will do master-master replication. My client has a flawed MS SQL Server system, with the replication hand-coded in Python. They don't want to pay very high licence fees for the MS SQL Server Enterprise version

Re: [GENERAL] Storing and accessing GeoData( Latitude and Longitude ) in PostgreSQL 8.3

Hello, You may want to have a look also at PgSphere (http://pgfoundry.org/projects/pgsphere) and Q3C (http://q3c.sf.net) which is actually a spherical indexing solution built specially for PostgreSQL with the best performance among all other methods (even within other databases). Regards, Ivan

Re: [GENERAL] Not Geography

"In case you are lost with that distinction, geometric data types are based on a flat 2D graph to locate a point, line, rectangle, polygon etc" Yes - this is what I am interested in with the eventual potential of 3D. Bob - Original Message - From: "Shane Ambler" <[EMAIL PROTECTED]>

Re: [GENERAL] Storing and accessing GeoData( Latitude and Longitude ) in PostgreSQL 8.3

Yes, there is a great solution,  take a look at PostGIS:  http://www.postgis.org/ It shurely will do all you want with geodata, but perhaps not as simple as you like... bye...Ludwig  Hello,I have a lot of GeoData( Latitude and Longitude ) in my PostgreSQL Database. Is store them as numeric at this

[GENERAL] Storing and accessing GeoData( Latitude and Longitude ) in PostgreSQL 8.3

Hello, I have a lot of GeoData( Latitude and Longitude ) in my PostgreSQL Database. Is store them as numeric at this time. But now I want to access the data and want to have all Datasets around a Geographic point. eg: "within a

Re: [GENERAL] Not Geography

Bob Pawley wrote: [GENERAL] Not GeographyI have PostgreSQL, c/w the geometry attachment, installed on Win XP. I need to get started using it. I am best able to do that by example, however a simple (geometry for dummies) description or a tool would also help. Are you wanting to use the geometric

Re: [GENERAL] How to recovery data from folder data installation?

Craig Ringer wrote: > Orlando Solarte wrote: > > Hi. > > > > I am trouble with my server postres on linux. It crashed!. I have > > folder postgres installation that was my server. Is possible > > recovery data from folder postgres installation? For example folder > > data? > > Unfortunately you

[GENERAL] Need help to clean up after failed CLUSTER

I'm using PostgreSQL 8.1 ... I had to terminate some clustering before it had completed. I think I have quite a bit of wasted disk space in half-baked table files. I need some suggestions for an easy way to find and clean out the files left over from the failed cluster operations. TIA!