Primary Key

"P. Dwayne Miller" <dmiller@espgroup.net> writes:

CREATE TABLE message
(
int4 msgid not null,
text msgtext,
CONSTRAINT cons_001_pk PRIMARY KEY on (msgid)
);

The first two actually create a PRIMARY KEY on msgid. The third seems
to have a PRIMARY KEY on 'oid', not 'msgid', though it does create a
unique index on 'msgid'.

After fixing the several obvious syntax errors, it works fine for me:

regression=# CREATE TABLE message
regression-# (
regression(# msgid int4 not null,
regression(# msgtext text,
regression(# CONSTRAINT cons_001_pk PRIMARY KEY (msgid)
regression(# );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'cons_001_pk' for table 'message'
CREATE
regression=# \d message
Table "message"
Attribute | Type | Modifier
-----------+---------+----------
msgid | integer | not null
msgtext | text |
Primary Key: cons_001_pk

regression=#

Is Cold Fusion perhaps doing strange things to the query behind your
back? None of those CREATE TABLE commands are legal SQL according
to my references.

regards, tom lane

My bad on the syntax of all three. I used your syntax (which is what I had originally used) and
got the same results with the \d command that you show.

I'm only using Cold Fusion to read data from the resulting table, not create the table... and I
still get an error when I have created the primary key using the table constraint syntax. Cold
Fusion is reporting that the primary key has been defined for the column oid. Using the correct
syntax with the first two CREATE TABLE statements, Cold Fusion reports the primary key field as
msgid.

Thanks for your reply,
Dwayne

Tom Lane wrote:

Tom Lane wrote:

After fixing the several obvious syntax errors, it works fine for me:

regression=# CREATE TABLE message
regression-# (
regression(# msgid int4 not null,
regression(# msgtext text,
regression(# CONSTRAINT cons_001_pk PRIMARY KEY (msgid)
regression(# );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'cons_001_pk' for table 'message'
CREATE
regression=# \d message
Table "message"
Attribute | Type | Modifier
-----------+---------+----------
msgid | integer | not null
msgtext | text |
Primary Key: cons_001_pk

regression=#

Is Cold Fusion perhaps doing strange things to the query behind your
back? None of those CREATE TABLE commands are legal SQL according
to my references.

I've been using the syntax "PRIMARY KEY (/column_name/ [, /column_name/
])," without the constraint name, and the "/COLUMN_NAME TYPE/ PRIMARY
KEY" syntax for sometime now. I may be admitting to SQL heresy in
saying that; but, that's the syntax I've seen in MySQL and in quite a
few SQL/database books.

AFIAK, it's a legal table creation statement.

Thomas Swan <tswan-lst@ics.olemiss.edu> writes:

AFIAK, it's a legal table creation statement.

The variant I showed is. The original one had an extraneous "ON" in the
FOREIGN KEY clause, and even more to the point all the column
declarations had column name and type name reversed. That's why I was
questioning the syntax ...

regards, tom lane

I'm new to this list, and Postgresql, and could use some advice from you
experienced users. We are very interested in making postrgresql work for
our project, but its missing one big feature, that is absolutely necessary
for a true OLTP shop.

Even more important that uptime to us, is to never put ourselves in a
position where we could lose data. I understand I can do a hot backup with
pg_dumpall. What we need on top of that is the ability to replay the
transaction logs against the previous database archive. Without such a
feature, even if I did a full backup a few times a day, we would be
vulnerable to losing hours of data (which would not be acceptable to our
users).

I can tell this has been designed to do exactly that, because its really
close. What would be needed is a hook to write the logs to disk/tape, when
they are full (and not overwrite them until they go elsewhere), and, the
ability to actually play back the logs, exactly at the right place, tied to
a specific archive.

I'm sure this is something that would benefit all our lives. Other than
just hiring a consultant to do so, is there some way to make this
happen? Other than eliminating all my single points of failover in the
hardware, is there some other way to solve this problem?

Thanks,
Naomi
--
Naomi Walker
Chief Information Officer
Eldorado Computing, Inc.
602-604-3100 ext 242

"P. Dwayne Miller" wrote:

My bad on the syntax of all three. I used your syntax (which is what I had originally used) and
got the same results with the \d command that you show.

I'm only using Cold Fusion to read data from the resulting table, not create the table... and I
still get an error when I have created the primary key using the table constraint syntax. Cold
Fusion is reporting that the primary key has been defined for the column oid. Using the correct
syntax with the first two CREATE TABLE statements, Cold Fusion reports the primary key field as
msgid.

SQLPrimaryKey() in the current psqlodbc driver doesn't
report the Primary key other than tablename_pkey.
It seems the cause.
I would change the implementatin of SQLPrimaryKey().
Dwayne, could you try the modified driver ?

regards,
Hiroshi Inoue

I can try it. Where do I get it.

My question would be why, if SQLPrimaryKey() only reported tablename_pkey, then why does my front end
return oid as the primary key?

Thanks,
Dwayne

Hiroshi Inoue wrote:

"P. Dwayne Miller" wrote:

"P. Dwayne Miller" wrote:

I can try it. Where do I get it.

I would send you the dll though I don't test it by myself.
OK ?

My question would be why, if SQLPrimaryKey() only reported tablename_pkey, then why does my front end
return oid as the primary key?

Don't you turn on the FAKEOIDINDEX option ?

regards,
Hiroshi Inoue

On Tue, 19 Jun 2001, Naomi Walker wrote:

Even more important that uptime to us, is to never put ourselves in a
position where we could lose data. I understand I can do a hot backup
with pg_dumpall. What we need on top of that is the ability to replay
the transaction logs against the previous database archive. Without
such a feature, even if I did a full backup a few times a day, we
would be vulnerable to losing hours of data (which would not be
acceptable to our users).

This is what I'd like too (though I'm not that bothered about
rolling forward from a dump if I can just do it by replaying
logs onto real datafiles).

I mentioned it a while ago:

http://fts.postgresql.org/db/mw/msg.html?mid=114397

but got no response.

You are aware that you can still lose up to (by default) 16Mb
worth of transactions in this scheme, I presume?

Matthew.

Please send it. And yes, I do have the Fake OID Index turned on. Although I have no idea what it does.

Thanks,
Dwayne

Hiroshi Inoue wrote:

Hmm, your using ColdFusion, so that goes through the ODBC driver, which
picks up the 'primary key' by looking for an index named 'foo_pkey',
I think. Ah, here it is:

in interfaces/odbc/info.c:

sprintf(tables_query, "select ta.attname, ia.attnum"
" from pg_attribute ta, pg_attribute ia, pg_class c, pg_index i"
" where c.relname = '%s_pkey'"
" AND c.oid = i.indexrelid"
" AND ia.attrelid = i.indexrelid"
" AND ta.attrelid = i.indrelid"
" AND ta.attnum = i.indkey[ia.attnum-1]"
" order by ia.attnum", pktab);

So, don't name the primary key constraint, or name it 'something_pkey'
and you should be fine. Something's falling back to trying to use
oid if it can't find a primary key: I'm note sure if that's inside the
ODBC driver, or in ColdFusion.

Hmm, seems we have other Access specific hacks in the ODBC driver:

/*
* I have to hide the table owner from Access, otherwise it
* insists on referring to the table as 'owner.table'. (this
* is valid according to the ODBC SQL grammar, but Postgres
* won't support it.)
*
* set_tuplefield_string(&row->tuple[1], table_owner);
*/

I bet PgAdmin would like to have that info.

Ross

At 12:26 PM 6/20/01 +0100, Matthew Kirkwood wrote:

On Tue, 19 Jun 2001, Naomi Walker wrote:

Even more important that uptime to us, is to never put ourselves in a
position where we could lose data. I understand I can do a hot backup
with pg_dumpall. What we need on top of that is the ability to replay
the transaction logs against the previous database archive. Without
such a feature, even if I did a full backup a few times a day, we
would be vulnerable to losing hours of data (which would not be
acceptable to our users).

This is what I'd like too (though I'm not that bothered about
rolling forward from a dump if I can just do it by replaying
logs onto real datafiles).

I mentioned it a while ago:

http://fts.postgresql.org/db/mw/msg.html?mid=114397

but got no response.

Well, so now there is at least TWO of us....

We should start the thread again.

You are aware that you can still lose up to (by default) 16Mb
worth of transactions in this scheme, I presume?

I'm just starting with Postgresql, but, I thought with fsync on this was
not the case. Is that not true or what else did I miss?

Matthew.

--
Naomi Walker
Chief Information Officer
Eldorado Computing, Inc.
602-604-3100 ext 242

At 13:41 20/06/01 -0700, Naomi Walker wrote:

Well, so now there is at least TWO of us....

We should start the thread again.

WAL based backup & recovery is something I have been trying to do in
background, but unfortunately I have no time at the moment. I do plan to
get back to it as soon as I can.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

On Wed, 20 Jun 2001, Naomi Walker wrote:

You are aware that you can still lose up to (by default) 16Mb
worth of transactions in this scheme, I presume?

I'm just starting with Postgresql, but, I thought with fsync on this
was not the case. Is that not true or what else did I miss?

I suppose that it rather depends on how you expected to
move the logs over. My approach was to archive the redo
when PG is done with them and only then to roll them
forward.

If a catastrophe occurs, then I wouldn't be able to do
anything with a half-full log.

Our Oracle setups use redo logs of only 1Mb for this
reason, and it doesn't seem to hurt too much (though
Oracle's datafile formats seem a fair bit denser than
Postgres's).

Matthew.

On Thu, Jun 21, 2001 at 11:01:29AM +0100, Matthew Kirkwood wrote:

On Wed, 20 Jun 2001, Naomi Walker wrote:

You are aware that you can still lose up to (by default) 16Mb
worth of transactions in this scheme, I presume?

I'm just starting with Postgresql, but, I thought with fsync on this
was not the case. Is that not true or what else did I miss?

I suppose that it rather depends on how you expected to
move the logs over. My approach was to archive the redo
when PG is done with them and only then to roll them
forward.

If a catastrophe occurs, then I wouldn't be able to do
anything with a half-full log.

Our Oracle setups use redo logs of only 1Mb for this
reason, and it doesn't seem to hurt too much (though
Oracle's datafile formats seem a fair bit denser than
Postgres's).

The above makes no sense to me. A hot recovery that discards some
random number of committed transactions is a poor sort of recovery.

Ms. Walker might be able to adapt one of the several replication
tools available for PG to do replayable logging, instead.

It seems to me that for any replication regime (symmetric or not,
synchronous or not, global or not), and also any hot-backup/recovery
approach, an update-log mechanism that produces a high-level
description of changes is essential. Using triggers to produce
such a log seems to me to be too slow and too dependent on finicky
administrative procedures.

IIUC, the regular WAL records are optimized for a different purpose:
speeding up normal operation. Also IIUC, the WAL cannot be applied
to a database reconstructed from a dump. If augmented to enable such
reconstruction, the WAL might be too bulky to serve well in that role;
it currently only needs to keep enough data to construct the current
database from a recent checkpoint, so compactness is has not been
crucial. But there's much to be said for having just a single
synchronous log mechanism. A high-level log mixed into the WAL, to
be extracted asynchrously to a much more complact replay log, might
be the ideal compromise.

The same built-in high-level logging mechanism could make all the
various kinds of disaster prevention, disaster recovery, and load
sharing much easier to implement, because they all need much the
same thing.

Nathan Myers
ncm@zembu.com

On Thu, 21 Jun 2001, Nathan Myers wrote:

I suppose that it rather depends on how you expected to
move the logs over. My approach was to archive the redo
when PG is done with them and only then to roll them
forward.

The above makes no sense to me. A hot recovery that discards some
random number of committed transactions is a poor sort of recovery.

Agreed. Nevertheless it's at least db_size - 1Mb better
than the current options.

Recovering the rest manually from log files is good enough
for us (indeed, much better than the potential loss of
performance or reliability from "real" replication).

If it horrifies you that much, think of it as 15-minutely
incremental backups.

Matthew.

matthew@hairy.beasts.org (Matthew Kirkwood) wrote in message news:<Pine.LNX.4.33.0106201212240.25630-100000@sphinx.mythic-beasts.com>...

On Tue, 19 Jun 2001, Naomi Walker wrote:

Even more important that uptime to us, is to never put ourselves in a
position where we could lose data. I understand I can do a hot backup
with pg_dumpall. What we need on top of that is the ability to replay
the transaction logs against the previous database archive. Without
such a feature, even if I did a full backup a few times a day, we
would be vulnerable to losing hours of data (which would not be
acceptable to our users).

This is what I'd like too (though I'm not that bothered about
rolling forward from a dump if I can just do it by replaying
logs onto real datafiles).

With stock PostgreSQL... how many committed transactions can one lose
on a simple system crash/reboot? With Oracle or Informix, the answer
is zero. Is that true with PostgreSQL in fsync mode? If not, does it
lose all in the log, or just those not yet written to the DB?

Thanks

John

With stock PostgreSQL... how many committed transactions can one

lose

on a simple system crash/reboot? With Oracle or Informix, the answer
is zero. Is that true with PostgreSQL in fsync mode? If not, does it
lose all in the log, or just those not yet written to the DB?

With WAL the theory is that it will not lose a committed transaction.
Bugs have plagged previous versions (7.1.2 looks clean) and it none
(Oracle, Informix, Postgres) can protect against coding errors in the
certain cases but from general power failure it's fine.

This assumes adequate hardware too. Some harddrives claim to have
written when they haven't among other things, but Postgres itself
won't lose the information -- your hardware might :do that silently
though.)