Andrew Sullivan wrote:
Note that, the last time I looked at it, there was no interlock to
ensure that your statement queue (which is basically just a log of
statements as executed on the "master") was not accidentally blown
away by your cleanup process before your target replicas were up to
date.
Robert Treat wrote:
Hmm almost sounds like what you really want is mammoth replicator... lower
level than slony, built into the db, can handle ddl (iirc) not oss
though.
Yes, that may be true but I think I will try out Slony first, as
the design of the DB (DDL) is quite static.
Jim Nasby wrote:
You can work around it right now, too; you just need an external
process that will find the active WAL file and periodically copy it to
the backup. I'm pretty sure there's info in the archives about the
details of setting this up, and there's also the PITRHA project on
pgFound
Jeff Davis wrote:
If it's a statement queue, what happens when you do "INSERT ... VALUES
(random())"? Can the statements be executed out of order on the slave or
are they serialized?
That is very relevant, and my ref to MySQL replication was only the
relatively ease of its setup. And in most
Jeff Davis wrote:
I don't know for sure, but I would guess not any time soon. A PITR
standby works by operating in recovery mode while it's waiting for the
WAL files to arrive. When you bring the database up, you're telling it
there are no more files to wait for, and to finish recovering and star
Jeff Davis wrote:
Standby mode means that the database is kept almost up to date with the
master, but is not "up". When the master goes down, you can bring the
standby machine up. Until then, you unfortunately can't even do read
queries on that machine.
Do you know if this will change in the
Jeff Davis wrote:
8.2 will fix this. You can send the WALs periodically even if they're
not full. In general, PITR will be substantially improved in 8.2 (thanks
Simon!).
This sounds very nice, and this will make PG an even more reliable tool.
The beta should be out soon enough. Download it (
Jeff Davis wrote:
8.2 makes PITR much easier to use for the situation you'd like. In 8.1,
a WAL might sit around for a while before it becomes full and then sent
off. 8.2 allows you to force a WAL to be sent, and it also allows a
standby mode.
This sounds really neat !
To me this sound like
Chander Ganesan wrote:
Keep in mind that while Slony-I provides you with a read-only replica
(able to service queries). At present WAL log replication (in 8.2 or
otherwise) would allow you to have a 'warm standby' type database -
which would be somewhat in sync (pending the latest transactions
Bill Moran wrote:
- No reliability. On slow days, WAL logs could take a long time to
rotate, so small but important transactions might not be replicated
for a long time.
So it is all right for backup but for replication it could end up laking
too much behind, and a fail over could be hou
Csaba Nagy wrote:
That's gone with 8.2, it will be possible to stream the last
modifications, or force a WAL recycle periodically, whatever fits you
better. There is some new infrastructure which allows these things,
although I didn't have the time to play with them.
This sound very nice, wher
Hi ...
I have been trying to find a replication to a payment system at the
company I work, and Slony-I is of cause the first thing that game into
my attention. But when reading chapter 23.3 in the PG manual, there is
this comment of PITR used as a replication tool.
I also saw the "pgpitrha"
Magnus Hagander wrote:
I know that part - I have my tickets already. I meant attendants, not
speakers.
Ups sorry :-)
/BL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Magnus Hagander wrote:
Speaking of which, are there any other interesting (or non-interesting)
people from the community that are gonig to be there? I know I am...
Please take a look for yourself : http://www.linuxforum.dk/2005/en
/BL
---(end of broadcast)
Bruce Momjian wrote:
We hope to have shared row locks in 8.1 for this usage.
Thanks, and good news, and just what I hoped for. Are there some kind af
priority orderet todo list somewhere ?
Looking forward to, see You in copenhagen af LinuxForum 2005.
/BL
---(end of broa
HI ...
I have been using PG for a large project at my work (soon in
production), and I use PG and a lot for FK/RI checks. This works nice
when using one or two threads (cuncurrent connections), but more than
this, and I start getting deadlocks in my FK triggers.
After this, I have been reading
Greg Stark wrote:
I understand that, I just can't see why an index lookup can't be used on "per
row" basis.
Well, how would that work?
Well, good point, the "per row" is a set of data selected as a product
of the "static" part os the query (non volatile parts), the only thing
you can do w
Florian G. Pflug wrote:
Because the _whole_ _point_ of an index is to find matching rows
_without_ scanning the whole table. IF you have to look at every row
anyway, then just might as well to an sequential scan.
I am sorry it took me this long to understand this, but I think I got it
now thanks
Greg Stark wrote:
If Postgres used an index it would call odd(), which would return 1 because
it's the first time, and then Postgres would go look up the rows where col is
1 and return all of them. That's a very different behaviour from if the index
isn't used. If all the records have col=1 then yo
Florian G. Pflug wrote:
Lets say, you have an query "select * from table where field =
function()". Now, according to the sql-spec, you would have to
scan each row in "table", call the function "functio()", and compare the
result. If the result of the call to "function()" matches the value in
"fi
Tom Lane wrote:
No, you'd still end up with a seqscan, because this WHERE clause offers
no chance of matching an index, and we don't do anything special with
stable functions beyond trying to match them to index conditions.
So, the executer uses the (first) value to find the index to use for ALL
Tom Lane wrote:
It has nothing to do with speed, it has to do with giving the correct
answer. We define "correct answer" as being the result you would get
from a naive interpretation of the SQL semantics --- that is, for every
row in the FROM table, actually execute the WHERE clause, and return th
Martijn van Oosterhout wrote:
No, it depends on your interpretation of the query. Note, I'm not up
with the SQL standard so maybe it doesn't work like this, but this is
what I think the problem is.
I just try to learn, so that is ok :-) Tom gave me a solution that
works, so now I struggle to un
Ragnar Hafstað wrote:
this has nothing to do with the return type. a volatile function is a
function that is not garanteed to return the same value given same
input parameters, (such as currval()).
when a volatile function is used thus:
SELECT * FROM mytable WHERE col=myvolatilefunc();
the planner
Michael Glaesemann wrote:
I don't believe it has necessarily anything to do with the return
type, but rather the return value. An index only works if you know
what the value is, and the return value for a volatile function is not
guaranteed to be the same for given parameters. Here's a contrived
Tom Lane wrote:
http://developer.postgresql.org/docs/postgres/xfunc-volatility.html
Ok, thanks I see why there is these three differant function types, but
I don't quite understand why the value from a volatile function, can't
be used as a index key. Is this because there is no return type gar
Michael Fuhr wrote:
currval() is volatile. See "Function Volatility Categories" in the
"Extending SQL" chapter of the documentation and search the list
archives for past discussion of currval()'s volatility.
Hmm, I can't find that chapter in the 7.4 manual, or am I looking the
wrong place ? I
Martijn van Oosterhout wrote:
Not quite, a single index entry needs to point to any number of rows,
which may or may not be visible depending on your transaction, so they
form a sort of linked list. But indeed, not terribly useful for your
purpose...
This make's sense, I keep forgetting the vers
Tom Lane wrote:
This is not legally optimizable into an indexscan, because currval() is
a volatile function. (It's easy to construct cases where its value
actually does change from row to row --- just use a nextval() as well.)
I am not sure what you mean by a "volatile function", and how this
Martijn van Oosterhout wrote:
But where in the documentation did you see anything saying that they
were unique? I imagine you just inferred that from somewhere. I'm not
sure where the documentation should be changed since nowhere actually
recommends them in any way.
Hmm, how about as a comment n
Hi ...
In my quest to get rid of the oid dependency, i have made some new low
level code with the help from many nice people from this community
(thanks for that), but I still have one somewhat big problem.
I am running PG 7.4.6, btw.
I have a "sale" table that have a BIGSERIAL as primary key, b
PFC wrote:
As a sidenote, I have a table with a primary key which is not a
sequence, and this query displays the non-existing sequence name. It
would be easy to check if the sequence exists (yet another join !),
only display sequences that exist ;)...
Hmm, I just tried the same, and got a
Michael Fuhr wrote:
Here's a first attempt at a view that shows tables and their primary
key columns and sequences. I chose a view instead of a function
because a view shows everything in the database with a single query,
which simplifies visual examination of the results. Modify it or
convert it
Alvaro Herrera wrote:
Most system catalogs use OIDs as primary keys. So they cannot just
disappear. But on user tables, there's not a lot of use for them IMHO.
Ok, I think it is about time it is stated more clearly in the documentation.
There's no internal row id on Postgres; having one would
Michael Fuhr wrote:
See the "System Catalogs" chapter in the documentation.
Ok, I think I will compile all the given information in this thread, to
make a new and more non oid'ish solution, as the dataset I manage are
going to grow quite a lot :-)
If you run "psql -E" you'll see the queries t
Alvaro Herrera wrote:
Yeah, though things get hairy that way because you have to peek at
pg_attribute to match the objsubid in pg_depend; and self-join pg_class
to get to the index itself. Not sure if it all can be done in a single
query.
Sounds like my task, to make an oid free insert/select,
Michael Fuhr wrote:
The PostgreSQL documentation discourages the use of OIDs for primary
keys. For example, the "Object Identifier Types" section in the
"Data Types" chapter says:
...
Thanks for taking you the time to snip this together, I think I will try
to find a way to find the propper pri
Martijn van Oosterhout wrote:
It means using OIDs as you described has very well known problems and
they will break on you eventually. You can mitigate the damage by
creating a UNIQUE index on the oid column but you'd better be sure your
application can handle the side-effects.
Ok, Tom told me a
Tom Lane wrote:
The thing you have to worry about is the possibility of duplicate OIDs
once your DB has been running long enough for the OID counter to wrap
around (2^32 OIDs). You should make sure that index is specifically
declared as UNIQUE, so that any attempt to insert a duplicate OID will
fa
Alvaro Herrera wrote:
You can create a function to get the sequence name attached to a table.
Of course, you should take into account the fact that there could be
more than one (two serial fields in a table are rare but not
impossible), but if your tables have only one sequence you should be OK.
Michael Fuhr wrote:
You could query the system catalogs for the table's primary key,
either on the client side or in a server-side function. The
pg_attrdef table even has the default value's nextval() expression
with the sequence name, which could be converted into a currval()
call.
This is not
Michael Fuhr wrote:
PostgreSQL 8.0 will have a pg_get_serial_sequence() function that
returns the sequence name for a particular column so you don't have
to construct it. This is useful when a table or column has been
renamed, in which case the above will probably break.
Quite nice but not what
Christian Kratzer wrote:
why should your application not want to know about the metadata of it's
own tables ? That sounds quite strange when you think about it.
Well, the ideer is to be compatible with mysql at the same level in the
code. This works nicely, as I have descriped, but I am concerned
Michael Glaesemann wrote:
You can use currval() to get the sequence value that was pulled from
your insert. You can check the documentation for usage, as well as
searching the archives for discussions of using OIDs as part of your
database logic.
I know this, but i like not to know anything abou
Hi ...
I am using postgresql 7.4 on a pontencial large DB system, and I am
quite happy of the performance of this database, as for now. Only one
thing worrys me, and I like to get some pease to my mind about this.
I use normal tabel ID (SERIAL and BIGSERIAL) all over the place for FK
constaints
On Thu, 2004-10-07 at 06:13, Scott Frankel wrote:
> PyGreSQL?
I will recommend pygresql, as the only thing don't like about it is its
funny name :-)
It's fast and quite feature complete, and have been used for big
projects like Zope.
/BL
---(end of broadcast)---
On Wed, 2003-09-03 at 22:12, Jonathan Bartlett wrote:
> Are you sure this works after you hit the 4 billion mark?
As long as the returened oid is unique on the table in current session !
/BL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the post
On Wed, 2003-09-03 at 17:28, Martijn van Oosterhout wrote:
> If you know the OID of a row, PostgreSQL doesn't have a special lookup table
> to find it. That's also why they're not unique; the backend would have to
> scan through every table to find out if the next one is available.
Ahh, thats not
On Wed, 2003-09-03 at 16:13, Tom Lane wrote:
> The reason OIDs shouldn't be considered unique is that there is no
> mechanism to enforce that they are unique --- unless you make one,
> that is, create a unique index on OID for a table. The system does
> not do that for you since it would be exces
On Wed, 2003-09-03 at 13:19, Martijn van Oosterhout wrote:
> But your insert function needs to know something about the table it's
> inserting into. The sequences have quite predicatable names. Besides, you
> can set the name yourself (DCL does this IIRC).
No it don't know anything about the table
On Wed, 2003-09-03 at 11:38, Shridhar Daithankar wrote:
> Well, what I do is, declare a serate sequence, retrive next available value and
> explicitly insert it into a integer field. That avoids having to retrieve the
> latest value again.
Yeps, this is what I call an application specific implim
On Tue, 2003-09-02 at 23:32, Tom Lane wrote:
> I don't believe Bruce has yet made any effort to update the SGML
> release-notes file for 7.4. Instead look at the CVS-tip HISTORY file:
> http://developer.postgresql.org/cvsweb.cgi/pgsql-server/HISTORY
Ok thanks, this is very usefull !
/BL
-
On Tue, 2003-09-02 at 22:21, Bruno Wolff III wrote:
> This will be a little easier than checking out a copy from CVS. However,
> I think the HISTORY file has more detail in it.
I think you are right, maybe a cvsview utility would help, regarding
this problem ? But a bugzilla tool, may also be nice
On Tue, 2003-09-02 at 11:55, Shridhar Daithankar wrote:
> That's fixed in 7.4. Vacuum in 7.4 prevents index bloat as well. Couple with
> autovacuum daemon or scheduled vacuums, things (hopefully) will be lot better
> than earlier days..
Are there a list of things that will be done in 7.4, or bet
On Tue, 2003-09-02 at 04:08, Vivek Khera wrote:
> I use it in 24/7/365 system which is heavily written to and read
> from. The drawbacks I have are:
How depressing, may I ask that PG version you are using ?
> 1) upgrade to major versions require dump/restore which is a
>significant amount of
On Fri, 2003-08-29 at 04:11, Martijn van Oosterhout wrote:
> OIDs have never beebn unique, it's just that most databases never get big
> enough to experience wraparound. They are also now optional per table and
> may soon no longer be available by default.
It would be a god idea to remove the oid
On Tue, 2003-08-19 at 23:10, scott.marlowe wrote:
> So the syntaxes are different, and one is apparently swallowed without
> error or anything, but in fact you have no fks in place.
Thanks, that helped.
/BL
---(end of broadcast)---
TIP 8: explain
On Tue, 2003-08-19 at 16:20, Lincoln Yeoh wrote:
> Install an application that can use both DBs. Muck around with it. If you
> can't tell the difference, then I'd say go with postgresql - transactions
> isn't bolted on, quite a number of other design wins too. If you can tell
> the difference a
On Tue, 2003-08-19 at 16:03, Tom Lane wrote:
> It's still bolted on. The entire concept that "transactional integrity
> is optional" is ludicrous, IMHO. "Integrity" and "optional" are
> contradictory.
Good point. Also the problem of MyISAM and InnoDB RI :-)
> One thing you should ask about MySQ
Feite Brekeveld wrote:
> If you have databases that large that they don't fit on a single disk
> (nowdays 75 Gb !) you definitely should consider to look for a RAID level 5
> solution. For instance using 7 disks of which 2 hot-spares. With the 75 G
> disks you would get to about 350 G diskspace w
Hi ...
I have been planning using the PostgreSQL database using a somhow large
database (and therefor some large tables). While doing this i really
like to know if it is possible to split data in a database og even a
table between several disks ?
I have tried to look into this problem, and it lo
61 matches
Mail list logo
