Hacking PG-fe protocol (long)

Hacking PG-fe protocol (long)

Post by Bob Smit » Fri, 06 Dec 2002 11:22:56



This note describes what I'm doing and why, and asks a couple
of simple questions.

WHY:
    The fundamental problem of Linux/Unix on a network server
is that you need to be root to edit a config file in /etc and
restart a service to make the changes take effect.  For the
most part real-time status and statistics are just not
available.  (_clearly_ just my opinion)

WHAT:
    At Fourelle we solved this problem by building our app
with a "DB-like" interface.  We recognized that most of our
data was arrays of structures which seemed to look like DB
tables.  We wrote an interface which let us view and edit the
contents of the tables directly.  The interface was a TCP
connection which accepted a (very) small subset of SQL.  All
of our structures which had config, status, and stats were now
data base tables.
    This was great for several reasons.  The web interface
had the same view of the system as the command-line interface
and as the SNMP interface.  The data base view simplified UI
design and coding.   Also, the developers migrated to putting
most of their internal tables into the DB in order to make
debugging easier.  We wrote a web interface which would let us
view and edit any table in the system.
     As an example, consider an "ethers" table:
 ___name___  ____address____  ____netmask____  
 lo          127.0.0.1        255.0.0.0
 eth0        192.168.1.11     255.255.255.0
 eth1        199.26.153.10    255.255.255.0

One of the user interface programs could change the IP address
of the box with the command:
  UPDATE ethers SET address =192.168.1.22 WHERE name = 'eth0'
I hope you get the idea of what we did from this example.

POSTGRESQL:
     One problem we had with this approach was that we needed
to write our own code in the UI program to handle the protocol.
We wrote it for C and Java, but not for PHP, TCL, or Perl.
I would like to switch to using the Postgres protocol in order
to use the PG interfaces in PHP, C, Perl,... hence my questions.

QUESTIONS:
 - Any comments on our DB view for config, status, stats
 - Any comments on the use of the Postgres protocol
 - The protocol uses a "T" to start a "Row Description" packet.
   The developer's docs give the packet layout but does not
   describe "type modifier".  Where is this documented?
 - What problems do you think I should expect?

thanks.
Bob Smith

 
 
 

1. Roadmap for FE/BE protocol redesign

How about a bitmap field of "additional features" supported by
client/backend that can be negotiated? (Doesn't have to be bitmap, of
course, but the idea..) That way the server (or client - doesn't matter
who goes first, I guess) could say "I support X and Y, but not Z and Q".
If the client supports both X and Y, both are enabled. If it supports
only X, then only X is enabled.

X and Y? Well, the first thing that comes to mind is SSL support. I'm
not sure if it's still that way, but at least it used to be a pretty
ugly kludge there with the connection being dropped and re-connected in
some cases. I also seem to recall there was some talk about on-wire
compression support - that would also be ideal for something like this.
I'm sure there can be more...

It should be easy to make it version-independent - just make both client
and server reject any "protocol features" that are unknown.

These are features that can be enabled/disabled in a backend/client of
the same version. It cannot (at least not easily) be handled with
protocol versioning, since you can have for example 7.4 with or without
SSL.

//Magnus

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

2. CVsup file

3. how to reference a database name in stored procedure

4. Truncating the Log File

5. 7.4 vs 8.0 WAS Roadmap for FE/BE protocol redesign

6. Manual order

7. Note about upcoming instability in FE/BE protocol

8. Beta Schedule (was Re: Roadmap for FE/BE protocol redesign)

9. Transform groups (more FE/BE protocol issues)

10. First draft of new FE/BE protocol spec posted for comments

11. problem with fe/be protocol and large objects