Обсуждение: Frontend/Backend protocol changes?
Have there been any changes at all in the Frontend/Backend protocol between 7.2 and 7.3? I ask, of course, because there were some unannounced changed to the protocol between 7.1 and 7.2, and I'd rather not get bitten again ;-) BTW, I maintain the open source PostgreSQL drivers used with a number of (commercial and open source) Smalltalk implementations. Thanks, Bruce Badger ljb wrote: > zhouhanok@vip.sina.com wrote: > > >> Hi, >> >> Could you tell me what's the latest postgresql protocol version? >> 2.3 or 2.= >> 4 or ...? >> > > > 2.0 > > > >> And where can I find the latest specification(details) about the >> protocol?= >> Is http://www.postgresql.org/idocs/index.php?protocol.html the latest >> one? >> > > > Yes that's the one. > It's also in the source distribution, installed in .../doc/html/ > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
No protocol changes I can think of. --------------------------------------------------------------------------- Bruce Badger wrote: > > Have there been any changes at all in the Frontend/Backend protocol > between 7.2 and 7.3? > > I ask, of course, because there were some unannounced changed to the > protocol between 7.1 and 7.2, and I'd rather not get bitten again ;-) > > BTW, I maintain the open source PostgreSQL drivers used with a number of > (commercial and open source) Smalltalk implementations. > > Thanks, > Bruce Badger > > ljb wrote: > > > zhouhanok@vip.sina.com wrote: > > > > > >> Hi, > >> > >> Could you tell me what's the latest postgresql protocol version? > >> 2.3 or 2.= > >> 4 or ...? > >> > > > > > > 2.0 > > > > > > > >> And where can I find the latest specification(details) about the > >> protocol?= > >> Is http://www.postgresql.org/idocs/index.php?protocol.html the latest > >> one? > >> > > > > > > Yes that's the one. > > It's also in the source distribution, installed in .../doc/html/ > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Badger <bbadger@BadgerSE.com> writes:
> Have there been any changes at all in the Frontend/Backend protocol
> between 7.2 and 7.3?
> I ask, of course, because there were some unannounced changed to the
> protocol between 7.1 and 7.2, and I'd rather not get bitten again ;-)
"Unannounced"? AFAIK doc/src/sgml/protocol.sgml has been current for
every release since 6.3 or thereabouts.
regards, tom lane
Tom Lane wrote: > Bruce Badger <bbadger@BadgerSE.com> writes: > > Have there been any changes at all in the Frontend/Backend protocol > > between 7.2 and 7.3? > > I ask, of course, because there were some unannounced changed to the > > protocol between 7.1 and 7.2, and I'd rather not get bitten again ;-) > > "Unannounced"? AFAIK doc/src/sgml/protocol.sgml has been current for > every release since 6.3 or thereabouts. As I remember, he got bitten by a change that wasn't prominent in our release notes. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: >Tom Lane wrote: > > >>Bruce Badger <bbadger@BadgerSE.com> writes: >> >> >>>Have there been any changes at all in the Frontend/Backend protocol >>>between 7.2 and 7.3? >>>I ask, of course, because there were some unannounced changed to the >>>protocol between 7.1 and 7.2, and I'd rather not get bitten again ;-) >>> >>> >>"Unannounced"? AFAIK doc/src/sgml/protocol.sgml has been current for >>every release since 6.3 or thereabouts. >> >> > >As I remember, he got bitten by a change that wasn't prominent in our >release notes. > > I do watch this list for any discussion of changes to the protocol, but from Tom's response I guess I should also be looking at protocol.sgml. Could someone point me to the definitive version ofthis file so I can monitor changes to it? Thanks. BTW, I think the main reason I got bitten at 7.2 was because the protocol changed, but the protocol version number did not. While I'm on the keyboard, I'd like to say how much I appreciate the work that Tom, Bruce et al put into making PostgreSQL the system it is. Thanks for a really great job. All the best, Bruce Badger
Bruce Badger wrote: > I do watch this list for any discussion of changes to the protocol, but > from Tom's response I guess I should also be looking at protocol.sgml. > Could someone point me to the definitive version of this file so I can > monitor changes to it? Thanks. > > BTW, I think the main reason I got bitten at 7.2 was because the > protocol changed, but the protocol version number did not. > > While I'm on the keyboard, I'd like to say how much I appreciate the > work that Tom, Bruce et al put into making PostgreSQL the system it is. > Thanks for a really great job. What exactly was the change? As I remember, it was something we didn't think would affect anybody, but it did affect you. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Hey all, I'm not sure if this is the correct list to ask this question, so I appologize ahead of time if it is misguided. I'm using DBD::Pg to insert large amounts of data into a Postgres installation. We will occassionally (once every few weeks, perhaps) get new chip layouts that need to be added. Each layout may have up to 250k spots which go into a Spot table. My current code is glacially slow - on an otherwise zippy dual-processor P4, it seems this insert will take 3 days. I've gotten a bit of feedback from the Perl dbi-users list: 1) Transactions: My current approach was to do this inside a transaction, but apparently the write-ahead-logging will nothandle 250k logged inserts well. Is this true, and I should commit after every 20 or so spots? 2) Indices: apparently every insert updates the indices on the table. From my reading of the documentation, the indicesaren't updated inside a transaction, but instead at the end. 3) COPY: I could use COPY, but apparently triggers are not, well, triggered under COPY. Is this true? I have datestamps on my inserts for audit trails. Thanks ahead of time for any help, jas.
"Jason E. Stewart" <jason@openinformatics.com> writes:
> I've gotten a bit of feedback from the Perl dbi-users list:
> 1) Transactions: My current approach was to do this inside a
> transaction, but apparently the write-ahead-logging will not handle
> 250k logged inserts well.
> Is this true, and I should commit after every 20 or so spots?
> 2) Indices: apparently every insert updates the indices on the
> table. From my reading of the documentation, the indices aren't
> updated inside a transaction, but instead at the end.
> 3) COPY: I could use COPY, but apparently triggers are not, well,
> triggered under COPY.
I dunno who the heck you got that advice from, but *none* of those
statements are correct, at least not in recent PG releases.
You definitely want to do lots of inserts per transaction. There's
probably not much further improvement to be had beyond a thousand or so
rows per transaction, though.
COPY is faster than a series of INSERTs because you bypass the
parsing and planning overhead needed for each insert.
But my suspicion is that the cycles are actually going into your
triggers. What triggers have you got on this table, and what are they
doing?
regards, tom lane
Hey Tom,
"Tom Lane" <tgl@sss.pgh.pa.us> writes:
> I dunno who the heck you got that advice from, but *none* of those
> statements are correct, at least not in recent PG releases.
Well, that's refreshing.
> You definitely want to do lots of inserts per transaction. There's
> probably not much further improvement to be had beyond a thousand or so
> rows per transaction, though.
OK, that's good, too.
> COPY is faster than a series of INSERTs because you bypass the
> parsing and planning overhead needed for each insert.
Ok.
> But my suspicion is that the cycles are actually going into your
> triggers. What triggers have you got on this table, and what are they
> doing?
Just one trigger, it tracks who inserted the row and when, and adds
this as a row to the Audit table. I keep an audit trail, so that if
it's an update (which it isn't in the case I'm writing about) I chain
the most recent audit to the previous one. I'm including the code at
the end.
If there's a better way to do this, I'd happily remove the trigger.
Thanks,
jas.
--
CREATE OR REPLACE FUNCTION audit () RETURNS OPAQUE AS ' DECLARE ts TIMESTAMP := ''now''; new_audit
INT4:= nextval(''"GENEX_ID_SEQ"''::text); BEGIN IF TG_OP = ''INSERT'' THEN INSERT INTO Audit_view
(audit_pk, modification_date, modified_by) VALUES (new_audit, ts, user()); ELSE
/*for UPDATE we keep a trail of audits */ INSERT INTO Audit_view
(audit_pk,audit_fk,modification_date,modified_by) VALUES (new_audit,OLD.audit_fk,ts,user());
ENDIF; UPDATE tableadmin SET audit_fk = new_audit WHERE UPPER(table_name) =
UPPER(text(TG_RELNAME)); NEW.audit_fk := new_audit; RETURN NEW; END; ' LANGUAGE 'plpgsql';
Hey Tom,
Thanks for the advice. You've already helped me see some major
weakness in my design. I've only been working with DB's for a couple
of years, and I'm self taught, so I have a lot to learn about good
design.
Also, it seems that your black list doesn't like qwest.net (my ISP) so
all my direct emails bounce...
<more comments inline>
"Tom Lane" <tgl@sss.pgh.pa.us> writes:
> "Jason E. Stewart" <jason@openinformatics.com> writes:
> > "Tom Lane" <tgl@sss.pgh.pa.us> writes:
> >> But my suspicion is that the cycles are actually going into your
> >> triggers. What triggers have you got on this table, and what are they
> >> doing?
>
> > CREATE OR REPLACE FUNCTION audit () RETURNS OPAQUE AS '
> > DECLARE
> > ts TIMESTAMP := ''now'';
> > new_audit INT4 := nextval(''"GENEX_ID_SEQ"''::text);
> > BEGIN
> > IF TG_OP = ''INSERT'' THEN
> > INSERT INTO Audit_view (audit_pk, modification_date, modified_by)
> > VALUES (new_audit, ts, user());
> > ELSE
> > /* for UPDATE we keep a trail of audits */
> > INSERT INTO Audit_view (audit_pk,audit_fk,modification_date,modified_by)
> > VALUES (new_audit,OLD.audit_fk,ts,user());
> > END IF;
>
> These inserts are probably fine.
>
> > UPDATE tableadmin SET audit_fk = new_audit
> > WHERE UPPER(table_name) = UPPER(text(TG_RELNAME));
>
> This seems fishy though. In the first place, why the UPPER() calls?
> TG_RELNAME is already in the correct case; AFAICS the only thing the
> UPPER() will do for you is create confusion if there are both "Foo"
> and "foo" tables. The UPPER() calls also prevent use of indexes.
> Make it
>
> UPDATE tableadmin SET audit_fk = new_audit
> WHERE table_name = text(TG_RELNAME);
>
> and make sure you have an index on tableadmin.table_name.
Tableadmin has a single row for every table in the DB (~50), and yes
it has an index.
I remember having to add the UPPER because I store the tables in
table_name in mixed case, but Postgres loses all case information
unless the table names are surrounded by double quotes in the CREATE
TABLE script. So text(TG_RELNAME) is all lowercase.
So I suppose I could store the name in all lowercase...
> BTW, a secondary possibility for performance problems is that either
> Audit_view or tableadmin might have triggers (such as foreign key
> enforcement triggers) that are slowing things down.
TableAdmin has a fkey constraint on its audit_fk column - I could drop
that since it's always handled by a trigger - the audit table is
pretty huge (since it logs every modification to every table) so
searching it is probably ugly.
Audit may be stupidly designed
Table "audit" Column | Type |
Modifiers
-------------------+-----------------------------+--------------------------------------------------audit_pk |
bigint | not null default nextval('"GENEX_ID_SEQ"'::text)audit_fk | integer
| modification_date | timestamp without time zone | not nullmodified_by | name |
Primary key: audit_pkey
Triggers: RI_ConstraintTrigger_2654857, RI_ConstraintTrigger_2654859,
[snip 30 other constraints]
RI_ConstraintTrigger_2655571, RI_ConstraintTrigger_2655573
It has an fkey constraint on the audit_fk (which can be removed by the
same logic as TableAdmin), it has a primary key constraint which be
removed by the same logic as can the default value.
Are all the RI_ConstraintTrigger_XXXXX triggers on _other_ tables that
affect this table in some way??? Because Audit shouldn't have any.
Thanks,
jas.
"Jason E. Stewart" <jason@openinformatics.com> writes:
> Are all the RI_ConstraintTrigger_XXXXX triggers on _other_ tables that
> affect this table in some way??? Because Audit shouldn't have any.
You should check, but I think those are all going to be ON UPDATE or ON
DELETE triggers, if they all appear to be in pairs. You probably have a
bunch of FK's referencing Audit from other tables? If so, they are not
hurting your insert performance. But an update or delete in Audit would
be expensive.
(BTW, I'm having a hard time visualizing why an audit-trail table would
be referenced by FKs from other tables.)
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > "Jason E. Stewart" <jason@openinformatics.com> writes: > > Are all the RI_ConstraintTrigger_XXXXX triggers on _other_ tables that > > affect this table in some way??? Because Audit shouldn't have any. > > You should check, but I think those are all going to be ON UPDATE or ON > DELETE triggers, if they all appear to be in pairs. You probably have a > bunch of FK's referencing Audit from other tables? If so, they are not > hurting your insert performance. But an update or delete in Audit would > be expensive. Right, I removed all the fkey constraints and they all disappeared. The audit table is never updated, only inserted into. BTW, is there some other way to have all rows in a table automatically timestamped? That would satisfy 99% of what I need. Maintaining the trail of audits could then be done for those rare cases where data is updated. > (BTW, I'm having a hard time visualizing why an audit-trail table would > be referenced by FKs from other tables.) Maybe it's a stupid design, but each table that needs on audit trail on it's rows has an audit_fk foreign key column. When I insert a row into that table, the trigger creates a new audit in the audit table and sets the audit_fk for the table. I suppose I could do it the other way around, put the fkey column in the Audit table, and either reference that rows oid, or the pkey value of the table entry (all the pkey values are drawn from the same sequence so they are unique across tables). But that would mean searching the Audit table to find a given entry, I can make an index for that column which would speed it up, but it would never be as fast as holding the audit_fk in the other table. Is there some advantage I don't see to having the column in the Audit table? Actually searching the Audit table is a very rare occurrence (only needed if somebody messed up some data and you want to find the culprit). Cheers, jas.
Bruce Momjian wrote: >What exactly was the change? As I remember, it was something we didn't >think would affect anybody, but it did affect you. > Here's the comment from the code (in the CursorResponseMessage class) I patched .... "Due to a change in the fontend-backend protocol version 0 2 0 0, I must check to see if another CursorResponseMessage follows right after the one that led to my creation. If there is, I take the last one as being the 'correct' one" Note: the "I" and "my" in the comment refer to a CursorResponseMessage instance. Also, there was a discussion on this list between Tom and I about the change and the direction it implied. The gist as I recall centered around the question of whether every side-effect of a query should return a distinct CursorResponseMessage in addition to the one for the explicitly invoked query.
"Jason E. Stewart" <jason@openinformatics.com> writes:
> BTW, is there some other way to have all rows in a table automatically
> timestamped? That would satisfy 99% of what I need.
Sure; set up a BEFORE INSERT OR UPDATE trigger to assign to a timestamp
column.
regards, tom lane